D7 - 如何用 Google Apps Script 将 Google 表单的回应即时同步在多个行事历上?

来到了第七天。老样子,先讲推荐的速解,如果你很急着用,这些 Add-On 可以帮上忙,第一是 Form to Calendar 但它十个 event 後就要收费; 第二是 Form Scheduler 有七天内要回应的限制。自己写的好处是,如果你一天突然要客制化,那这篇文章应该帮的上忙。对於想知道怎麽做的人,让我们开始吧!

先来个小测验

答案会在今天的文章中!

今天的目标

电脑还不普及时,会到市立图书馆借电脑,那时需要用图书证到柜台,询问哪个时段可以使用,然後就可以用个三十分钟;又或是之前在做会议室的简单借用时,老板就问说能不能让行事历分开,不然这样看得很累。所以今天的关键问题是...

  1. 如何将 Google 表单的结果同步在多个行事历(Google Calendar)上?
  2. 如何即时将表单的结果同步到行事历上?

特别提醒,这边的「客制化」Email,因为会限定是要 Google 产品,一般人会是 Gmail,或是企业与组织的 Google Workspace(简单来说你的学校、组织的基本上是用 Google Drive 传档案的话,基本上就是)。那我们就开始吧!


如何将 Google 表单的结果同步在多个行事历(Google Calendar)上?

前三步跟 D6 差不多,会调整程序码,但借用一下影片。

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

一样开启一个表单,并串回应与GAS。

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

Step 2 搜集回应并读取资料

假设我们表单搜集到这样的回应。

这个步骤也跟 D6 差不多,所以直接借用程序码。

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

读起来的样子,读起来没问题。

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

这步也跟 D6 很像,主要是做资料的转换,这次我们先将要处理的时间用 Google Sheet 搞定。

同时调整 Step 2 的程序把,把 let range = sheet.getRange(2,2,2,5); 换成 let range = sheet.getRange(2,2,2,7); 。因为我们多抓了两栏,也因为我直接把资料处理在 Google Sheet 上搞定,所以不用再 getDisplayValues,更改过後会长这样。

function readData() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(2,2,2,7);
  let data =  range.getValues()
  return data
}

好,来看看能不能运作。

接着我们一样来检查要给 Calendar 的资料。因为比较复杂的 Date Object 已经让 Google Sheet 搞定,这边我们就可以简化程序码成...

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

    let user = request[0];
    let meeting_room = request[1];
    let title = user + '_Meeting_Room_' + meeting_room;
    let start_time = request[5];
    let end_time = request[6];
    Logger.log(title + '\n' + start_time + '\n' + end_time);
  }
}

一样试跑看看能不能运作。

看起来可以!好,上面三步骤跟 D6 差不多,就比较快速带过。接着会是今天的重点,开启并设置不同的 Calendar。

Step 4 先建立好要用的 Google Calendar 并取得其 ID

首先,如果你还没有固定给「会议室」的日历的话,就先建立日历。

建立好後,可以在「设定」这边取得日历的 ID。

接着,将两个 ID 输入到 GAS 的 environment.gs 当中。下面这段录影片当时写太快了,这边我们用为全域变数,用 var 比较合适。

var room_A_ID = "[email protected]"
var room_B_ID = "[email protected]"

然後在昨日基础的程序码上加上「分派给会议室 ID」的部分,就完成了。这边简单的写,如果当会议室很多时,可以考虑另外用一个 Mapping 会议室 和 Calendar ID 的试算表。

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

    let user = request[0];
    let meeting_room = request[1];
    let title = user + '_Meeting_Room_' + meeting_room;
    let start_time = request[5];
    let end_time = request[6];

    let calendar_id;
    if (meeting_room=="A"){
      calendar_id = room_A_ID;
    } else if (meeting_room=="B"){
      calendar_id = room_B_ID;
    }
    let cal = CalendarApp.getCalendarById(calendar_id);
    let event = cal.createEvent(title, start_time, end_time);

    Logger.log('Create Calendar of '+ meeting_room +' \n'+ title + '\n' + start_time + '\n' + end_time);
  }
}

完整起来长这样。

以上就完成了我们的 Q1。但我们实际使用上,更想看到的是一但有人填写了表单,就更新到行事历上,这个要怎麽做到?让我们来看看 Q2。


Q2. 如何即时将表单的结果同步到行事历上?

前面四个 Step 跟 Q1 一样,这边主要说明「要加上的部分」,也就是 Step 4 。

Step 4 使用 onEdit Trigger 来自动更新你的表单

onEdit(e) 是一个 Trigger,每当有人更新表单时,这个 Trigger 就会启动。我们透过一段简单的程序码来看它的功能,以下这段程序码是会跳出说,工作表中刚更动的数值。

function onEdit(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;
  SpreadsheetApp.getActiveSpreadsheet().toast(range.getValues());
}

实际用起来,会长这样——

了解其基本功能後,我们来看要怎麽样「结合」起我们 Q1 的部分。

Step 4-1 先把预处理的「公式」套用到整个表单

还记得 Step 2 我们有先对「时间」与「日期」做处理吗?这边我们要先设定说它的处理是会延续下去的。其实就是很单纯的让公式套用而已——

Step 4-2 让新增的资料自动执行

那接下来,就是将 onEdit()setUpCalendar 进行整合。先给一个最简单的整合方式,把两个程序码合并,这个合并需要一些调整。像是拿掉 setUpCalendar 第一段并加上个可传入的变数。同时,请将 onEdit() 转为其他「非预设」的 function Name(onOpen() 也是预设)。简单来说是改名,主要原因是因为待会我们要用系统的 Trigger。如果用 onEdit(e) 会出现些 BUG(底了很久QQ)。我改成autoUpdate,并直接写死说我要取最新更新的那列(e.range / range.getRow())的七个栏(let data = ...)。

function setUpCalendar(data) {
  // let data = readData();
  ...
  
}

function autoUpdate(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  let range = e.range;
  let new_row = range.getRow();
  let data = SpreadsheetApp.getActiveSheet().getRange(new_row,2,1,7).getValues();
  setUpCalendar(data)
}

好,接着我们来设定 Trigger,这边因为是示范,所以我是直接贴上一笔新的资料,如果是实际在使用表单,条件请改成「表单送出时」(要先确认表单有连接到 Google Sheet 喔,请看 Step 1)

好,设定完後我们来看看功能是不是有运作。

看起来有,恭喜!完成了自动设定表单罗!再让我们看看今天的全部程序码——

function readData() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(2,2,2,7);
  let data =  range.getValues()
  return data
}

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

    let user = request[0];
    let meeting_room = request[1];
    let title = user + '_Meeting_Room_' + meeting_room;
    let start_time = request[5];
    let end_time = request[6];

    let calendar_id;
    if (meeting_room=="A"){
      calendar_id = room_A_ID;
    } else if (meeting_room=="B"){
      calendar_id = room_B_ID;
    }
    let cal =CalendarApp.getCalendarById(calendar_id)
    let event = cal.createEvent(title, start_time, end_time);

    Logger.log('Create Calendar of '+ meeting_room +' \n'+ title + '\n' + start_time + '\n' + end_time);
  }
}


function autoUpdate(e) {
  // Set a comment on the edited cell to indicate when it was changed.
  let range = e.range;
  let new_row = range.getRow();
  let data = SpreadsheetApp.getActiveSheet().getRange(new_row,2,1,7).getValues();
  setUpCalendar(data)
}

一样提醒,行事历有 Quota 限制。好,那今天就是我们的 D7,明天 D8 会继续介绍结合如何将 Google Calendar 上的事件与更新全部列出到 Google Sheet 上。如果有疑问可以到 Facebook Group,想开很久这次铁人赛才真的开起来哈哈哈,欢迎来当 Founding Member。如果不想错过可以订阅按赞小铃铛(?),也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。


<<:  Day 07-Terraform 写起来不够 DRY 的问题,这解 Terragrunt 你试试看

>>:  IOS、Python自学心得30天 Day-4 TensorFlow 资料处理

[前端暴龙机,Vue2.x 进化 Vue3 ] Day26. Vue3 Composition API 使用(二)

前一篇说到 该怎麽写 data 的资料,找回双向绑定机制 !!! 这边先小小的补充一下 XD 在 O...

Day18 - 物理模拟篇 - 弹力、引力与磁力III - 成为Canvas Ninja ~ 理解2D渲染的精髓

二维弹性模拟(第二部分) 我们在上一篇文做完了整体案例场景的搭建,而我们接下来则是要把後续的物理运算...

Leetcode: 630. Course Schedule III | 含C++笔记

思路: 我一开始看到这题,感觉他像可以用Greedy解法解的问题,然後又想他是III,所以他也可以用...

Day28-终於要进去新手村了-HTML DOM 观念介绍

我们上一篇文章讲到了HTML DOM的阶层以及树状图,接下来我们就举两个例子来厘清一下观念,也让我自...

每个人都该学的30个Python技巧|技巧 9:快速建立数列—range函式(字幕、衬乐、练习)

昨天的巢状判断式有没有成功扰乱你呢?不管懂不懂,都要记得常常去复习哦,这样才会有深刻的印象!(๑•̀...