D8 - 如何用 Google Apps Script 将 Google Calendar 上的事件与更新全部列出到 Google Sheet 上？

来到了第 8 天。但一样先讲结论，如果你很急着用，可以直接使用这份 Add-On: Calendar to Sheet。如果想要自己试试看，那可以持续阅读。对於想知道怎麽做的人，让我们开始吧！

先来个小测验

答案会在今天的文章中！

今天的目标

我们已经介绍了如何用 GAS 达到 (Google Form ->) Google Sheet -> Google Calendar 的流程。但如果今天我是想要反过来将 Google Calendar 上面的事件列出到 Google Sheet 呢？ Google Calendar 内建的汇出是弄成 ICS 档案。有没有更简单的方式可以直接在 Google Sheet 上面看到？这就是我们今天的主题。

1. 如何读取 Google Calendar 上面的所有事件？
2. 如何读取 Google Calendar 的特定事件？
3. 如何将这些事件写入 Google Sheet？

那我们就开始吧！

Q1. 如何读取 Google Calendar 上面的所有事件？

Step 1 开启 Google Sheet，并串起 GAS

因为我们要用到 Google Sheet ，所以一样用其作为开启的管道。

一样执行时会有「需要验证」出现，让我借用一下 D2 的影片。

Step 2 确认要抓的行事历资料并将其 ID 记录到 GAS 上

这边我们沿用昨天的两份行事历，待会会主要抓 meeting_room_A，也就是灰色的部分。

备注：这边只做了四个在同一天的 mock events，但基本上可以抓出全部的事件。

如果是自己做，可以在建玩行事历後，在「设定」这边取得日历的 ID。

接着，将两个 ID 输入到 GAS 的 environment.gs 当中。

这边都跟昨天差不多。

Step 3 用 Calendar.Events.list(cal_ID) 抓出所有的 Calendar Event！

先用方式一，但因为这个 API 是旧版本的服务（v3），所以要多做点操作。但因为会列出所有的 Events，所以还是先介绍它。首先开立个 Calendar Service，在右手边的服务中取得。

搞定 v3 的 Calendar API 後，就可以直接用其服务了抓到所有的 Calendar 物件了！

其 Object 大致上长这样，我们待会再来看怎麽将其写入 Google Sheet 当中。

读出来的 Events 会依照创造的时间点排列，个人是建议可以先全部抓下来後再用 Google Sheet 进行排序 Sort。但实际使用上，我们会想知道一段时间的内事件，要怎麽进行有时间的搜索？这就到我们的 Q2 读取特定 Google Calendar 事件。

Q2. 如何读取 Google Calendar 的特定事件？

前两步 Step 1 / Step 2 都跟 Q1 一样。

Step 3 用 getEvents() 来取得特定时间内的行事历

很简单，基本上就是用 getEvents(startTime, endTime) 来取得资料。要记得输入的 Time 都要是 Date Object。这边来示范一次怎麽抓。

function getCalendarEvents(){
  let cal = CalendarApp.getCalendarById(room_A_ID);
  var date = new Date();
  var firstDay = new Date(date.getFullYear(), date.getMonth(), 1);
  var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  var events = cal.getEvents(firstDay, lastDay);
  Logger.log(events);
  Logger.log(events.length);
}

实际跑起来的样子——

好，那接下来要怎麽「读事件」呢？这就会回到我们最开始的小测验。答案如下图...。

至於 for...in 不行，有一个可能是因为 Calendar Array 不是 enumerable（出处），但详细仍需要细节的研究。而答案写成程序码会变成这样——

function getCalendarEvents(){
  let cal = CalendarApp.getCalendarById(room_A_ID);
  var date = new Date();
  var firstDay = new Date(date.getFullYear(), date.getMonth(), 1);
  var lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  var events = cal.getEvents(firstDay, lastDay);
  for(let event of events){
    Logger.log(event.getStartTime())
  }

  for (let i=0; i< events.length;i++){
    Logger.log(events[i].getEndTime())
  }
}

好，那实际读起来长什麽样子，在这边我两个方式都示范；用方式一读开始时间，用方式二读结束时间。

其他功能，则可以参考其他每一个 Object 还可以用的 Methods: Calendar Event。如果想搜寻特定名称的事件，可以考虑加上 options 。另外对於「整日事件」，则有另外的抓法可以参考—— getEventsForDay(date, options)。

个人使用心得，虽然 Q1 是使用比较旧版的 API，但实际上它在抓资料时比较不罗唆，不会要限定时间和全天或时段事件。

Q3. 如何将这些事件写入 Google Sheet 中？

Step 1 / 2 / 3 在 Q1 / Q2 都已经陈述过，这边就专注在读出来的资料要怎麽写入。

这边的逻辑是要「取范围」 -> 「输入数值」，重点也会是这两步骤的细节怎麽写。

Step 4 取资料表的范围并用 setValues() 输入数值

这边用一个假的资料来做示范，今天也主要先示范如何输入「矩形、正方形范围」的资料。假设我要在 A2:B4 这个范围内输入输入一个矩形——

先来看看程序码。

function writeDate(){
  let data = [['a','b','c'],['d','e','f']];
  let starting_row = 2;
  let starting_col = 1;
  let num_row = data.length;
  let num_col = data[0].length;
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(starting_row, starting_col,num_row,num_col);
  range.setValues(data);
}

也来看看这段程序码会跑出的结果。

再来我们来读一下 Code。其中大部分跟读资料差不多，因为都要先取得范围，可以回去参考 D6。其中一个不同的是，会是从 Array in array 的结构中取得 numRows（要取多少列） 和 numColumns（要取多少行）。这标要清楚的是 Array 的基本架构，可以搭配 W3C: Array 一起阅读。

另外一个与 D6 不同的是透过 setValuse()，在使用时主要是要注意「范围」要先给出来。也就是我要先知道我要贴多少行与列，这边就用 .length 的方式取得，然後我们再将 data 改成外部输入的变数。最後写资料的程序码改成这样。

function writeData(data){
  let starting_row = 2;
  let starting_col = 1;
  let num_row = data.length;
  let num_col = data[0].length;
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(starting_row, 
                             starting_col, 
                             num_row, 
                             num_col);
  range.setValues(data);
}

Step 5 将 Q1/Q2 串到 Q3

我们来将前面两题串到第三题，我们先分别将 Q1 和 Q2 的 Code 写成 Array in array 的格式。像是 Q1 的长这样——

function getCalendarEventList(){
  var options = {
    maxResults: 100
  };
  let events = Calendar.Events.list(room_A_ID, options);
  let writed_data =[];

  for (let item of events.items){
    writed_data.push([item.id,
                      item.summary,
                      item.start,
                      item.end]);
  };
  Logger.log(writed_data)
}

跑起来长这样——

而 Q2 写起来长这样——

function getCalendarEvents(){
  let cal = CalendarApp.getCalendarById(room_A_ID);
  let date = new Date();
  let firstDay = new Date(date.getFullYear(), date.getMonth(), 1);
  let lastDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  let events = cal.getEvents(firstDay, lastDay);
  let writed_data = []
  for(let event of events){
    writed_data.push([event.getTitle(),
                      event.getId(),
                      event.getStartTime(),
                      event.getEndTime()])
  }
  Logger.log(writed_data)
}

跑起来长这样——

那最後就只要把 Logger.log(writed_data) 换成 writeData(writed_data) 就可以写入资料库了。

最後跑起来长这样——

Reference

Google Advanced Calendar Service
Google Developer: Record time and activities in Calendar and Sheets

好，那我们就完成了。今天的份量比较多，主要学了...

1. 用 Calendar.Events.list(cal_ID) 抓出所有的 Calendar Event
2. 用 getEvents() 来取得特定时间内的行事历事件
3. 取资料表的范围并用 setValues() 输入数值
4. 并结合以上三者

字数也来到了七千多（但这次贴的程序码比较多），总之，希望能有所帮助。今天比较值得一提的是， Read Google Calendar Events 并没有特别提到 Quota 限制，虽然相信还是会有，所以大家使用上仍是要多加留意。好，那今天就是我们的 D8，明天 D9 会来讲讲怎麽列出 Google Drive 里面所有档案 ID 与设定复杂权限。如果还有问题，透过留言之外，也可以到 Facebook Group，想开很久这次铁人赛才真的开起来哈哈哈，欢迎来当 Founding Member。如果不想错过可以订阅按赞小铃铛（？），也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。