CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Simon Davies
Simon Davies

Reputation: 11

Formula entry to cell via App Script in Sheets

I am trying to set up this formula in a cell and it wont let me save. I can't figure out how to get this into a cell

quotelist.getRange("R" + rowNumberForQuote).setFormula("=if(R"&rowNumberForQuote&"="New Quote","New Quote",if(isna(VLOOKUP(B"&rowNumberForQuote&",'Form responses 4'!B:C,2,false))=True,"Sent To Customer",(VLOOKUP(B"&rowNumberForQuote&",'Form responses 4'!B:C,2,false))))");

If Cell R Row Number = New Quote then Write "New Quote"
If Not then Vlookup for in B Row Number of Form Reponses 4
If Vlookup is #N/A then Write "Sent to Customer"
if Vlookup finds then display answer

Upvotes: 0

Views: 52

Answers (1)

Wicket
Wicket

Reputation: 38131

Quick fix: Replace the formula (the parameter of setFormula):

"=if(R"&rowNumberForQuote&"="New Quote","New Quote",if(isna(VLOOKUP(B"&rowNumberForQuote&",'Form responses 4'!B:C,2,false))=True,"Sent To Customer",(VLOOKUP(B"&rowNumberForQuote&",'Form responses 4'!B:C,2,false))))"

by

`=if(R${rowNumberForQuote}="New Quote","New Quote",if(isna(VLOOKUP(B${rowNumberForQuote},'Form responses 4'!B:C,2,false))=True,"Sent To Customer",(VLOOKUP(B${rowNumberForQuote},'Form responses 4'!B:C,2,false))))`

The original formula has syntax errors as it's using & instead of + and didn't scaped the ". The proposed formula use strings template. IHMO this is better because it's easier easier to read and makes unnecessary to escape simple/double quote characters (', ").

Another option is to replace the & and escape the " that should be escaped, like \"New Quote\" instead of "New Quote" and \"Sent To Customer\" instead of "Sent To Customer".

Upvotes: 1

Related Questions