Apps Script记录Google Sheets中AppSheet修改与复制粘贴的变更

• 团队共享Google Sheets中的Master工作表日均至少2次修改，要求所有变更同步写入Changelog工作表留痕
• 现有实现的能力缺口：
  • 仅通过onEdit事件捕获手动编辑操作
  • 无法覆盖AppSheet写入、多来源跨表复制粘贴触发的变更
  • 需要实现无遗漏的全量变更记录
• 当前运行的脚本（原英文内容已翻译为中文注释，移除所有外链）：

function onEdit(e) {
  addChangelog(e);
}

function addChangelog(e) {
  // 功能：将表格变更记录到"ChangeLog"工作表
  // 记录列字段：时间戳、工作表名、单元格地址、列标题、行标签、新值
  // 版本1.1，编写者--Hyde，2014年7月30日

  // 自定义配置项：需记录变更的工作表列表
  // 列表中的名称支持正则匹配，正则元字符需做转义处理
  // 若要监听所有工作表，可配置为'.+'
  const sheetsToWatch = ['Master', 'TODAY'];
  const changelogSheetName = "ChangeLog";
  const timestamp = new Date();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const cell = sheet.getActiveCell();
  const sheetName = sheet.getName();

  // 跳过Changelog工作表自身的变更记录
  if (sheetName === changelogSheetName) return;

  // 校验当前工作表是否在监听列表内
  let matchFound = false;
  for (let i = 0; i < sheetsToWatch.length; i++) {
    if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
  if (!matchFound) return;

  // 获取当前单元格对应的列标题（第一行取值）、行标签（A列取值）
  const columnLabel = sheet.getRange(1, cell.getColumn()).getValue();
  const rowLabel = sheet.getRange(cell.getRow(), 1).getValue();

  // 若Changelog工作表不存在，则自动创建并初始化表头
  let changelogSheet = ss.getSheetByName(changelogSheetName);
  if (!changelogSheet) {
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
    changelogSheet.appendRow(["时间戳", "工作表名", "单元格地址", "列标题", "行标签", "新值"]);
    changelogSheet.setFrozenRows(1);
  }

  // 追加变更记录
  changelogSheet.appendRow([timestamp, sheetName, cell.getA1Notation(), columnLabel, rowLabel, cell.getValue()]);
}

• onEdit为简单触发事件，仅能捕获用户在表格UI内的手动单元格编辑动作，对AppSheet API写入、脚本批量调用setValue方法、大段复制粘贴的场景，无法稳定触发事件，导致记录遗漏
• 原有逻辑没有对工作表的单元格值做前后状态比对，无法识别非事件触发的变更

• 核心思路：保留原有onEdit的实时手动变更记录能力，补充定时快照比对逻辑，覆盖所有事件触发不到的变更场景
• 具体实现：
  • 在脚本属性中存储上一次快照的全量单元格值
  • 配置时间驱动触发器，按固定频率运行快照比对逻辑，拉取当前监听工作表的全量值，和历史快照做逐单元格对比
  • 对识别到的差异值，统一按原有Changelog字段格式追加记录
  • 比对完成后更新存储的快照值，作为下一次比对的基准
• 补充的快照比对脚本（可直接整合到原有代码中）：

// 快照比对定时触发函数
function runSnapshotDiff() {
  const scriptProps = PropertiesService.getScriptProperties();
  const sheetsToWatch = ['Master', 'TODAY'];
  const changelogSheetName = "ChangeLog";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timestamp = new Date();

  // 遍历所有需要监听的工作表
  sheetsToWatch.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;
    const currentValues = sheet.getDataRange().getValues();
    const propKey = `snapshot_${sheetName}`;
    const lastSnapshot = JSON.parse(scriptProps.getProperty(propKey) || '{}');

    // 逐单元格比对差异
    for (let rowIdx = 0; rowIdx < currentValues.length; rowIdx++) {
      for (let colIdx = 0; colIdx < currentValues[rowIdx].length; colIdx++) {
        const cellAddr = sheet.getRange(rowIdx+1, colIdx+1).getA1Notation();
        const oldVal = lastSnapshot[cellAddr];
        const newVal = currentValues[rowIdx][colIdx];
        // 识别到值变化则写入Changelog
        if (oldVal !== newVal) {
          const columnLabel = sheet.getRange(1, colIdx+1).getValue();
          const rowLabel = sheet.getRange(rowIdx+1, 1).getValue();
          const changelogSheet = ss.getSheetByName(changelogSheetName);
          changelogSheet.appendRow([
            timestamp,
            sheetName,
            cellAddr,
            columnLabel,
            rowLabel,
            newVal
          ]);
        }
      }
    }
    // 更新快照存储
    scriptProps.setProperty(propKey, JSON.stringify(currentValues));
  });
}

• 配置说明：
  • 代码编写完成后，在Google Apps Script界面设置时间驱动触发器，选择runSnapshotDiff函数，触发频率设置为每15分钟一次，即可覆盖所有非手动编辑的变更场景
  • 快照值存储在脚本属性中，不占用工作表空间，不会额外增加表格冗余数据

• 原有脚本在初始化Changelog工作表时，存在表头列顺序和实际写入顺序不匹配的问题，调整后已对齐列顺序，避免日志记录错位
• 定时轮询的频率可根据团队修改频次调整，对于日均2次以上的修改场景，15分钟的轮询频率足够覆盖所有变更，不会产生过多的脚本运行负载
• 快照比对逻辑会和原有onEdit的实时记录形成互补，不会重复记录同一次变更（因为快照更新后会作为下一次比对的基准，实时记录后的变更会在快照更新时被覆盖）

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