基于日期自动设置下拉列表值的Apps Script故障排查求助

参考Stack Overflow帖子修改代码后，执行无效果、无报错也无日志记录。代码逻辑为监听I列（Do Date，公式列，引用J列SW Date加365天），当Do Date等于当天时，将对应行H列的下拉列表值改为"Needs SW Owner Review"，后续计划添加邮件通知功能。

原代码如下：

function onEdit(event){
  var colI = 9;  // Column Number of "I"

  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == colI) {
    // An edit has occurred in Column I

    //Get the Due date and current date then set as same time.
    var doDate = changedRange.getValue().setHours(12, 0, 0);
    let today = new Date().setHours(12, 0, 0)

    //Get the difference of the 2 dates
    var dateDifference = Math.round((doDate - today) / 8.64e7)

    //Set value to dropdown depending on difference
    var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colI - 1);
    if (dateDifference == 0) {
      group.setValue("Needs SW Owner Review");
    }
  }
}

1. 原代码无效的核心原因 #

onEdit是简单触发器，仅在用户手动编辑单元格时触发。而I列是公式计算生成的内容，公式更新不会触发onEdit事件，所以代码永远不会进入判断逻辑。

2. 修正方案：改用定时触发器+批量检查 #

既然计划用午夜定时触发，直接编写批量检查所有行的函数，再配置定时执行：

修改后的代码 #

function checkDueDatesAndUpdateStatus() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const doDateCol = 9; // I列（Do Date）
  const statusCol = 8; // H列（状态列）
  const today = new Date();
  
  // 标准化日期：统一设置为当天12点，避免时间差干扰比较
  const normalizedToday = new Date(today.getFullYear(), today.getMonth(), today.getDate()).setHours(12, 0, 0);

  // 获取表格所有数据（假设表头在第1行）
  const dataRange = sheet.getDataRange();
  const allValues = dataRange.getValues();

  // 遍历每一行检查日期
  for (let rowIndex = 1; rowIndex < allValues.length; rowIndex++) { // 跳过表头行
    const doDate = allValues[rowIndex][doDateCol - 1]; // 数组索引从0开始，列号需减1
    
    if (doDate instanceof Date) { // 确保单元格值是日期类型
      const normalizedDoDate = new Date(doDate.getFullYear(), doDate.getMonth(), doDate.getDate()).setHours(12, 0, 0);
      
      // 日期匹配时更新状态
      if (normalizedDoDate === normalizedToday) {
        sheet.getRange(rowIndex + 1, statusCol).setValue("Needs SW Owner Review");
      }
    }
  }
}

代码说明 #

• 标准化日期：统一将日期的时间部分设为12:00:00，避免因时间差导致日期比较失败
• 批量遍历：检查所有数据行，无需依赖单元格编辑事件
• 类型校验：确保Do Date单元格的值是有效日期，避免非日期值引发错误

3. 配置定时触发器 #

1. 打开Google表格的脚本编辑器（工具→脚本编辑器）
2. 点击左侧「触发器」图标（闹钟样式）
3. 点击「添加触发器」，配置参数：
   • 选择函数：checkDueDatesAndUpdateStatus
   • 事件源：时间驱动
   • 类型：日计时器
   • 时间段：午夜到1点（或你需要的执行时段）

4. 测试方法 #

• 手动运行checkDueDatesAndUpdateStatus函数（脚本编辑器→运行），查看日志（查看→日志）确认执行情况
• 修改某行的SW Date（J列），让Do Date（I列）等于当天，手动运行函数验证H列是否更新

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