如何在Google Sheets Apps Script中添加arrayformula至L2:L1000

错误原因 #

代码报错是因为字符串中的双引号未转义。在JavaScript字符串里，若要包含双引号，必须用反斜杠\转义，否则会被识别为字符串的结束标记，引发语法错误。

另外，ARRAYFORMULA的特性是只需放在区域的首个单元格（此处为L2），就会自动应用到下方所有行，无需逐个设置L2到L1000的单元格。

修正后的代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // 选中L2单元格，ARRAYFORMULA会自动填充至下方行
  var cell = sheet.getRange("L2");
  // 用单引号包裹公式字符串，避免转义内部双引号
  cell.setFormula('=arrayformula(if(ISBLANK(A2),"",if(and(M2="PLANNING",N2="TEST ONE"),"N",if(and(ISBLANK(J2),G2+7>today()),"Y",if(K2>G2+7,"Y","N")))))');
}

可选：限制公式作用范围到L1000 #

如果需要严格限定公式只处理到第1000行，可以修改公式中的引用范围，让ARRAYFORMULA仅作用于指定区间：

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange("L2");
  cell.setFormula('=arrayformula(if(ISBLANK(A2:A1000),"",if(and(M2:M1000="PLANNING",N2:N1000="TEST ONE"),"N",if(and(ISBLANK(J2:J1000),G2:G1000+7>today()),"Y",if(K2:K1000>G2:G1000+7,"Y","N")))))');
}

内容的提问来源于stack exchange，提问作者Van Latte