实现Google Sheets onChange触发器对所有工作表新增行生效

我在Google Sheets中使用Query+IMPORTRANGE从其他表格导入数据，A2单元格公式为：
=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/XXXXXX","Sheet1!A2:G"),"select *",0)
表格的H列为下拉列表类型，需要实现新增行时自动将该行H列的下拉值设置为上一行H列值的下一个选项（如上一行是Alex，新行则为Amek）。

我通过onChange触发器触发以下脚本（无法使用onEdit触发器），但它仅在Sheet1中生效，需要让脚本对表格中所有工作表（如Sheet2、Sheet3等）的新增行都生效。原脚本如下：

function myFunction(e) {
  const ssn = e.source.getActiveSheet().getName();
  const ss = SpreadsheetApp.getActive().getSheetByName(ssn);

  //ss.getRange("I2").setValue(ssn);

  var rg = ss.getRange(2, 8, ss.getLastRow() - 1);
  var vl = rg.getValues();
  var dv = rg.getDataValidation().getCriteriaValues()[0];

  // Find the last non-empty cell value in column 8
  var lastCellValue = null;
  for (var i = vl.length - 1; i >= 0; i--) {
    if (vl[i][0] !== '') {
      lastCellValue = vl[i][0];
      break;
    }
  }

  // If there's no lastCellValue found, start from -1
  var lastIndex = lastCellValue !== null ? dv.indexOf(lastCellValue) : -1;

  vl.forEach((r, i) => {
    var c = rg.getCell(i + 1, 1);
    if (c.isBlank()) {
      // Calculate the new index by adding 1 to the last index
      var newIndex = (lastIndex + 1) % dv.length; // Use modulo to wrap around if necessary
      // Set the new value in the current cell
      c.setValue(dv[newIndex]);
      // Update lastIndex for the next iteration
      lastIndex = newIndex;
    }
  });
}

修改后的脚本（支持所有工作表） #

function myFunction(e) {
  // 仅响应新增行的变更事件
  if (e.changeType !== 'INSERT_ROW') return;

  // 获取触发事件的目标工作表
  const activeSheet = e.source.getActiveSheet();
  
  // 获取H列的数据验证规则，无规则则终止执行
  const dvRule = activeSheet.getRange('H2').getDataValidation();
  if (!dvRule) return;
  const dvOptions = dvRule.getCriteriaValues()[0];
  if (!dvOptions.length) return;

  // 获取H列从第2行开始的所有单元格值
  const lastRow = activeSheet.getLastRow();
  const hRange = activeSheet.getRange(2, 8, lastRow - 1);
  const hValues = hRange.getValues().flat();

  // 查找H列最后一个非空值
  let lastCellValue = null;
  for (let i = hValues.length - 1; i >= 0; i--) {
    if (hValues[i] !== '') {
      lastCellValue = hValues[i];
      break;
    }
  }

  // 计算起始索引，无初始值则从-1开始（第一个选项对应索引0）
  let lastIndex = lastCellValue !== null ? dvOptions.indexOf(lastCellValue) : -1;

  // 遍历H列空白单元格，依次填充下一个下拉选项
  hValues.forEach((value, index) => {
    if (!value) {
      const newIndex = (lastIndex + 1) % dvOptions.length;
      const targetCell = activeSheet.getRange(index + 2, 8);
      targetCell.setValue(dvOptions[newIndex]);
      lastIndex = newIndex;
    }
  });
}

关键修改说明 #

• 新增事件过滤：仅当变更类型为INSERT_ROW时执行逻辑，避免无关操作触发脚本
• 直接获取触发工作表：通过e.source.getActiveSheet()直接拿到新增行所在的工作表，无需额外通过名称查找，适配所有工作表
• 增加安全判断：检查H列是否存在数据验证规则，避免无规则时脚本报错
• 优化数组处理：用flat()将二维数组转为一维，简化值的遍历逻辑

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