用Google Apps Script彙整試算表多張活頁的成績

同事使用Google表單設計測驗給學生進行週考，每週約有6-7科，每次考試都存至同一個Google試算表的不同活頁。

同事需要每週抓該週的數次考試成績彙整成單個試算表，做週考成績表的統整與發放。若是手工進行， 需要每頁抓取資料後排序再統整，但若遇上有一些學生缺考某些科，則需要再多花時間調整。

假設一學期週考15次，每次需要20分鐘彙整，共兩學期，這樣至少要花600分鐘進行這項工作。由於是重複的工作，應當使用程式來完成，因此我就用Google Apps script幫他寫了一隻程式。程式碼我會直接貼在此文後。

程式的邏輯是這樣：

1. 事先在目的試算表先建立好所有學生的資料，重點是要有email欄位，因為測驗表單會紀錄學生的email，每位學生只會考一次，所以可以使用email作為index。
2. 把目的試算表的所有email先存成array，命名為emailArray，以便後續作業。
3. 指定來源試算表的url，讀取指定的那些活頁。讀取每個活頁，儲存活頁的名稱，歷遍每個row（每個學生的單次考試資料)，取得每個row的email和分數，然後用email去找它在emailArray中的哪個位置（使用indexOf)，這可以幫助定位出「這筆學生分數應該放在目的試算表的哪個row）
4. 在目的試算表的Column部份，其欄名使用來源試算表的活頁名稱，並將該次該科學生的成績按照上一步驟得到的位址資訊填入目的試算表的該欄
5. 當該科考試的成績填完之後，再讀取下一個指定的活頁，直到指定的活頁都讀取完。
6. 其中會發現有些來源資料表的email未被列在目的資料表的emailArray中（使用indexOf會return -1)，遇到這樣的資料則將之Logger.log印出。

使用此程式之後，每週只需要更改需要讀取的活頁index，即可快速彙整成績

程式如下。需在Google Apps Script中使用，並允許一些試算表的讀取權限。

var sourceUrl = 'https://docs.google.com/spreadsheets/d/XXXXXX/';  //成績來源試算表

var targetUrl = 'https://docs.google.com/spreadsheets/d/oooooo/';  //登錄在哪個試算表

var startSheet = 37;     //從第幾個sheet開始登錄成績

var sheetNum = 6;        //共有幾個sheet要登錄成績

var targetSheetIndex = 0 //登錄在第幾個sheet(第一個Sheet的index為0)

var emailCol = 5;        //email在第幾欄(E欄為第5欄)

var subjectColumn = 6;   //登錄從第幾欄開始(F欄為第6欄)

function getData() {

  var sourceSS = SpreadsheetApp.openByUrl(sourceUrl);

  var sourceSheets = sourceSS.getSheets();

  var targetSS = SpreadsheetApp.openByUrl(targetUrl);

  var targetSheets = targetSS.getSheets();  

  var targetRowNum = targetSheets[targetSheetIndex].getLastRow();

  // 從targetSheet獲取email array。從2d array變成 1d array

  var emailArray = targetSheets[targetSheetIndex].getRange(2,emailCol,targetRowNum-1,1).getValues().flat();

  

  //歷遍每個週考的sheet

  for(var i = startSheet; i < startSheet + sheetNum ; i++) {

    var subject = sourceSheets[i].getName();

    // 在targetSheet寫入科目欄名

    targetSheets[targetSheetIndex].getRange(1,subjectColumn ,1 ,1).setValue(subject);

    //尋找該科目在array的哪個index

    var rowNum = sourceSheets[i].getLastRow();

    

    // 到每頁成績表的每列找資料，紀錄email欄位和科目欄位

    for (var j = 2; j <= rowNum; j++) {

      var email = sourceSheets[i].getRange(j, 2).getValue();

      var score = sourceSheets[i].getRange(j, 3).getValue();

      // 到彙整試算表找email相等的欄位，填入對應的科目分數

      var emailPos = emailArray.indexOf(email);

      if (emailPos > -1) {

        targetSheets[targetSheetIndex].getRange(emailPos + 2, subjectColumn).setValue(score);

      }

      else{

        Logger.log(email + " " + subject + " "+ score);

      }

    }

    subjectColumn += 1;

  }

}

function main(){

  getData();

}