🍄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?