iT邦幫忙

2021 iThome 鐵人賽

DAY 6
0
Modern Web

整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度系列 第 6

D6 - 如何用 Google Apps Script 將 Google 表單收到的時間同步在 Google Calendar 上?

  • 分享至 

  • xImage
  •  

來到了第六天,我們要來操作 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,我們明天見。

====

補充

有許多小夥伴卡在日期(Date)的部分。因為我使用的是英文版的表單,所以期待日期 request 收到的格式會是 2021/9/5 與 09:00,而許多夥伴在使用的格式在日期/時間部分可能會回傳中文,或是被 Excel 的預設時間格式所騙,例如,點入只顯示時間欄的任一 Cell,顯示的都會是完整時間(e.g. 看起來是 19:00,但實際點擊會 2023/01/01 19:00)。

因為日期是個大坑,我的建議是
(1)請使用者輸入「開始時間」(整數,24 小時制)與「使用小時」(整數),會比較好運算。
(2)對 F 欄位使用 getHour 與 getMiunte,再搭配 Date Object 使用。參考連結:https://www.w3schools.com/jsref/jsref_gethours.asp

Welcome join the Facebook Group to feedback to me!


上一篇
D5 - 如何用 Google Apps Script 搭配 HTML 客製 Google 表單的回應信件?
下一篇
D7 - 如何用 Google Apps Script 將 Google 表單的回應即時同步在多個行事曆上?
系列文
整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
s303112009
iT邦新手 5 級 ‧ 2022-09-03 15:50:47

您好,想詢問一下,按方程式碼輸入完成後,沒有自動新增至google日曆的原因,謝謝您

我要留言

立即登入留言