要怎麼樣依照範本複製並改動 Google Sheet,並一次性地的將結果搜集到同一份 Google Sheet 之內?這是我們今天的題目,實際的應用場景是,扣回之前在做「面試系統」時有個需求是能「一次創造不同的評分表,並搜集分散在不同評分表的分數」。我們將這題拆成兩部分——
第一部分是「創造表單」,第二部分是「取得表單中的結果」
那我們會分成兩天回答。
*實際在面試時,因為報到數不一定,我們會現場再依照人數分組。如果想知道細節,我們再另外寫。
好,定義好大致 Input / Output 後,我們開始進入 GAS 的環節。
今天我們用 Google Sheet 作為連結 GAS 的管道,讓我們借用 D14 的影片。
一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。
接著,我們要先設定這張 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("面試官"));
}
上面執行起來的畫面是這樣——
核對一下資料,看來抓得沒錯。那我們程式碼一段段來看——
getSheetData
並且用 getSheetByName(sheet_name)
讓我們可以讀取我們選定的表單的。createTable
的部分,則是先用 Logger 讀取兩份表單(「面試者」與「面試官」)的資料。當讀取好資料後,接著就是複製並將資料寫入我們的範本了!
這邊我們先示範如何複製「一張」表單,我們在上面的基礎之上,再接續寫下去。一樣先上程式碼——
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()
group_attendee[2]
來取得第二組的資料,以此類推。template.copy
複製一份表單,並將其名稱設定為「第_組 面試官:___」。setValue
寫入面試官的名稱與組別。group_attendee
,並將其數值輸入表單。那接著就可以結合我們前面的章節,像是 D4 - 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email? 1. 取得 Google Sheet 的資料架構 來寄給特定的收件者。
那關於怎麼樣取得資料,因為篇幅也比較長,我們就留到明天說。好,那今天就到這邊!今天比較是應用題,更多是 Google Sheet 的操作。 Google Sheet 本身就是很強大的工具,搭配 GAS 更是會讓大家如虎添翼。
那今天就到這邊,鐵人賽也接近了尾聲。也進入了最關鍵的 Sheet 的部分,希望內容對大家有所幫助。如果還有問題,透過留言之外,也可以到 Facebook Group,想開很久這次鐵人賽才真的開起來,歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。