管理员如何通过API将组织所有用户的Google Calendar事件导入Google Sheets

前置操作要求 #

• 你作为企业超级管理员，需要先在Google Admin控制台为当前Apps Script项目开启全域委托权限，授予日历读、组织用户列表读的权限
• 进入脚本编辑器的「服务」面板，添加Admin SDK API、Calendar API两个高级服务
• 确认你的账号拥有全局读取所有用户日历的权限

修改后可运行代码 #

function export_all_users_gcal_to_gsheet() {
  // 配置时间范围和搜索过滤规则，可自行修改
  const startTime = new Date("2024-01-01 00:00:00");
  const endTime = new Date("2024-12-31 23:59:59");
  const searchOption = {search: '-canceled'}; // 默认过滤已取消的事件

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();
  // 写入表头
  const header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]];
  sheet.getRange(1, 1, 1, 14).setValues(header);
  
  // 获取组织内所有用户列表
  const userList = AdminDirectory.Users.list({
    customer: 'my_customer',
    maxResults: 500, // 可根据组织规模调整
    viewType: 'domain_public'
  }).users || [];

  let currentRow = 2;
  // 遍历所有用户拉取日历
  for (let user of userList) {
    const userEmail = user.primaryEmail;
    try {
      const cal = CalendarApp.getCalendarById(userEmail);
      if (!cal) continue; // 跳过无日历权限的用户
      const events = cal.getEvents(startTime, endTime, searchOption);
      
      // 写入当前用户的所有事件
      for (let i = 0; i < events.length; i++) {
        const event = events[i];
        const details = [[
          userEmail,
          event.getTitle(), 
          event.getDescription(), 
          event.getLocation(), 
          event.getStartTime(), 
          event.getEndTime(), 
          '', 
          ('' + event.getVisibility()), 
          event.getDateCreated(), 
          event.getLastUpdated(), 
          event.getMyStatus(), 
          event.getCreators(), 
          event.isAllDayEvent(), 
          event.isRecurringEvent()
        ]];
        sheet.getRange(currentRow, 1, 1, 14).setValues(details);
        // 写入时长计算公式
        sheet.getRange(currentRow,7).setFormula('=(HOUR(F' + currentRow + ')+(MINUTE(F' + currentRow + ')/60))-(HOUR(E' + currentRow + ')+(MINUTE(E' + currentRow + ')/60))').setNumberFormat('.00');
        currentRow++;
      }
      // 批量拉取避免触发配额，可根据需要调整延迟时长
      Utilities.sleep(500);
    } catch (e) {
      console.log(`访问用户${userEmail}日历失败：${e.message}`);
      continue;
    }
  }
}

function onOpen() {  
  Browser.msgBox('操作说明', '1) 点击工具 -> 脚本编辑器\n2) 按需修改代码内的时间范围和过滤规则\n3) 运行export_all_users_gcal_to_gsheet函数即可', Browser.Buttons.OK);
}

使用注意事项 #

• 如果组织内用户量超过500，需要增加分页拉取用户的逻辑
• 不需要拉取全量用户的话，可以自行构造用户邮箱数组替换掉AdminDirectory拉取的userList
• 首次运行时会弹出权限申请弹窗，按提示完成授权即可

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