Call formula, NOT value, from another cell, and execute formula in new cell

Question

I am trying to find a way to call the formula used in B3 (currently a simple AVERAGE formula) into G3 and use it there. The green cells are static values used for testing. If I do "=B3" in G3 I will get the value from B3 which is not what I want. I want the average of F3 and H3. Basically like a long range, updatable copy/paste or FormulaArray. If that makes sense. As the sheet I will be implementing this on will have many instances of formulas I would like to be able to alter them in one place and have the update go through automatically rather than needing to copy/paste. I will also be calling the formulas from another document. So I would like a method by which I could change B3 to a MULTIPLY formula and it would automatically apply that change to the cell in the G column, referencing the relative cells of course. Is this even possible?

I did find a question asking something quite similar to this:

How do you get the formula from a cell instead of the value?

But of the two workable solutions, Ruben's CELLFORMULA managed to pull the function in as a string (Like in G5), which is close! And Luiz's did not work for me at all, though it did seem like he was attempting to do what I am wanting. I fiddled with it but I couldn't get it to work no matter what I did. None of the other suggestions worked at all.

Here is a test sheet I made to illustrate what I'm looking for:

Call Formula Test Sheet

Additional:

As I said, Ruben's code worked perfectly as it seems it was intended, and Luiz stated that he made some adjustments of his own in order to get the formula to execute in the new cell. But copying and pasting Luiz's code yielded only errors and attempting to tweak it myself was a lost cause as I am only recently getting into scripting and coding in general. I just did my best to compare and contrast Ruben's original code with Luiz's altered code and spot any differences that may have been mistakes. Something like this:

function CELLFORMULA(reference) {
  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet();
  var formula = ss.getActiveRange().getFormula();
  var re = /cellformula\((.*)\);/g;
  var args = re.exec(formula);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range.getFormula();
}

It seemed like maybe Luiz wasn't calling out variables as Ruben had? I'm not sure. But the fundamental difference between the two codes seems to be:

var args = formula.match(/=\w+\((.*)\)/i);

in Ruben's vs what's found in Luiz's (unaltered):

 re = /cellformula\((.*)\);/g;
  args = re.exec(formula);

It seems to me that the re.exec(formula) was Luiz's piece of code that should result in executing the formula rather than just putting it in the cell as a string, but I could certainly be wrong.

As I said, I am new to this and the "(/=\w+((.))/i)" and "/cellformula((.));/g" parts are completely over my head. I tried to search for that syntax to understand it better but had no luck.

Answer 1

As I said, I am new to this and the "(/=\w+((.))/i)" and "/cellformula((.));/g" parts are completely over my head. I tried to search for that syntax to understand it better but had no luck.

They are regular expressions.

---

To be able to replace a cell formula by another formula, a custom function can't be used but this could be done by an installable on edit function.

For simplicity, the following code will be very specific: if formula on B3 is changed then the formula on G7 will be updated.

function updateFormula(e) {
  var formula = e.range.getFormulaR1C1();
  if(e.range.rowStart == 3 && e.range.columnStart == 2 && formula != ""){
    var sheet = e.range.getSheet();
    var target = sheet.getRange('G7');
    target.setFormulaR1C1(formula);
  }
}

Remember to create an installable on edit trigger that calls the above function and if you want to test it from the script editor checkout How can I test a trigger function in GAS?