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

## 簡介 Google Apps Script

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

## 建立 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)`
:::warning
一定要是這兩個名稱，[因為這是 Apps Script 規定的](https://developers.google.com/apps-script/guides/web#url_parameters)，至於用的方法如函式的名稱，用 `GET` 的方式呼叫，則函式命名 `doGet(e)`，`POST` 則用 `doPost(e)`。
:::


function doPost(e) {

}

然後裡面就設一個區域變數 `params` 來接收 `e.parameter` 所解析出來的值 ( [e.parameter 的規範](https://developers.google.com/apps-script/guides/web#url_parameters) )

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



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

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


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


參考資料
---
* [Google Apps Script 官方文件 - spreadsheet method](https://developers.google.com/apps-script/reference/spreadsheet/sheet)
* [oxxostudio - 簡易後端實作 ( Google Apps Script )](https://www.oxxostudio.tw/articles/201805/backend-apps-script.html)
* [Google sheet 試算表表單串接api](https://iandays.com/2018/02/08/googleformapi/index.html)
* [Webduino 學習手冊 - Google 試算表 ( 原理 )](https://tutorials.webduino.io/zh-tw/docs/socket/useful/google-sheet-1.html)

其他延伸資料
---
* [實作 LINE 聊天機器人 ( Google Apps Script )](https://www.oxxostudio.tw/articles/201804/line-bot-apps-script.html)