2017年3月2日

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

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

Wayne Fu 0 A+
過去在相關的系列文曾多次說明,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 操作試算表系列文章:

0 0
如這篇文章對你有幫助,歡迎「分享」到 FB、「追蹤」粉絲團、「訂閱」最新文章

8 則留言:

  1. 這很好用~只要寫好一個參數只做一件事,就滿安全的了
    最好用的地方就是可以跨越帳號限制
    一般帳號共用的,無法被 google apps 的帳號檢視(舊方法就不能用)
    用這方法就可以看到資料

    回覆刪除
  2. 不好意思 能請問一下"getPublicLock()"的功用嗎?
    看官方文件似乎沒有出現這個東西
    還是功能完全等於"getScriptLock()"
    因為你說還附贈凍結試算表
    凍結試算表應該是用"getDocumentLock()"吧?
    那user的部分也凍結了嗎?

    回覆刪除
    回覆
    1. 謝謝您的回報,查了一下文件,原來 api 去年有更新了。

      已修改本文程式碼,請將原本的 getPublicLock() 改為 getScriptLock() 即可。

      刪除
  3. 您好,版大的文章真是非常的實用,解決了我很多的問題,目前有一事請教,我是用excel vba 把本地的欄位一直上傳到google試算表,目前還算順利,但有一個問題是當google的資料超過某個數量時,例如已上傳了2000行,當上傳到第2001行時,希望把A2那行刪除,讓google試算表永遠保持最新的2000行即可。不知道在google這裡要怎麼增加程序,來達到此目的,感謝分享,謝謝

    回覆刪除
    回覆
    1. 你好,如果熟悉 js 的話,請參考官方文件 https://developers.google.com/apps-script/reference/spreadsheet/

      利用 apps script 來操作 google spreadsheet

      刪除
    2. 版大您好,說實話我還是初學者,我的apps script 是網路上抓下來略做修改而來,所以對於js等等並不熟悉,這個功能困擾我很久,以下是我的程式碼,不知要修改或增加哪寫程式碼,才能做到我想要的功能,Sorry若有冒犯還請見諒,以下是我目前的apps Script,我試著用spreadsheet來用,但sheet1的資料還是無法刪除,(我手動執行是可以刪除第二行)但不知道要如何放在主程式中,讓他自動執行。再次感謝

      function del_row() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('A2:K2').activate();
      spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
      spreadsheet.getRange('2:2').activate();
      };



      以下是主程式,

      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,
      saleda = para.saleda,

      saledaF = para.saledaF,
      saledaG = para.saledaG,
      saledaH = para.saledaH,
      saledaI = para.saledaI,
      saledaJ = para.saledaJ,
      saledaK = para.saledaK,
      saledaL = para.saledaL;


      sheet1.insertRow([name, sex, remark, saleda, saledaF, saledaG, saledaH, saledaI, saledaJ, saledaK, saledaL]);


      }

      刪除
    3. 有試過用將del_row()用條件方式觸發條件方式執行,還是無法觸發,感覺上insertRow不會觸動(我手動去修改sheet1的資料,確實有觸動條件,刪除了一行),不知哪裡有問題?若方便再請指點一番,感恩。

      刪除

張貼留言注意事項:

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

TOP