Google Scripts Generated VLOOKUP Formula Does not Calculate Properly

Question

I am writing formulas to cells programmatically.

The following line of code: formulaCells.setFormulaR1C1('=iferror(VLOOKUP(R[0]C[3],AutoCat!A:B,2,FALSE),"Requires Category")');

Adequately writes this formula into all the cells in the target Google Sheets file represented by formulaCells

=iferror(VLOOKUP(D2,AutoCat!A:B,2,FALSE),"Requires Category")

But the problem is that the formula defaults to the error flag "Requires Category" when it is written by Google Scripts, but if I write the very same formula manually into Google Sheets, the actual item identified by the VLOOKUP results.

This is so frustrating!

If I hover over the Google Script generated formula, the true solution from the VLOOKUP even appears in the flyover, but for some reason, does not appear in the cell.

Please help! Why will a Google Scripts generated formula not calculate properly wen the same formula entered manually will?

Answer 1

OK, after thinking I'd exhausted my mental resources on this, I tried one last thing, and it actually worked.

For some unknown reason, though the formula is accurate, Google Scripts generated formulas do not play well with a VLOOKUP which searches an unlimited range for a solution. So even though the VLOOKUP was finding the correct solution, it was not displaying it as the formula result.

I fixed this by creating in the Google Sheets file a named range of the data the VLOOKUP would search called AutoCategory, and then inserted that named range into the Google Scripts generated formula, and BOOM! the formula started working.

Here is the final code in Scripts: formulaCells.setFormulaR1C1('=iferror(VLOOKUP(R[0]C[3],AutoCategory,2,FALSE),"Requires Category")');

And here is the final formula which is generated in Google Sheets: =iferror(VLOOKUP(D2,AutoCategory,2,FALSE),"Requires Category")

Answer 2

setFormulaR1C1 requires R1C1 notation, which you provided with R[0]C[3], but AutoCat!A:B is A1 notation.

You could switch to setFormula() and pass in only A1 notation, but I think that using named ranges is a very good practice when combined with Google Apps Script.