Google 試算表 x GAS 學生學科成績查詢系統

// Code.gs - 學生學科成績查詢系統核心後端邏輯
function doGet() {
  return HtmlService.createTemplateFromFile('Index')
    .evaluate()
    .setTitle('學科成績查詢系統') 
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

// 取得全校有效班級清單 (支援雙重身分/資源班拆分)
function getClasses() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("學生資料");
    if (!sheet) return [];
    var data = sheet.getDataRange().getDisplayValues();
    if (data.length < 2) return [];
    var idx = -1;
    for(var i=0; i<data[0].length; i++){
        if(String(data[0][i]).indexOf("班") > -1) { idx = i; break; }
    }
    if (idx === -1) return [];
    var classes = new Set();
    for (var i = 1; i < data.length; i++) {
      var rawClass = String(data[i][idx]).trim();
      var parts = rawClass.split(/[,，、/ ]+/);
      parts.forEach(p => { if(p.trim()) classes.add(p.trim()); });
    }
    return Array.from(classes).sort(function(a, b) {
      return a.localeCompare(b, undefined, {numeric: true, sensitivity: 'base'});
    });
  } catch (e) { return []; }
}

// 取得考試參數設定與年級總人數
function getExamParams() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("參數設定");
  var config = { enrollment: {}, radarTarget: "" };
  if (sheet) {
    var data = sheet.getDataRange().getDisplayValues();
    if (data.length > 1 && data[1].length > 4) config.radarTarget = String(data[1][4]).trim();
    for (var i = 1; i < data.length; i++) {
      var examName = String(data[i][0]).trim();
      var count7 = parseInt(data[i][1]); var count8 = parseInt(data[i][2]); var count9 = parseInt(data[i][3]); 
      if (examName) {
        config.enrollment[examName] = {};
        if (!isNaN(count7)) config.enrollment[examName]["1"] = count7;
        if (!isNaN(count8)) config.enrollment[examName]["2"] = count8;
        if (!isNaN(count9)) config.enrollment[examName]["3"] = count9;
      }
    }
  }
  return config;
}

// 智慧年級自動辨識 (支援 701/101 格式)
function detectGrade(classStr) {
  var s = String(classStr).trim();
  if (s.length === 3 && !isNaN(s)) {
     var firstChar = s.charAt(0);
     if (firstChar === '1' || firstChar === '7') return "1";
     if (firstChar === '2' || firstChar === '8') return "2";
     if (firstChar === '3' || firstChar === '9') return "3";
  }
  return "";
}