Using an apostrophe (') in a formula within a setFormula

Question

I have the following script that I receive the error

SyntaxError: missing ) after argument list (line 9, file "tracker.gs")

function tracker() {
  var spreadsheetIDs = ["myID1","myID2"];
  var i=0,sheet,thisID="";

  for (i=0;i<spreadsheetIDs.length;i+=1) {
    thisID=spreadsheetIDs[i];

    sheet = SpreadsheetApp.openById(thisID).getSheetByName('History');
    sheet.getRange("A2").setFormula('=IF(Login!G3=0,"",QUERY(ARRAYFORMULA({IMPORTRANGE(Core!B1,"History!A1:AC4000");IMPORTRANGE(Core!B1,"History!A4001:AC8000");IMPORTRANGE(Core!B1,"History!A8001:AC12000")}),"Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29 where Col2 ='"&Login!C2&"'",0))'); 
    
  };
};

I have identified that it is having the apostrophes around '"&Login!C2&"' that is causing the issues at the end of the formula, however this is a crucial criteria in the QUERY function.

How can I resolve this error?

Rubén · Accepted Answer · 2020-10-06T19:37:34.003

You have to escape them by prepending a \ or use template literal instead of a literal string

Replace

sheet.getRange("A2").setFormula('=IF(Login!G3=0,"",QUERY(ARRAYFORMULA({IMPORTRANGE(Core!B1,"History!A1:AC4000");IMPORTRANGE(Core!B1,"History!A4001:AC8000");IMPORTRANGE(Core!B1,"History!A8001:AC12000")}),"Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29 where Col2 ='"&Login!C2&"'",0))');

by

sheet.getRange("A2").setFormula('=IF(Login!G3=0,"",QUERY(ARRAYFORMULA({IMPORTRANGE(Core!B1,"History!A1:AC4000");IMPORTRANGE(Core!B1,"History!A4001:AC8000");IMPORTRANGE(Core!B1,"History!A8001:AC12000")}),"Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29 where Col2 =\'"&Login!C2&"\'",0))');

or

sheet.getRange("A2").setFormula(`=IF(Login!G3=0,"",QUERY(ARRAYFORMULA({IMPORTRANGE(Core!B1,"History!A1:AC4000");IMPORTRANGE(Core!B1,"History!A4001:AC8000");IMPORTRANGE(Core!B1,"History!A8001:AC12000")}),"Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29 where Col2 ='"&Login!C2&"'",0))`);

Using an apostrophe (') in a formula within a setFormula

1 Answers1