๐ŸšงSheetFields, SheetField

  • ๐Ÿ‘” 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?