โจ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]
}
Last updated
Was this helpful?