iT邦幫忙

2021 iThome 鐵人賽

DAY 26
0
Modern Web

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

D26 如何用 Apps Script 自動化地創造與客製 Google Sheet?(三)依照範本大量複製試算表

  • 分享至 

  • xImage
  •  

今天的目標:

要怎麼樣依照範本複製並改動 Google Sheet,並一次性地的將結果搜集到同一份 Google Sheet 之內?這是我們今天的題目,實際的應用場景是,扣回之前在做「面試系統」時有個需求是能「一次創造不同的評分表,並搜集分散在不同評分表的分數」。我們將這題拆成兩部分——

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

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

那我們會分成兩天回答。


Q1. 如何一鍵創造一系列可以分享的試算表?

Input

  • 面試者的表,設定好姓名與組別
  • 面試官的表,設定好姓名與組別
  • 參數表,設定要複製的資料夾與範本

  • 分數表的範本如下圖

*實際在面試時,因為報到數不一定,我們會現場再依照人數分組。如果想知道細節,我們再另外寫。

Process

  • 開啟 GAS
  • 取得 Sheet 上的資料
  • 複製範本給面試官

Output

  • 每個面試官會收到一張資料表,其中有他的名字、所在組數以及該組的學生

好,定義好大致 Input / Output 後,我們開始進入 GAS 的環節。


Step 1 從 Google Sheet 進入 GAS

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

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

接著,我們要先設定這張 Google Sheet 上面的參數們。


Step 2 從 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("面試官"));
}

上面執行起來的畫面是這樣——

核對一下資料,看來抓得沒錯。那我們程式碼一段段來看——

  1. 首先我們設定一個全域變數是這份表單本身, ss 是在 GAS 中常常用於稱呼目標 SpreadSheet 的縮寫。
  2. 再來我們寫一段程式 getSheetData 並且用 getSheetByName(sheet_name) 讓我們可以讀取我們選定的表單的。
  3. 最後是 createTable 的部分,則是先用 Logger 讀取兩份表單(「面試者」與「面試官」)的資料。

當讀取好資料後,接著就是複製並將資料寫入我們的範本了!


Step 3 複製表單並寫入面試者資料

這邊我們先示範如何複製「一張」表單,我們在上面的基礎之上,再接續寫下去。一樣先上程式碼——

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()

  1. 首先我們在 For 迴圈外面,分別設定了我們要用到的幾個數值,包含面試者、面試官的資料、範本的試算表以及即將要「面試官連結」表單的網址們。
  2. 第二步,我們將受試者設定為 Dictionary 的結構,我們後面可以用 group_attendee[2] 來取得第二組的資料,以此類推。
  3. 再來,我們先針對每個面試官,都用 template.copy 複製一份表單,並將其名稱設定為「第_組 面試官:___」。
  4. 再來,透過 setValue 寫入面試官的名稱與組別。
  5. 接著,搜尋屬於面試官組別的面試者 group_attendee,並將其數值輸入表單。
  6. 倒數第二步,再將表單的 url 們寫回面試官的資料表。
  7. 最後,將表單移動到我們目標資料夾中。

那接著就可以結合我們前面的章節,像是 D4 - 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email? 1. 取得 Google Sheet 的資料架構 來寄給特定的收件者。

那關於怎麼樣取得資料,因為篇幅也比較長,我們就留到明天說。好,那今天就到這邊!今天比較是應用題,更多是 Google Sheet 的操作。 Google Sheet 本身就是很強大的工具,搭配 GAS 更是會讓大家如虎添翼。


那今天就到這邊,鐵人賽也接近了尾聲。也進入了最關鍵的 Sheet 的部分,希望內容對大家有所幫助。如果還有問題,透過留言之外,也可以到 Facebook Group,想開很久這次鐵人賽才真的開起來,歡迎來當 Founding Member。如果不想錯過可以訂閱按讚小鈴鐺(?),也歡迎留言跟我說你還想知道什麼做法/主題。我們明天見。


上一篇
D25 - 如何用 Apps Script 自動化地創造與客製 Google Sheet?(二)結合股票價格通知與信件
下一篇
D27 - 如何用 Apps Script 自動化地創造與客製 Google Sheet?(四)蒐集大量試算表中的回應
系列文
整合 Google 服務的燃料——透過 Google Apps Script (GAS) 加速你的工作速度30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言