Parse #Error When Adding Formula with Javascript

Question

I am having a really weird phenomenon happen in Google Sheets, and I've found nothing on this issue in my research. I'm adding a query formula with javascript and getting a #Error on the sheet. After quadruple checking I wasn't messing up the formula string (the first and most obvious thing to cause such an error) and satisfying myself that the formula was correct for syntax, I cut the formula from the formula bar, hit enter to make the cell blank, then pasted the formula I just cut back in, and boom. Correct result, no error. No change to the formula whatsoever.

So is the a bug with the script editor/sheet interaction? Is a bug in Sheets? Am I doing something wrong (can't imagine what)?

I even followed another post's suggestion and replaced the comma argument separator with a semicolon, but no change in behavior. I have pasted the relevant code here, but I have also shared a Google sheet with the link below that demonstrates the issue.

To reproduce, follow these steps:

Go to the sheet via the link below.
Open Script Editor, and run the function "AddFormula"
When the script runs it will add the formula shown below and you will see a #Error (parse error).
Cut the formula from the formula bar on the sheet and press enter, creating the blank cell.
Copy the formula you just cut back into the cell. Here you will see the correct result of the formula displayed instead of the #Error.

For reference, the relevant code is here:

var wbID1 = "1tujKM_cAePTjBVS6q-gzGheSOAVz68vI0yi_LigCvyw";
var wb = SpreadsheetApp.openById(wbID1);  //The entire workbook.
var wsT = SpreadsheetApp.openById(wbID1).getSheetByName("TData");   //T=Target Worksheet

function AddFormula() {
  wsT.clear();
  wsT.getRange(2,1).setValue("AL");
  var TargetRange = wsT.getRange(2,1).getValue();
  var QueryString = "=QUERY(States!A2:E; \"Select D where B = '" + TargetRange + "'\")";
  wsT.getRange(2,3).setFormulaR1C1(QueryString);
}

The constructed formula in question resolves to:

=QUERY(States!A2:E, "Select D where B = 'AL'")

A demo sheet is here that reproduces the issue: Demo Sheet

It would be great if there was a solution to this issue, but if that is too much to ask, I would love to simply be affirmed that I'm not crazy! Thanks all!

Ron M Ron M · Accepted Answer · 2021-02-11T20:20:12

Use Range.setFormula(formula), since your formula uses an A1 notation States!A2:E

If you use Range.setFormulaR1C1(formula), the given formula must be in R1C1 notation.

Example: =SUM(R[-3]C[0]:R[-1]C[0])

Your Code:

function AddFormula() {
  wsT.clear();
  wsT.getRange(2,1).setValue("AL");
  var TargetRange = wsT.getRange(2,1).getValue();
  var QueryString = "=QUERY(States!A2:E; \"Select D where B = '" + TargetRange + "'\")";
  wsT.getRange(2,3).setFormula(QueryString);
}

Output:

Parse #Error When Adding Formula with Javascript

1 Answers