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

12 則留言:

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

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

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

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

      刪除
  3. 我出現Cross Domain的問題,反覆測試多次後,你的Google 試算表少一個步驟,要共用開放所有人可編輯,才能寫入表單資料,謝謝您的教學喔~寫得很好

    回覆刪除
    回覆
    1. 您好,試算表不需要共用喔,否則所有人都看得到資料,就失去使用 doPost 的意義了。

      基本上試算表的資料,是靠 apps script 的指令產生的,如果無法寫入的話 → 代表 apps script 沒有存取試算表的權限 → 代表你的 apps script 可能不是按照本文流程所製作的

      不然的話,請檢查「四、部署為網路應用程式」,你的設定有沒有正確

      刪除
  4. 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,
    address = para.address,
    phone = para.phone;
    sheet1.appendRow([name, address, phone]); // 插入一列新的資料
    }

    1.我複製您的程式碼後,在GOOGLE excel上 跟您的不同,您A的地方是日期,可我用的A的地方就是name

    回覆刪除
    回覆
    1. 是的,按照我的範例程式碼不會有日期,有日期的 code 是我另外寫的,供我自己紀錄用。

      刪除
    2. 謝謝您的回覆,如果我也想要加上日期要輸入那些程式碼呢?

      刪除
    3. 本篇提供的是基礎概念,進階的需求請找 js 教學或 Google apps script、Google 試算表說明書,否則要視為客製案子。

      刪除
  5. 測試時有出現圖片上的錯誤。
    發布後 按送出 執行寫值進去EXCEL,但有另開新視窗上面文字是 (指令碼已完成,但未傳回任何值。)

    回覆刪除
    回覆
    1. 你漏了附上圖片喔~
      要有回傳值,請閱讀系列文第二篇

      刪除

張貼留言注意事項:

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

TOP