如何为Google Sheets新插入行的单元格自动添加公式？

一、现有触发方案失效的原因 #

当onChange触发器以INSERT_ROW类型触发时，脚本运行在Google的服务器端，没有用户交互的上下文，此时调用getActiveRange()无法获取到用户插入后高亮的新行（该高亮状态属于客户端操作上下文，服务器端脚本无法访问），导致activeRange为空或指向错误单元格，后续设置公式的逻辑自然失效。

而手动运行脚本时，是在用户当前的操作上下文里执行，getActiveRange()能正确拿到高亮的新行，所以可以正常工作。

二、更优解决方案 #

方案1：使用数组公式（无需脚本，推荐） #

直接在H列表头下方的单元格（比如H2）输入数组公式，自动为所有行（包括新插入的行）生成求和公式，完全不需要脚本：

=ARRAYFORMULA(IF(ROW(A:A)=1,"总时长",IF(TRIM(A:A)="","",SUMIF(ROW(J:J),ROW(J:J),J:J))))

• 逻辑说明：
  • ROW(A:A)=1：判断是否为表头行，显示“总时长”
  • TRIM(A:A)=""：如果A列（项目名称列）为空，H列也留空
  • SUMIF(ROW(J:J),ROW(J:J),J:J)：对当前行的J列及之后所有单元格求和，和原公式SUM(Jx:x)效果一致

方案2：修改脚本（保留触发逻辑） #

如果必须使用脚本，修改触发后的逻辑，不再依赖getActiveRange()，改为直接定位需要设置公式的行：

function initializeTrigger(){
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();
}

function myFunction(e){
  if(e.changeType !== 'INSERT_ROW') return;
  
  const sheet = e.source.getSheetByName('Sheet1');
  const lastRow = sheet.getLastRow();
  // 获取H列从第2行到最后一行的范围（假设第1行是表头）
  const targetColRange = sheet.getRange(2, 8, lastRow - 1);
  const targetValues = targetColRange.getValues();
  
  // 遍历所有H列单元格，为空的则设置求和公式
  targetValues.forEach((value, index) => {
    const currentRow = index + 2;
    if(value[0] === ''){
      sheet.getRange(currentRow, 8).setFormula(`=SUM(J${currentRow}:${currentRow})`);
    }
  });
  
  Browser.msgBox('Formula automatically added!');
}

• 改进点：
  • 直接通过e.source获取触发的表格，避免依赖全局的getActiveSpreadsheet()
  • 遍历H列的空单元格，为其设置公式，不依赖客户端的活动范围
  • 逻辑更鲁棒，无论新行插在中间还是末尾都能生效

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