D6 - 如何用 Google Apps Script 将 Google 表单收到的时间同步在 Google Calendar 上?

来到了第六天,我们要来操作 Google Calendar,一样会先从较简单的案例开始。但一样先讲结论,如果你很急着用於跟别人约时间,我个人蛮推荐 CalendlyGreenHouse,之前面试时不少外商都直接用这两种。但如果你是想要...跟很多 Calendar 结合(e.g. 会议室的预约系统),那确实可以考虑自己简单开发。如果时间紧凑,可以直接使用这份 Add-On: Form to Calendar,但十个 event 後就要收费。也可以用 Form Scheduler,但有七天的回应限制时间。

好,那如果上述情况你都了解後,仍想一起加入这趟旅程的话,我们就开始吧。

先来几个小测验


今天的目标

现在几乎每天都会打开 Google Calendar,也很常用 Google Form。在新创公司时,因为会议室只有两间,大家有时会抢着使用。当时老板就问说,有没有机会弄一个预约系统?就延伸出两个目标问题——「我要将 Google 表单上的时间回应结合到 Google Calendar 上」?

以这个问题来说,我们会需要将「表单中的资料」输入对应 Google Calendar 当中。这边有两种做法,第一种比较简单,就是开一个新的 Google Calendar,将资料表上的资料全部丢进入,这适合一次性的做法。第二种就比较复杂,是随着资料变动来更新 Google Calendar,这样就会需要有个预设好的 Calendar,再复杂就是将资料丢到不同的 Calendar 中,在这边会先示范第一种,第二种会在明天的文章跟大家说明都示范给大家看怎麽用。

一样先提醒 Quota,一个免费帐号基本上每天透过 GAS 只能创造 5000 个 Google Calendar Event。如果有超过的可能可以考虑直接用付费软件。

「我要将 Google 表单上的时间回应结合到 Google Calendar 上」?

Step 1 开启 Google 表单,并从回应的连结中串起 GAS

录制完才发现我已经写到第六天了,拍谢让我穿越时空一下。一样执行时会有「需要验证」出现,让我借用一下 D2 的影片。

Step 2 搜集回应并读取资料

那假设今天有两位同仁填写了借用表,想在 9/5 日上午分别借用 A、B 会议室。

当表单有资料後,此时我们可以参照 D4 的方式,从 GAS 中读取资料。这边我们用一个 readData() 来进行资料的读取。因为今天我们要处理「日期」,Date() Object 在运算上相对比较复杂,这边我们介绍一个工具叫 getDisplayValues() 它和 getValues()有什麽不同呢?让我们来看看,这边我们抓取同一个范围,然後试着用这两种方法分别取其值。

function readData() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(2,2,2,5);
  let data = range.getValues();
  let display_data =  range.getDisplayValues()
  Logger.log(data)
  Logger.log(display_data)
  // return data
}

可以发现,如果透过 getValues() 来取得资料,会取得 Google Sheet 背後真正存着的资料,日期不会单纯显示日期,而会是一个 Date Object(Sun Sep 05 00:00:00 GMT+08:00 2021)。但透过 getDisplayValues() 我们可以单纯取得表面上看到的资料。那这又有什麽差别呢?如果我要将两个 Date Object 相加,在不依靠外部封包如 Moment.js 下,写出来的方式其实不怎麽友善(当然,如果只是简单的加上时间,那就用 setTime() 即可,但很多时候我们的时间操作并不会那麽单纯)。

而 Google Sheet 本身是个「试算表」,其实很简化很多我们在操作资料上的细节。像如果我们要将时间 上午 09:00 变成 9:00 ,可以直接透过 Google Sheet 的「资料」达到。善於分别这两者,能大幅地加速我们处理资料的效率。

好,了解了这个概念後,就可以简化我们实际要对「日期时间」的处理方式。像是时间可以直接写成 new Date(request[3] + " " +request[4])

function readData() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data= sheet.getRange(2,2,2,5).getDisplayValues()
  Logger.log(new Date(data[0][3] + " " +data[0][4]))
  // return data
}

这边是先让大家理解 Date Object 的使用细节,而这段写法我们会移动到另外一个 Function 再做资料处理,这边就先简化成单纯资料。

function readData() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data= sheet.getRange(2,2,2,5).getDisplayValues()
  return data
}

Step 3 将读取的资料写成 Google Calendar 要的格式

好,那接着在这边我们先创立「一个」日历,统一纪录使用时间,一个日历的事件有几件基本的内容需要包含,包含事件名称、开始时间、结束时间。那我们先来看看怎魔读 Step 2 的资料。

示范影片中 for 回圈的 i 设定是由 1 开始,所以只有 Print 出 Edward 的资料。如果是由 0 开始的话,也会 Print 出 Emily 的资料。程序码的话长这样——

function setUpCalendar() {
  let data = readData();
  for (let i = 0; i < data.length; i++) {
    let request = data[i];

    // request example => [Emily, A, 1, 2021/9/5, 09:00]
    let user = request[0];
    let meeting_room = request[1];
    let title = user + '_Meeting_Room_' + meeting_room;
    let start_time = new Date(request[3] + " " +request[4]);
    let end_hour = start_time.getHours() + Number(request[2])
    let end_time = new Date(start_time);
    end_time.setHours(end_hour)
    Logger.log(title + '\n' + start_time + '\n' + end_time);
  }

这边的细节其实是 Date Object 的操作,我们会把重点放在 GAS 本身。你会发现 Date 的处理比较复杂,这也是为什麽会很推荐先把计算在 Google Sheet 中完成後再读进来(像是先把时间和日期相加)。这边示范怎麽样用 Google Sheet 完成这样的资料清理,而且法也是小测验的答案(D)= E2 + F2 + (D2/24)

其中比较值得一提的是,在 Logger.log() 中运 \n 一样可以导致在 console 中的换行。用这段的产出会是,分别就是我们的事件名称、开始时间、结束时间。

Edward_Meeting_Room_B
Sun Sep 05 2021 09:00:00 GMT+0800 (Taipei Standard Time)
Sun Sep 05 2021 10:00:00 GMT+0800 (Taipei Standard Time)

而这也是 Google Calendar 要的格式。理解格式後,接着就是与日历结合了。

Step 4 将读取好的资料写入 Google Calendar

在这边我们用 CalendarApp.createCalendar('Meeting Rooms'); 来创造日历,并将其命名为 cal。接着再透过 cal.createEvent(title, startTime, endTime) 来创建行程。而每一个日历、行程都会有对应的 ID,我们可以透过 getId() 来取得(会方便之後的操作)。

设定行事历的 Code 写出来後会长这样——

function setUpCalendar() {
  let cal = CalendarApp.createCalendar('Meeting Rooms');
  let data = readData();
  
  for (let i = 0; i < data.length; i++) {
    let request = data[i];

    // request example => [Emily, A, 1, 2021/9/5, 09:00]
    let user = request[0];
    let meeting_room = request[1];
    let title = user + '_Meeting_Room_' + meeting_room;
    let start_time = new Date(request[3] + " " +request[4]);
    let end_hour = start_time.getHours() + Number(request[2])
    let end_time = new Date(start_time);
    end_time.setHours(end_hour)
    Logger.log(title + '\n' + start_time + '\n' + end_time);
    let event = cal.createEvent(title, start_time, end_time);
    Logger.log(event.getId())
  }
  Logger.log(cal.getId())
}

好,那今天我们知道了要怎麽样:

  1. 用 GAS 创建行事历与事件,并取得 ID。
  2. 将 Google 表单的结果,丢入 Google Calendar 当中。
  3. 相对於用 JavaScript 来处理资料,可以直接用 Google Sheet 完成,再搭配 getDisplayValues() 取得处理後的资料。

一样提醒,创建行事历会有 Quota 限制。好,那今天就是我们的 D6,明天 D7 会继续介绍怎麽将「表单中的内容」丢到「不同的 Google Calendar」当中的方式,如果不想错过可以订阅按赞小铃铛(?),也欢迎留言跟我说你还想知道什麽做法/主题。如果还有问题,透过留言之外,也可以到 Facebook Group,我们明天见。


<<:  Day06 测试写起乃 - before、after

>>:  [Day 6] Button

Day 2 - Using Google reCAPTCHA with ASP.NET Web Forms C#「我不是机器人」验证

=x= 🌵 CONTACT Page 寄信页的「我不是机器人」验证功能,後端实作。 Google r...

D10 - 如何用 Google Apps Script 自动化对 Google Drive 的操作?(二)自动列出所有档案并设定权限

来到了第十天。我们的习惯是先讲结论,如果你很急着用,可以直接使用这份 Add-On: Drive E...

Day 01: 【序】– 架构与设计、代码、工程师

「你因为两个原因来读这本书:首先,你是位程序设计师。再者,你想成为一位更好的程序设计师」 取自: ...

【心得】checkbox表单实作-待办清单

上一次介绍了各种表单的使用 那麽实战练习必不可少呀! 今天就演练演练自制待办清单吧 首先来看一下想做...

javascript网页座标(screen,page,client)

下面三种座标的(x,y)都是从左上角开始为(0,0) screen 根据整个萤幕的宽高 page 根...