Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)

目標:

要怎麼樣依照範本複製並改動 Google Sheet,並一次性地的將結果搜集到同一份 Google Sheet 之內?今天的結果預期長這樣——

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图

這是我們今天的題目,實際的應用場景是,扣回之前在做「面試系統」時有個需求是能「一次創造不同的評分表,並搜集分散在不同評分表的分數」。我們將這題拆成兩部分——

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图1

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

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

那我們分成兩天回答。昨天回答了第一題,今天會專注在第二題。

Q2 如何搜集很多試算表中的內容?

先來釐清 Input 和 Output。

Input

  • 有一張表記錄著很多試算表的連結或 ID 如下圖。我們這邊借用 D26 的產出,或是你也可以參照 D9 – (一)列出所有檔案 ID 與相關資訊 列出目標的表單。
    Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图2
  • 同時,每個子表單內有貼入固定位置的資料要讀取出來如下圖
Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图3

Output

  • 在指定表單中,有個回對表可以讓我們寫上每個受試者對應的分數。

好,定義完大致 Input / Output 後,我們開始進入 GAS 的環節。考量到不是每個人都會經歷 D26,我們一樣從頭開始講起,如果已經會的朋友可以到 Step 3。

Step 1 從 Google Sheet 進入 GAS

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

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图4

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

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图5

接著,我們要先設定這張 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])
  }
}

跑起來長這樣——

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图6\

這邊有任何不懂,都要記得回去看 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 號的分數上——

Google Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應(2021 鐵人賽 D27)插图

那我們來看看程式碼——

  1. 首先,先讓我們拿到表單的資料(interviewers_data)與要寫上分數的表單(result_sheet)。
  2. 針對每一位評審寫一個 for 迴圈(總共有 interviewers_num 位評審)。
  3. 再來要講解一下,每抓完三位老師的分數,我們就要重新換填寫下一組的面試者。而下一組的面試者的開頭會基於前一組的人數,所以我們設定了 prev_num_attendee 一開始為 0,並在每一輪的結束(最後一行的 if 將其設定為這一輪的面試者人數。)
  4. 迴圈裡面,我們則是很單純的依序(每組有三位,三位的順序定義為 interview_seq)寫上分數。

那就會有我們的結果了,搜集其他表單的資料也可以這樣如法炮製。也可以搭配之前的章節像是 D13 -(三)一次搜集很多 Google Form 內的回應 等來做更客製化的操作。好,那今天就到這邊!這兩天比較是應用題,讓本來就很強大的 Google Sheet,搭配 GAS 更是會讓如虎添翼。

目錄

Scroll to Top