✨split data into tabs

💾 程式：Google Sheet

// 🔸 app
const app = {};

// 🔸 app.spreadsheet
// const (所以直接存儲在 app 屬性中就好)
app.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();  

// 🔸 app.sheets()
app.sheets = function(){
  return app.spreadsheet.getSheets()
};

// 🔸 app.sourceSheet()
app.sourceSheet = function(){
  return app.sheets()[0]
};

// 🔸 app.sourceSheetName()
app.sourceSheetName = function(){
  return app.sourceSheet().getName()
};

// 🔸 unique course names
// 傳回 H 欄的所有科目（去除重複）
app.uniqueCourses = function uniqueCourses(){
  return app.sourceSheet()
    .getRange("H2:H")           // H column
    .getValues()                // 2D data
    .map(row => row[0])         // 每列取第一筆資料 (只有一筆資料)
    .unique()
    .filter(x => x);            // ⭐️ remove empty string
};


// 🔸 app.insertSheetByName(rawName)
// 將分科的資料放到新的頁面中
app.insertSheetByName = function (rawName, {
  autoresize = true         // resize cols
}={}) {

  // tab name 
  const name = tabName(rawName);

  // get worksheet by name
  let ws = app.spreadsheet.getSheetByName(name);

  // if already exists, quit.
  if(ws){ 
    log(`⚠️ 頁面「${name}」已經存在，取消插入試算表。`);
    return 
  };

  // source sheet name
  const sourceSheetName = app.sourceSheetName();

  // 1. create a worksheet
  ws = app.spreadsheet.insertSheet();  

  // 2. rename sheet
  // const rawName = "1011010101.國語文"
  
  ws.setName(name);

  // 3. set formula to A2 cell
  ws.getRange("A2")
    .setFormula(`=FILTER(${sourceSheetName}!B2:K,${sourceSheetName}!H2:H="${rawName}")`)

  // 4. copy title row from source sheet
  app.sourceSheet()
    .getRange("B1:L1")
    .copyTo(ws.getRange("A1:K1"))   // 注意：原始資料從 B 欄開始。

  // 5. 調整欄位寬度
  if(autoresize) app.resizeColumns(ws);

  // 6. 除了「補考成績」欄位外，鎖住其他欄位。
  app.protectSheet(ws);

};

// 🔸 protect sheet
// 6. 除了「補考成績」欄位外，鎖住其他欄位。
app.protectSheet = function(sheet){
  
  var protection = sheet.protect();                            // protect sheet
  protection.setUnprotectedRanges([sheet.getRange("I2:I")]);   // unprotect I column

  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());

  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
};

// 🔸 split data into separate tabs
app.splitIntoTabs = function(){

  const courses = app.uniqueCourses();
  log(`🚩 開始產生 ${courses.length} 個分頁。`);

  courses.forEach( rawName => {
    app.insertSheetByName(rawName);
  });

  log(`🏁 產生 ${courses.length} 個分頁完成。`);
};

// 🔸 delete all sheets execpt source sheet
app.deleleteAllGeneratedSheets = function(){
  app.sheets().forEach( sheet => {
    const name = sheet.getName();
    if(name !== app.sourceSheetName()) {
      app.spreadsheet.deleteSheet(sheet);
      log(`⚠️ 頁面「${name}」已刪除❗`);
    }
  })
};

// 🔸 autoresize all columns in `sheet`
// 似乎對中文不管用❓
app.resizeColumns = function(sheet) {
  // var sheet = SpreadsheetApp.getActiveSheet();
  // var dataRange = sheet.getDataRange();
  // var i = dataRange.getColumn();            // first col
  // var n = dataRange.getNumColumns();        // number of columns
  // sheet.autoResizeColumns(i, n);

  sheet.setColumnWidths(1, 6, 60);
  sheet.setColumnWidths(7, 1, 200);     // 科目
};

// 🔸 「科目」欄位名稱不能簡化，因為有的科目有不同科目代碼，
// 例如：101101040E.生物、105101040E.生物
function tabName(rawValue) {
  return rawValue;
  // 1. split by "."
  // 2. return last part
  // return rawValue.split(".")[1]
}
PreviousexamplesNextmigrate sheets into files

Last updated