如何遍历多个日历查找全员可用时段

我有一段Google Apps Script，功能是遍历自己未来两周的日历，筛选出周一至周五9:00-18:00（排除12:00-13:00午餐时段）内时长至少30分钟的空闲时段，并以表格形式邮件发送给自己。现在希望修改脚本，添加同事的日历ID，查找两人或多人的共同空闲时段，猜测需要用包含所有目标日历ID的数组，但不确定具体实现方式，求指导。

原有脚本如下：

function findAvailableTimeSlotsAndEmail() {
  var calendar = CalendarApp.getDefaultCalendar();
  var startDate = new Date();
  var endDate = new Date();
  endDate.setDate(startDate.getDate() + 14);
  var events = calendar.getEvents(startDate, endDate);
  var availableSlots = [];
  var currentSlot = null;

  for (var i = 0; i < 14; i++) {
    var date = new Date();
    date.setDate(startDate.getDate() + i);
    if (date.getDay() == 0 || date.getDay() == 6) {
      // Skip weekends
      continue;
    }
    for (var j = 9; j <= 17; j++) {
      if (j == 12) {
        // Skip lunch hour
        continue;
      }
      for (var k = 0; k < 2; k++) {
        var minutes = k * 30;
        var startTime = new Date(date.getFullYear(), date.getMonth(), date.getDate(), j, minutes);
        var endTime = new Date(date.getFullYear(), date.getMonth(), date.getDate(), j, minutes + 30);
        var hasEvent = false;
        for (var l = 0; l < events.length; l++) {
          var event = events[l];
          if (event.getStartTime() < endTime && event.getEndTime() > startTime) {
            hasEvent = true;
            break;
          }
        }
        if (!hasEvent) {
          if (currentSlot == null) {
            currentSlot = [date, startTime, endTime];
          } else {
            if (currentSlot[0].getTime() == date.getTime() && currentSlot[2].getTime() == startTime.getTime()) {
              // extend the current slot if it's consecutive
              currentSlot[2] = endTime;
            } else {
              // otherwise, add the current slot to the list of available slots and start a new slot
              availableSlots.push(currentSlot);
              currentSlot = [date, startTime, endTime];
            }
          }
        } else {
          // if the current slot is not available, add it to the list of available slots (if it exists) and reset the current slot
          if (currentSlot != null) {
            availableSlots.push(currentSlot);
            currentSlot = null;
          }
        }
      }
    }
    // add the final current slot to the list of available slots
    if (currentSlot != null) {
      availableSlots.push(currentSlot);
      currentSlot = null;
    }
  }

  if (availableSlots.length > 0) {
    // Generate the HTML table for the available time slots
    var html = "<table><tr><th>Date</th><th>Start Time</th><th>End Time</th><th>Duration</th></tr>";
    for (var i = 0; i < availableSlots.length; i++) {
      var slot = availableSlots[i];
      var start = slot[1];
      var end = slot[2];
      var duration = (end - start) / 3600000;
      var formattedDuration = (duration === 1) ? "1 hour" : (duration + " hours");
      html += "<tr><td>" + slot[0].toDateString() + "</td><td>" + start.toLocaleTimeString() + "</td><td>" + end.toLocaleTimeString() + "</td><td>" + formattedDuration + "</td></tr>";
    }
    html += "</table>";

    // Send an email with the available time slots table
    var recipient = "example@email.com";
    var subject = "Upcoming Availability";
    var body = "My Availability is below in EST:<br><br>" + html;
    MailApp.sendEmail(recipient, subject, "", {htmlBody: body});
  }
}

实现思路与修改步骤 #

核心逻辑是收集所有目标日历的事件，判断某个时段是否在所有日历中都无冲突，具体步骤如下：

• 定义目标日历ID数组：把自己和同事的日历ID放在数组中，默认日历可通过CalendarApp.getDefaultCalendar().getId()获取，同事的日历ID需从Google日历设置中复制（通常为邮箱格式或字符串）。
• 批量获取所有日历的事件：遍历日历数组，逐个拉取每个日历在目标时间段内的事件，合并为总事件列表。
• 修改空闲判断逻辑：将原单日历冲突检查，改为判断时段是否在所有日历中都无重叠事件。
• 调整邮件内容：修改主题和正文表述，匹配“共同空闲”的场景，避免歧义。

修改后的完整脚本 #

function findCommonAvailableTimeSlotsAndEmail() {
  // 1. 定义所有目标日历ID数组：自己的默认日历 + 同事的日历ID
  var calendarIds = [
    CalendarApp.getDefaultCalendar().getId(),
    "colleague1@example.com", // 替换成同事1的日历ID
    "colleague2@example.com"  // 可添加更多同事日历ID
  ];
  
  var startDate = new Date();
  var endDate = new Date();
  endDate.setDate(startDate.getDate() + 14);
  
  // 2. 收集所有日历的事件
  var allEvents = [];
  calendarIds.forEach(function(calendarId) {
    var calendar = CalendarApp.getCalendarById(calendarId);
    if (calendar) { // 确保日历可访问
      var events = calendar.getEvents(startDate, endDate);
      allEvents = allEvents.concat(events);
    }
  });
  
  var availableSlots = [];
  var currentSlot = null;

  for (var i = 0; i < 14; i++) {
    var date = new Date(startDate);
    date.setDate(startDate.getDate() + i);
    // 跳过周末
    if (date.getDay() === 0 || date.getDay() === 6) {
      continue;
    }
    // 遍历工作时段：9:00-12:00，13:00-18:00（排除12-13点午餐）
    for (var j = 9; j <= 17; j++) {
      if (j === 12) {
        continue;
      }
      // 每个小时拆分成两个30分钟时段
      for (var k = 0; k < 2; k++) {
        var minutes = k * 30;
        var startTime = new Date(date.getFullYear(), date.getMonth(), date.getDate(), j, minutes);
        var endTime = new Date(date.getFullYear(), date.getMonth(), date.getDate(), j, minutes + 30);
        
        // 3. 判断该时段是否在所有日历中都无冲突
        var hasConflict = false;
        for (var l = 0; l < allEvents.length; l++) {
          var event = allEvents[l];
          // 事件与当前时段有重叠则判定冲突
          if (event.getStartTime() < endTime && event.getEndTime() > startTime) {
            hasConflict = true;
            break;
          }
        }
        
        if (!hasConflict) {
          // 合并连续的空闲时段
          if (currentSlot === null) {
            currentSlot = [date, startTime, endTime];
          } else {
            if (currentSlot[0].getTime() === date.getTime() && currentSlot[2].getTime() === startTime.getTime()) {
              currentSlot[2] = endTime;
            } else {
              availableSlots.push(currentSlot);
              currentSlot = [date, startTime, endTime];
            }
          }
        } else {
          // 遇到冲突，保存当前已有的连续空闲时段
          if (currentSlot !== null) {
            availableSlots.push(currentSlot);
            currentSlot = null;
          }
        }
      }
    }
    // 保存当天最后一个连续空闲时段
    if (currentSlot !== null) {
      availableSlots.push(currentSlot);
      currentSlot = null;
    }
  }

  if (availableSlots.length > 0) {
    // 生成HTML表格
    var html = "<table border='1' cellpadding='8'><tr><th>日期</th><th>开始时间</th><th>结束时间</th><th>时长</th></tr>";
    availableSlots.forEach(function(slot) {
      var start = slot[1];
      var end = slot[2];
      var duration = (end - start) / 3600000;
      var formattedDuration = duration === 1 ? "1小时" : duration + "小时";
      html += `<tr>
                <td>${slot[0].toLocaleDateString()}</td>
                <td>${start.toLocaleTimeString([], {hour: '2-digit', minute: '2-digit'})}</td>
                <td>${end.toLocaleTimeString([], {hour: '2-digit', minute: '2-digit'})}</td>
                <td>${formattedDuration}</td>
              </tr>`;
    });
    html += "</table>";

    // 发送邮件
    var recipient = "your-email@example.com"; // 替换成你的邮箱
    var subject = "未来两周共同空闲时段";
    var body = "以下是你和同事未来两周的共同空闲时段（工作日9:00-18:00，排除午餐12:00-13:00）：<br><br>" + html;
    MailApp.sendEmail(recipient, subject, "", {htmlBody: body});
  } else {
    // 无空闲时段时发送提示邮件
    var recipient = "your-email@example.com";
    var subject = "无共同空闲时段";
    var body = "未来两周（工作日9:00-18:00）你和同事没有共同的空闲时段。";
    MailApp.sendEmail(recipient, subject, body);
  }
}

关键修改说明 #

• 新增calendarIds数组，支持添加任意多个日历ID
• 批量拉取所有日历的事件并合并，确保覆盖所有参与者的日程
• 冲突判断逻辑改为检查所有事件，只有全日历无冲突才视为空闲
• 优化时间格式化，生成更友好的中文时间显示
• 新增无空闲时段时的邮件提示

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