/**
* SheetFields
* ===========
* 負責解析試算表頁面，並儲存找到的欄位索引。
* ```
* new SheetFields([ ['班級', /班級/u], ['姓名', /^姓名$/u] ])
* ```
*/
class SheetFields {
/**
* 負責解析頁面的欄位索引(index)，
* 欄位名稱、樣式也在這裡設定。
/**
* SheetField
* ==========
* 儲存找到的欄位索引。
*/
class SheetField {
// init
constructor(name, pattern) {
this.name = name;
this.pattern = pattern;
this.index = undefined;
}
}
* ```
* new SheetFields('頁面名稱', [ ['班級', /班級/u], ['姓名', /^姓名$/u] ])
* ```
* @param {string} sheetName - 要解析欄位的頁面名稱
* @param {Array<[Name, Pattern]>} arr - 欄位 [名稱, 樣式(regex)] 的陣列
*/
constructor(sheetName, arr){
this._fields = new Map();
for(let [name, pat] of arr){
this._fields.set(name, new SheetField(name, pat));
}
this.parseSheet(sheetName);
}
// fields.indexOfField(name)
indexOfField(name) {
return this._fields.get(name).index;
}
// fields.patternOfField(name)
patternOfField(name) {
return this._fields.get(name).pattern;
}
// fields.fieldNames
get fieldNames() {
return this.array.map(f => f.name);
}
// fields.array: SheetField[]
get array(){
return [...this._fields.values()];
}
// fields.notFoundYet: SheetField[]
get notFoundYet() {
return this.array.filter(f => f.index === undefined);
}
// fields.allFound
get allFound() {
return this.notFoundYet.length === 0;
}
// fields.parseSheet(name)
parseSheet(sheetName){
let filename = '- sheetFields.parseSheet()';
// get sheet's data range values
let sheet = app.sheet.byName(sheetName);
let values = sheet.getDataRange().getValues(); // 2D array
if (values.length === 0) throw new Error(`「${sheetName}」頁面沒有任何資料。`);
LOG(filename, `🚩 開始解析「${sheetName}」頁面的「${this.fieldNames}」欄位索引(共 ${values.length} 列, ${values[0].length} 欄)`)
// 開始搜尋欄位的 index
outerFor:
for(let row of values){
for(let [i, cell] of row.entries()){
// for each field (index not found yet)
for(let f of this.notFoundYet){
cell = cell.replace(/\s/g, ''); // ⭐️ remove all white spaces
if (f.pattern.test(cell)) {
f.index = i;
LOG(filename, `😀 找到「${f.name}」欄位 index = ${i}`)
}
}
if (this.allFound) break outerFor;
}//end: inner for
}//end: outer for
if (!this.allFound) {
throw new Error(`以下欄位沒找到：${this.notFoundYet.map(f => f.name)}`);
}
LOG(filename, `🏁 解析成功。`)
}// end: parseSheet()
}