Google Apps Script 寄出客製化的表單並搜集分散在 Google Sheet 中的回應（ㄧ）複製並客製你的 Google Form （2021 鐵人賽 D11）

目標

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

1. 要如何複製客製化 Google 表單？
2. 要如何集中很多表單中的資料（回應）？

因為篇幅關係，這邊會分成三篇來寫，第一篇與第二篇回應第一題；第三篇回應第二題。但一樣先講結論，如果你很急著用，可以直接使用這份 Add-On: Form Publisher，功能非常強大。自己寫的好處是，如果你一天突然要做些客製化，那就可以持續閱讀閱讀此篇，那讓我們開始吧！

先來個小測驗

答案會在今天的文章中！

Q1. 我要如何複製客製化 Google 表單？

複製表單有兩種方式。一種是比較簡單的「複製範本」，簡單來說就是針對一個表單複製，然後再行更改其中的元素。第二種是「從零製作」，這種就比較複雜，因為會是透過 GAS 完整製作表單，會需要比較熟悉 GAS。我們今天會先講方式一，方式二會在明天講。

方式ㄧ：複製範本

Step 1 開啟 Google Sheet，並串起 GAS

等等，不是要講 Google Form 嗎，怎麼又是先開啟 Google Sheet？那是因為我們想要開很多個創客製化表單的話，總要先有個表先設定說，需要設定哪些參數，而這就是 Google Sheet 派上場的地方。方式一我們先簡單地示範如何複製並客製表單表單，簡單的會用下圖左側綠色的參數，來生成右側橙色的參數。白話文就是，我期待在創造完後後它會告訴我各表單的連結。

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

一樣執行時會有「需要驗證」出現，借用一下 D2 的影片：Google Apps Script (GAS) 的環境設定、專案結構（Trigger）與四種打包方式。

Step 2 設定要作為 Template 的 Google From ID

這邊我們就直接取得 ID，那可以在 GAS 的後台用直接設定參數，也可以像這樣寫在 Google Sheet 上。考量到有些人會用不同的範本來生成表單，這邊就直接寫在 Google Sheet 上。

完整取得 ID 的方式在 D9 有詳細的介紹可參考：如何用 Google Apps Script 自動化對 Google Drive 的操作？（一）列出所有檔案 ID 與相關資訊

Step 3 從 GAS 中讀取 Google Sheet 的資料

那我們之前從 D4 （如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email？）起介紹的抓「欄數」與「列數」，都是直接設定好要抓多少行與列，今天介紹比較彈性地設定的兩個手法：

1. getLastRow() ：抓出表單中最下面有數值的「那一列」
2. getLastColumn()：抓出表單中最右側有數值的「那一欄」。

所以以我們的範圍來說，就會是「列」抓到第 10 列，「行」抓到第 4（D） 行。但，如果我們要搭配之前常用的 getRange(start_row, start_col, numRows, numCols) 來取得範圍的話，我們會需要將 numRows 和 numCols 寫成——

let numRows = sheet.getLastRow() - start_row +1;
let numCols = sheet.getLastColumn() - start_col +1;

之所以最後還要有一個 +1 ，原因是因為如果一開始的 start_row 和 start_col 本身也要算進去，如果單純用最後一欄有資料的 index 值去減掉最一開始的 index 值，那跟我們想要的結果就會差了 1 如下圖。這也是我們最一開始題目的答案。

所以完整的讀檔程式碼長這樣——

function readData(){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let start_row = 3;
  let start_col = 1;
  let numRows = sheet.getLastRow() - start_row +1;
  let numCols = sheet.getLastColumn() - start_col +1;
  let values = sheet.getRange(start_row,start_col,numRows,numCols).getValues();
  Logger.log(values)
  return values;
}

跑起來長這樣——

讀出來後會以 Array in array 的形式操作，我們待會會直接進入操作，想補概念可以回到 D4 ～

Step 4 將讀取到的範本用 makeCopy() 複製

好的，那我們現在將 Google Sheet 中的資料讀到 GAS 當中後，我們要依據 ID 抓出檔案並將其複製。我們先來快速看一下 makeCopy(name, destination) 怎麼用。

function copyForm(){
  let target_id = template_id;
  let form_file = DriveApp.getFileById(target_id);
  let new_form_Id = form_file.makeCopy("D11 Test").getId()
  Logger.log(new_form_Id)
}

跑起來會長這樣——

補充的是，在上面的程式碼中，我們用的是 makeCopy(name) ，也就是只填一個參數的話，那個參數就會是新檔案的名稱。如果填入兩個，就會是 makeCopy(name, destination)，也就是目的地的位置（要是個 folder object，需要用 DriveApp.getFolderById() 等方式來取得。那如果我們用的是沒有參數，也就是直接輸入 makeCopy() 的話，就會是在原地用預設的名稱複製（通常會是「＿＿＿＿的副本」或「Copy of ＿＿＿＿」）

好，那我們複製完檔案後，它現在仍是個一模一樣的表單，我們要怎麼樣放入客製的部分？這就到了 FormApp 的主場了。

Step 5 將複製後的表單用 FormApp() 的功能改寫

好，那將這 makeCopy 和 Step 3 整合起來，就會變成下列這段程式碼。

function copyForm(){
  let target_id = template_id;
  let form_file = DriveApp.getFileById(target_id);
  let new_form_Id = form_file.makeCopy("D11 Test").getId()
  let new_form = FormApp.openById(new_form_Id);
  new_form.setDescription('Hello, new form!')
          .setConfirmationMessage('Thanks for responding!')
          .setAcceptingResponses(false);
}

這邊主要是對 form Object 進行操作，就簡單地運以下三個功能。

1. 設定敘述（`setDescription()“）
2. 設定填答完畢的訊息（`setConfirmationMessage()“）
3. 設定先不接受回應（setAcceptingResponses()）

跑起來長這樣——

整個 FormApp() 也正是「如何操作」 Google Form 的精髓所在，明天我們會大版面介紹如何完整使用其中的功能們。好，那這樣我們接著就要整合 Step 3 的讀取資料，和 Step 4 與 Step 5 了。

Step 6 依照 Google Sheet 上的資訊製作 Google 表單

而完整整合起來會長這樣。

function copyForm(){
  let full_table_data = readData();
  for (row_data of full_table_data){
    let target_id = row_data[0];
    let form_file = DriveApp.getFileById(target_id);

    let new_form_name = row_data[1];
    let new_form_Id = form_file.makeCopy(new_form_name).getId()

    let new_form_description = row_data[2];
    let new_form = FormApp.openById(new_form_Id);
    new_form.setDescription(new_form_description)
            .setConfirmationMessage('Thanks for responding!')
            .setAcceptingResponses(false);
  }
}

讓我們來試跑看看——

好，那我們最後當然會希望能追蹤我們所產生的表單，這就到了 Step 7 的寫入表單。

Step 7 將創造後的表單 ID 寫回 Google Sheet

那一樣搭配我們從 D8（ 如何用 Google Apps Script 將 Google Calendar 上的事件與更新全部列出到 Google Sheet 上？）開始每天見面的老朋友 writeData() 將 ID 寫回 Google Sheet。

function writeData(data){
  let starting_row = 3;
  let starting_col = 4;
  let num_row = data.length;
  let num_col = data[0].length;
  let sheet = SpreadsheetApp.getActiveSheet();
  let range = sheet.getRange(starting_row, 
                             starting_col, num_row, num_col);
  range.setValues(data);
}

並將其與 copyForm() 結合即可。

function copyForm(){
  let full_table_data = readData();
  let new_form_id_arr = []
  for (row_data of full_table_data){
    let target_id = row_data[0];
    let form_file = DriveApp.getFileById(target_id);

    let new_form_name = row_data[1];
    let new_form_Id = form_file.makeCopy(new_form_name).getId()

    let new_form_description = row_data[2];
    let new_form = FormApp.openById(new_form_Id);
    new_form.setDescription(new_form_description)
            .setConfirmationMessage('Thanks for responding!')
            .setAcceptingResponses(false);
            
    new_form_id_arr.push([new_form_Id]);
  }
  writeData(new_form_id_arr)
}

最後結果會長這樣——

今天主要講了下列功能：

1. getLastRow() 和 getLastColumn()
2. makeCopy() 的細節使用差異
3. 用 FormApp 來基礎地調整表單的內容

好，那看完今天的教學，至少知道如何將 Google 複製並調整基本的元素了（撒花）

好，那基本上我們就搞定了。但如果今天我們是想要高度客製化的表單怎麼辦？像是不同人要設定不同的照片，甚至是有些人不要某些題目怎麼辦？那就會到我們的方法二。也就是明天的主題。

一樣提醒，用FormApp.create()來創造表單是有 Quota 限制——每天不超過 250 份。如果還有問題，透過留言之外，也可以到 Facebook Group，想開很久這次鐵人賽才真的開起來哈哈哈，歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺（？），也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。