實作 Google 試算表表單串接API (Google Apps Script)

tags: googleCloud

使用 Google 表單建立了一份 問題回報表單 作為客服、會計部門和 RD 部門之間的專案需求單。雖然開放 回報報表 的讀取權限可以讓申請人追蹤目前的需求處理進度，但在查詢進度上不是非常友善，RD 也不容易追蹤尚未處理的需求。因此若能將 Google 試算表 的資料抓回 CRM 並套用 datatables，不但能使用 datatables 的篩選及排序功能，也能將資料再處理成方便檢視的樣式，如：Google 表單的圖片上傳後，在 Google 試算表上是一串以逗號分隔的網址字串，把字串傳回 CRM 後可做成另開檢視的按鈕，就不需分別複製圖片網址。

簡介 Google Apps Script

Google Apps Script 是一種基於 JavaScript 的雲端腳本語言用來擴展 Google Apps (Gamil、雲端硬碟、文件、試算表、簡報 … 等) ，像是擴充套件，以及建立 Web 應用程式，雖然有 額度限制 但對小專案來說已經足夠。

建立 Apps Script 專案

STEP 1 : 新增 Google Apps Script

登入 Google 的雲端硬碟後，從左側選單點選「新增」，新增 Google Apps Script。

若選單中沒有出現 Google Apps Script，則點選最下方的 「連結更多應用程式」，搜尋並「連結」 Google Apps Script 就會出現在選單中了。

STEP 2 : 點選後會開啟一個新專案的編輯畫面，先把這個專案的名稱改為你的自定義名稱。

STEP 3 : 開始撰寫函式

此時將編輯畫面上已存在的預設的函式 myFunction() 改為doGet(e) 或是 doPost(e)

一定要是這兩個名稱，因為這是 Apps Script 規定的，至於用的方法如函式的名稱，用 GET 的方式呼叫，則函式命名 doGet(e)，POST 則用 doPost(e)。

function doPost(e) {

}

然後裡面就設一個區域變數 params 來接收 e.parameter 所解析出來的值 ( e.parameter 的規範 )

function doPost(e) {
    var params = e.parameter;
}

再來宣告對應的試算表，以及試算表的分頁：

• 試算表的取得方法可用： openById(id) 或 openByUrl(url)， Id 是指這份試算表的 Id (如下圖)，Url 則是試算表的完整網址，此次實作使用 openById 為例。

• 分頁取得方法則有： getSheetByName(name) 依分頁名稱取得，及 getSheets() 直接取得整份文件的分頁，此次實作使用 getSheets 再指定第一個分頁 getSheets()[0]。

因此我們會得到目前的函式如下：

function doPost(e) {
    var SpreadSheet = SpreadsheetApp.openById("填入你的試算表ID");
    var Sheet = SpreadSheet.getSheets()[0];
}

getLastRow 的方法可以讓我們取得最後一行，像是我們資料目前有135行，那最後一行就是135行。
接著我們再使用 getSheetValues(startRow, startColumn, numRows, numColumns) 選取我們要的資料範圍。

function doPost(e) {
    var SpreadSheet = SpreadsheetApp.openById("填入你的試算表ID");
    var Sheet = SpreadSheet.getSheets()[0];
    var LastRow = Sheet.getLastRow();
    var data = Sheet.getSheetValues(2, 1, LastRow, 9);
}

最後回傳參數的方法依需求有三種方式：

• 字串

return ContentService.createTextOutput(字串);

• HTML

return HtmlService.createHtmlOutput(HTML);

• JSON

  return ContentService.createTextOutput(JSON.stringify(result))
         .setMimeType(ContentService.MimeType.JSON);

這樣就完成我們這次範例 Apps Script 部分的程式了:

function doPost(e) {
    var SpreadSheet = SpreadsheetApp.openById("填入你的試算表ID");
    var Sheet = SpreadSheet.getSheets()[0];
    var LastRow = Sheet.getLastRow();
    var data = Sheet.getSheetValues(2, 1, LastRow, 9);

    return ContentService.createTextOutput(JSON.stringify(data))
         .setMimeType(ContentService.MimeType.JSON);
}

STEP 3 : 部署

完成程式後，我們必須將程式發佈成網路應用程式才能被使用。點選上方選單「發佈 > 部署為網路應用程式」，這時可能會出現授權視窗，一路允許下去就可以了。

在專案版本欄位選擇「新增」並填上你的版本號，最後一項「具有應傭程式存取權的使用者」必須選擇「任何人，甚至是匿名者」，你的應用程式才能被讀取。

每次更新程式後都必須再發佈一次，且都需再新增一個新的版本號

點選部署後，就可以得到你的 API 網址了。

參考資料

• Google Apps Script 官方文件 - spreadsheet method
• oxxostudio - 簡易後端實作 ( Google Apps Script )
• Google sheet 試算表表單串接api
• Webduino 學習手冊 - Google 試算表 ( 原理 )

其他延伸資料

• 實作 LINE 聊天機器人 ( Google Apps Script )