Google Apps Script读取表格时间恒定多出29分43秒异常排查

问题现象 #

编写Google Apps Script的onEdit触发器读取表格时间字段时，获取到的时间值恒定比表格内原始值多29分43秒，暂未定位到偏移来源，当前测试代码如下：

function onEdit(e){  
  var cal = CalendarApp.getCalendarById("h8ueab6ooel3iv8c7v4nd6bkvg@group.calendar.google.com");
  Logger.log(cal)
  // var arrayDate, arrayTimeStart, arrayTimeEnd;
  var DateStart = new Date();
  var DateEnd = new Date();
  var table = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dlinna = table.getLastRow();
  var data = table.getRange("A2:E"+dlinna).getValues();
  
   
  for (var i=data.length-1; i<data.length;i++){
    Logger.log(data[i][2]);
       
  }
}

相关参考截图：

• 表格原始行数据
  
• 异常输出的时间值
  

问题根因 #

这个固定29分43秒的偏移是Google Sheets与Apps Script的已知兼容问题，和业务代码逻辑无关，触发逻辑如下：

• 表格内的时间列为无日期锚点的纯时间格式，Sheets内部以浮点值存储这类数据：数值1代表1个完整自然日（24小时），0.5代表当日12点，0代表当日0点。
• 调用getValues()方法时，Apps Script会自动将单元格值转换为对应JS类型，对于纯时间值会默认绑定1899-12-30作为基准日期，转换为JS原生Date对象。
• 当脚本项目的时区设置、表格文件的时区设置不一致时，这个跨纪元的日期转换会叠加一个固定的历史序列化误差，数值恰好为29分43秒，偏移量和单元格内填写的具体时间值无关。

修复方案 #

两种方案二选一即可解决问题：

• 方案1：统一时区（无代码改动，成本最低）
  1. 打开目标Google Sheets文件，点击顶部菜单「文件」-「设置」，在「常规」选项卡下确认当前文件配置的时区，保存设置。
  2. 打开绑定的Apps Script编辑器，点击左侧边栏「项目设置」，取消勾选「让 Apps Script 自动设置时区」，手动选择和表格完全一致的时区，保存配置。
  3. 重新运行触发器，时间偏移会直接消失。
• 方案2：手动解析时间值（彻底绕开自动转换bug，适配多时区场景）
  不依赖getValues()自动生成的Date对象，手动读取Sheets存储的原始序列化值计算时间，从逻辑层面避开转换bug，修改后的可运行代码示例：

function onEdit(e){  
  var cal = CalendarApp.getCalendarById("h8ueab6ooel3iv8c7v4nd6bkvg@group.calendar.google.com");
  var table = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dlinna = table.getLastRow();
  var data = table.getRange("A2:E"+dlinna).getValues();
  // 直接读取表格本身配置的时区，从根源避免时区不匹配问题
  var sheetTimezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  const MILLISECONDS_PER_DAY = 86400000;

  for (var i=data.length-1; i<data.length;i++){
    var timeSerialValue = data[i][2];
    // 纯时间值为小于1的浮点数，直接计算对应毫秒数
    var correctDate = new Date();
    correctDate.setHours(0,0,0,0);
    correctDate.setMilliseconds(Math.round(timeSerialValue * MILLISECONDS_PER_DAY));
    // 输出结果和表格显示的时间完全一致
    Logger.log(Utilities.formatDate(correctDate, sheetTimezone, "yyyy-MM-dd HH:mm:ss"));
  }
}

避坑说明 #

• 不要直接信任getValues()返回的无日期纯时间对应的Date对象，这个自动转换bug已存在多年，官方至今未做修复。
• 所有绑定Sheets的Apps Script项目，都建议手动将脚本时区和表格时区设为一致，否则除了这个固定偏移外，还可能出现整小时/半小时的时区计算错误。

内容的提问来源于stack exchange，提问作者Семён Лютоев