求助：开发Google Apps Script实现单元格超时着色（替代条件格式）

在Google Sheets的「Stocks」工作表中，C列内容为空白或24小时制时间，用于记录用户最后更新数据的时间。希望当单元格时间与当前时间相差超过2小时时，单元格自动变为黄色以提醒更新。

使用条件格式无法稳定实现：复制粘贴行时会覆盖条件格式，导致应用范围出现空白。所用条件格式公式为：=TIMEVALUE(now()-time(2,0,0))>TIMEVALUE(C1)，应用于整列。

核心需求 #

• 无需自动录入时间，不需要OnEdit触发，使用15分钟定时触发器即可
• 检查C列中超过2小时的时间单元格，将其标黄
• 未超时或空白的单元格，恢复为所在行的原有背景色

失败的尝试代码 #

function checkTime(e) {

  var sheet = e.source.getActiveSheet();

  // do nothing if not column 3
  if (e.range.getColumn() !== 3) return
  if (sheet.getName() !== "Stocks") return

  const mainsheet = sheet.getSheetByName("Stocks")
  const times = mainsheet.getRange("C2:C").getValues()

  // not sure how to make this if-statement

  // set the cell color
  e.range.setBackground("yellow")
  }

可行解决方案及实现思路 #

实现思路 #

1. 放弃依赖事件对象e：定时触发器执行时无事件对象，需主动定位到「Stocks」工作表
2. 批量处理C列数据：获取有效数据范围，遍历每个单元格计算与当前时间的差值
3. 颜色规则判断：
   • 若单元格时间与当前时间差超过2小时，设置背景为黄色
   • 空白或未超时的单元格，保留原有背景色
4. 设置15分钟时间驱动触发器，定期执行脚本

完整代码 #

function checkStocksUpdateTime() {
  // 定位目标工作表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Stocks");
  if (!sheet) return;

  // 获取C列有效数据范围（假设第1行为表头）
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // 无数据行时直接返回

  const timeRange = sheet.getRange("C2:C" + lastRow);
  const times = timeRange.getValues();
  const originalBackgrounds = timeRange.getBackgrounds();
  const currentTime = new Date();
  const twoHoursMs = 2 * 60 * 60 * 1000; // 2小时对应的毫秒数

  // 遍历每个单元格处理
  for (let i = 0; i < times.length; i++) {
    const cellTime = times[i][0];
    let targetColor = originalBackgrounds[i][0]; // 默认保留原有颜色

    if (cellTime instanceof Date) {
      const timeDiff = currentTime - cellTime;
      if (timeDiff > twoHoursMs) {
        targetColor = "#ffff00"; // 黄色
      }
    }

    // 设置单元格背景色
    timeRange.getCell(i + 1, 1).setBackground(targetColor);
  }
}

触发器设置步骤 #

1. 打开目标Google Sheets，点击「扩展程序」→「Apps脚本」
2. 将上述代码粘贴到脚本编辑器，保存项目
3. 点击左侧时钟图标进入「触发器」页面
4. 点击「添加触发器」：
   • 选择函数：checkStocksUpdateTime
   • 事件源：「时间驱动」
   • 时间类型：「分钟计时器」
   • 间隔：「每15分钟」

关键说明 #

• 主动获取数据范围，避免复制粘贴行导致的格式丢失问题
• 使用毫秒数计算时间差，解决日期跨天的判断误差
• 保留原有背景色，不干扰工作表其他格式设置
• 定时触发器自动执行，无需手动操作

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