iT邦幫忙

2021 iThome 鐵人賽

DAY 27
0
Modern Web

整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度系列 第 27

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。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。


上一篇
D26 如何用 Apps Script 自動化地創造與客製 Google Sheet?(三)依照範本大量複製試算表
下一篇
D28 - 如何打包 Apps Script 的程式碼?(一) 變成擴充功能似的 UI 按鍵
系列文
整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度30

尚未有邦友留言

立即登入留言