Google Apps Script读取Sheet创建日历事件时间偏移问题排查

问题背景 #

我正在开发一个Google Apps Script，用于基于Google Sheets中的数据自动创建Google Calendar事件。脚本执行成功且能创建事件，但日历中的事件时间始终存在错误——例如Sheet中设置的是上午8:00，日历里却显示为上午8:52，时间偏移了约52分钟。

原问题代码 #

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var calendarId = 'your_calendar_id_here@gmail.com'; // 替换为你的日历ID
  var calendar = CalendarApp.getCalendarById(calendarId);

  // 检查是否成功获取日历
  if (!calendar) {
    Logger.log("未找到日历，请检查日历ID。");
    return;
  }

  // 获取指定范围的数据
  var dataRange = sheet.getRange("P2:U" + sheet.getLastRow());
  var rows = dataRange.getValues();

  for (var i = 0; i < rows.length; i++) {
    var eventName = rows[i][0]; // P列：事件名称
    var eventDate = new Date(rows[i][1]); // Q列：日期
    var startTime = rows[i][2]; // R列：开始时间
    var endTime = rows[i][3]; // S列：结束时间
    var description = rows[i][4]; // T列：描述
    var location = rows[i][5]; // U列：地点（可选）

    if (isNaN(eventDate.getTime())) {
      Logger.log('第' + (i + 2) + '行日期无效');
      continue;
    }

    if (startTime && endTime) {
      var startDateTime = new Date(eventDate);
      startDateTime.setHours(Math.floor(startTime * 24), (startTime * 24 * 60) % 60);

      var endDateTime = new Date(eventDate);
      endDateTime.setHours(Math.floor(endTime * 24), (endTime * 24 * 60) % 60);

      // 在Google日历中创建事件
      calendar.createEvent(eventName, startDateTime, endDateTime, {
        description: description,
        location: location
      });

      Logger.log('已创建事件: ' + eventName + ', 开始时间: ' + startDateTime + ', 结束时间: ' + endDateTime);
    } else {
      Logger.log('第' + (i + 2) + '行时间无效');
    }
  }
}

已尝试的排查步骤 #

• 验证Calendar对象：确认getCalendarById()能成功获取日历，事件可创建说明权限正常。
• 检查日期格式：用=ISDATE()公式验证Sheet中的日期列均为有效日期。
• 检查时间格式：用=ISNUMBER()公式确认开始/结束时间列返回TRUE，时间值有效。
• 合并日期与时间：尝试用setHours()将时间值与事件日期合并，但时间偏移问题仍存在。

问题咨询 #

1. 如何确保从Sheet读取的时间与日历中设置的时间完全匹配？
2. 有没有更可靠的方法处理Google Sheets的时间值，避免出现时间偏移？

问题解决与修正代码 #

后续排查发现Google Sheets的时区设置被意外修改，这是导致时间偏移的核心原因。以下是修正后可正常运行的完整代码：

function createCalendarEvents() {
  // 通过ID选择日历
  const calendarId = 'your-calendar-id@group.calendar.google.com'; // 替换为你的实际日历ID
  const calendar = CalendarApp.getCalendarById(calendarId);

  // 获取活动表格及指定数据范围（P2:U）
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName'); // 替换为你的表格名称
  const dataRange = sheet.getRange("P2:U" + sheet.getLastRow());
  const rows = dataRange.getValues(); // 获取范围内所有数据

  // 遍历行并创建事件
  for (var i = 0; i < rows.length; i++) {
    var eventName = rows[i][0];        // P列：事件名称
    var eventDate = new Date(rows[i][1]); // Q列：事件日期
    var startTime = new Date(rows[i][2]); // R列：开始时间
    var endTime = new Date(rows[i][3]);   // S列：结束时间
    var description = rows[i][4];      // T列：事件描述
    var location = rows[i][5];         // U列：事件地点

    // 关键信息缺失则跳过
    if (!eventName || !eventDate || !startTime || !endTime) {
      Logger.log(`第${i + 2}行数据缺失`);
      continue;
    }

    // 设置正确的开始/结束时间的小时和分钟
    const startDateTime = new Date(eventDate);
    startDateTime.setHours(startTime.getHours(), startTime.getMinutes(), 0, 0); // 设置开始时间
    
    const endDateTime = new Date(eventDate);
    endDateTime.setHours(endTime.getHours(), endTime.getMinutes(), 0, 0); // 设置结束时间

    // 记录计算后的时间用于调试
    Logger.log(`开始时间: ${startDateTime}`);
    Logger.log(`结束时间: ${endDateTime}`);

    // 创建日历事件
    try {
      calendar.createEvent(eventName, startDateTime, endDateTime, {
        description: description,
        location: location
      });
      Logger.log(`已创建事件: ${eventName}, 开始时间: ${startDateTime}, 结束时间: ${endDateTime}`);
    } catch (e) {
      Logger.log(`第${i + 2}行创建事件失败: ${e.message}`);
    }
  }
}

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