首頁猿問在 setFormula...

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

JavaScript

慕斯709654 2023-04-20 10:24:00

我有以下腳本，我收到錯誤SyntaxError: missing ) 在參數(shù)列表之后（第 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))'); };};我已經(jīng)確定它在'"&Login!C2&"'周圍有撇號導(dǎo)致公式末尾出現(xiàn)問題，但這是 QUERY 函數(shù)中的一個關(guān)鍵標準。我該如何解決這個錯誤？

查看完整描述

1 回答

holdtom

TA貢獻1805條經(jīng)驗獲得超10個贊

您必須通過在前面加上 a\或使用模板文字而不是文字字符串來轉(zhuǎn)義它們

代替

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

經(jīng)過

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

反對回復(fù) 2023-04-20