โœจ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

Was this helpful?