來到了第六天,我們要來操作 Google Calendar,一樣會先從較簡單的案例開始。但一樣先講結論,如果你很急著用於跟別人約時間,我個人蠻推薦 Calendly 或 GreenHouse,之前面試時不少外商都直接用這兩種。但如果你是想要...跟很多 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。如果有超過的可能可以考慮直接用付費軟體。
錄製完才發現我已經寫到第六天了,拍謝讓我穿越時空一下。一樣執行時會有「需要驗證」出現,讓我借用一下 D2 的影片。
那假設今天有兩位同仁填寫了借用表,想在 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 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 要的格式。理解格式後,接著就是與日曆結合了。
在這邊我們用 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())
}
好,那今天我們知道了要怎麼樣:
一樣提醒,創建行事曆會有 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!