D27 - 如何用 Apps Script 自动化地创造与客制 Google Sheet？（四）蒐集大量试算表中的回应

今天的目标：

要怎麽样依照范本复制并改动 Google Sheet，并一次性地的将结果搜集到同一份 Google Sheet 之内？今天的结果预期长这样——

这是我们今天的题目，实际的应用场景是，扣回之前在做「面试系统」时有个需求是能「一次创造不同的评分表，并搜集分散在不同评分表的分数」。我们将这题拆成两部分——

第一部分是「创造表单」，第二部分是「取得表单中的结果」

1. 如何一键创造一系列可以分享的试算表？
2. 如何搜集很多试算表中的内容？

那我们分成两天回答。昨天回答了第一题，今天会专注在第二题。

Q2 如何搜集很多试算表中的内容？

先来厘清 Input 和 Output。

Input

• 有一张表记录着很多试算表的连结或 ID 如下图。我们这边借用 D26 的产出，或是你也可以参照 D9 - （一）列出所有档案 ID 与相关资讯 列出目标的表单。
  

• 同时，每个子表单内有贴入固定位置的资料要读取出来如下图

Output

• 在指定表单中，有个回对表可以让我们写上每个受试者对应的分数。

好，定义完大致 Input / Output 後，我们开始进入 GAS 的环节。考量到不是每个人都会经历 D26，我们一样从头开始讲起，如果已经会的朋友可以到 Step 3。

Step 1 从 Google Sheet 进入 GAS

今天我们用 Google Sheet 作为连结 GAS 的管道，让我们借用 D14 的影片。

一样第一次按下 GAS 中的「执行」会有「存取验证」需要大家按一下。这边仍是借用一下 D2 的影片。

接着，我们要先设定这张 Google Sheet 上面的参数们。

Step 2 从 Google Sheet 中读取要设定的参数

以这题为例，就是我们「面试官」这个 Tab 中的 C2:C10 的位置。直接上要的部分

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();
  return sheet.getRange(start_row, start_col, numRow, numCol).getValues();
}

function readInterviewersData(){
  Logger.log("面试官: \n"+getSheetData("面试官"));
  let data = getSheetData("面试官");
  for (row_data of data){
  	Logger.log('the link of '+ row_data[0]+' is: '+row_data[2])
  }
}

跑起来长这样——

\

这边有任何不懂，都要记得回去看 D4 - 如何透过 Google Apps Script 来整合 Google Form / Google Sheet 并自动寄出客制的 Email？ 补概念喔。

Step 3 搜集资料并写入表单

好，那最後我们就要将一张张表单的资料写入我们的成绩总表。

function getSocres(){
  let interviewers_data = getSheetData("面试官");
  let result_sheet = ss.getSheetByName('面试者');
  let prev_num_attendee = 0;
  let interviewers_num = interviewers_data.length;
  for (let i =0 ; i < interviewers_num; i++){
    let interview_seq = i%3;
    let interviewer_data = interviewers_data[i];
    let score_sheet_url = interviewer_data[2];
    let score_sheet = SpreadsheetApp.openByUrl(score_sheet_url).getSheetByName('分数表');
    let num_attendee = score_sheet.getLastRow()-3;
    let scores = score_sheet.getRange(4,3,num_attendee,1).getValues();
    result_sheet.getRange(2+ prev_num_attendee,4+ interview_seq, num_attendee,1).setValues(scores);  
    if((interview_seq == 2) && (i!=0)){prev_num_attendee += (num_attendee);}
  };
};

跑起来长这样。可以发现 David 的分数是第二组第一个，也顺利写到了对应受试者 8~14 号的分数上——

那我们来看看程序码——

1. 首先，先让我们拿到表单的资料（interviewers_data）与要写上分数的表单（result_sheet）。
2. 针对每一位评审写一个 for 回圈（总共有 interviewers_num 位评审）。
3. 再来要讲解一下，每抓完三位老师的分数，我们就要重新换填写下一组的面试者。而下一组的面试者的开头会基於前一组的人数，所以我们设定了 prev_num_attendee 一开始为 0，并在每一轮的结束（最後一行的 if 将其设定为这一轮的面试者人数。）
4. 回圈里面，我们则是很单纯的依序（每组有三位，三位的顺序定义为 interview_seq）写上分数。

那就会有我们的结果了，搜集其他表单的资料也可以这样如法炮制。也可以搭配之前的章节像是 D13 -（三）一次搜集很多 Google Form 内的回应 等来做更客制化的操作。好，那今天就到这边！这两天比较是应用题，让本来就很强大的 Google Sheet，搭配 GAS 更是会让如虎添翼。

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