進階使用Google|將自己設計的表單安全的送進Google Sheets (Create新增)
進擊的 Google 小技巧 (1)
Google Apps Script是個讓人能夠以快速、簡單的方式開發App並整合Google產品的開發平台。它讓你可以直接在瀏覽器中的editor編輯程式碼並且直接在Google主機上執行,省去了架設主機的程序。
最常見的應用不外乎是將自己設計的HTML表單結合Google Sheet儲存使用者送出的表單資料,跳過Google Form,直接將AJAX Request發送至Google Sheet。
設定Google Sheet
建立一個儲存表單資料的spreadsheet,並在第一列的欄位中填入與HTML表單欄位名稱(input name)相對應的名稱。此外,欄位的順序並不講求要一致但名稱是case-sensitive的。
建立Google Script
表單建立完成之後,緊接著是最重要的部分:撰寫接收表單資料的Script。
點選位於剛建立好的表單上方選單中的 [工具] 下拉選單中的 [指令碼編輯器]。
一打開Google Script可以看到一個既有名為myFunction的function。
大概理解一下該如何撰寫Google Apps Script:
Requirements for web apps
A script can be published as a web app if it meets these requirements:
- It contains a
doGet(e)
ordoPost(e)
function. - The function returns an HTML service
HtmlOutput
object or a Content serviceTextOutput
object.
呼叫google script
scriptURL?name=Hayley&age=26
定義doGet(e):從e parameter中挑選name及age這兩個變數,並使用以這兩個變數構成的字串建立一個TextOutput物件。
function doGet(e){var param = e.parameter;
var name = param.name;
var age = param.age;var msg = 'name: ' + name + ', age: ' + age;return ContentService.createTextOutput(msg);}
頁面得到的顯示如下:
name: Hayley, age: 26
接著看看另一個建立HtmlOutput的範例:
function doGet(e) {var params = JSON.stringify(e);
return HtmlService.createHtmlOutput(params);}
頁面得到的顯示如下:
"queryString": “name=Hayley&age=26”,
"parameter": {
"name": "Hayley",
"age": "26"
},
"contextPath": "",
"parameters": {
"name": [
"Hayley
"],
"age": [
"26"
]
}, "contentLength": -1
“ When a user visits an app or a program sends the app an HTTP GET
request, Apps Script runs the function doGet(e)
. ”
當user向server發出網頁請求(request)的同時,browser會送出一個HTTP GET request,即會呼叫doGet()這個function。
複製/貼上 下方的gist至編輯器
大概理解一下整段程式碼在做什麼。
想像一下如果同時有多位使用者送出表單,有多筆的資料同時要寫入資料表是否會有衝突產生?為了避免上述的情形發生,必須要使用LockService來加以防範。
LockService的lock,建立了使用者在執行程式碼上的限制。以下方的getScriptLock()
為例,它能確保區段的程式碼在指定的時間範圍內無法被同時多次地執行,其他使用者只能排隊等候,猶如在收銀台前等待結帳的顧客。若程式碼無法在指定的時間範圍內執行完畢,會產生錯誤的可能性就大了。
var lock = LockService.getScriptLock();
lock.waitLock(30000); // 獨佔的30秒
// 在lock的這段期間內,所要處理的事情lock.releaseLock();
如同釋放記憶體一般,在指定區段的程式碼執行完畢後記得釋放lock。即便忘了做這個動作,所有的lock也會在script執行完畢後被釋放,但如果能隨手釋放便能減少錯誤發生的機率。
try... catch
內的程式碼,嘗試要在lock住的30秒內完成資料寫入檔案的動作。
首先要開啟試算表(Spreadsheet)中名稱為sheet1的工作表(Sheet)。
var SHEET_NAME = 'sheet1';// 開啟給定ID的Spreadsheet檔案
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty('key'));
var sheet = doc.getSheetByName(SHEET_NAME);
不知讀者是否內心有個疑問,不知道這個”key”是從哪來的?
仔細看一下程式碼第11行的註解,這個script在被deploy之前先執行了一個setup()
function。
var SCRIPT_PROP = PropertiesService.getScriptProperties();function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty('key', doc.getId());
}
大概理解一下,PropertiesService提供了一個類似Object型態的Properties儲存空間,以key: value
這樣成對的方式來儲存資料。在setup()
function中,我們將Spreadsheet的ID存入Properties中,而其對應的key值為”key”。
接著就是重頭戲—資料寫入 的部分了。
// 指定header為工作表中的第幾列,這裡我們預設值為1,
var headRow = e.parameter.header_row || 1;
// 取得資料表欄位名稱
var headers = sheet.getRange(1, 1, 1, sheet.getLastColunm()).getValues()[0];
呼叫sheet.getRange(row, column, numRows, numColumns)
方法會得到一個以(row, column)做為左上方的欄位,和列數(numRows)、行數(numColumns)所圍出的一個範圍(Range)。
這裡取得工作表上的欄位名稱是為了與表單上的的欄位名稱做比對,以便依序地把值加入到陣列中。
var row = [];for (i in headers) { // for/in loop
if (headers[i] === 'Timestamp') {
// 參照上方的excel截圖,第一個欄位(headers[0])及為'Timestamp'
// 呼叫new Date()取得現在的時間,作為送出表單的時間,儲存於row[0]中
row.push(new Date());
} else {
// 表單上的資料是利用工作表的欄位名稱來做對應,並依序儲存到row陣列中
row.push(e.parameter(headers[i]));
}
}
最後就是把該筆資料塞入工作表
sheet.getRange(nextRow, 1, 1, row.length).setValues(row);
最後剩下來的部分就是給送出表單的使用者feedback,顯示出成功或是失敗的訊息。
一不小心就碎念了這麼多,很高興您這麼有耐心的看完了,希望本篇對您有所幫助^^
喜歡本篇文章,歡迎按下Medium的「claps」以及LikeCoin的「Like」給我們鼓勵鼓勵!
若是長按50下我們會明白你更喜歡這類型的文章,會讓之後的編輯盡量發揮這部分的內容!
LikeCoin的「Like」是能讓我們收到一點點支持的簡單按鈕,請幫我們點至+5。