Google Sheets - setFormulaR1C1(formula) doesn't seem to work for VLOOKUP formula

Question

I am hoping to find a way to address this Formula parsing error without have to manually change things each time I need to run the script.

I had hoped that Google Sheets - setFormulaR1C1(formula) doesn't seem to work would resolve my situation, but adding semi-colons fails to make a difference.

I am running a script which adds the following formula to cells in a column that has been added the sheet:

range.setFormulaR1C1('=VLOOKUP((INDIRECT("R[0]C[-28]";false)-INDIRECT("R[0]C[-29]";false));Moon!$B$19:$C$68;2;true)');`

It results in an ERROR# with the message Formula parse error.

However, clicking on formula in the entry field causes the correct expected value to appear above the formula on the left side of the edit box.

I can 'correct' the problem manually by adding an opening and a closing parenthesis to the formula. (I can also then delete those same parentheses and the formula still works with no parsing error.) I can then copy the formula down to the rows below and it works fine. If I attempt to copy the formula before adding (and deleting) the parentheses, it continues to fail.

Exhibits:

1. Displays the ERROR# in column AN.

2. Shows the resolution of the 'Error' to the correct expected value, "Full Moon", when clicking on the formula in the edit box.

3. Shows the Error message adjacent to the cell.

4. Shows the formula modified by adding '(' at the start and ')' at the end of the formula.

5. Shows that the parsing error is gone and the correct expected value, Full Moon, now appears in the cell.

6. Shows that I can remove the opening and closing parentheses that I added and the formula still works with no parsing error.

7. Shows that the parsing error is still gone and the expected value appears.

8. Shows that copying the formula (which is now identical in appearance to the original entry provided by the script) down to other rows produces the expected values with no parsing errors.

Answer 1

Since you are inserting a formula that use R1C1 notation as a text argument of INDIRECT and A1 notation as argument of another function, you should use setFormula instead of setFormulaR1C1.