D26 如何用 Apps Script 自动化地创造与客制 Google Sheet？（三）依照范本大量复制试算表

今天的目标：

要怎麽样依照范本复制并改动 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 更是会让大家如虎添翼。

那今天就到这边，铁人赛也接近了尾声。也进入了最关键的 Sheet 的部分，希望内容对大家有所帮助。如果还有问题，透过留言之外，也可以到 Facebook Group，想开很久这次铁人赛才真的开起来，欢迎来当 Founding Member。如果不想错过可以订阅按赞小铃铛（？），也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。