# 各班平均及前三名

{% tabs %}
{% tab title="💾 程式" %}

* [google sheet](https://docs.google.com/spreadsheets/d/1g1hZTwpC3RgcjgTdE41abm9fOtx2X-ks7zHKnQ3hUsw/edit#gid=549433062), [「各班平均及前三名」範本 (2022.07.08 版)](https://docs.google.com/spreadsheets/d/1g1hZTwpC3RgcjgTdE41abm9fOtx2X-ks7zHKnQ3hUsw/edit#gid=2001648390)
* [apps script](https://script.google.com/u/1/home/projects/1oGVk0OXf5gRcrwBMVR3UeyvvHq_56lTxTi37IugjjpkAAef2wp9kL2eM/edit), [處理各班平均 (2022.07.08)](https://script.google.com/u/1/home/projects/1oGVk0OXf5gRcrwBMVR3UeyvvHq_56lTxTi37IugjjpkAAef2wp9kL2eM/edit)
* OneCompiler ⟩ [從「班級標題」抓出「班級編號」](https://onecompiler.com/javascript/3y93rtjqx)
  {% endtab %}

{% tab title="🍄  RawData" %}
⬆️ 需要： [app.datarangevaluesfromsheet](https://lochiwei.gitbook.io/web/appendix/gas/app/member/app.datarangevaluesfromsheet "mention")

```javascript
// 🔸 app.rawData
app.rawData = new RawData(app.dataRangeValuesFromSheet('原始報表'));
```

:floppy\_disk: RawData

```javascript
// RawData
// - 🔸 data: [[string]]                // 原始資料
// - 🔸 commonColumns: [string]         // 原始資料
// - 🔸 indexOfCommonColumn (dict)      // 共有欄位索引
class RawData {

  // 🔸 custom class type name
  get [Symbol.toStringTag]() { return 'RawData' }

  // 🔸 init
  constructor(data){      // data from data range values

    if(!data) throw new Error(`⛔ RawData: data is null.`);

    // for debugging
    this.debugCounter = 0;

    // normalize all cell values (remove all whitespaces)
    this.data = data.map(row => 
      row.map(value => (value + '').removeWhitespaces())
    ); 

    this.commonColumns = ['座號', '姓名', '總分', '平均分數', '不及格數', '班級名次', '班級進退', '類組名次', '類組進退', '類組百分名次'];

    // parse phase 1: 找共有欄位索引
    this._parseCommonColumnIndices();     // 🔸 get this.indexOfCommonColumn

    // parse phase 2: 逐列抓出各班學生資料
    this._parseAllRows();                 // 🔸 get app.classes, app.students

  }

  // 🔸 parse common column indices
  _parseCommonColumnIndices(){

    for(const [index, row] of Object.entries(this.data)){

      // if not class header row, skip this row
      if(!this.isClassHeaderRow(row)) continue;   

      // now, it's class header row
      this.indexOfCommonColumn = row.indexOfValues(this.commonColumns);
      const n = Object.keys(this.indexOfCommonColumn).length;
      log(`ℹ️ 在第 ${index + 1} 列找到共有欄位(共 ${n} 欄)，索引值如下：`);
      log(this.indexOfCommonColumn)

      // braak for-loop
      break;    
    }

    // if not found, throw error
    if(!this.indexOfCommonColumn) throw new Error(
      `RawData._parseCommonColumnIndices(): 在「原始報表」中找不到共有欄位「${this.commonColumns}」`
    );
  }

  // 🔸 (學生列) 共有欄位儲存格資料: for student row, common columns
  _cell(row, columnName) {
    return row[this.indexOfCommonColumn[columnName]];
  }

  // 🔸 parse all classes/students data from rows
  _parseAllRows(){

    let currentClass = null;
    app.students = [];
    app.classes = [];

    // ------------------- helpers -------------------

    // 🔸 throw error if ...
    function throwErrorIf(condition, rowMsg, errMsg){
        if(condition) {
          log(rowMsg);
          log(row);
          throw new Error(errMsg);
        }
    }

    // 🔸 course score in current class
    function scoreInCurrentClass(row, course){
      const i = currentClass.indexOfCourse[course];     // 課程欄位索引
      return +String(row[i]).replace('#', '');          // '#53' -> '53' -> 53
    }

    // ------------------- parse all rows -------------------
    for(const row of this.data){

      // ------------------- student row -------------------
      // create new Student and push it into app.students
      if(this.isStudentRow(row)){

        // defence
        throwErrorIf(!currentClass, `ℹ️ 學生列資料：`, `⛔ 錯誤：找到學生資料，但卻沒有相對的 "Class" 物件❗`);

        // create new student
        const stu = new Student(currentClass.classNo, this._cell(row, '座號'), this._cell(row, '姓名'));
        app.students.push(stu);

        // defence
        throwErrorIf(
          !currentClass.courses || !currentClass.indexOfCourse,
          `ℹ️ 學生列資料：`,
          `⛔ 錯誤：找到學生資料，但卻沒有相對的「課程欄位索引」❗學生資料如下：`
        );

        // 登記課程分數
        stu.score = {};   // new dict

        for(const course of currentClass.courses){
          stu.score[course] = scoreInCurrentClass(row, course);
        }

        // 登記統計資料
        stu.stats = {
          '總分': +this._cell(row, '總分').replace(',', ''),
          '平均分數': this._cell(row, '平均分數'),
          '班級名次': +this._cell(row, '班級名次'),
        };

        // skip to next row
        continue;
      }

      // ------------------- class title -------------------
      // create new Class, push it into app.classes.
      if(this.isClassTitleRow(row)){

        // get class no. from row
        const classNo = this._getClassNoFromClassHeaderRow(row);

        // create new Class
        currentClass = new Class(classNo);
        app.classes.push(currentClass);

        // skip to next row
        continue;
      }

      // ------------------- class header -------------------
      // 找出班級課程的欄位索引，放入 currentClass 中
      if(this.isClassHeaderRow(row)){

        // defence
        if(!currentClass) {
          log(`⛔ 錯誤：RawData._parseAllRows() 找到「班級表頭」列，但卻沒有相對的「班級」資訊❗「班級表頭」如下：`);
          log(row);
          throw new Error(`沒有相對的「班級」資訊❗`);
        }

        const startIndex = row.indexOf('姓名') + 1;
        const endIndex = row.indexOf('總分');
        const courses = row.slice(startIndex, endIndex).filter(x => x !== '');

        currentClass.courses = courses;
        currentClass.indexOfCourse = row.indexOfValues(courses);

        // log(`ℹ️ RawData._parseAllRows()：${currentClass.classNo} 班「課程與欄位索引」：`);
        // log(currentClass.indexOfCourse);

        // skip to next row
        continue;
      }

      // ------------------- class average -------------------
      // 將班級平均放入 currentClass 中
      if(this.isClassAverageRow(row)){

        // defence
        if(!currentClass || !currentClass.courses || !currentClass.indexOfCourse) {
          log(`⛔ 錯誤：RawData._parseAllRows() 找到「班級平均」列，但卻沒有相對的「班級科目欄位」資訊❗「班級平均」如下：`);
          log(row);
          throw new Error(`沒有相對的「班級」資訊❗`);
        }

        currentClass.average = {};        // new dict
        currentClass.averageOfAllAverages = +row[this.indexOfCommonColumn['平均分數']];

        for(const course of currentClass.courses){
          const i = currentClass.indexOfCourse[course];
          const score = +row[i];
          currentClass.average[course] = score;
        }

        // skip to next row
        continue;
      }

    }// end: for-loop
  }

  // 🔸 get class NO from class header row
  _getClassNoFromClassHeaderRow(row){

    const i = row.findIndex(value => value.includes('成績一覽表'));

    if(i < 0){
      log(`⛔ 此列找不到有「成績一覽表」等文字的儲存格，本列資料如下：`);
      log(row);
      throw new Error(`RawData._getClassNoFromClassHeaderRow: 錯誤！`);
    }

    // 1. 班級編號
    //                                        ╭─1─╮ 
    const found = row[i].match(/\d{3,}[^\d\s]+(\d{3})/);
    
    if(!found){
      log(`⛔ 此列找不到有「班級編號」，相關儲存格內容如下：`);
      log(row[i]);
      throw new Error(`RawData._getClassNoFromClassHeaderRow: 錯誤！`);
    }
    
    return found[1];            // capture group 1
  }

  // 🔸 is class title row
  isClassTitleRow(row){
    return row.some(value => value.includes('成績一覽表'));
  }

  // 🔸 is class header row
  isClassHeaderRow(row){
    return row.containsSubarray(this.commonColumns);
  }

  // 🔸 is student row
  isStudentRow(row){

    const seatNoCell = +this._cell(row, '座號');
    const nameCell = this._cell(row, '姓名');

    if(!(0 < seatNoCell && seatNoCell < 100)) return false;   // 不是座號
    if(!(typeof nameCell === 'string')) return false;              // 不是姓名
    if(!(1< nameCell.length && nameCell.length < 30)) return false; // 不是姓名

    return true;    // 有座號、有姓名
  }

  // 🔸 is class average row
  isClassAverageRow(row){
    return row.includes('平均');
  }
  
}
```

{% endtab %}

{% tab title="⬆️ 需要" %}

* [arr.indexdictionaryforvalues](https://lochiwei.gitbook.io/web/js/val/builtin/arr/ext/arr.indexdictionaryforvalues "mention") - 找出相關欄位的索引。
* [arr.containssubarray](https://lochiwei.gitbook.io/web/js/val/builtin/arr/ext/arr.containssubarray "mention") - 判斷某資料列是哪種資料。
* [arr.removeduplicates](https://lochiwei.gitbook.io/web/js/val/builtin/arr/ext/arr.removeduplicates "mention") - 用於合併所有班級的課程。
* [str.removewhitespaces](https://lochiwei.gitbook.io/web/js/val/prim/str/method/str.removewhitespaces "mention") - 將儲存格的值「標準化」。
* [sort-by-multiple-keys](https://lochiwei.gitbook.io/web/appendix/algorithms/sort/sort-by-multiple-keys "mention")
* [custom-formula](https://lochiwei.gitbook.io/web/appendix/gas/tips/custom-formula "mention") - 用紅色底色顯示平均低於 60 的分數。
* [app.sheet.prototype](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype "mention") - 運用 <mark style="color:blue;">`sheet.setValues()`</mark>填各班平均、前三名表格。
* [sheet.rangebyrect](https://lochiwei.gitbook.io/web/appendix/gas/app/prototypes/app.sheet.prototype/sheet.rangebyrect "mention") - 簡化選取 [range](https://lochiwei.gitbook.io/web/appendix/gas/classes/range "mention")
  {% endtab %}
  {% endtabs %}