提升工作效率的秘密武器：學習 GAS 自動化處理表單資料，簡單管理行事曆

發表於 2023-05-27 更新於 2023-08-03 分類於雜談

Google AD

撰寫一篇文章其實真的很花時間，如果你願意「關閉 Adblock (廣告阻擋器)」來支持我的話，我會非常感謝你ヽ(・∀・)ﾉ

Liker 讚賞

這篇筆記文章如果對你有幫助，
你可以考慮花 30 秒登入 LikeCoin 並點擊下方拍手按鈕(最多五下)免費支持與鼓勵我。
或者你可以也可以請我「喝一杯咖啡(Donate)」。

｜

Google 表單與 Google 行事曆是我們工作與生活上很常使用的工具，所以這一篇將會介紹如何使用 GAS 來整合這兩個工具，讓我們可以更有效率的使用這兩個工具唷！

Google

前言

情境模擬

那麼這邊一開始我先提一下這一篇文章的情境跟

首先架設你是一個普通的行政人員，而你會提供 Google 表單給長官填寫，而長官填寫完表單後，你會需要依照 Google Sheet 內容去手動新增到 Google 行事曆。

那麼為了解決這問題，這一篇文章就誕生了，因此我也會以這個情境來做為範例，並使用使用者故事的方式來描述這個情境

使用者可以透過 Google 表單填寫資料
使用者填寫完表單後，資料會自動寫入 Google 試算表
當資料寫入 Google 試算表後，每過十分鐘會自動將資料新增到 Google 行事曆

上面就是我們的使用者故事，如果沒有其他問題的話，我們就可以開始進入正題了！

事前準備

首先這邊我們要先準備幾個東西

Google 帳號
Google 表單
Google 行事曆

Google 帳號基本上應該是不用我在介紹怎麼註冊了，畢竟 Google 帳號應該是幾乎人手一個吧？（應該對吧？）

如果你真的沒有的話，就…麻煩申請一個，否則你無法繼續往下閱讀。

Google 行事曆

首先這邊我們會需要建立一個測試用的行事曆，以便我們稍後可以做一些調整與測試，所以這邊你可以點一下下方連結進入 Google 行事曆

Google 行事曆

接著點選左方的「+」，新增行事曆

新增行事曆

然後選「建立新行事曆」

建立新行事曆

接下來輸入標題、說明與選取時區（通常會自動選取你的時區），你可以依照自己需求填寫，最後點選「建立日曆」就可以了

建立日曆

這樣你的左側就會多出一個日曆了

新日曆

那麼由於後面我們會需要使用到這個日曆的 ID，所以點一下剛剛你新增的日曆並滾到下方找到「整合日曆」並把「日曆 ID」記下來

日曆 ID

日曆 ID 大概會長這樣

1	da39a3ee5e6b4b0d3255bfef95601890afd80709@group.calendar.google.com

(別擔心，上面日曆 ID 只是示範而已，所以不是真的。)

取得之後我們就可以開始建立表單了！

Google 表單

接下來就是要來建立我們的 Google 表單了，你可以點一下下方連結進入 Google 表單

Google 表單

接著點一下「+」新增 Google 表單

Google 表單

接下來你應該會看到初始化的表單畫面，這邊你就可以依照自己需求去做調整

初始化表單

那麼為了方便示範，所以我這邊有做一些調整，你可以參考一下

完成後，就要來連結試算表了，首先先點一下上方的「回覆」，然後接著點一下「連結至試算表」

連結試算表

這邊會要求你選擇要建立新試算表還是連結到現有的試算表，這邊我們選擇「建立新試算表」，名稱建議你改一下比較好，避免找不到

連結試算表

接下來 Google 應該會自動跳出試算表

試算表

如果沒有的話，你也可以透過「回覆」中的「在試算表中查看」來進入試算表

檢視試算表

到目前為止，我們終於把表單與試算表建立完成，接下來就準備進入寫程式的部分囉！

GAS(Google Apps Script)

Google Apps Script

GAS 的全名是 Google Apps Script，簡單來講就是 Google 所提供的一個平台（廢話），為什麼我們要選用 GAS 呢？因為 GAS 可以讓我們使用 JavaScript 來撰寫，而且還可以直接使用 Google 的 API，所以我們如果要操作 Google 相關的服務的話，那麼 GAS 會是非常方便好用的平台。

因此你如果是一名前端工程師的話，你會格外好上手，但如果你是非工程相關背景的人員的話，這邊我也會盡可能搭配截圖說明與詳細的步驟，讓你可以製作出一個屬於自己的自動化表單。

那麼就不廢話了，讓我們開始進入正題吧！

建立 GAS 專案

接下來我們就要來建立一個 GAS(Google Apps Script) 專案，那麼該怎麼建立呢？其實很簡單，找到你剛剛使用 Google 表單關聯的試算表，然後點一下上方的「擴充功能」，然後點一下「App Script」就可以囉

App Script

基本上不意外你目前畫面應該是停留在初始畫面

初始畫面

如果不是的話，可能要請你多加確定一下你的試算表是否正確，或是你是否有點錯地方。

Note
如果你沒有在試算表中點擊「擴充功能」並進入「App Script」的話，可能會有某些問題存在，後面我還會再補充，但這邊會建議還是從 Google 試算表進入 GAS 唷。

接下來我要請你在 myFunction 貼入以下程式碼

1
2
3

function myFunction() {
  Logger.log('Hello World');
}

然後按下「存檔」，接著先選擇「myFunction」再按下「執行」，這時候你應該會看到下方的「執行結果」會出現 Hello World，這代表你的 GAS 專案已經可以正常運作了

這樣我們就可以確定我們的 GAS 是正常運作的。

連接試算表

接下來呢？接下來我們要來連接上試算表取得試算表的資料，你可以先貼入以下程式碼取代原本的 myFunction（不用害怕，下方程式碼我都會補上註解說明）

function getSheetData() {
  // 取得目前的試算表，也就是你的表單所關聯的試算表
  const spreadsheet = SpreadsheetApp.getActiveSheet(); 

  // 設定要取得的試算表資料範圍，這邊我們取得的範圍是 B2 到 F
  const getRange = spreadsheet.getRange('B2:F');

  // 將取得的資料轉換成二維陣列（你可以想像成一種資料格式）
  const displayDataValues =  getRange.getDisplayValues();

  // 將資料印出來，讓你可以在「執行紀錄」看到結果
  Logger.log(displayDataValues);

  // 回傳取得的資料（先不用管這個）
  return displayDataValues
}

接下來一樣按下「存檔」，接著先選擇「myFunction」再按下「執行」，這時候應該會跳出一個「需要授權」的視窗，按一下審查權限

審查權限

接著會跳出另一個視窗，你必須選擇你當前使用 GAS 的帳號，因為 GAS 需要使用你的帳號來取得試算表的資料，所以你必須選擇你當前使用 GAS 的帳號

選擇你的帳號

然後就會跳出一個看起來很可怕的提示「這個應用程式未經 Google 驗證」，點一下「進階」，然後再點一下「前往 XXX 的專案（不安全）」

這個應用程式未經 Google 驗證

Note
這邊不用擔心，因為這只是 Google 本身的安全警告，只要我們確定是自己的專案，就可以放心的點選「前往 XXX 的專案（不安全）」，而這個權限確認的行為只有初次使用時會出現，之後就不會再出現了。

下一個視窗是告知你這個 GAS 專案要存取你的試算表，點一下「允許」就可以了

接下來你應該可以在「執行紀錄」看到一推空格，這是因為我們沒有資料導致的，因此這是正常的

執行紀錄

(如果你發現沒有任何東西出現，你可以嘗試點第二次執行，有時候會因為權限機制關係導致第一次執行失敗)

接下來讓我們回到表單新增一些資料進來（請務必透過表單填寫，不要手動在 Google Sheet 新增）

新增資料

這時候我們再回到 GAS 嘗試執行一次，你應該就可以看到你剛剛填寫的資料出現在「執行紀錄」中了

執行紀錄

那麼為了方便我們稍後的資料處理，所以我將程式碼稍微調整一下改成以下，你可以貼入你的 GAS 專案中

function getSheetData() {
  // 取得目前的試算表，也就是你的表單所關聯的試算表
  const spreadsheet = SpreadsheetApp.getActiveSheet();
  // 設定要取得的試算表資料範圍，這邊我們取得的範圍是 B2 到 F
  const getRange = spreadsheet.getRange('B2:F');
  // 將取得的資料轉換成二維陣列（你可以想像成一種資料格式）
  const displayDataValues =  getRange.getDisplayValues();
  
  // 資料暫存處
  const data = [];

  // 使用迴圈逐一處理每一列的資料
  for (let i = 0; i < displayDataValues.length; i++) {
    // 檢查該列的第一個值是否存在，如果存在表示有資料
    if (displayDataValues[i][0]) {
      // 將資料整理成物件的格式，以便後續使用
      data.push({
        email: displayDataValues[i][0], // 預約者 Email
        name: displayDataValues[i][1], // 預約者姓名
        date: displayDataValues[i][2], // 預約日期
        startTime: displayDataValues[i][3], // 預約開始時間
        endTime: displayDataValues[i][4], // 預約結束時間
      });
    }
  }
  // 將資料印出來，讓你可以在「執行紀錄」看到結果
  Logger.log(data);
  // 回傳取得的資料（先不用管這個）
  return data
}

那麼這邊的 0、1、2、3、4 是什麼意思呢？也就是對應著我們試算表上方的 B、C、D、E、F，也就是我們剛剛在試算表中設定的資料範圍，這邊你可以自行調整，但記得要對應到你的試算表資料範圍

資料範圍

最後這邊點一下左側「程式碼.gs」的點點點符號，選重新命名

重新命名

Note
.gs 是 GAS 的副檔名，重新命名時不用加上副檔名。

將這個檔案名稱改成 getSheetData 即可

getSheetData

到這邊我們取得試算表資料的方式完成了！所以就讓我們繼續下一個步驟吧！

建立類環境變數

這邊標題我特別打了「類環境變數」，因為 GAS 中並沒有環境變數的概念，所以才特別這樣打，避免大家誤會。

首先這邊簡單說一下什麼是環境變數，環境變數是一個可以讓你在不同環境中使用不同的資料的一種機制，例如你在開發環境中的資料庫帳號密碼可能會跟正式環境不同，所以你可以透過環境變數來區分，這樣你就可以在不同環境中使用不同的值。

那麼我們剛剛前面有複製了 Google 行事曆的 ID，為了方便我們未來維護，所以這邊我們將會使用類環境變數的觀念來建立一個變數，這樣未來如果要修改的話，就不用一個一個去修改，只要修改這個變數就可以了。

所以這邊請你點一下檔案旁邊的加號建立一個指令碼，名稱就叫做「Env」

Env

內容如下：

1
2
3

const env = {
  calendarId: 'da39a3ee5e6b4b0d3255bfef95601890afd80709@group.calendar.google.com', // 請貼入你的日曆 ID
}

(再次提醒，上面日曆 ID 只是示範而已，所以不是真的。)

這樣就完成了，超級簡單吧！

連接 Google 行事曆

接下來一樣請你在建立一個檔案叫做「main」，因為我們將會把主要的程式碼寫在這邊，接下來呢？請你貼入以下程式碼

function setCalendar() {
  // 取得試算表資料，它會是一個陣列（一種資料格式）
  const sheetData = getSheetData();

  // 取得 Google 日曆
  const calendar = CalendarApp.getCalendarById(env.calendarId);
}

接下來一樣會出現「需要授權」的視窗，這邊我就不重複示範了，你只需要點一下「審查權限」，然後再點一下「進階」，最後點一下「前往 XXX 的專案（不安全）」，然後再點一下「允許」就可以了，而這也只會出現一次。

基本上這邊你不會出現任何東西，因為我們還沒有寫任何東西，只是為了先解決審查權限而已。

使用進階的時間處理將資料儲存到 Google 行事曆

接下來這邊我先提一下比較進階的做法也比較困難，這邊想請你在左邊建立一個檔案，叫做「parseTime」，然後貼入以下程式碼

function parseTime(date, time) {
  const timeRegex = /([上下]午) (\d+):(\d+):(\d+)/;
  const match = time.match(timeRegex);

  if (!match) {
    throw new Error(`Invalid time format: ${time}`);
  }

  let hours = parseInt(match[2], 10);
  const minutes = parseInt(match[3], 10);
  const seconds = parseInt(match[4], 10);

  if (match[1] === '下午' && hours < 12) {
    hours += 12;
  }

  const parsedTime = new Date(date);
  parsedTime.setHours(hours, minutes, seconds);

  return parsedTime;
}

parseTime

這段程式碼稍微有一點複雜，你只需要知道這一段程式碼是在幫你將儲存在 Google Sheet 的時間轉換成 Google 行事曆可以使用的時間格式就可以了。

讓我們回到 main.gs 中，然後把剩餘的程式碼補上

function setCalendar() {
  // 取得試算表資料，它會是一個陣列（一種資料格式）
  const sheetData = getSheetData();

  // 取得 Google 日曆
  const calendar = CalendarApp.getCalendarById(env.calendarId);

  // 使用迴圈逐一處理每一筆資料
  sheetData.forEach((data) => {
    // 將開始時間與結束時間轉換成 Google 行事曆可以使用的時間格式
    const startTime = parseTime(data.date, data.startTime);
    const endTime = parseTime(data.date, data.endTime);

    // 建立 Google 行事曆的事件，將 Google 試算表的資料寫入到 Google 行事曆中
    calendar.createEvent(data.name, startTime, endTime, {
      description: data.email, // 將預約者的 Email 寫入到 Google 行事曆的描述中
    });
  });
}

接下來你再按一下「執行」，跑完之後你就可以在你的行事曆中看到剛剛你填寫的資料了！

Google 行事曆

恭喜你已經成功將資料新增到 Google 行事曆了！

使用簡單的時間處理將資料儲存到 Google 行事曆

前面說真的，實在太難了！

所以這邊我也額外提供另一種方式來處理時間，因為 Google 行事曆有既定特殊的時間格式，所以才要額外使用 parseTime，但對於非工程師底的人來講，這件事情非常困難，所以我們可以使用 Google 試算表的一個小技巧解決這問題，請你打開你的試算表，然後新增兩個欄位，分別是 開始時間 與 結束時間

試算表調整

接著再開始時間輸入 =D2+E2，而結束時間則是 =D2+F2，後面就以此類推直接往下拉就好

時間處理

透過這方式，我們就可以輕鬆把時間轉換成 Google 行事曆可以使用的格式了，所以我們可以將 parseTime 移除，然後將 main.gs 改成以下

function setCalendar() {
  // 取得試算表資料，它會是一個陣列（一種資料格式）
  const sheetData = getSheetData();

  // 取得 Google 日曆
  const calendar = CalendarApp.getCalendarById(env.calendarId);

  // 使用迴圈逐一處理每一筆資料
  sheetData.forEach((data) => {
    // 建立 Google 行事曆的事件，將 Google 試算表的資料寫入到 Google 行事曆中
    calendar.createEvent(data.name, data.startTime, data.endTime, {
      description: data.email, // 將預約者的 Email 寫入到 Google 行事曆的描述中
    });
  });
}

因此如果你要調整成這樣的話，那麼你的 getSheetData 也要做一些調整，請你將 getSheetData 改成以下

function getSheetData() {
  // 取得目前的試算表，也就是你的表單所關聯的試算表
  const spreadsheet = SpreadsheetApp.getActiveSheet();
  // 設定要取得的試算表資料範圍，這邊我們取得的範圍是 B2 到 H
  const getRange = spreadsheet.getRange('B2:H');
  // 將取得的資料轉換成二維陣列（你可以想像成一種資料格式）
  const displayDataValues =  getRange.getValues(); // <- 將這邊的 getDisplayValues 改成 getValues
  
  // 資料暫存處
  const data = [];

  // 使用迴圈逐一處理每一列的資料
  for (let i = 0; i < displayDataValues.length; i++) {
    // 檢查該列的第一個值是否存在，如果存在表示有資料
    if (displayDataValues[i][0]) {
      // 將資料整理成物件的格式，以便後續使用
      data.push({
        email: displayDataValues[i][0], // 預約者 Email
        name: displayDataValues[i][1], // 預約者姓名
        date: displayDataValues[i][2], // 預約日期
        startTime: displayDataValues[i][5], // 預約開始時間
        endTime: displayDataValues[i][6], // 預約結束時間
      });
    }
  }
  // 將資料印出來，讓你可以在「執行紀錄」看到結果
  Logger.log(data);
  // 回傳取得的資料（先不用管這個）
  return data
}

Note
getValues 跟 getDisplayValues 差別在於，一個是取得試算表的原始資料，一個是取得試算表的顯示資料。

重複新增的問題

接下來你應該會發現一個問題，只要你重複執行 setCalendar，你的行事曆就會一直重複新增已經存在的資料，所以這邊要請你建立一個檔案叫做「isEventExist」，然後貼入以下程式碼

function isEventExist(calendar, name, startTime, endTime) {
  const events = calendar.getEvents(startTime, endTime, {
    search: name,
  });

  return events.length > 0;
}

接著將 main.gs 改成以下

function setCalendar() {
  // 取得試算表資料，它會是一個陣列（一種資料格式）
  const sheetData = getSheetData();

  // 取得 Google 日曆
  const calendar = CalendarApp.getCalendarById(env.calendarId);

  // 使用迴圈逐一處理每一筆資料
  sheetData.forEach((data) => {
    // 檢查該事件是否已經存在
    const isExist = isEventExist(calendar, data.name, data.startTime, data.endTime);

    // 如果該事件不存在，則建立 Google 行事曆的事件，將 Google 試算表的資料寫入到 Google 行事曆中
    if (!isExist) {
      calendar.createEvent(data.name, data.startTime, data.endTime, {
        description: data.email, // 將預約者的 Email 寫入到 Google 行事曆的描述中
      });
    }
  });
}