用 Google Apps Script 操作試算表 (3)延遲寫入資料庫 + 使用族群

過去在相關的系列文曾多次說明，Google 試算表適合當成小型資料庫來使用，不建議當成真正的資料庫來運作，也就是不能拿來作為規模比較大的商業使用，為什麼呢？

試算表是免費的服務，在運作上、效能上自然比不上獨立運行的主機。而且 Google 提供的某些 API 也有每日配額的限制，除非付費提升額度。

除了以上提到的限制，試算表在寫入資料時，是採取平行運算。這也就是說，發生了同一時間、多筆寫入請求時，是有可能產生意想不到的錯誤、或很難診斷的 bug。

本篇除了說明如何解決這個大問題，也大致介紹一下，適合使用 Google 試算表來當資料庫的族群。

(圖片出處: pixabay.com)

一、適合使用的族群

既然拿試算表當資料庫的限制很多，問題也不少，為何還要採用這樣的方案呢？主要的原因當然是「免費」。

實際上，架設資料庫伺服器的環境及成本太高，一旦架設後，無論你有沒有使用，每個月就是要燒錢。不管提供的服務是賺錢或賠錢，都要持續支出這一項固定成本，如果期初沒有一定的資本額，是很難做這樣的投資。

雖然試算表資料庫的效能不佳，不過有些族群，不需要資料庫有多好的效能，也沒多大的流量，那麼就很適合使用，例如：

• 因教育目的而使用
• 新創事業
• (比較小的)中小企業
• 個人使用

以 "新創事業" 來說，創新的服務其實能否存活都是未知數，也許 1~2 年內就倒了，大手筆建置資料庫也滿冒險的。那麼 Google 提供的各種免費服務也算是「天使投資人」，如果事業真的做成功了、流量起來了、有賺到錢了，那麼看要付費放寬 Google 的額度限制，或是再來考慮架設自己的資料庫環境也可以。

如同 +Pulipuli Chen 在「利用 Google 試算表當小型資料庫 (4)」的留言，Google 等於免費提供 "Backend as a Service"、"Serverless" 這樣的環境，在事業初期省去很多處理後端的麻煩事，可以專心在前端發揮各種想像力、創造力。

二、延遲寫入的必要性

試算表使用初期，在流量不大時，很難發生時間相近時，有多筆寫入資料的請求，很可能相隔幾小時才有一次寫入資料的機會。

但知名度提升、或舉辦活動時，就會常常發生幾秒鐘之內需要寫入多筆資料，類似演唱會要搶票的狀況，那麼試算表會發生什麼狀況呢？

以最常見的「會員系統」來舉例，每新加入一個會員，會員編號就要順編下去。而順編之前，要先知道試算表原本有幾筆資料，假設查到原本有 10 個會員，編號就要從 11 開始。

當短時間內同時有兩、三個會員加入，由於試算表處理請求是平行運算，這幾個寫入資料的請求，可能同時查到原本有 10 個會員，導致這 3 個新會員，編號都成了 11 號，這就麻煩了。

因此處理試算表的各種請求，平行運算會是個麻煩，必須想辦法變成依序處理，才不會造成各種邏輯錯誤。

三、延遲語法

Google 發現試算表同時寫入的問題後，發佈了這篇「Concurrency and Google Apps Script」說明，使用「Lock Service」用來解決平行運算的問題。

從字面上看，取名 Lock 意味把時間鎖住的意思，主要有兩種用法，以下都用官方範例來說明：

2018.2.28 更新：因官方更改 api，原本的 getPublicLock() 需要改用 getScriptLock()。

1. 強制等待一段時間

function test() {
var lock = LockService.getScriptLock(); // 啟動 LockService, 凍結試算表其他動作

lock.waitLock(30000); // 凍結 30 秒

// 這裡的程式碼，是凍結期間所有要做的事情

lock.releaseLock(); // 解除凍結
}
所有的使用說明，請看註解文字即可。

事情都做完後，執行 lock.releaseLock()，可以讓其他平行運算的程式開始執行。

這段程式碼的風險是，我們設定凍結的秒數期間，有沒有辦法做完所有的事？如果不行的話，時間一到，其他的平行運算就會開始動作。


2. 測試等待一段時間

為了解決上述的風險，官方提供另外一個方式，讓我們可以知道凍結期間有沒有完成任務，請見以下範例：

function test() {
var lock = LockService.getScriptLock();

lock.tryLock(30000); // 嘗試凍結 30 秒

if (lock.hasLock()) {
// 這裡的程式碼，是凍結期間所有要做的事情
} else {
// 凍結期間萬一事情沒做完，也許可以寄 email 通知給自己
// Gmail API 的參數分別是 email, 主題, 內容
GmailApp.sendEmail("自己的 email", "凍結失敗", "凍結 30 秒還不夠");
}
}
使用說明請看註解文字。

這一段利用 lock.tryLock() 來嘗試凍結一段時間，如果發現事情來不及完成時，則執行備案的程式碼，例如寄 email 通知給自己，好知道要進入試算表資料庫檢查看看，是否有異狀或慘劇發生。

四、小結

官方提供的延遲語法，看起來仍然無法完美地讓平行運算改為依序執行，只能根據自己長久累積的經驗，來嘗試究竟應該設定多久的延遲時間。當然，如果把數字設得很大也是一種解決方式。

不過跟以往比起來，已經能解決多數狀況了。在熟悉本系列文後，我相信 Google Apps Script 搭配試算表，可以成為幾近完美的小型資料庫方案。

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

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

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