本部落格已搬遷, 3秒後跳轉...

Google Apps Script ＋ Spread Sheet = 微後端 | Laplace's Lab

Google Apps Script ＋ Spread Sheet = 微後端

| |

若想以Google表單來做一個簡易的daily time tracking，提交一次表單就是一筆數據，但每天的時間總是會適當地分配，如此就得重複填寫並送出表單…這真是一個不聰明的方法，若自己寫的話因為某些原因難以避開CORS…就試著用Apps Script來解決這件事吧，我也沒寫過呢。

Create

Google Apps Script Doc : Web Apps

Requirements for web apps

A script can be published as a web app if it meets these requirements:

-It contains a doGet(e) or doPost(e) function.
-The function returns an HTML service HtmlOutput object or a Content service TextOutput object.

首先建立Google雲端試算表和Apps Script，複製試算表網址“docs.google.com/spreadsheets/d/{Sheet ID}/edit#gid=0”當中的{Sheet ID}，寫Apps Script的時候會需要這部分的資訊。


若找不到Apps Script，點選連結更多應用程式，過濾Google的應用程式就能看到了，然後就將它連結至個人的雲端硬碟。

建好表格欄位後就可以來寫App Script了

Apps Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function doGet(e) {
  // parameter
  var params = e.parameter,
      date = params.Date,
      name = params.Name,
      proj = params.Proj,
      hours = params.Hours,
      des = params.Des;

  // google sheet
  var spreadSheet = SpreadsheetApp.openById({Sheet ID}),       # 需要Sheet ID
      sheet = spreadSheet.getSheets()[0],  // focus on sheet 1
      lastRow = sheet.getLastRow();
  
  // set value <= getRange(row, col)
  sheet.getRange(lastRow + 1, 1).setValue(date);
  sheet.getRange(lastRow + 1, 2).setValue(name);
  sheet.getRange(lastRow + 1, 3).setValue(proj);
  sheet.getRange(lastRow + 1, 4).setValue(hours);
  sheet.getRange(lastRow + 1, 5).setValue(des);

  // complete => return true
  return ContentService.createTextOutput(true);
}

app script寫好之後，可以另外寫個debug.gs來執行看看

1
2
3
4
5
6
7
8
9
10
11
12
function debug() {
  var status = doGet({
      "parameter": {
        "Date" : "2019-05-06",
        "Name" : "LaplaceTW",
        "Proj" : "Proj-19-05-06",
        "Hours" : "4",
        'Des' :"Daily Time Tracking",
      }
   });
  Logger.log("Status : %s" , status);
}

沒問題的話，就部署為網路應用程式，部署成功後可取得API網址

Test


用瀏覽器的開發人員模式console發送request測試

Passing an array into Google Apps Script ?

官方文件對參數的解釋是這樣的:

e.parameter
An object of key/value pairs that correspond to the request parameters. Only the first value is returned for parameters that have multiple values.
{“name”: “alice”, “n”: “1”}

e.parameters
An object similar to e.parameter, but with an array of values for each key
{“name”: [“alice”], “n”: [“1”, “2”]}

但實際測試的時候我也遇到如同Webduino官方教學文所說的，怎麼試就是無法透過e.parameters這個欄位傳遞參數呀！最後我也採用了相同的做法(汗)，直接傳遞字串到Apps Script裡頭再分割數據。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// doGet(e)
var params = e.parameter,
    data = ['d1', 'd2', 'd3', 'd4', 'd5'],
    len = data.length,
    counter = 1.0;

while(counter <= len){
  var index = (counter - 1.0),
      arr = params[data[index]];
  if(arr != undefined){
    arr = arr.split(',');
    // set value <= getRange(row, col)
    sheet.getRange(lastRow + counter, 1).setValue(arr[0]);
    sheet.getRange(lastRow + counter, 2).setValue(arr[1]);
    sheet.getRange(lastRow + counter, 3).setValue(arr[2]);
    sheet.getRange(lastRow + counter, 4).setValue(arr[3]);
    sheet.getRange(lastRow + counter, 5).setValue(arr[4]);
  }
  counter ++;
}

Lock

在透過Ajax對Apps Script發送請求寫入Spread Sheet的測試過程中，原本前端會對User填寫的每筆數據個別送出Ajax請求，雖然過於頻繁並非好的處理方式，但這也才讓我意識到同時間多個寫入請求的衝突及數據覆寫問題，所以後來整合成一次性的Ajax請求，改為在Apps Script處理、分割數據的方式。但這樣還沒解決衝突問題，偉大的Google當然也有考量到潛在的寫入衝突問題:

Class Lock

A representation of a mutual-exclusion lock.

This class allows scripts to make sure that only one instance of the script is executing a given section of code at a time. This is particularly useful for callbacks and triggers, where a user action may cause changes to a shared resource and you want to ensure that aren’t collisions.

藉由鎖定Script的方式，確保每個時刻只會有唯一的讀寫動作，來避免衝突問題。另外，SpreadsheetApp.flush()的使用也需特別留意。

1
2
3
4
5
6
7
8
9
10
// get a script lock for modifying a shared resource
var lock = LockService.getScriptLock();
lock.waitLock(30000);

// Do Something

// you should call SpreadsheetApp.flush() prior to releasing the lock,
// to commit all pending changes to the spreadsheet
SpreadsheetApp.flush();
lock.releaseLock();

Version

這問題耗了我整天的時間在測試…當Script更新並重新部署後，接下來卻發現，Ajax送出的請求一直抓不到參數，每個欄位都是呈現Undefined的狀態，但Debug.gs測試結果卻正常的很!!! 真是見鬼了…折騰半天我從測試結果合理推測部署的Web App所執行的不是最新版本，我在Stack Overflow也有找到相關討論:

建立一個Web App再另外建立Apps Script把相關邏輯寫成library，然後在Web App去呼叫它，這是一種方式，但我覺得以簡單的應用而言太拐彎抹角了。只要修改並善用debug測試，確認沒問題後再部署為一個新版本，如此外部呼叫就不會有上述的舊版本問題。

0%