Google Apps Script 寄出客製化的 Google 表單並搜集分散在 Google Sheet 中的回應（三）一次搜集很多 Google Form 內的回應 （2021 鐵人賽 D13）

目標

很多時候我們會需要搜集些不同的資料。像是 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()

關係的架構圖如下，要注意的是，必須要先對表單 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。如果不想錯過可以訂閱按讚小鈴鐺（？），也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。