请求改进Google Sheets的onEdit日志脚本以支持批量单元格变更记录

以下是满足所有需求的改进脚本，支持批量单元格变更记录、删除标记、空操作过滤，以及解决复制粘贴时新旧值为空的问题：

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

// 核心变更追踪函数，可直接绑定可安装触发器
function trackChanges(e) {
  const targetSheetName = "Sheet1";
  const logSheetName = "ChangeLog";
  
  // 仅处理指定工作表的变更
  const editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() !== targetSheetName) return;
  
  const range = e.range;
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  const user = Session.getActiveUser().getEmail() || '匿名用户';
  const timestamp = new Date();
  
  // 初始化日志工作表
  let logSheet = e.source.getSheetByName(logSheetName);
  if (!logSheet) {
    logSheet = e.source.insertSheet(logSheetName);
    const headers = ['Timestamp', 'User', 'Cell Reference', 'Old Value', 'New Value'];
    logSheet.appendRow(headers);
    logSheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  }
  
  // 获取所有单元格的新值
  const newValues = range.getValues();
  // 处理旧值获取：区分单个/批量操作
  let oldValues;
  if (numRows === 1 && numCols === 1) {
    // 单个单元格变更，直接使用事件对象的旧值
    oldValues = [[e.oldValue || '']];
  } else {
    // 批量操作，通过版本历史获取编辑前的值（需可安装触发器权限）
    try {
      const currentVersion = e.source.getVersion();
      const previousVersion = e.source.getVersion(currentVersion - 1);
      const prevSheet = previousVersion.getSheetByName(targetSheetName);
      oldValues = prevSheet.getRange(range.getRow(), range.getColumn(), numRows, numCols).getValues();
    } catch (err) {
      // 权限不足时提示无法获取旧值
      oldValues = Array(numRows).fill().map(() => Array(numCols).fill('无法获取旧值'));
    }
  }
  
  // 遍历每个单元格生成日志
  for (let i = 0; i < numRows; i++) {
    for (let j = 0; j < numCols; j++) {
      const oldVal = oldValues[i][j] || '';
      const newVal = newValues[i][j] || '';
      
      // 过滤新旧值均为空的无效操作
      if (oldVal === '' && newVal === '') continue;
      
      // 获取单元格A1标记
      const cellRef = SpreadsheetApp.getActiveSpreadsheet().getRange(range.getRow() + i, range.getColumn() + j).getA1Notation();
      
      // 处理删除操作：标记红色“数据已删除”
      let logNewVal = newVal;
      let fontColor = null;
      if (newVal === '' && oldVal !== '') {
        logNewVal = '数据已删除';
        fontColor = '#ff0000';
      }
      
      // 添加日志行并设置格式
      const logRow = [timestamp, user, cellRef, oldVal, logNewVal];
      const newRow = logSheet.appendRow(logRow);
      if (fontColor) {
        logSheet.getRange(newRow.getRow(), 5).setFontColor(fontColor);
      }
    }
  }
}

关键改进说明 #

• 批量单元格单独日志：通过嵌套循环遍历变更范围的每个单元格，为每个单元格生成独立的日志条目，不再遗漏批量操作的变更记录。
• 删除操作红色标记：当单元格内容被清空（旧值非空、新值为空）时，自动将“New Value”列替换为“数据已删除”并设置红色字体，直观标记删除操作。
• 空操作过滤：判断新旧值均为空时跳过记录，避免生成无意义的日志条目。
• 复制粘贴新旧值修复：批量操作时通过版本历史获取编辑前的单元格值，解决同表复制粘贴时无法获取旧值的问题。

重要注意事项 #

由于Google Sheets的简单触发器（onEdit）没有访问版本历史的权限，批量操作时获取旧值需要使用可安装触发器：

1. 打开脚本编辑器，点击左侧的「触发器」图标（闹钟样式）；
2. 点击「添加触发器」；
3. 配置选项：
   • 选择函数：trackChanges
   • 选择事件源：「从电子表格」
   • 选择事件类型：「编辑时」
4. 保存并完成权限授权。

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