如何让Google Sheets中ID/ROW变更时触发onMyEdit函数？

核心问题说明 #

Google Sheets的简单onEdit触发器仅响应用户手动编辑单元格的操作，公式自动更新（比如你从KDCAlerts拉取数据导致ID/ROW列变化）不属于手动编辑范畴，因此不会触发原有的onMyEdit函数。需要通过可安装触发器或源头触发的方式解决。

方案一：使用可安装onChange触发器响应KDCLog的公式更新 #

可安装onChange触发器能响应表格的多种变更事件，包括公式计算结果更新，步骤如下：

1. 修改触发器创建函数 #

替换原creatTrigger函数，创建可安装的onChange触发器：

function createOnChangeTrigger() {
  // 检查是否已存在目标触发器
  const existingTriggers = ScriptApp.getProjectTriggers().filter(t => 
    t.getHandlerFunction() === "onKDCLogChange" && t.getEventType() === ScriptApp.EventType.ON_CHANGE
  );
  if (existingTriggers.length === 0) {
    ScriptApp.newTrigger("onKDCLogChange")
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onChange()
      .create();
  }
}

2. 编写变更处理函数 #

创建onKDCLogChange函数，检测KDCLog的ID/ROW列是否有变化，并执行原onMyEdit的核心逻辑：

function onKDCLogChange(e) {
  // 仅响应包含公式更新的"EDIT"类型变更
  if (e.changeType !== "EDIT") return;

  const kdcLogSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("KDCLog");
  if (!kdcLogSheet) return;

  // 定义ID列和ROW列范围（假设ID是A列、ROW是B列，根据实际结构调整）
  const lastRow = kdcLogSheet.getLastRow();
  const idColumn = kdcLogSheet.getRange(`A2:A${lastRow}`);
  const rowColumn = kdcLogSheet.getRange(`B2:B${lastRow}`);
  
  // 用脚本属性存储上一次的ID/ROW哈希值，对比判断是否有更新
  const scriptProps = PropertiesService.getScriptProperties();
  const lastHash = scriptProps.getProperty("KDCLogIDRowHash");
  
  // 计算当前ID/ROW列的哈希值
  const currentValues = idColumn.getValues().concat(rowColumn.getValues());
  const currentHash = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, JSON.stringify(currentValues)));
  
  // 哈希值变化则说明ID/ROW有更新，执行状态处理逻辑
  if (currentHash !== lastHash) {
    scriptProps.setProperty("KDCLogIDRowHash", currentHash);
    processKDCLogStatus(kdcLogSheet);
  }
}

// 抽离原onMyEdit的核心处理逻辑，便于复用
function processKDCLogStatus(sheet) {
  const extSS = SpreadsheetApp.openById("1b5qiNxxxxxxxxxRuLf-8dTBgRU9cHLBbd2A");
  const extSH = extSS.getSheetByName("KDCAlerts");
  
  // 原onMyEdit中处理STATUS列的后续代码放此处
  // ...
  // ...
}

3. 激活触发器 #

手动运行一次createOnChangeTrigger函数，完成权限授权后触发器即可生效。

方案二：在KDCAlerts中添加触发器，从源头触发处理 #

因为KDCLog的ID/ROW变更本质是KDCAlerts的修改导致的，直接在KDCAlerts所在表格添加onEdit触发器，当KDCAlerts变更时主动更新KDCLog的STATUS，效率更高：

1. 在KDCAlerts表格的脚本编辑器中添加代码 #

function createKDCAlertsEditTrigger() {
  const existingTriggers = ScriptApp.getProjectTriggers().filter(t => 
    t.getHandlerFunction() === "onKDCAlertsEdit"
  );
  if (existingTriggers.length === 0) {
    ScriptApp.newTrigger("onKDCAlertsEdit")
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onEdit()
      .create();
  }
}

function onKDCAlertsEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "KDCAlerts") return;
  
  // 仅当KDCAlerts的D、E、H列（对应KDCLog公式引用的列）变更时触发
  const affectedColumns = [4,5,8]; // D=4、E=5、H=8，根据实际列调整
  if (!affectedColumns.includes(e.range.getColumn())) return;
  
  // 打开KDCLog表格并执行状态更新
  const kdcLogSS = SpreadsheetApp.openById("替换为KDCLog表格的实际ID");
  const kdcLogSheet = kdcLogSS.getSheetByName("KDCLog");
  
  processKDCLogStatus(kdcLogSheet);
}

// 和方案一中的处理函数一致，复用状态更新逻辑
function processKDCLogStatus(sheet) {
  const extSS = SpreadsheetApp.openById("1b5qiNxxxxxxxxxRuLf-8dTBgRU9cHLBbd2A");
  const extSH = extSS.getSheetByName("KDCAlerts");
  
  // 原onMyEdit中处理STATUS列的后续代码放此处
  // ...
  // ...
}

2. 激活触发器 #

手动运行createKDCAlertsEditTrigger函数完成授权，后续KDCAlerts的关键列变更时会直接触发KDCLog的STATUS更新。

关键注意事项 #

• 可安装触发器需要用户授权，首次运行时需允许脚本访问表格数据。
• 方案二更高效，直接从变更源头触发，避免了KDCLog端的全量检查；方案一更适合无法修改KDCAlerts脚本的场景。

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