如何让Google Apps Script的onEdit/onChange函数适配Query动态数据

公式动态生成的行不会触发onEdit或onChange触发器（这两个仅响应用户手动编辑操作），可通过以下两种方式实现需求：

方案一：在数据源Sheet A中触发操作（推荐） #

既然Sheet B的数据来自Sheet A的新增，直接在Sheet A的onEdit触发器中同步处理Sheet B的H列，能实时响应新增行，无需等待定时任务。

实现步骤： #

1. 打开Sheet A的脚本编辑器（点击「扩展程序」→「Apps Script」）
2. 替换原有脚本为以下代码：

function onEdit(e) {
  const sourceSheet = e.source.getActiveSheet();
  // 仅当编辑的是数据源表（Sheet1）且为有效数据行时触发
  if (sourceSheet.getName() !== "Sheet1") return;
  
  const editedRow = e.range.getRow();
  // 跳过表头行（假设表头在第1行）和空行
  if (editedRow < 2 || sourceSheet.getRange(editedRow, 1).getValue() === "") return;

  // 打开Sheet B所在表格
  const targetSpreadsheet = SpreadsheetApp.openById("Sheet B的表格ID"); // 替换为Sheet B的ID
  const targetSheet = targetSpreadsheet.getSheetByName("Sheet B的表名"); // 替换为Sheet B的表名

  // 获取Sheet B当前有效数据行数（Query返回的行数）
  const targetDataRows = targetSheet.getRange("A:A").getValues().filter(String).length;
  // 定位到对应新增行的H列单元格
  const targetCell = targetSheet.getRange(targetDataRows, 8);
  
  // 随机选择下拉列表值（与原脚本逻辑一致）
  const dropdownValues = targetCell.getDataValidation().getCriteriaValues()[0];
  targetCell.setValue(dropdownValues[Math.floor(Math.random() * dropdownValues.length)]);
}

3. 保存脚本后测试：在Sheet A的Sheet1中新增一行数据，等待Sheet B同步后，检查对应行H列是否已自动设置随机下拉值。

方案二：为Sheet B设置定时触发器 #

若无法修改Sheet A的脚本，可给Sheet B设置定时触发器，定期检查新增行并处理H列。

实现步骤： #

1. 打开Sheet B的脚本编辑器
2. 添加以下脚本：

function processNewRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const hColumn = sheet.getRange(1, 8, values.length, 1).getValues();
  
  // 遍历所有行，处理H列为空且A-G列有数据的行
  for (let i = 1; i < values.length; i++) { // 跳过表头行（假设表头在第1行）
    const row = i + 1;
    // 判断是否为Query新增的有效行且未处理过H列
    if (values[i][0] !== "" && hColumn[i][0] === "") {
      const targetCell = sheet.getRange(row, 8);
      const dropdownValues = targetCell.getDataValidation().getCriteriaValues()[0];
      targetCell.setValue(dropdownValues[Math.floor(Math.random() * dropdownValues.length)]);
    }
  }
}

3. 设置定时触发器：
   • 在脚本编辑器左侧点击「触发器」图标
   • 点击「添加触发器」
   • 选择processNewRows函数，事件源选「时间驱动」，根据数据更新频率设置执行周期（如每分钟、每5分钟一次）

注意事项： #

• 方案一需确保Sheet A和Sheet B已完成IMPORTRANGE的授权验证
• 方案二的定时频率可按需调整，避免不必要的资源消耗

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