๐งSheetFields, SheetField
/**
* SheetFields
* ===========
* ่ฒ ่ฒฌ่งฃๆ่ฉฆ็ฎ่กจ้ ้ข๏ผไธฆๅฒๅญๆพๅฐ็ๆฌไฝ็ดขๅผใ
* ```
* new SheetFields([ ['็ญ็ด', /็ญ็ด/u], ['ๅงๅ', /^ๅงๅ$/u] ])
* ```
*/
class SheetFields {
/**
* ่ฒ ่ฒฌ่งฃๆ้ ้ข็ๆฌไฝ็ดขๅผ(index)๏ผ
* ๆฌไฝๅ็จฑใๆจฃๅผไนๅจ้่ฃก่จญๅฎใ
* ```
* 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()
}
Last updated
Was this helpful?