Google script .setFormula() only working with explicit string

Question

In Google sheets, I get a "formula parse error" in the cell this code edits:

var A1one = 'C4';
var A1two = 'B4';
var A1three = 'B4';
var wholeFormula = '=if(today()<D$1,,' + A1one + '+if(isNumber(' + A1two + '),' + A1three + ',0)';
var wholeFormulaA1 = '=if(today()<D$1,,C4+if(isNumber(B4),B4,0))';
trackCell1.setFormula(wholeFormula);

But if I change the code as follows it works fine (only change is in last line):

var A1one = 'C4';
var A1two = 'B4';
var A1three = 'B4';
var wholeFormula = '=if(today()<D$1,,' + A1one + '+if(isNumber(' + A1two + '),' + A1three + ',0)';
var wholeFormulaA1 = '=if(today()<D$1,,C4+if(isNumber(B4),B4,0))';
trackCell1.setFormula(wholeFormulaA1);

wholeFormula and wholeFormulaA1 should be identical. Why does Google Sheets treat them differently?

I assume this is some kind of recalculation error. If I run the first code example above, and the cell displays #ERROR!, I can fix it by double clicking the cell and hitting ENTER on the keyboard. This forces the sheet to recalculate (I guess?), and the proper result of the formula is displayed in the cell.

But how do I get Google Sheets to update the cell automatically when using the first code example?

Answer 1

I guess you missed ) at the end, try:

var try = '=if(today()<D$1,,' + A1one + '+if(isNumber(' + A1two + '),' + A1three + ',0))';

---

To force script recalculate the formula, use SpreadsheetApp.flush();

I can fix it by double clicking the cell and hitting ENTER on the keyboard. This forces the sheet to recalculate (I guess?), and the proper result of the formula is displayed in the cell.

I think when you reenter the formula manually, it checks if ) is missed and repairs this automatically. This won't happen when you enter the formula by script.