💼高中獎學金名冊

由「原始報表」產生「獎學金得獎名單」

💾 程式

• google sheet

• google apps script

🔲 待辦

• 修正只抓高二、高三，沒有高一的問題。

improvements

main

💾 google apps script

// main
function main() {
  
  // get/parse raw data
  app.rawData = new RawData('原始報表', ['班級', '座號', '姓名', '平均分數', '名次']);
  
  // Student.all is ready

  // 得獎名單
  makeAwardTable();
}

// make award table
function makeAwardTable(){

  const data = Student.dataForAwardTable();
  
  app.makeTable('得獎名單', data, {

    columnWidths: (r) => [
      [1, 2, 40],     // 編號	班級
      [3, 1, 60],     // 姓名
      [4, 1, 100],    // 日常生活表現
      [5, 1, 80],     // 學業總平均
      [6, 1, 60],     // 金額
      [7, 1, 120],    // 蓋章或簽名
    ],

    ranges: {

      border: (r) => [r.inset({top: 3, bottom: 12})],
      gridlines: (r) => [r.inset({top: 3, bottom: 12})],
      alignCenter: (r) => [r.inset({bottom: 12})],
      gray: (r) => [r.nthRow(4)],
      mergeAcross: (r) => [r.nthRow(1)],

      numberFormat: (r) => [
        {format: '0.0', ranges: [r.nthColumn(5).inset({top: 4, bottom: 12})]}
      ],
    }

  });
}

RawData

// 📅 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;
  }
  
}

Student

/**
 * Student
 * =======
 * 主要功能：儲存「平均分數、名次」
 */
class Student {

  // init
  constructor(classNo, seatNo, name, avg, rank){
    this.classNo = classNo;
    this.seatNo = seatNo;
    this.name = name;
    this.average = +avg;     // 平均分數
    this.rank = +rank;       // 名次
  }

  // 高二、三：每班 1 名，學業成績 >= 80。
  get isAwarded() {
    // 第一學期
    if (+app.semester === 1) {
      return this.rank === 1 && this.average >= 80;
    } 
    // 第二學期
    else {
      if (+this.classNo < 200) return this.rank <= 2;     // 高一  ：2 名／班
      return this.rank === 1 && this.average >= 80;       // 高二三：1 名／班、學業成績 >= 80
    }
  }

  // toString
  toString() {
    return `${this.classNo}-${this.seatNo} ${this.name}：平均分數 = ${this.average}, 名次 = ${this.rank}`;
  }

  // row for table (with index: i)
  rowForAwardTable(i) {
    return [i, this.classNo, this.name, '表現優秀', this.average, app.award, ''];
  }

}

// 🔸 all students
Student.all = [];

// Student award list data
Student.dataForAwardTable = function(){

  const students = Student.all.filter(stu => stu.isAwarded);

  const header = ['編號',	'班級',	'姓名',	'日常生活表現',	'學業總平均',	'金額',	'蓋章或簽名'];
  const cols = header.length;

  const title = [`臺北市 ${app.year} 學年度第 ${app.semester} 學期高級中等學校學生獎學金印領清冊`].padEndSpaces(cols - 1);
  const subtitle = [`校名：臺北市立陽明高級中學`].padEndSpaces(cols - 1);
  const line = [].padEndSpaces(cols);

  /* ------------------------ 寫入表尾 --------------------------- */

  const n = students.length;               // 總人數
  const total = n * +app.award;         // 合計總金額
  const junior = (+app.semester === 2) ? `；一年級上學期名額併入本學期，爰每班分配 2 名` : '';

  const footer = [
    [`合計總人數：${n}`, '', '', `合計總金額：${total}`, '', '', ''],
    [`學校總人數：`, '', '', `學校總班級數：`, '', '', ''],
    ['', '', '', '', '', '', ''],
    ['承辦人：',	'', '',		'註冊組長：',	'教務主任：',	'',	'校長：'],
    ['', '', '', '', '', '', ''],
    ['', '', '', '', '', '', ''],
    ['出納：',	'', '',		'主計人員：',	'會計主任：', '', '',],
    ['', '', '', '', '', '', ''],
    ['', '', '', '', '', '', ''],
    ['⭐️ 本表填妥後，請核章。', '', '', '', '', '', ''],
    [`⭐️ 二、三年級每班分配 1 名${junior}。`, '', '', '', '', '', ''],
  ];

  return [
    title, subtitle, line,
    header, 
    ...students.map((stu, i) => stu.rowForAwardTable(i+1)),
    line,
    ...footer,
  ];
};