如何在Google Sheet中获取日历事件并统计无会议空闲天数

修复后的完整代码 #

function syncCalendarAndCountFreeDays() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Calendarios");
  
  // 读取配置：日历ID、统计起止日期
  var calId = sheet.getRange("B1").getValue();
  var startDate = new Date(sheet.getRange("B3").getValue());
  var endDate = new Date(sheet.getRange("C3").getValue());
  
  // 校验日历ID有效性
  var cal = CalendarApp.getCalendarById(calId);
  if (!cal) {
    Logger.log("B1单元格的日历ID无效，请检查");
    return;
  }
  
  // 清空旧的事件数据（第6行及以后的列B-F）
  sheet.getRange(6, 2, sheet.getLastRow() - 5, 5).clearContent();
  
  // 拉取日历事件并写入表格
  var events = cal.getEvents(startDate, endDate);
  for (var i = 0; i < events.length; i++) {
    sheet.getRange(i + 6, 2).setValue(events[i].getStartTime());
    sheet.getRange(i + 6, 3).setValue(events[i].getEndTime());
    sheet.getRange(i + 6, 4).setValue(events[i].getTitle());
    sheet.getRange(i + 6, 5).setValue(events[i].getLocation());
    sheet.getRange(i + 6, 6).setValue(events[i].getDescription());
  }
  
  // 统计空闲天数：遍历起止日期内的每一天，检查是否无任何会议
  var freeDays = 0;
  var currentDay = new Date(startDate);
  
  while (currentDay <= endDate) {
    // 设定当天的完整时间范围（00:00 到 23:59:59）
    var dayStart = new Date(currentDay.setHours(0, 0, 0, 0));
    var dayEnd = new Date(currentDay.setHours(23, 59, 59, 999));
    
    // 检查当天是否有事件
    var dailyEvents = cal.getEvents(dayStart, dayEnd);
    if (dailyEvents.length === 0) {
      freeDays++;
    }
    
    // 日期往后推一天
    currentDay.setDate(currentDay.getDate() + 1);
  }
  
  // 把统计结果写入B4单元格（可根据自己的需求改位置）
  sheet.getRange("B4").setValue(freeDays);
  Logger.log("同步完成，空闲天数：" + freeDays);
}

修复点&新增功能说明 #

• 解决变量冲突：原脚本里start_time、end_time被重复赋值导致逻辑混乱，现在拆分了配置日期和事件日期的变量名
• 新增旧数据清空：每次同步前自动清掉之前的事件记录，避免新旧数据混在一起
• 加了日历ID校验：如果B1的ID不对，直接提示并终止，不会无意义报错
• 新增空闲天数统计：遍历你设置的起止日期，每天检查有没有会议，把无会议的天数统计出来写入表格（默认B4，可自行修改单元格位置）
• 优化日期判断：确保每天的时间范围覆盖全天，不会漏判跨天的事件

使用注意事项 #

1. 第一次运行要给脚本授权访问日历的权限，跟着页面提示操作即可
2. 确认B1的日历ID正确，B3、C3的日期是Google表格能识别的格式（比如2024/05/01）
3. 直接运行syncCalendarAndCountFreeDays函数即可完成事件同步和空闲天数统计

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