在 setFormula 中的公式中使用撇号 (')

Question

我有以下脚本，我收到错误SyntaxError: missing ) 在参数列表之后（第 9 行，文件“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))');       };};我已经确定它在'"&Login!C2&"'周围有撇号导致公式末尾出现问题，但这是 QUERY 函数中的一个关键标准。我该如何解决这个错误？

Answer 1

您必须通过在前面加上 a\或使用模板文字而不是文字字符串来转义它们

代替

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))');

经过

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))');

或者

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))`);