Lost on how to make setFormula on calling cell work

Question

This is my apps script function which is essentially trying to reverse sheets' FORMULATEXT:

function textFormula(formula) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  cell.setFormula(formula);
}

This is how I'm calling it in a cell in Sheets:

And this is the error I'm getting:

From what I gathered, I should be able to use my custom function to affect the calling cell? Idk, lost.

Is this an [XY problem](https://meta.stackexchange.com/a/233676)? — doubleunary, Jun 12 '22 at 06:42
@doubleunary Yes, maybe. X = taking a formula and replacing all the cell references with a different column. — Bmr, Jun 13 '22 at 02:19

score 0 · Accepted Answer · answered Jun 12 '22 at 06:42

0

The only way a custom function can change the values in the spreadsheet is by returning values. You cannot use any of the various .set methods in a custom function, and thus cannot modify formulas.

answered Jun 12 '22 at 06:42

doubleunary

13,842
3
18
51

Lost on how to make setFormula on calling cell work

1 Answers1