如何通过Google Apps Script追踪电子表格的公式变更？

Google Apps Script的onEdit事件对象默认不会提供公式修改前的旧公式，因为事件触发时单元格已更新为新公式，无法直接从事件参数中获取历史值。要实现公式变更追踪，可通过预缓存单元格的公式状态来解决，具体方案如下：

核心思路 #

用PropertiesService存储每个单元格的最新公式，每次编辑时：

• 从缓存读取该单元格的旧公式
• 对比当前单元格的新公式
• 记录变更后，更新缓存为新公式

修改后的完整代码 #

function onChangeHistory(e) {
  // 跳过LogBook工作表的编辑，避免循环记录
  const editedSheet = e.range.getSheet();
  if (editedSheet.getName() === "LogBook") return;

  const timestamp = new Date();
  const user = Session.getActiveUser().getEmail();
  const sheetName = editedSheet.getName();
  const rangeAddress = e.range.getA1Notation();
  const cellKey = `${sheetName}!${rangeAddress}`;

  // 获取脚本属性缓存的旧公式
  const props = PropertiesService.getScriptProperties();
  const cachedOldFormula = props.getProperty(cellKey);
  const newFormula = e.range.getFormula();
  
  // 初始化旧值和新值
  let oldValue = e.oldValue;
  let newValue = e.value;

  // 处理公式变更的情况
  let formulaChanged = false;
  let oldFormula = "";
  if (newFormula) {
    oldFormula = cachedOldFormula || "";
    formulaChanged = oldFormula !== newFormula;
    // 如果是公式变更，更新值为公式计算结果
    if (formulaChanged) {
      newValue = e.range.getValue();
      // 如果之前是值而非公式，旧值取e.oldValue
      if (!oldFormula) {
        oldValue = e.oldValue || "";
      }
    }
  } else if (cachedOldFormula) {
    // 从公式改为普通值的情况
    oldFormula = cachedOldFormula;
    formulaChanged = true;
    oldValue = e.range.getValue(); // 取公式的最后计算值
  }

  // 记录变更到LogBook
  const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LogBook");
  if (!logSheet) {
    // 如果LogBook不存在，创建工作表并添加表头
    const newLogSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("LogBook");
    newLogSheet.appendRow(["时间戳", "操作人邮箱", "工作表名称", "单元格地址", "旧值", "新值", "旧公式", "新公式"]);
    logSheet = newLogSheet;
  }

  // 格式化公式（添加单引号避免被解析）
  const formattedOldFormula = oldFormula ? `'${oldFormula}` : "";
  const formattedNewFormula = newFormula ? `'${newFormula}` : "";

  // 只有当值或公式有变更时才记录
  if (oldValue !== newValue || formulaChanged) {
    logSheet.appendRow([
      timestamp,
      user,
      sheetName,
      rangeAddress,
      oldValue || "",
      newValue || "",
      formattedOldFormula,
      formattedNewFormula
    ]);
  }

  // 更新缓存：如果当前单元格有公式则存储，否则删除缓存（避免冗余）
  if (newFormula) {
    props.setProperty(cellKey, newFormula);
  } else {
    props.deleteProperty(cellKey);
  }
}

// 初始化缓存：首次运行时批量存储现有公式（可选，用于追踪之前的公式）
function initFormulaCache() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const props = PropertiesService.getScriptProperties();
  
  sheets.forEach(sheet => {
    if (sheet.getName() === "LogBook") return;
    const dataRange = sheet.getDataRange();
    const formulas = dataRange.getFormulas();
    const startRow = dataRange.getRow();
    const startCol = dataRange.getColumn();

    formulas.forEach((row, rowIndex) => {
      row.forEach((formula, colIndex) => {
        if (formula) {
          const cellAddress = `${sheet.getName()}!${SpreadsheetApp.getActiveSpreadsheet().getRange(startRow + rowIndex, startCol + colIndex).getA1Notation()}`;
          props.setProperty(cellAddress, formula);
        }
      });
    });
  });
}

关键说明 #

1. 缓存机制：用PropertiesService.getScriptProperties()存储每个单元格的公式，键为工作表名称!单元格地址，确保唯一性。
2. 公式变更判断：覆盖三种场景：
   • 公式修改为另一个公式
   • 公式修改为普通值
   • 普通值修改为公式
3. 避免循环记录：跳过对LogBook工作表的编辑，防止触发重复记录。
4. 初始化缓存：首次使用时运行initFormulaCache()，将现有工作表的所有公式存入缓存，确保历史公式也能被追踪。

使用注意事项 #

• 为onChangeHistory函数设置可安装的onEdit触发器（简单触发器权限有限，无法访问Session.getActiveUser()）。
• 若工作表中有大量公式，可改用隐藏工作表存储缓存数据，替代PropertiesService的存储上限限制。

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