💼各班平均及前三名
⬆️ 需要： app.dataRangeValuesFromSheet()
// 🔸 app.rawData
app.rawData = new RawData(app.dataRangeValuesFromSheet('原始報表'));// 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('平均');
}
}arr.indexDictionaryForValues() - 找出相關欄位的索引。
arr.containsSubarray() - 判斷某資料列是哪種資料。
arr.removeDuplicates() - 用於合併所有班級的課程。
str.removeWhitespaces() - 將儲存格的值「標準化」。
💡 條件格式：自訂公式 - 用紅色底色顯示平均低於 60 的分數。
app.sheet.prototype - 運用
sheet.setValues()填各班平均、前三名表格。💾 sheet.rangeByRect() - 簡化選取 Range
Last updated