iT邦幫忙

2021 iThome 鐵人賽

DAY 13
0
Modern Web

整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度系列 第 13

如何用 Apps Script 寄出客製化的 Google 表單並搜集分散在 Google Sheet 中的回應?(三)一次搜集很多 Google Form 內的回應

今天的目標

很多時候我們會需要搜集些不同的資料。像是 Marketing 在做大規模但針對不同組織的調查問卷。如果只是三份、五份的問卷要做客製化、統整算還好;但如果是一百份、甚至上千份時,總不能一個個複製了吧。此時就會遇到個問題——

  1. 要如何複製客製化 Google 表單?
  2. 要如何集中很多表單中的資料(回應)?

因為篇幅關係,這邊會拆成三篇來寫,第一篇與第二篇回應 Q1;第三篇回應 Q2。今天這篇是針對 Q2 的第三篇,昨天我們講了怎麼樣客製 Google 表單。而今天來到了第十三天,我們打算用 GAS 完成搜集表單的回覆。一樣先講結論,如果你很急著用,可以直接使用這份 Add-On: Form Publisher,功能非常強大。自己寫的好處是,如果你一天突然要做些高度客製化,那此篇會有幫助。這篇的定位比較像是字典、工具,在你需要用的時候可以來參照。那就讓我們開始吧!


Q2. 要如何集中很多表單中的資料(回應)?

Step 1 開啟 Google Sheet,並串起 GAS

好,那因為我們要用到 Google Sheet ,所以一樣用其作為開啟的管道。一樣借用 D8 的影片。

一樣執行時會有「需要驗證」出現,借用一下 D2 的影片。

Step 2 從 Google Sheet 中取得表單的 ID 們

好,那我們來借用一下 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 裡面的表單,又要怎麼辦?

Step 3

為了說明,這邊簡單把六份表單都填寫了一份。那為什麼不能每張 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()

Step 4 將資料寫回 GAS

好,那幫我們收到表單後,要怎麼寫入 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。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。


上一篇
D12 - 如何用 Apps Script 寄出客製化的表單並搜集分散在 Google Sheet 中的回應?(二)大幅度客製你的 Google Form
下一篇
D14 - 如何用 Apps Script 自動化地創造與客製 Google Docs?(一) 以 NDA 為例的大架構與簡單複製
系列文
整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度30

尚未有邦友留言

立即登入留言