Google Apps Script 自動化地創造與客製 Google Sheet？（三）依照範本大量複製試算（2021 鐵人賽 D26）

目標：

要怎麼樣依照範本複製並改動 Google Sheet，並一次性地的將結果搜集到同一份 Google Sheet 之內？這是我們今天的題目，實際的應用場景是，扣回之前在做「面試系統」時有個需求是能「一次創造不同的評分表，並搜集分散在不同評分表的分數」。我們將這題拆成兩部分——

第一部分是「創造表單」，第二部分是「取得表單中的結果」

1. 如何一鍵創造一系列可以分享的試算表？
2. 如何搜集很多試算表中的內容？

那我們會分成兩天回答。

Q1. 如何一鍵創造一系列可以分享的試算表？

Input

• 面試者的表，設定好姓名與組別
• 面試官的表，設定好姓名與組別
• 參數表，設定要複製的資料夾與範本

• 分數表的範本如下圖

*實際在面試時，因為報到數不一定，我們會現場再依照人數分組。如果想知道細節，我們再另外寫。

Process

• 開啟 GAS
• 取得 Sheet 上的資料
• 複製範本給面試官

Output

• 每個面試官會收到一張資料表，其中有他的名字、所在組數以及該組的學生

好，定義好大致 Input / Output 後，我們開始進入 GAS 的環節。

Step 1 從 Google Sheet 進入 GAS

今天我們用 Google Sheet 作為連結 GAS 的管道，讓我們借用 D14 的影片。

一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。

接著，我們要先設定這張 Google Sheet 上面的參數們。

Step 2 從 Google Sheet 中讀取要設定的參數

接著就是用 GAS 讀取數據，那因為這邊有兩張表，我們在寫的 Code 就會跟以往不同。我們先上程式碼——

var ss = SpreadsheetApp.getActiveSpreadsheet();

function getSheetData(sheet_name){
  let sheet = ss.getSheetByName(sheet_name);
  let start_row = 2;
  let start_col = 1;
  let numRow = sheet.getLastRow() - 1;
  let numCol = sheet.getLastColumn() - 1;
  return sheet.getRange(start_row, start_col, numRow, numCol).getValues();
}

function createTable(){
  Logger.log("面試者: \n"+getSheetData("面試者"));
  Logger.log("面試官: \n"+getSheetData("面試官"));
}

上面執行起來的畫面是這樣——

核對一下資料，看來抓得沒錯。那我們程式碼一段段來看——

1. 首先我們設定一個全域變數是這份表單本身， ss 是在 GAS 中常常用於稱呼目標 SpreadSheet 的縮寫。
2. 再來我們寫一段程式 getSheetData 並且用 getSheetByName(sheet_name) 讓我們可以讀取我們選定的表單的。
3. 最後是 createTable 的部分，則是先用 Logger 讀取兩份表單（「面試者」與「面試官」）的資料。

當讀取好資料後，接著就是複製並將資料寫入我們的範本了！

Step 3 複製表單並寫入面試者資料

這邊我們先示範如何複製「一張」表單，我們在上面的基礎之上，再接續寫下去。一樣先上程式碼——

var template_drive_id = ss.getSheetByName('參數表').getRange(1,2).getValue();
var template_ss_id = ss.getSheetByName('參數表').getRange(2,2).getValue();


function moveFiles(sourceFileId, targetFolderId) {
  var file = DriveApp.getFileById(sourceFileId);
  var folder = DriveApp.getFolderById(targetFolderId);
  file.moveTo(folder);
}

function createTable(){
  // Named attendee to prevent confuse interviewer with interviewee
  let attendees_data = getSheetData("面試者");
  let interviewers_data = getSheetData("面試官");
  let template = SpreadsheetApp.openById(template_ss_id)
  let new_sheet_urls=[]
  let group_attendee = {}
  for(attendee_group in group_attendee){
      let attendee_group = attendee[2];
	  if (group_attendee.get(attendee_group)){
	  	group_attendee[attendee_group].push([attendee[0],attendee[1]]);
	  }else{
	  	group_attendee[attendee_group]= [[attendee[0],attendee[1]]];
	  }
  }
  for (interviewer_data of interviewers_data){
    let interviewer_name = interviewer_data[0]
    let interviewer_group = interviewer_data[1]

    let new_ss = template.copy("第"+ interviewer_group+ "組 面試官："+interviewer_name)
    let sheet = new_ss.getSheetByName('分數表')
    sheet.getRange(1,2).setValue(interviewer_name)
    sheet.getRange(2,2).setValue(interviewer_group)

    let no_attendee_this_group = group_attendee[interviewer_group].length;
    sheet.getRange(4,1,no_attendee_this_group,2).setValues(group_attendee[interviewer_group]);
    moveFiles(new_ss.getId(), template_drive_id)
	new_sheet_urls.push([new_ss.getUrl()]);
  }
  
  let url_nums = new_sheet_urls.length;
  ss.getSheetByName('面試官').getRange(2,3,url_nums,1).setValues(new_sheet_urls);
}

來試著跑跑看，我影片中的程式碼在最後有加入一個 return 作為 Early Stop，但上面的程式碼沒有，所以理論上只要複製上面這段，就可以幫大家跑完如下——

我們來看看主程式碼的 createTable()

1. 首先我們在 For 迴圈外面，分別設定了我們要用到的幾個數值，包含面試者、面試官的資料、範本的試算表以及即將要「面試官連結」表單的網址們。
2. 第二步，我們將受試者設定為 Dictionary 的結構，我們後面可以用 group_attendee[2] 來取得第二組的資料，以此類推。
3. 再來，我們先針對每個面試官，都用 template.copy 複製一份表單，並將其名稱設定為「第＿組 面試官：＿＿＿」。
4. 再來，透過 setValue 寫入面試官的名稱與組別。
5. 接著，搜尋屬於面試官組別的面試者 group_attendee，並將其數值輸入表單。
6. 倒數第二步，再將表單的 url 們寫回面試官的資料表。
7. 最後，將表單移動到我們目標資料夾中。

那接著就可以結合我們前面的章節，像是 D4 – 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email？ 1. 取得 Google Sheet 的資料架構 來寄給特定的收件者。

那關於怎麼樣取得資料，因為篇幅也比較長，我們就留到明天說。好，那今天就到這邊！今天比較是應用題，更多是 Google Sheet 的操作。 Google Sheet 本身就是很強大的工具，搭配 GAS 更是會讓大家如虎添翼。