用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料

過去曾寫過「利用 Google 試算表當小型資料庫﹍讀取+寫入+搜尋」等 4 篇系列文，簡單列一下該系列使用方案的優缺點：

• 讀取、搜尋資料時，需要發佈或分享試算表，因此適合安全性較低的資料。
• 查詢資料很方便，Google 提供了各種 API，也可使用資料庫 SQL 語法。
• 因為有 API，所以程式比較好寫。

本篇開始的系列文，將使用 Google Apps Script (GAS) 來操作試算表，優缺點則完全相反：

• 不需發佈或分享試算表，適合安全性高的資料。
• 查詢資料不方便，免費的 API 有限制。
• 如超過 API 使用限制、又不想付費，搜尋程式就要自己寫。

因此本系列文，比較適合進階的前端工程師參考，需要具備 HTML / JS / JQUERY 基礎。

(圖片出處: pixabay.com)

一、GAS 運作原理

先簡單說明一下原理，每個 Google 試算表都提供了指令碼(GAS)的功能，可寫 script 來操作讀取、寫入的流程。

Google 更強的地方，是可以將每個試算表的指令碼發佈成網頁應用程式，直接從外部來呼叫程式，也就是從我們的網頁/部落格，直接傳送 get/post 請求給發佈的網頁應用程式，就能控制試算表的讀取、寫入了。

既然讀者是為了安全的資料傳輸，而選擇本系列文的操作方式，那麼本系列文只說明 post 請求的處理方式。

而 GAS 處理 post 請求的函數為 doPost，之後會以此來舉例。

二、製作試算表

因為是寫給進階使用者看的，就不提供詳細的圖文說明了，製作試算表這簡易的過程，用文字應該就可以了：

• 進入「Google Drive」首頁、登入帳號。
• 按「新增」→ 選擇「Google 試算表」
• 填入試算表標題
• 試算表第一列填入自訂的欄位標題
• 工作表名稱建議改用英文，例如 "sheet1"，方便程式操作。

這些步驟很簡單就完成了，效果就像以下這個試算表，本篇將以此來當範例說明：

• https://docs.google.com/spreadsheets/d/106tP9D89pnEgvZTuM3_ahMJRPjCSD3qthth-GEhGMIE/edit?usp=sharing

三、使用 GAS 寫入試算表

製作完試算表後，按「工具」→「指令碼編輯器」，就可以開始寫 Apps Script 了：




上方的標題請修改，紅框標示的區塊，就是執行 Apps Script 指令碼的地方：




可參考以下的範例程式碼：

var ss = SpreadsheetApp.getActiveSpreadsheet(),
sheet1 = ss.getSheetByName("sheet1"); // "sheet1" 改成你的工作表名稱

function doPost(e) {
var para = e.parameter, // 存放 post 所有傳送的參數
method = para.method;

if (method == "write") {
write_data(para);
}
if (method == "read") {
// 這裡放讀取資料的語法 下一篇說明
}

}

function write_data(para) {
var name = para.name,
sex = para.sex,
remark = para.remark;
sheet1.appendRow([name, sex, remark]); // 插入一列新的資料
}
簡單說明一下運作方式：

• doPost 會處理傳過來的 post 請求
• post 傳送的參數會放在 e.parameter，以物件方式儲存
• doPost 為了同時處理讀取、寫入的請求，傳遞的參數 method 的值分成 "write" 或 "read" 來處理
• 參數 method 給予 "write" 值時，執行 write_data 函數，寫入試算表
• 傳送的參數名稱與 para 的屬性名稱要吻合，才能讀到資料；例如參數名稱是 name，就要用 para.name 來讀取
• 最後用內建函數 appendRow 插入一列新資料

為了測試 doPost 能否正常運作，可以執行下面這個測試函數：

function test(){
var e = {
parameter:{
"method": "write",
"name": "Wayne",
"sex": "male",
"remark": "測試寫入功能"
}
}
doPost(e);
}


四、部署為網路應用程式

儲存程式碼後，按「檔案」→「管理版本」，可以進行管理：



填寫備註文字後，按下「儲存新版本」，請記下你要發佈的版本號，如上圖是 "1"。


按下確定後，按「發佈」→「部署為網路應用程式」：



選擇版本號，選擇「任何人，甚至是匿名者」，按「部署」→ 允許所有權限




如上圖，記下自己的網路應用程式網址，就像下面這樣的格式，之後會用到：

https://script.google.com/macros/s/AKfycbxN6sBikcLtDNxzZU1-hrylbGUzJ1qD2yXjZjmiw9ra7hPHPgo/exec

五、用 jQuery 送出 post 請求

熟悉 jQuery 操作的話，可使用 Ajax 來傳送 post 請求，以下是個簡單的範例：

<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<script>
$.ajax({
type: "post",
data: {
"method": "write",
"name": "Wayne",
"sex": "male",
"remark": "測試寫入功能"
},
url: "https://script.google.com/macros/s/AKfycbxN6sBikcLtDNxzZU1-hrylbGUzJ1qD2yXjZjmiw9ra7hPHPgo/exec" // 填入網路應用程式網址
});
</script>
以上 url 參數紅色網址字串，請改為自己的網路應用程式網址。

method、name、sex、remark 等參數名稱，需要與 GAS 的設定一樣。

想要測試效果，直接執行以上程式碼，重整本篇文章頁面，就可看到資料寫入下面這個範例試算表了：

六、用表單送出 post 請求

除了用 JS 控制，更常見的是使用表單傳送資料，以下是一個簡單的 form 表格範例 HTML 碼，用來輸入要寫入試算表的資料：

<iframe name="hidden_iframe" style="display:none;"></iframe>
<form action="https://script.google.com/macros/s/AKfycbxN6sBikcLtDNxzZU1-hrylbGUzJ1qD2yXjZjmiw9ra7hPHPgo/exec" method="post" target="hidden_iframe">
<input type="hidden" name="method" value="write" />
<input type="text" name="name" placeholder="填入稱謂" title="填入稱謂" /><br/>
<input type="text" name="sex" placeholder="填入性別" title="填入性別" /><br/>
<input type="text" name="remark" placeholder="填入註解" title="填入註解" /><br/>
<input type="submit" value="提交" />
</form>
以上紅字的網址參數請改為自己的網路應用程式網址。

method、name、sex、remark 等參數名稱，需要與 GAS 的設定一樣。


效果類似下面這個表格，可自行測試。輸入資料後，重整本篇文章頁面，就可看到資料寫入前面的範例試算表了。

看完 GAS 寫入試算表資料庫的教學後，下一篇要說明的是讀取試算表的功能。

用 Google Apps Script 操作試算表系列文章：

製作試算表資料庫系列文章：

• 利用 Google 試算表當小型資料庫 (1)製作資料庫__更新
• 利用 Google 試算表當小型資料庫 (2)讀取、儲存資料庫__更新
• 利用 Google 試算表當小型資料庫 (3)簡易查詢與排序 (及新版試算表讀取流程)
• 利用 Google 試算表當小型資料庫 (4)使用 SQL 語法讓搜尋功能更強大