đRawData
đž google apps script
// đ
revision history:
// 2022.07.28:
// - å° mainColumns æžå
Ĩ constructor() į忏īŧåå
Ĩ RawData
// - indexOfColumn æšåæžæŧ RawData
// - _cell() æšįē cellValue()
// - đ é¤č˛: Object.entries(this.data) æšįē this.data.entries() - (integer-base index)
// đ RawData
// č˛ č˛Ŧåæåå§čŗæ
// đ¸ data: [[string]] // åå§čŗæ(å
¨é¨čŊįēå串)
// đ¸ mainColumns: [string] // ä¸ģčĻæŦäŊ(â įąéäēå串ãéé (startWith)ãåŗå¯īŧä¸é䏿¨Ąä¸æ¨Ŗã)
// đ¸ indexOfColumn (dict) // å
ąææŦäŊį´ĸåŧ
class RawData {
// custom class type name
get [Symbol.toStringTag]() { return 'RawData' }
// init
constructor(sheetName, mainColumns){ // data from data range values
// đ¸ .mainColumns
this.mainColumns = mainColumns;
let data = app.dataRangeValuesFromSheet(sheetName);
if(!data) throw new Error(`â RawData: æžä¸å°čŠĻįŽčĄ¨ã${sheetName}ãįčŗæ`);
// đ¸ .data
// normalize all cell values (remove all whitespaces)
this.data = data.map(row =>
row.map(value => (value + '').removeWhitespaces())
);
// đ¸ .indexOfColumn
this.parseColumnIndices(); // parse phase 1: æžå
ąææŦäŊį´ĸåŧ
// parse phase 2: éåæåēåįå¸įčŗæ
// đ¸ app.students
this.parseAllRows();
}
// parse main column indices
parseColumnIndices(){
for(const [i, row] of this.data.entries()){
// if not header row, skip
if(!this.isHeaderRow(row)) continue;
// đ¸ .indexOfColumn
// now, it's a header row
this.indexOfColumn = this._indexDictForMainColumnsFromRow(row);
const n = Object.keys(this.indexOfColumn).length;
log(`âšī¸ įŦŦ ${i + 1} åīŧã${row}ã`);
log(`âšī¸ å¨įŦŦ ${i + 1} åæžå°ä¸ģčĻæŦäŊã${this.mainColumns}ã(å
ą ${n} æŦ)īŧį´ĸåŧåŧåĻä¸īŧ`);
log(this.indexOfColumn);
// braak for-loop
break;
}
// if not found, throw error
if(!this.indexOfColumn) throw new Error(
`â RawData._parseColumnIndices(): å¨ãåå§å ąčĄ¨ã䏿žä¸å°ä¸ģčĻæŦäŊã${this.mainColumns}ã`
);
}
// find indices for main cols from row
_indexDictForMainColumnsFromRow(row){
let dict = {};
for(const colName of this.mainColumns){
const i = row.findIndex(x => x.startsWith(colName)); // âī¸ äžåĻīŧåå§ãį§įŽãåį¨ąåžéĸéæãᎍčãīŧ
if(i < 0) throw new Error(`â RawData._indexDictForMainColumnsFromRow:ã${row}ãæ˛æã${colName}ãéåæŦäŊåį¨ąã`);
dict[colName] = i;
}
return dict;
}
// đ¸ å˛åæ ŧčŗæ:(for main columns)
cellValue(row, colName) {
return row[this.indexOfColumn[colName]];
}
// đ¸ parse all students data from rows
parseAllRows(){
for(const row of this.data){
if(this.isStudentRow(row)) Student.all.push(this._studentFromRow(row));
}
log(`âšī¸ å
ąæžå° ${Student.all.length} åå¸įčŗæã`);
log(`âšī¸ įŦŦä¸åå¸įīŧ${Student.all[0]}`);
}
// new student from row
_studentFromRow(row) {
return new Student(...this.mainColumns.map(col => this.cellValue(row, col)));
}
// đ¸ æ¯åĻįē襨é åīŧåĢæįį´ãåē§čãå§åįīŧ
isHeaderRow(row){
// å°æŧæ¯åä¸ģčĻæŦäŊåį¨ą(name)īŧæŦåéŊæä¸åå
į´ åŧ(value)æ¯äģĨéååį¨ąéå§į(startsWith)ã
return this.mainColumns.every(name => row.some(value => value.startsWith(name)));
}
// đ¸ is student row
isStudentRow(row){
const classNo = +this.cellValue(row, 'įį´');
const seatNo = +this.cellValue(row, 'åē§č');
if(!(100 < classNo && classNo < 320)) return false; // 䏿¯įį´
if(!(0 < seatNo && seatNo < 70)) return false; // 䏿¯åē§č
if(!this.cellValue(row, 'å§å')) return false; // 䏿¯å§å
const rank = +this.cellValue(row, 'åæŦĄ');
if(!(0 < rank && rank < 4)) return false; // 䏿¯åä¸å
return true;
}
}
Last updated
Was this helpful?