如何让Google Sheet指定单元格在任意单元格编辑时自动更新为当前日期时间

由于Google Sheets的内置函数（如NOW()）会随表格自动刷新，无法满足“仅当指定区域被编辑时才更新时间”的需求，因此需要通过Google Apps Script来实现该功能。以下是具体实现步骤和代码：

步骤说明 #

1. 打开你的仓储总布置图Google Sheet，点击顶部菜单栏的扩展程序 > Apps 脚本
2. 清空编辑器中的默认代码，粘贴下方的脚本
3. 点击编辑器顶部的保存按钮，给脚本命名（例如AutoUpdateTimestamp）
4. 首次运行需要授权：点击运行按钮，按照提示完成权限授予（Google会提示脚本未验证，选择高级 > 转到[你的脚本名称]即可继续）

脚本代码 #

function onEdit(e) {
  // 定义需要监控的编辑区域（A1:S80）
  const watchArea = e.source.getRange("A1:S80");
  // 定义要更新时间戳的目标区域（I21:K22）
  const targetArea = e.source.getRange("I21:K22");
  
  // 获取当前被编辑的单元格/区域
  const editedRange = e.range;
  
  // 检查编辑操作是否发生在监控区域内
  if (isOverlapping(editedRange, watchArea)) {
    // 获取当前日期时间
    const currentTime = new Date();
    // 将目标区域全部设置为当前时间
    targetArea.setValue(currentTime);
  }
}

// 辅助函数：判断两个区域是否存在重叠
function isOverlapping(rangeA, rangeB) {
  const a = {
    top: rangeA.getRow(),
    bottom: rangeA.getRow() + rangeA.getNumRows() - 1,
    left: rangeA.getColumn(),
    right: rangeA.getColumn() + rangeA.getNumColumns() - 1
  };
  
  const b = {
    top: rangeB.getRow(),
    bottom: rangeB.getRow() + rangeB.getNumRows() - 1,
    left: rangeB.getColumn(),
    right: rangeB.getColumn() + rangeB.getNumColumns() - 1
  };
  
  // 检查两个区域是否不重叠，取反得到重叠结果
  return !(a.bottom < b.top || a.top > b.bottom || a.right < b.left || a.left > b.right);
}

功能说明 #

• onEdit是Google Sheets的内置简单触发器，会在用户手动编辑单元格时自动执行
• 脚本会先验证编辑操作是否发生在A1:S80范围内，只有符合条件时才会更新目标区域
• 辅助函数isOverlapping用于处理批量编辑的情况（比如选中多个单元格粘贴内容），确保只要编辑区域和监控区域有重叠就触发更新
• 触发后，I21:K22区域内的所有单元格都会被设置为当前的日期和时间

注意事项 #

• 该脚本仅对手动编辑生效，公式自动计算导致的单元格内容变化不会触发时间更新
• 如果需要将日期和时间分开显示（比如I21显示日期，J21显示时间），可以修改targetArea.setValue(currentTime)为：

  // 示例：I21设为日期，J21设为时间
  targetArea.getCell(1,1).setValue(currentTime.toLocaleDateString());
  targetArea.getCell(1,2).setValue(currentTime.toLocaleTimeString());
  

• 脚本授权是安全的，因为只有你能访问自己的Google Sheet和脚本

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