Google Sheets Script =SyntaxError: missing ) after argument list (line 19, file "Reset.gs")

[yvettew78]

Can anyone please help me with the following error.


var cell = sheet.getRange("E44");
cell.setFormula("=IF(AND(E3="Cash",E4="Residential",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,2,FALSE),IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,2,FALSE),IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,3,FALSE),IF(AND(E3="Cash",E4="Commercial",E5="three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,4,FALSE),IF(AND(E3="Finance",E4="Residential",E5="Single"),VLOOKUP(C10,'RRP Pricing'!A2:G58,5,FALSE),IF(AND(E3="Finance",E4="Residential",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,6,FALSE),IF(AND(E3="Finance",E4="Commercial",E5="Three"),VLOOKUP(C10,'RRP Pricing'!A2:G58,7,FALSE),0)');

For some reason it comes up with an error when I use it in the script I am creating,

 

[Norie]

You start with a double quote but end with a single quote, try enclosing the entire formula in single quotes.

Also, you need to escape the single quotes around the sheet name.

This code will work but there still appears to be a problem with the formula and I can't quite figure out what.

  var cell = sheet.getRange("E44");
  cell.setFormula('=IF(AND(E3="Cash",E4="Residential",E5="Single"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,2,FALSE),' +
                  'IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,2,FALSE),' +
                  'IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,3,FALSE),' +
                  'IF(AND(E3="Cash",E4="Commercial",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,4,FALSE),' +
                  'IF(AND(E3="Finance",E4="Residential",E5="Single"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,5,FALSE),' + 
                  'IF(AND(E3="Finance",E4="Residential",E5="Three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,6,FALSE),' +
                  'IF(AND(E3="Finance",E4="Commercial",E5="Three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,7,FALSE),0)');

 

[yvettew78]

You start with a double quote but end with a single quote, try enclosing the entire formula in single quotes.

Also, you need to escape the single quotes around the sheet name.

This code will work but there still appears to be a problem with the formula and I can't quite figure out what.

  var cell = sheet.getRange("E44");
  cell.setFormula('=IF(AND(E3="Cash",E4="Residential",E5="Single"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,2,FALSE),' +
                  'IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,2,FALSE),' +
                  'IF(AND(E3="Cash",E4="Residential",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,3,FALSE),' +
                  'IF(AND(E3="Cash",E4="Commercial",E5="three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,4,FALSE),' +
                  'IF(AND(E3="Finance",E4="Residential",E5="Single"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,5,FALSE),' +
                  'IF(AND(E3="Finance",E4="Residential",E5="Three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,6,FALSE),' +
                  'IF(AND(E3="Finance",E4="Commercial",E5="Three"),VLOOKUP(C10,\'RRP Pricing\'!A2:G58,7,FALSE),0)');

Thank you for your reply.

The code worked but when I run the script Cell E44 comes up with a formulas parse error but if I double click on the cell and press enter, it fixes itself and the error disappears. Any idea why this is happening?