如何用AppScript在Google Sheets每日记录K列变动行的历史？

核心思路 #

通过编辑事件监听追踪K列的实时变动，将当日变动临时存储在隐藏工作表中，再通过每日定时触发器将当日的变动记录归档到历史表，实现自动留存每日变动历史。

准备工作 #

1. 确保你的表格包含：
   • 数据工作表（对应你提供的目标表，名称设为数据，可自行修改常量）
   • 用于存储历史的工作表（若不存在，脚本会自动创建，名称为历史记录）
2. 打开Google Sheets的AppScript编辑器：点击「扩展程序」→「Apps Script」

完整代码 #

// 配置常量，可根据实际情况修改
const DATA_SHEET_NAME = "数据";
const HISTORY_SHEET_NAME = "历史记录";
const TEMP_SHEET_NAME = "临时快照";
const TRACK_COLUMN = "K"; // 要追踪变动的列

// 监听单元格编辑，记录K列变动到临时表
function onEdit(e) {
  const editRange = e.range;
  const activeSheet = editRange.getSheet();
  
  // 仅处理目标工作表的K列编辑
  if (activeSheet.getName() !== DATA_SHEET_NAME || editRange.getColumn() !== columnToIndex(TRACK_COLUMN)) {
    return;
  }

  const rowNum = editRange.getRow();
  const changeTime = new Date();
  const oldVal = e.oldValue || "";
  const newVal = e.value || "";
  
  // 获取变动行的完整数据
  const fullRowData = activeSheet.getRange(rowNum, 1, 1, activeSheet.getLastColumn()).getValues()[0];
  
  // 获取或创建隐藏的临时快照表
  let tempSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TEMP_SHEET_NAME);
  if (!tempSheet) {
    tempSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(TEMP_SHEET_NAME);
    // 写入临时表表头：基础信息+原数据表头
    const dataHeader = activeSheet.getRange(1, 1, 1, activeSheet.getLastColumn()).getValues()[0];
    tempSheet.appendRow(["记录时间", "行号", "旧值", "新值", ...dataHeader]);
    // 隐藏临时表避免误操作
    tempSheet.hideSheet();
  }
  
  // 将变动记录写入临时表
  tempSheet.appendRow([changeTime, rowNum, oldVal, newVal, ...fullRowData]);
}

// 列字母转列索引（如K→11）
function columnToIndex(column) {
  let index = 0;
  for (let char of column) {
    index = index * 26 + (char.charCodeAt(0) - 64);
  }
  return index;
}

// 每日归档当日变动记录到历史表
function archiveDailyChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.getSheetByName(TEMP_SHEET_NAME);
  let historySheet = ss.getSheetByName(HISTORY_SHEET_NAME);
  
  // 自动创建历史记录表（若不存在）
  if (!historySheet) {
    historySheet = ss.insertSheet(HISTORY_SHEET_NAME);
    // 复制临时表的表头到历史表
    const header = tempSheet.getRange(1, 1, 1, tempSheet.getLastColumn()).getValues()[0];
    historySheet.appendRow(header);
  }
  
  // 定义今日时间范围（本地时间）
  const today = new Date();
  const startOfDay = new Date(today.getFullYear(), today.getMonth(), today.getDate());
  const endOfDay = new Date(startOfDay.getTime() + 24 * 60 * 60 * 1000 - 1);
  
  // 读取临时表所有数据（跳过表头）
  const tempData = tempSheet.getRange(2, 1, tempSheet.getLastRow() - 1, tempSheet.getLastColumn()).getValues();
  // 筛选今日的变动记录
  const todayChanges = tempData.filter(row => {
    const recordTime = new Date(row[0]);
    return recordTime >= startOfDay && recordTime <= endOfDay;
  });
  
  // 将今日变动写入历史表
  if (todayChanges.length > 0) {
    historySheet.getRange(historySheet.getLastRow() + 1, 1, todayChanges.length, todayChanges[0].length).setValues(todayChanges);
  }
  
  // 删除临时表中的今日记录（保留表头）
  const rowsToDelete = tempData.map((row, idx) => {
    const recordTime = new Date(row[0]);
    return recordTime >= startOfDay && recordTime <= endOfDay ? idx + 2 : null;
  }).filter(row => row !== null);
  
  if (rowsToDelete.length > 0) {
    tempSheet.deleteRows(rowsToDelete[0], rowsToDelete.length);
  }
}

设置每日定时触发器 #

1. 在AppScript编辑器左侧点击「触发器」图标（闹钟形状）
2. 点击「添加触发器」，配置如下：
   • 选择函数：archiveDailyChanges
   • 事件源：时间驱动
   • 触发器类型：日计时器
   • 时间选择：每日23:00-24:00（根据你的需求调整）
3. 保存并完成脚本权限授权

关键说明 #

• 临时表作用：隐藏状态的临时表用于缓存当日变动，避免频繁写入历史表影响性能，同时防止误操作丢失未归档的记录。
• 变动记录内容：每条记录包含变动时间、行号、K列新旧值，以及变动行的完整数据，便于后续追溯。
• 权限注意：首次运行脚本会要求授权，按照提示完成即可，脚本仅访问当前表格数据，无额外权限请求。

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