很多時候我們會需要搜集些不同的資料。像是 Marketing 在做大規模但針對不同組織的調查問卷。如果只是三份、五份的問卷要做客製化、統整算還好;但如果是一百份、甚至上千份時,總不能一個個複製了吧。此時就會遇到個問題——
因為篇幅關係,這邊會拆成三篇來寫,第一篇與第二篇回應 Q1;第三篇回應 Q2。今天這篇是針對 Q2 的第三篇,昨天我們講了怎麼樣客製 Google 表單。而今天來到了第十三天,我們打算用 GAS 完成搜集表單的回覆。一樣先講結論,如果你很急著用,可以直接使用這份 Add-On: Form Publisher,功能非常強大。自己寫的好處是,如果你一天突然要做些高度客製化,那此篇會有幫助。這篇的定位比較像是字典、工具,在你需要用的時候可以來參照。那就讓我們開始吧!
好,那因為我們要用到 Google Sheet ,所以一樣用其作為開啟的管道。一樣借用 D8 的影片。
一樣執行時會有「需要驗證」出現,借用一下 D2 的影片。
好,那我們來借用一下 D12 已經做好的試算表。
接著我們要取得的是橘色欄的表單 ID 們。
function readSheetData(){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getActiveSheet();
let start_row = 3;
let start_col = 9;
let numRows = sheet.getLastRow() - start_row +1;
let numCols = 1;
let values = sheet.getRange(start_row,start_col,numRows,numCols).getValues();
Logger.log(values);
return values;
}
執行起來長這樣,如果這段有任何的不懂,可以翻一下 D4- 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email? ,有詳細解釋 Google Sheet 的存取喔!
那如果是要取得分散在 Drive 裡面的表單,又要怎麼辦?
為了說明,這邊簡單把六份表單都填寫了一份。那為什麼不能每張 Sheet 直接連結一張表單,直接填寫連結回應呢?這會回到 Google Sheet 的架構如下。可以發現表單到 Sheet 是單向的會通,但反過來則不是。Ref: Importing data into a Google Forms response sheet
所以,接下來先講怎麼抓確定是 Submit 後的表單回應,主要是用 getResponses
來取得表單的回應。以下就完整讓大家看看讀資料的功能,一樣先上程式碼。
function readFormData(form_id){
let form = FormApp.openById(form_id);
let formResponses = form.getResponses();
for (let i = 0; i < formResponses.length; i++) {
let formResponse = formResponses[i];
let itemResponses = formResponse.getItemResponses();
for (let j = 0; j < itemResponses.length; j++) {
let itemResponse = itemResponses[j];
Logger.log('Response #%s to the question "%s" was "%s"',
(i + 1).toString(),
itemResponse.getItem().getTitle(),
itemResponse.getResponse());
}
}
}
function readForms(){
let data = readSheetData()
for (let i=0; i< data.length;i++){
Logger.log("Read Form #" + i)
readFormData(data[i])
}
}
跑起來長這樣——
其中兩個最關鍵的 API 分別是——
關係的架構圖如下,要注意的是,必須要先對表單 getResponse()
後才能 getItemResponses()
好,那幫我們收到表單後,要怎麼寫入 GAS?
跟之前一樣,要弄成 Google Sheet 要的格式(Array in array),搭配我們從 D8( 如何用 Google Apps Script 將 Google Calendar 上的事件與更新全部列出到 Google Sheet 上?)提到的方式 writeData()
將 ID 寫回 Google Sheet。
一樣先上程式碼——
function writeData(data){
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Result");
let starting_row = sheet.getLastRow()+1;
let starting_col = 1;
let num_row = data.length;
let num_col = data[0].length;
let range = sheet.getRange(starting_row,
starting_col,
num_row,
num_col);
range.setValues(data);
}
這次比較不同的是,我們不再用同個 Tab / Sheet ,而是另外開一個 Sheet 來丟上,我們將另外一個 Sheet 命名為 Result。
那此時,我們有幾個方式可以操作這個 Sheet ,主要是用它的名字做區隔, API 則是 getSheetByName(name)
,這也是為何 Google Sheet 中不能有相同的工作表(Sheet)。
那會問說,那能用 ID 抓嗎?可以的,但會比較麻煩。這邊直接刻了一版給大家。
function getSheetById(){
let target_id = "your_target_id_in";
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();
let target_sheet = sheets.find(sheet => sheet.getSheetId().toString() == target_id);
Logger.log(target_sheet);
}
執行起來長這樣——
好,那怎麼結合上面這段呢?這邊直接寫好成兩個 Array in Array,細節可以回頭參考 D8。我們稍微改造一下前面 readForms()
的 function。
function readAndWriteFormData(form_id){
let result_array =[]
let form = FormApp.openById(form_id);
let formResponses = form.getResponses();
for (let i = 0; i < formResponses.length; i++) {
let formResponse = formResponses[i];
let item_result_title=["Form ID"]
let item_result_value=[form_id]
let itemResponses = formResponse.getItemResponses();
for (let j = 0; j < itemResponses.length; j++) {
let itemResponse = itemResponses[j];
item_result_title.push(itemResponse.getItem().getTitle())
item_result_value.push(itemResponse.getResponse().toString())
}
result_array.push(item_result_title)
result_array.push(item_result_value)
}
Logger.log(result_array)
writeData(result_array)
}
function readAndWriteFormResponses(){
let data = readSheetData()
for (let i=0; i< data.length;i++){
Logger.log("Read Form #" + i)
readAndWriteFormData(data[i])
}
}
跑起來長這樣——
最後的結果長這樣——
因為每個表單有不同的問題,我們則可以再用資料表本身的功能進行排列與整理。那最後寄出的部分,則可以參考 D4- 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email?
好,那今天我們講解了怎麼樣整合 Google Form 與 Google Sheet 的資料。好,那今天就是我們的 D13。如果還有問題,透過留言之外,也可以到 Facebook Group,想開很久這次鐵人賽才真的開起來哈哈哈,歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。