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

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

A+
google-apps-script-spreadsheet-write-data-用 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",方便程式操作。

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




三、使用 GAS 寫入試算表


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

google-apps-script-spreadsheet-write-data-1-用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料


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

google-apps-script-spreadsheet-write-data-2-用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料


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

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);
}




四、部署為網路應用程式


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

google-apps-script-spreadsheet-write-data-3-用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料

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


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

google-apps-script-spreadsheet-write-data-4-用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料

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


google-apps-script-spreadsheet-write-data-5-用 Google Apps Script 操作試算表 (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 碼,用來輸入要寫入試算表的資料:

<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 操作試算表系列文章:

4 則留言:

  1. 我複製了你的程式碼
    然後在我的IIS伺服器上編輯了一個SHEET.HTM
    最後用了瀏覽器開了SHEET.HTM這個網頁檔
    是可以執行,但是有錯誤訊息...(指令碼已完成,但未傳回任何值。)
    回去你的SPREADSHEET,可以看到我POST上的訊息,但是是亂碼
    請問我是哪裡錯了?還是漏了甚麼?

    回覆刪除
    回覆
    1. 我沒有你的作業環境可以測試,只能猜是不是編碼設定的問題
      你可以用不同的環境交叉測試來找原因

      刪除
  2. 完全照你的範例操作,修改了連結,sheet1也改成表單名稱,最後出現Script function not found: doPost,是少複製了嗎?

    回覆刪除
    回覆
    1. 我無法看到你的所有作業環境,要請自行 debug 了. 最簡單的方法,就是完全跟我做的一模一樣,等成功後,再來逐步改成你的 code,這樣 debug 比較快。

      刪除

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
◎ 請在相關文章留言,與文章無關的主題請至「Blogger 中文論壇」。
◎ 提問若無法提供足夠的資訊供判斷,可能會被無視。建議先參考這篇「Blogger 提問技巧及注意事項」。
◎ CSS 相關問題非免費諮詢,建議使用「Chrome 開發人員工具」尋找答案。
◎ 手機版相關問題請參考「Blogger 行動版範本的特質」→「三、行動版範本不一定能執行網頁版工具」。
◎ 非官方範本問題、或貴站為商業網站 ,請參考「本站諮詢頁面」→「1. 諮詢服務」
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 為了過濾垃圾留言,所有留言不會即時發佈,請稍待片刻。
◎ 本站「已關閉自刪留言功能」。

TOP