💼國中成績一覽表

⭐️ 重點

由原始資料(全部學生成績列表)挑出「國語文、英語文、社會、數學、理化」五科成績，產生「各班平均」、「各班前3名」、「校排前30名」，與「各班成績表」。

「社會」分為三科：「歷史」、「地理」、「公民」，分數都一樣，只算一個「社會」成績。

💾 程式

• google sheet

• google apps script

💈範例

💾 google apps script

// 「各班成績表」
function makeClassTable(classNo){

  // prepare data

  let data = Student.dataForClassTable(classNo);

  // set values into sheet

  const sheet = app.sheetByName(classNo);
  sheet.clear();
  sheet.setValues(data);

  // ----------------------- format tables -----------------------

  const rows = data.length;
  const cols = data[0].length;
  const dataRange = sheet.range(1,1,rows, cols);

  // ┌─────┐
  // │ 畫線 │
  // └─────┘

  dataRange
    .setBorder(true, true, true, true, true,true);

  // ┌─────────┐
  // │ 數字格式 │ 
  // └─────────┘
  dataRange
    .inset({top: 2, left: 3, right: 2})
    .setNumberFormat('0.0');

  // ┌─────┐
  // │ 對齊 │
  // └─────┘
  dataRange.firstNColumns(3).alignCenter();   // 前三欄：「班級、座號、姓名」
  dataRange.firstNRows(2).alignCenter();      // 前兩列：標題、表頭

  // ┌─────────┐
  // │ 水平合併 │
  // └─────────┘
  dataRange.nthRow(1)         // 標題
    .mergeAcross()
    .setFontSize(14)
    .setFontWeight('bold');

  // ┌───────┐
  // │ 背景色 │
  // └───────┘
  [
    dataRange.nthRow(2),      // 表頭
    dataRange.cell(rows, 3),  // 「平均」格
    
  ].forEach(rng => rng.setBackground(app.color.gray))
  

  // ┌─────────┐
  // │ 欄位寬度 │
  // └─────────┘
  sheet.setColumnWidths(1, 2, 40);           // 「座號」欄
  sheet.setColumnWidths(3, cols - 4, 60);    //
  sheet.setColumnWidths(cols - 1, 2, 40);    // 「班排	校排」欄

  // ┌─────────┐
  // │ 條件格式 │
  // └─────────┘

  const scoresRange = dataRange.inset({top: 3, left: 3, right: 4});   // 五科分數區
  const averageRange = dataRange.nthColumn(10);                       // 「平均」欄
  const classRankRange = dataRange.nthColumn(cols - 1);               // 「班排」欄
  const schoolRankRange = dataRange.nthColumn(cols);                  // 「校排」欄

  // 分數 < 60
  const redIfLessThan60 = SpreadsheetApp
    .newConditionalFormatRule()    // ⭐️ ConditionalFormatRuleBuilder
    .whenNumberLessThan(60)                 // - 🔸 condition
    .setBackground(app.color.lightRed)      // - 🔸 formats
    .setRanges([scoresRange, averageRange]) // - 🔸 ranges
    .build();                      // ⭐️ ConditionalFormatRule

  // 班排前3
  const blueIfLessThan4 = SpreadsheetApp
    .newConditionalFormatRule()    // ⭐️ ConditionalFormatRuleBuilder
    .whenNumberLessThan(4)                 // - 🔸 condition
    .setBackground(app.color.lightBlue)      // - 🔸 formats
    .setRanges([classRankRange]) // - 🔸 ranges
    .build();                      // ⭐️ ConditionalFormatRule

  // 校排前30
  const greenIfLessThan31 = SpreadsheetApp
    .newConditionalFormatRule()    // ⭐️ ConditionalFormatRuleBuilder
    .whenNumberLessThan(31)                 // - 🔸 condition
    .setBackground(app.color.lightGreen)      // - 🔸 formats
    .setRanges([schoolRankRange]) // - 🔸 ranges
    .build();                      // ⭐️ ConditionalFormatRule

  [
    redIfLessThan60,      // score < 60
    blueIfLessThan4,      // top 3 each class
    greenIfLessThan31,    // top 30 at school

  ].forEach(rule => sheet.appendConditionalFormatRule(rule));

};

⬆️ 需要

• app.range.prototype

• arr.indexDictionaryForValues()

• arr.padEndSpaces()

• arr.sum(), .average() - 計算總分、平均

📅 歷史

• 2022.07.21：平均與老闆跑出來的不一樣（原因：原來的欄位順序跟後來的不一樣）

• 2022.07.24：全新改版，用 Google Apps Script 重新改寫。

improvements

🚩 main

function main() {

  log(`學生數：${app.students.length}`);
  log(`班級：${app.classes}`);

  // first 20 students
  // Array.from({length: 20}, (_, i) => i).forEach(i => log(app.students[i]));

  // first class
  log(`第一班的班平均：`)
  log(Class.all[0].score);

  // --------------------------- 製表 ---------------------------
  
  makeAverageTable(); // 「各班平均」表 
  makeTop3Table();    // top 3
  makeTop30Table();   // top 30

  // 各班成績表
  app.classes.forEach(classNo => makeClassTable(classNo));
  
}

// 班排
function setRankOfClass(){
  // 🔸 student.rankOfClass
  app.classes.forEach(classNo => {
    app.students.filter(s => s.classNo === classNo && s.total !== '')   // 排除完全沒分數的人
      .rank('rankOfClass', (a, b) => +b.total - +a.total);
  });
}

// 校排
function setRankOfSchool(){
  // 🔸 student.rankOfSchool
  app.students.filter(s => s.total !== '')   // 排除完全沒分數的人
      .rank('rankOfSchool', (a, b) => +b.total - +a.total);
}

// ?01 - ?06 班成績表
function makeClassTable(classNo){

  app.makeTable(classNo, Student.dataForClassTable(classNo), {

    // column widths
    columnWidths: (r, {cols=r.cols()}={}) => [[1, cols, 60]],

    // ranges
    ranges: {

      // border
      border: (r) => [r.inset({top: 1, bottom: 1})],

      // grid lines
      gridlines: (r) => [r.inset({top: 1})],

      // gray ranges
      gray: (r, {rows=r.rows()}={}) => [
        r.nthRow(2), 
        r.cell(rows, 3)
      ],

      // bold
      bold: (r) => [r.nthRow(1)],

      // merge across
      mergeAcross: (r) => [r.nthRow(1)],

      // align center
      alignCenter: (r) => [r],

      // ----- number format -----

      numberFormat: (r, {cols=r.cols()}={}) => [
        {format: '0.00', ranges: [
          r.inset({top: 2, left: 3, right: 2}),
        ]},
        {format: '00', ranges: [
          r.nthColumn(2).inset({top: 2, bottom: 1})
        ]},
        {format: '0', ranges: [
          r.lastNColumns(2).inset({top: 2, bottom: 1})
        ]},
      ],

      // ----- conditinoal format -----

      scores: (r, {cols=r.cols()}={}) => [
        r.inset({top: 2, left: 3, right: 4}),
        r.nthColumn(cols - 2).inset({top: 2, bottom: 1}),
      ],

      top3: (r) => [r.lastNthColumn(2).inset({top: 2, bottom: 1})],
      top30: (r) => [r.lastNthColumn(1).inset({top: 2, bottom: 1})],
    },

  });
}

// 「各班平均」表 
function makeAverageTable(){
  app.makeTable('各班平均', Class.dataForAverageTable, {
    columnWidths: (r, {cols=r.cols()}={}) => [[1, cols, 60]],
    ranges: {

      border: (r) => [r.inset({top: 1, bottom: 1})],
      gridlines   : (r) => [r],
      numberFormat: (r) => [{format: '0.0', ranges: [r.inset({top: 2, left: 1})]}],
      mergeAcross : (r) => [r.nthRow(1)],
      alignCenter : (r) => [r],
      gray: (r, {rows=r.rows()}={}) => [r.nthRow(2), r.cell(rows, 1)],

      scores: (r) => [
        r.inset({top: 2, left: 1, right: 2}),   // 五科分數區
        r.lastNthColumn(1).inset({top: 2, bottom: 1}),   // 平均欄
      ],
    }
  });
}

// 「班排前三」表 
function makeTop3Table() {
  app.makeTable('班排前三', Student.dataForTop3Table(), {
    columnWidths: (r, {cols=r.cols()}={}) => [[1, cols, 60]],
    ranges: {
      gridlines: (r) => [r.inset({top: 1})],
      border: (r) => [r.inset({top: 1})],
      alignCenter: (r) => [r],            // whole data range
      mergeAcross: (r) => [r.nthRow(1)],  // title
      bold: (r) => [r.nthRow(1)],         // title
      gray: (r) => [r.nthRow(2)],         // header
      numberFormat: (r) => [{format: '00', ranges: [r.nthColumn(2).inset({top: 2})]}],  // seat No.
    }
  });
}

// 「top 30」表 
function makeTop30Table() {
  app.makeTable('校排前三十', Student.dataForTop30Table(), {

    columnWidths: (r, {cols=r.cols()}={}) => [[1, cols, 60]],
    
    ranges: {
      gridlines: (r) => [r.inset({top: 1})],
      border: (r) => [r.inset({top: 1})],
      alignCenter: (r) => [r],            // whole data range
      mergeAcross: (r) => [r.nthRow(1)],  // title
      bold: (r) => [r.nthRow(1)],         // title
      gray: (r) => [r.nthRow(2)],         // header

      numberFormat: (r) => [
        {format: '00', ranges: [r.nthColumn(2).inset({top: 2})]},         // seat No.
        {format: '0.0', ranges: [r.inset({top: 2, left: 3, right: 1})]},  // 總分	平均
      ],
    }
  });
}

⭐️ makeTable

⭐ app.makeTable()

// 🔸 app.makeTable
app.makeTable = function(sheetName, data, {

  // internal parameters for `app.makeTable` function
  sheet = app.sheetByName(sheetName),
  rows = data.length,
  cols = data[0].length,
  dataRange = sheet.range(1,1,rows, cols),

  // ⭐ set column widths
  columnWidths,     // 欄位寬度：(dataRange) => [[startCol, numCols, width]]

  // ⭐ subranges in dataRange
  ranges = {

    border,         // 粗框線：(dataRange) => [Range]
    gridlines,      // 細格線：(dataRange) => [Range]

    numberFormat,   // 儲存格格式：(dataRange) => [{format: '0.0', ranges: [Range]}]
    alignCenter,    // 置中對齊 ：(dataRange) => [Range]

    gray,           // 灰色：(dataRange) => [Range]
    lightBlue,      // 淡藍：(dataRange) => [Range]
    lightGreen,     // 淡綠：(dataRange) => [Range]
    bold,           // 粗體：(dataRange) => [Range]

    mergeAcross,    // 水平合併：(dataRange) => [Range]

    // conditional formats
    scores,         // 分數區：不及格者淡紅 (dataRange) => [Range]
    top3,           // 班排前三：淡藍 (dataRange) => [Range]
    top30,          // 校排前三十：淡綠(dataRange) => [Range]
  },

}={ranges: {}}){

  // ----------------------- set values into sheet -----------------------

  sheet.clear();
  sheet.setValues(data);

  // ----------------------- format tables -----------------------

  // ┌───┐
  // │ 畫線 │
  // └───┘
  
  // grid lines first
  if (ranges.gridlines) {
    ranges.gridlines(dataRange).forEach(range => 
      range.setBorder(true, true, true, true, true, true)
    )
  }

  // border last
  if (ranges.border) {
    ranges.border(dataRange).forEach(range => 
      range
        .setBorder(true, true, true, true, null, null, 'black', SpreadsheetApp.BorderStyle.SOLID_MEDIUM)
    )
  }

  // ┌─────┐
  // │ 數字格式 │ 
  // └─────┘
  if (ranges.numberFormat) {
    ranges.numberFormat(dataRange).forEach(({format, ranges}) => 
      ranges.forEach(range => range.setNumberFormat(format))
    )
  }

  // ┌───┐
  // │ 對齊 │
  // └───┘
  if (ranges.alignCenter) {
    ranges.alignCenter(dataRange).forEach(range => 
      range.alignCenter()
    )
  }

  // ┌─────┐
  // │ 水平合併 │
  // └─────┘
  if (ranges.mergeAcross) {
    ranges.mergeAcross(dataRange).forEach(rng => rng.mergeAcross())
  }

  // ┌───┐
  // │ 粗體 │
  // └───┘
  if (ranges.bold) {
    ranges.bold(dataRange).forEach(rng => rng.setFontWeight('bold'))
  }

  // dataRange.nthRow(1)         // 標題
  //   .setFontSize(14)

  // ┌────┐
  // │ 背景色 │
  // └────┘
  if (ranges.gray) {
    ranges.gray(dataRange).forEach(range => 
      range.setBackground(app.color.gray)
    )
  }

  if (ranges.lightBlue) {
    ranges.lightBlue(dataRange).forEach(range => 
      range.setBackground(app.color.lightBlue)
    )
  }

  if (ranges.lightGreen) {
    ranges.lightGreen(dataRange).forEach(range => 
      range.setBackground(app.color.lightGreen)
    )
  }

  // ┌─────┐
  // │ 欄位寬度 │
  // └─────┘

  if (columnWidths) {
    columnWidths(dataRange).forEach(([start, cols, width]) => 
      sheet.setColumnWidths(start, cols, width)
    )
  }

  // ┌─────┐
  // │ 條件格式 │
  // └─────┘

  if (ranges.scores) {
    sheet.appendConditionalFormatRule(app.rules.redIfLessThan60(ranges.scores(dataRange)))
  }

  if (ranges.top3) {
    sheet.appendConditionalFormatRule(app.rules.blueIfTop3(ranges.top3(dataRange)))
  }

  if (ranges.top30) {
    sheet.appendConditionalFormatRule(app.rules.greenIfTop30(ranges.top30(dataRange)))
  }

};

color

💾 app.color

// 🔸 app.color
app.color = {
  gray: '#eeeeee',
  lightRed: "#ffcccc",
  lightGreen: '#ccffcc',
  lightBlue: '#ccccff',
};

rules

// 🔸 app.rules
app.rules = {

  // 分數 < 60
  redIfLessThan60(ranges) {
    return SpreadsheetApp
      .newConditionalFormatRule()           // ⭐️ ConditionalFormatRuleBuilder
      .whenNumberLessThan(60)               // - 🔸 condition
      .setBackground(app.color.lightRed)    // - 🔸 formats
      .setRanges(ranges)                    // - 🔸 ranges
      .build();                             // ⭐️ ConditionalFormatRule
  },

  // 班排前3
  blueIfTop3(ranges) {
    return SpreadsheetApp
      .newConditionalFormatRule()
      .whenNumberLessThan(4)              // n < 4
      .setBackground(app.color.lightBlue) // light blue
      .setRanges(ranges)                  // given ranges
      .build(); 
  },

  // 校排前30
  greenIfTop30(ranges) {
    return SpreadsheetApp
      .newConditionalFormatRule() 
      .whenNumberLessThan(31)               // when: n < 31
      .setBackground(app.color.lightGreen)  // light green 
      .setRanges(ranges)                    // given ranges
      .build(); 
  },

} 

🍄 RawData

💾 google apps script

// RawData
// - 🔸 data: [[string]]                // 原始資料
// - 🔸 mainColumns: [string]     // 主要欄位
// - 🔸 indexOfColumn (dict)      // 共有欄位索引
class RawData {

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

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

    let data = app.dataRangeValuesFromSheet(sheetName);

    if(!data) throw new Error(`⛔ RawData: 找不到試算表「${sheetName}」的 data range`);

    // for debugging
    this.debugCounter = 0;

    // 🔸 .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
  // 🔸 app.indexOfColumn
  _parseColumnIndices(){

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

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

      // now, it's a header row
      app.indexOfColumn = this._indexDictForMainColumnsFromRow(row);
      const n = Object.keys(app.indexOfColumn).length;
      log(`ℹ️ 在第 ${i + 1} 列找到主要欄位(共 ${n} 欄)，索引值如下：`);
      log(app.indexOfColumn)

      // braak for-loop
      break;    
    }

    // if not found, throw error
    if(!app.indexOfColumn) throw new Error(
      `RawData._parseColumnIndices(): 在「原始報表」中找不到主要欄位「${app.mainColumns}」`
    );
  }

  // find indices for main cols from row
  _indexDictForMainColumnsFromRow(row){
    
    let dict = {};
    
    for(const colName of app.mainColumns){
        const i = row.findIndex(x => x.startsWith(colName));  // ⭐️ 原始科目名稱後面還有編號！
        if(i < 0) throw new Error(`⛔ array.indexDictionaryForValues(): ${row} doesn't have value "${colName}"`);
        dict[colName] = i;
    }
    
    return dict;
  }

  // 🔸 (學生列) 儲存格資料: for student row (main columns)
  _cell(row, colName) {
    return row[app.indexOfColumn[colName]];
  }

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

    // 🔸 app.students
    app.students = [];

    // ------------------- parse all rows -------------------
    for(const row of this.data){
      if(this.isStudentRow(row)) app.students.push(new Student(row));
    }// end: for-loop
  }

  // 🔸 是否為表頭列（含有班級、座號、姓名等）
  isHeaderRow(row){
    // 對於每個主要欄位名稱(name)，本列都有一個元素值(value)是以這個名稱開始的(startsWith)。
    return app.mainColumns.every(name => row.some(value => value.startsWith(name)));
  }

  // 🔸 is student row
  isStudentRow(row){

    const classNo = +this._cell(row, '班級');
    const seatNo = +this._cell(row, '座號');

    if(!(700 < classNo && classNo < 910)) return false;   // 不是班級
    if(!(0 < seatNo && seatNo < 70)) return false;   // 不是座號
    if(!this._cell(row, '姓名')) return false;   // 不是姓名

    // 成績 >= 0
    return app.courseColumns.every(crs => +this._cell(row, crs) >= 0);
  }
  
}

🍄 Class

class Class {

  // init
  constructor(classNo) {

    this.classNo = classNo;
    this.students = app.students.filter(s => s.classNo === classNo);
    this.count = this.students.length;

    // this.score (dict), this.scores (array)
    this._setupAverages();
  }

  // setup averages
  _setupAverages(){

    this.scores = [
      'chinese', 'english', 'society', 'math', 'nature'
    ].map(crs => this._average(crs));

    this.score = {
      chinese: this._average('chinese'),
      english: this._average('english'),
      society: this._average('society'),
      math   : this._average('math'),
      nature : this._average('nature'),
    };

    this.total = this.scores.sum();
    this.average = this.scores.average();
  }

  _average(course){
    const studentsWithScore = this.students.filter(s => s.score[course] !== '');
    return +studentsWithScore.map(s => +s.score[course]).average().toFixed(2);
  }

  get rowForAverageTable() {
    return [
      this.classNo,
      this.score.chinese,
      this.score.english,
      this.score.society,
      this.score.math,
      this.score.nature,
      this.total,
      this.average,
    ];
  }

  get footerForClassTable() {
    return [
      '', '', '平均', ...this.scores, '', '', '', '',
    ];
  }

}

// Class.init()
Class.init = function(){

  // all classes
  Class.all = app.classes.map(classNo => new Class(classNo));

  // Class.average
  Class.average = {
    chinese: Class.all.map( cls => cls.score.chinese).average(),
    english: Class.all.map( cls => cls.score.english).average(),
    society: Class.all.map( cls => cls.score.society).average(),
    math   : Class.all.map( cls => cls.score.math   ).average(),
    nature : Class.all.map( cls => cls.score.nature ).average(),
  };

  // Class.total
  Class.total = Object.values(Class.average).sum();

  // Class.dataForAverageTable
  Class.dataForAverageTable = (() => {

    const header = ['班級',	'國語',	'英語',	'社會',	'數學',	'自然',	'總分',	'平均'];
    const cols = header.length;
    const footer = [
      '平均', 
      Class.average.chinese,
      Class.average.english,
      Class.average.society,
      Class.average.math,
      Class.average.nature,
      Class.total,
      ''
    ];

    return [
      [`${app.year} 學年第 ${app.semester} 學期「${app.grade}」第${app.exam}次定期評量每班各科平均`].padEndSpaces(cols - 1),  // title
      header,   // header
      ...Class.all.map(cls => cls.rowForAverageTable), // rows for each class
      footer,
    ];

  })();
};

// Class.from()
Class.from = function(classNo){
  return Class.all.filter(cls => +cls.classNo === +classNo)[0];
};

🍄 Student

// Student
// - 🔸 classNo: string
// - 🔸 seatNo : string
// - 🔸 name   : string
// - 🔸 score  : dict {string: number}            // defined by RawData._parseAllRows()
// - 🔸 courses: [string]
// - 🔸 scores: [number]
// - 🔸 rowForTop3Table: [string] (getter)
class Student {

  // init
  constructor(row){
    
    this.classNo = app.cellValue(row, '班級');
    this.seatNo = app.cellValue(row, '座號');
    this.name = app.cellValue(row, '姓名');

    this.score = {
      chinese: app.cellValue(row, '語文(國語文)'),
      english: app.cellValue(row, '語文(英語文)'),
      society: app.cellValue(row, '社會(歷史)'),
      math   : app.cellValue(row, '數學'),
      nature : app.cellValue(row, '自然科學(理化)'),
    };

    // 如果全部都沒成績，就以「空白」紀錄。
    this.total = this.scores.map(v => +v).reduce((sum, value) => sum + value) || '';
    this.average = this.total ? (this.total / 5).toFixed(2) : '';
  }

  get courses(){
    return Object.keys(this.score);
  }

  get scores(){
    return Object.values(this.score);
  }

  // 為「各班成績表」準備「列資料」
  get rowForClassTable() {
    return [
      this.classNo,
      this.seatNo,
      this.name,
      this.score.chinese,
      this.score.english,
      this.score.society,
      this.score.math,
      this.score.nature,
      this.total,
      this.average,
      this.rankOfClass,
      this.rankOfSchool,
    ];
  }

  // 班班前三
  get rowForTop3Table() {
    // 班級	座號	姓名	名次
    return [
      this.classNo,
      this.seatNo,
      this.name,
      this.rankOfClass,
    ];
  }

  // row for top 30
  get rowForTop30Table() {
    // '班級',	'座號',	'姓名',	'總分',	'平均',	'校排'
    return [
      this.classNo,
      this.seatNo,
      this.name,
      this.total,
      this.average,
      this.rankOfSchool,
    ];
  }

}

// data for 各班成績表
Student.dataForClassTable = function(classNo){

  const header = [
    '班級',   '座號',
    '姓名',   '國語文',
    '英語文', '社會',
    '數學',   '理化',
    '總分',   '平均',
    '班排',   '校排'
  ];

  const cols = header.length;   // 欄位數

  return [

    [app.classTableTitle].padEndSpaces(cols - 1),     // title
    header,                                           // header

    // student rows for this class
    ...app.students.filter(s => s.classNo === classNo).map(s => s.rowForClassTable),

    Class.from(classNo).footerForClassTable,   // footer
  ];

};

// data for top 3
Student.dataForTop3Table = function() {
  
  const header = ['班級',	'座號',	'姓名',	'名次'];
  const cols = header.length;
  const title = [`${app.year} 學年第 ${app.semester} 學期${app.grade}第${app.exam}次段考班排前三`].padEndSpaces(cols - 1);

  const students = app.students
    .filter(s => +s.rankOfClass < 4)
    .sort((a,b) => +a.classNo - +b.classNo || +a.rankOfClass - +b.rankOfClass);

  return [
    title,
    header,
    ...students.map(s => s.rowForTop3Table),
  ];

};

// data for top 30
Student.dataForTop30Table = function() {

  const header = ['班級',	'座號',	'姓名',	'總分',	'平均',	'校排'];
  const cols = header.length;
  const title = [`${app.year} 學年第 ${app.semester} 學期${app.grade}第${app.exam}次段考校排前三十`].padEndSpaces(cols - 1);

  const students = app.students
    .filter(s => +s.rankOfSchool < 31)
    .sort((a,b) => +a.rankOfSchool - +b.rankOfSchool);

  return [
    title,
    header,
    ...students.map(s => s.rowForTop30Table),
  ];

};