要怎麼樣依照範本複製並改動 Google Sheet,並一次性地的將結果搜集到同一份 Google Sheet 之內?今天的結果預期長這樣——
這是我們今天的題目,實際的應用場景是,扣回之前在做「面試系統」時有個需求是能「一次創造不同的評分表,並搜集分散在不同評分表的分數」。我們將這題拆成兩部分——
第一部分是「創造表單」,第二部分是「取得表單中的結果」
那我們分成兩天回答。昨天回答了第一題,今天會專注在第二題。
先來釐清 Input 和 Output。
有一張表記錄著很多試算表的連結或 ID 如下圖。我們這邊借用 D26 的產出,或是你也可以參照 D9 - (一)列出所有檔案 ID 與相關資訊 列出目標的表單。
同時,每個子表單內有貼入固定位置的資料要讀取出來如下圖
好,定義完大致 Input / Output 後,我們開始進入 GAS 的環節。考量到不是每個人都會經歷 D26,我們一樣從頭開始講起,如果已經會的朋友可以到 Step 3。
今天我們用 Google Sheet 作為連結 GAS 的管道,讓我們借用 D14 的影片。
一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。
接著,我們要先設定這張 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? 補概念喔。
好,那最後我們就要將一張張表單的資料寫入我們的成績總表。
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 號的分數上——
那我們來看看程式碼——
interviewers_data
)與要寫上分數的表單(result_sheet
)。interviewers_num
位評審)。prev_num_attendee
一開始為 0,並在每一輪的結束(最後一行的 if
將其設定為這一輪的面試者人數。)interview_seq
)寫上分數。那就會有我們的結果了,搜集其他表單的資料也可以這樣如法炮製。也可以搭配之前的章節像是 D13 -(三)一次搜集很多 Google Form 內的回應 等來做更客製化的操作。好,那今天就到這邊!這兩天比較是應用題,讓本來就很強大的 Google Sheet,搭配 GAS 更是會讓如虎添翼。
那今天就到這邊,鐵人賽也接近了尾聲。最關鍵的 Sheet 的部分也都講到重點了,希望內容對大家有所幫助。如果還有問題,透過留言之外,也可以到 Facebook Group,想開很久這次鐵人賽才真的開起來,歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。