如何使用Google Apps Script读取Google Sheets中的时间并创建日历事件

我懂这种卡壳的感觉！折腾好几个小时找不到解决办法真的闹心，我来帮你搞定这个时间格式转换的问题，刚好我之前也处理过类似的需求。

首先得先搞清楚Google Sheets里时间的两种常见存储方式，这是解决问题的关键：

• 一种是用户直接输入的纯文本HH:mm（比如单元格里就是"14:30"）
• 另一种是Sheets自带的时间格式单元格（本质是日期对象，只是显示成HH:mm，底层是从1900年1月1日开始的小数）

场景1：处理纯文本格式的时间输入 #

如果用户在Sheets里输入的是纯文本的HH:mm，直接读取后可以直接用，但为了保险，还是可以加个验证和格式化的函数：

function formatTextTimeToHHmm(textTime) {
  // 验证格式是否为HH:mm
  const timeRegex = /^([01]\d|2[0-3]):([0-5]\d)$/;
  if (!timeRegex.test(textTime)) {
    throw new Error("时间格式不正确，请输入HH:mm格式的时间");
  }
  return textTime; // 已经是正确格式，直接返回
}

// 读取Sheets中的时间示例
function getTimeFromSheets() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的表单响应表");
  const timeCellValue = sheet.getRange("A2").getValue(); // 假设时间在A2单元格
  const formattedTime = formatTextTimeToHHmm(timeCellValue);
  Logger.log(formattedTime); // 输出类似"14:30"
  return formattedTime;
}

场景2：处理Sheets自带时间格式的单元格 #

这种情况更常见，用户设置了单元格格式为时间，输入后Sheets自动转成日期对象。这时候需要把日期对象转换成HH:mm格式的字符串：

function formatSheetTimeToHHmm(sheetTime) {
  // sheetTime是从Sheets读取到的Date对象
  const hours = String(sheetTime.getHours()).padStart(2, "0");
  const minutes = String(sheetTime.getMinutes()).padStart(2, "0");
  return `${hours}:${minutes}`;
}

// 读取Sheets时间并格式化示例
function getFormattedTimeFromSheetDate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("你的表单响应表");
  const timeCellValue = sheet.getRange("A2").getValue(); // 这里拿到的是Date对象
  const formattedTime = formatSheetTimeToHHmm(timeCellValue);
  Logger.log(formattedTime); // 输出类似"14:30"
  return formattedTime;
}

整合到创建Google日历事件的代码里 #

拿到格式化后的HH:mm时间后，你需要和日期拼接成完整的DateTime对象，再传给Calendar服务：

function createCalendarEventWithTime() {
  // 假设日期从Sheets读取，比如"2024-05-20"
  const dateStr = "2024-05-20";
  // 从Sheets读取并格式化后的时间
  const formattedTime = getFormattedTimeFromSheetDate(); // 或者getTimeFromSheets()，根据你的场景选
  // 拼接成完整的日期时间字符串
  const dateTimeStr = `${dateStr}T${formattedTime}:00`;
  const eventStartTime = new Date(dateTimeStr);
  
  // 创建日历事件
  const calendar = CalendarApp.getDefaultCalendar();
  calendar.createEvent(
    "测试事件",
    eventStartTime,
    new Date(eventStartTime.getTime() + 60 * 60 * 1000), // 1小时时长
    {description: "从Sheets读取时间创建的事件"}
  );
}

重要注意事项：时区问题 #

一定要确保你的Google Sheets、Google Apps Script项目、Google Calendar的时区是一致的！不然会出现时间偏移的问题。

• 在Sheets里：文件 > 设置 > 区域和时区
• 在Apps Script里：编辑 > 项目属性 > 时区

这样调整后，时间转换就不会出问题啦，你可以根据自己的实际场景选择对应的处理方式，测试一下应该就能正常创建日历事件了。

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