記名測驗- AI出題（v3.1.1）

線上測驗一站完成：出題、作答、計分、回傳、繳交檢核與教師審閱。

本工具協助老師透過 AI 快速產生可貼入 NewPlus 的學生端測驗 HTML，並搭配 Google Sheet 自動記錄成績、統計答題表現、檢核繳交狀況；另可搭配教師端檢核頁進行分區更新、圖表分析、學習心得審閱與教師備註。
【 AI出題的前置作業-影片簡介】

一、複製 Apps Script 程式碼

請先按「複製 Apps Script 程式碼」，再貼到 Google Sheet 的 Apps Script 編輯器。程式碼已內嵌於本頁並預設隱藏，教師不需要展開即可直接複製；學生固定名單請在 Google Sheet 建立完成後，直接到「學生固定名單」工作表維護。

const SCORE_SHEET_NAME = '成績紀錄';
const QUESTION_STATS_SHEET_NAME = '題目統計分析';
const QUIZ_CHECK_SHEET_NAME = '測驗繳交檢核';
const TEACHER_REVIEW_SHEET_NAME = '教師審閱紀錄';
const ROSTER_SHEET_NAME = '學生固定名單';


function setupSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  initializeSheets_(ss);
}

function doGet(e) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    initializeSheets_(ss);

    const action = e.parameter.action || '';
    const callback = e.parameter.callback || '';

    let result = {
      ok: false,
      message: 'unknown action'
    };

    if (action === 'getRosterStatus') {
      result = getRosterStatus_(ss);
    }

    if (action === 'validateStudent') {
      result = validateStudentForSubmit_(ss, {
        className: e.parameter.className || '',
        seatNo: e.parameter.seatNo || '',
        studentName: e.parameter.studentName || ''
      });
    }


    if (action === 'teacherGetOverview') {
      result = teacherGetOverview_(ss, e.parameter.className || '');
    }

    if (action === 'teacherGetQuizCheckRows') {
      result = teacherGetQuizCheckRows_(ss, e.parameter.className || '');
    }

    if (action === 'teacherGetScoreDistribution') {
      result = teacherGetScoreDistribution_(ss, e.parameter.className || '');
    }

    if (action === 'teacherGetQuestionStats') {
      result = teacherGetQuestionStats_(ss);
    }

    if (action === 'teacherGetReflections') {
      result = teacherGetReflections_(ss, e.parameter.className || '');
    }

    if (action === 'teacherSaveReview') {
      result = teacherSaveReview_(ss, {
        className: e.parameter.className || '',
        seatNo: e.parameter.seatNo || '',
        studentName: e.parameter.studentName || '',
        reviewStatus: e.parameter.reviewStatus || '',
        teacherNote: e.parameter.teacherNote || ''
      });
    }

    if (callback) {
      return ContentService
        .createTextOutput(callback + '(' + JSON.stringify(result) + ');')
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
    }

    return jsonOutput_(result);

  } catch (err) {
    const fallback = {
      ok: false,
      message: String(err)
    };

    const callback = e && e.parameter ? e.parameter.callback : '';

    if (callback) {
      return ContentService
        .createTextOutput(callback + '(' + JSON.stringify(fallback) + ');')
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
    }

    return jsonOutput_(fallback);
  }
}

function doPost(e) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const data = JSON.parse(e.postData.contents || '{}');

    initializeSheets_(ss);

    const valid = validateStudentForSubmit_(ss, {
      className: data.className || '',
      seatNo: data.seatNo || '',
      studentName: data.studentName || ''
    });

    if (!valid.allowed) {
      return jsonOutput_({
        ok: false,
        message: valid.message || '此學生不在固定名單內，拒絕交卷。'
      });
    }

    const scoreSheet = getOrCreateSheet_(ss, SCORE_SHEET_NAME, getScoreHeaders_());
    const statsSheet = getOrCreateSheet_(ss, QUESTION_STATS_SHEET_NAME, getQuestionStatsHeaders_());

    const now = new Date();

    const quizTitle = data.quizTitle || '';
    const className = data.className || '';
    const seatNo = normalizeSeatNo_(data.seatNo || '');
    const studentName = data.studentName || '';
    const score = data.score || 0;
    const usedSeconds = data.usedSeconds || 0;
    const learningReflection = data.learningReflection || '';

    scoreSheet.appendRow([
      now,
      quizTitle,
      className,
      seatNo,
      studentName,
      score,
      usedSeconds,
      learningReflection
    ]);

    const questionDetails = Array.isArray(data.questionDetails) ? data.questionDetails : [];
    updateQuestionStats_(statsSheet, questionDetails);
    updateQuizCheck_(ss, quizTitle, className);

    return jsonOutput_({
      ok: true,
      message: 'saved',
      questionCount: questionDetails.length
    });

  } catch (err) {
    return jsonOutput_({
      ok: false,
      message: String(err)
    });
  }
}

function initializeSheets_(ss) {
  getOrCreateSheet_(ss, SCORE_SHEET_NAME, getScoreHeaders_());
  getOrCreateSheet_(ss, QUESTION_STATS_SHEET_NAME, getQuestionStatsHeaders_());
  getOrCreateSheet_(ss, TEACHER_REVIEW_SHEET_NAME, getTeacherReviewHeaders_());

  const rosterSheet = getOrCreateSheet_(ss, ROSTER_SHEET_NAME, getRosterHeaders_());
  formatRosterSheet_(rosterSheet);
  moveSheetToLast_(ss, rosterSheet);
}

function moveSheetToLast_(ss, sheet) {
  if (!ss || !sheet) {
    return;
  }

  ss.setActiveSheet(sheet);
  ss.moveActiveSheet(ss.getNumSheets());
}

function formatRosterSheet_(sheet) {
  sheet.getRange('A1:C1').setFontWeight('bold');
  sheet.setColumnWidth(1, 140);
  sheet.setColumnWidth(2, 90);
  sheet.setColumnWidth(3, 160);
}

function formatQuizCheckSheet_(sheet) {
  sheet.getRange('A1:K1').setFontWeight('bold');
  sheet.setColumnWidth(1, 150);
  sheet.setColumnWidth(2, 180);
  sheet.setColumnWidth(3, 120);
  sheet.setColumnWidth(4, 80);
  sheet.setColumnWidth(5, 120);
  sheet.setColumnWidth(6, 110);
  sheet.setColumnWidth(7, 100);
  sheet.setColumnWidth(8, 150);
  sheet.setColumnWidth(9, 80);
  sheet.setColumnWidth(10, 110);
  sheet.setColumnWidth(11, 320);
}

function formatTeacherReviewSheet_(sheet) {
  sheet.getRange('A1:F1').setFontWeight('bold');
  sheet.setColumnWidth(1, 120);
  sheet.setColumnWidth(2, 80);
  sheet.setColumnWidth(3, 120);
  sheet.setColumnWidth(4, 120);
  sheet.setColumnWidth(5, 360);
  sheet.setColumnWidth(6, 160);
}

function jsonOutput_(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

function isRosterRestrictionEnabled_(ss) {
  return true;
}

function getRosterHeaders_() {
  return [
    '班級',
    '座號',
    '姓名'
  ];
}

function getScoreHeaders_() {
  return [
    '時間',
    '測驗名稱',
    '班級',
    '座號',
    '姓名',
    '分數',
    '作答秒數',
    '學習心得'
  ];
}

function getQuestionStatsHeaders_() {
  return [
    '題號',
    '題目簡稱',
    '選項A',
    '選項B',
    '選項C',
    '選項D',
    '正解',
    '答對人數',
    '答錯人數',
    '未作答'
  ];
}

function getQuizCheckHeaders_() {
  return [
    '檢核時間',
    '測驗名稱',
    '班級',
    '座號',
    '姓名',
    '繳交狀態',
    '送出次數',
    '最近送出時間',
    '分數',
    '作答秒數',
    '備註'
  ];
}

function getTeacherReviewHeaders_() {
  return [
    '班級',
    '座號',
    '姓名',
    '審閱狀態',
    '教師備註',
    '更新時間'
  ];
}

function formatTeacherReviewSheet_(sheet) {
  sheet.getRange('A1:F1').setFontWeight('bold');
  sheet.setColumnWidth(1, 120);
  sheet.setColumnWidth(2, 80);
  sheet.setColumnWidth(3, 120);
  sheet.setColumnWidth(4, 120);
  sheet.setColumnWidth(5, 360);
  sheet.setColumnWidth(6, 160);
}

function getOrCreateSheet_(ss, sheetName, headers) {
  let sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
  }

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.setFrozenRows(1);

  if (sheetName === ROSTER_SHEET_NAME) {
    formatRosterSheet_(sheet);
  }

  if (String(sheetName || '').indexOf(QUIZ_CHECK_SHEET_NAME) === 0) {
    formatQuizCheckSheet_(sheet);
  }

  if (sheetName === TEACHER_REVIEW_SHEET_NAME) {
    formatTeacherReviewSheet_(sheet);
  }

  return sheet;
}

function getRosterStatus_(ss) {
  const sheet = getOrCreateSheet_(ss, ROSTER_SHEET_NAME, getRosterHeaders_());
  const lastRow = sheet.getLastRow();
  let count = 0;

  if (lastRow >= 2) {
    const rows = sheet.getRange(2, 1, lastRow - 1, 3).getValues();
    rows.forEach(function(row) {
      if (row[0] && row[1] && row[2]) {
        count += 1;
      }
    });
  }

  return {
    ok: true,
    hasRoster: count > 0,
    rosterCount: count,
    restrictRoster: isRosterRestrictionEnabled_(ss)
  };
}

function validateStudentForSubmit_(ss, item) {
  const restrictRoster = isRosterRestrictionEnabled_(ss);

  if (!restrictRoster) {
    return {
      ok: true,
      allowed: true,
      restrictRoster: false,
      message: '未啟用固定名單限制。'
    };
  }

  const rosterStatus = getRosterStatus_(ss);

  if (!rosterStatus.hasRoster) {
    return {
      ok: true,
      allowed: false,
      restrictRoster: true,
      message: '老師已啟用固定名單限制，但尚未建立固定名單，請通知老師。'
    };
  }

  const sheet = getOrCreateSheet_(ss, ROSTER_SHEET_NAME, getRosterHeaders_());
  const lastRow = sheet.getLastRow();

  const className = String(item.className || '').trim();
  const seatNo = normalizeSeatNo_(item.seatNo || '');
  const studentName = String(item.studentName || '').trim();
  const key = makeStudentKey_(className, seatNo, studentName);

  if (!className || !seatNo || !studentName) {
    return {
      ok: true,
      allowed: false,
      restrictRoster: true,
      message: '請先填寫班級、座號與姓名。'
    };
  }

  const rows = lastRow >= 2
    ? sheet.getRange(2, 1, lastRow - 1, 3).getValues()
    : [];

  for (let i = 0; i < rows.length; i++) {
    const rowKey = makeStudentKey_(
      String(rows[i][0] || '').trim(),
      normalizeSeatNo_(rows[i][1] || ''),
      String(rows[i][2] || '').trim()
    );

    if (rowKey === key) {
      return {
        ok: true,
        allowed: true,
        restrictRoster: true,
        message: '固定名單比對通過。'
      };
    }
  }

  return {
    ok: true,
    allowed: false,
    restrictRoster: true,
    message: '班級、座號與姓名不符合老師建立的固定名單，請確認是否填錯，或通知老師。'
  };
}

function updateQuestionStats_(sheet, questionDetails) {
  if (!Array.isArray(questionDetails) || questionDetails.length === 0) {
    return;
  }

  const lastRow = sheet.getLastRow();
  const existingRows = lastRow >= 2
    ? sheet.getRange(2, 1, lastRow - 1, 10).getValues()
    : [];

  const rowMap = {};

  existingRows.forEach(function(row, index) {
    const questionNo = String(row[0] || '').trim();
    if (questionNo) {
      rowMap[questionNo] = {
        rowNumber: index + 2,
        questionTitle: row[1] || '',
        optionA: row[2] || '',
        optionB: row[3] || '',
        optionC: row[4] || '',
        optionD: row[5] || '',
        correctAnswer: row[6] || '',
        correctCount: Number(row[7]) || 0,
        wrongCount: Number(row[8]) || 0,
        blankCount: Number(row[9]) || 0
      };
    }
  });

  questionDetails.forEach(function(item) {
    const questionNo = String(item.questionNo || '').trim();
    if (!questionNo) {
      return;
    }

    const questionTitle = String(item.questionTitle || item.questionText || '').replace(/\s+/g, ' ').slice(0, 80);
    const optionA = String(item.optionA || '').trim();
    const optionB = String(item.optionB || '').trim();
    const optionC = String(item.optionC || '').trim();
    const optionD = String(item.optionD || '').trim();
    const correctAnswer = String(item.correctAnswer || '').trim();
    const studentAnswer = String(item.studentAnswer || '').trim();
    const isBlank = item.isBlank === true || studentAnswer === '' || studentAnswer === '未作答';
    const isCorrect = item.isCorrect === true;

    if (!rowMap[questionNo]) {
      sheet.appendRow([
        questionNo,
        questionTitle,
        optionA,
        optionB,
        optionC,
        optionD,
        correctAnswer,
        0,
        0,
        0
      ]);

      rowMap[questionNo] = {
        rowNumber: sheet.getLastRow(),
        questionTitle: questionTitle,
        optionA: optionA,
        optionB: optionB,
        optionC: optionC,
        optionD: optionD,
        correctAnswer: correctAnswer,
        correctCount: 0,
        wrongCount: 0,
        blankCount: 0
      };
    }

    if (questionTitle) { rowMap[questionNo].questionTitle = questionTitle; }
    if (optionA) { rowMap[questionNo].optionA = optionA; }
    if (optionB) { rowMap[questionNo].optionB = optionB; }
    if (optionC) { rowMap[questionNo].optionC = optionC; }
    if (optionD) { rowMap[questionNo].optionD = optionD; }
    if (correctAnswer) { rowMap[questionNo].correctAnswer = correctAnswer; }

    if (isBlank) {
      rowMap[questionNo].blankCount += 1;
    } else if (isCorrect) {
      rowMap[questionNo].correctCount += 1;
    } else {
      rowMap[questionNo].wrongCount += 1;
    }

    sheet.getRange(rowMap[questionNo].rowNumber, 1, 1, 10).setValues([[
      questionNo,
      rowMap[questionNo].questionTitle,
      rowMap[questionNo].optionA,
      rowMap[questionNo].optionB,
      rowMap[questionNo].optionC,
      rowMap[questionNo].optionD,
      rowMap[questionNo].correctAnswer,
      rowMap[questionNo].correctCount,
      rowMap[questionNo].wrongCount,
      rowMap[questionNo].blankCount
    ]]);
  });

  sortQuestionStatsByQuestionNo_(sheet);
}

function updateQuizCheck_(ss, quizTitle, className) {
  const rosterSheet = getOrCreateSheet_(ss, ROSTER_SHEET_NAME, getRosterHeaders_());
  const scoreSheet = getOrCreateSheet_(ss, SCORE_SHEET_NAME, getScoreHeaders_());
  const checkSheet = getOrCreateSheet_(ss, getQuizCheckSheetName_(className), getQuizCheckHeaders_());

  removeExistingQuizCheckRows_(checkSheet, quizTitle, className);

  const rosterRows = rosterSheet.getLastRow() >= 2
    ? rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, 3).getValues()
    : [];

  const scoreRows = scoreSheet.getLastRow() >= 2
    ? scoreSheet.getRange(2, 1, scoreSheet.getLastRow() - 1, 8).getValues()
    : [];

  const rosterMap = {};
  const activeRoster = [];

  rosterRows.forEach(function(row) {
    const rClass = String(row[0] || '').trim();
    const rSeat = normalizeSeatNo_(row[1] || '');
    const rName = String(row[2] || '').trim();

    if (!rClass || !rSeat || !rName) {
      return;
    }

    if (className && rClass !== className) {
      return;
    }

    const key = makeStudentKey_(rClass, rSeat, rName);
    rosterMap[key] = true;
    activeRoster.push({
      className: rClass,
      seatNo: rSeat,
      studentName: rName,
      key: key
    });
  });

  const submitMap = {};
  const outsideList = [];

  scoreRows.forEach(function(row) {
    const submitTime = row[0];
    const rQuizTitle = String(row[1] || '').trim();
    const rClass = String(row[2] || '').trim();
    const rSeat = normalizeSeatNo_(row[3] || '');
    const rName = String(row[4] || '').trim();
    const score = row[5];
    const usedSeconds = row[6];

    if (quizTitle && rQuizTitle !== quizTitle) {
      return;
    }

    if (className && rClass !== className) {
      return;
    }

    const key = makeStudentKey_(rClass, rSeat, rName);

    if (!submitMap[key]) {
      submitMap[key] = {
        count: 0,
        latestTime: '',
        score: '',
        usedSeconds: '',
        quizTitle: rQuizTitle,
        className: rClass,
        seatNo: rSeat,
        studentName: rName
      };
    }

    submitMap[key].count += 1;
    submitMap[key].latestTime = submitTime;
    submitMap[key].score = score;
    submitMap[key].usedSeconds = usedSeconds;

    if (!rosterMap[key]) {
      outsideList.push({
        quizTitle: rQuizTitle,
        className: rClass,
        seatNo: rSeat,
        studentName: rName,
        count: submitMap[key].count,
        latestTime: submitTime,
        score: score,
        usedSeconds: usedSeconds
      });
    }
  });

  const now = new Date();
  const outputRows = [];

  activeRoster.forEach(function(student) {
    const submit = submitMap[student.key];

    if (!submit) {
      outputRows.push([
        now,
        quizTitle,
        student.className,
        student.seatNo,
        student.studentName,
        '未繳',
        0,
        '',
        '',
        '',
        '固定名單中有此學生，但本測驗尚未交卷'
      ]);
      return;
    }

    outputRows.push([
      now,
      quizTitle,
      student.className,
      student.seatNo,
      student.studentName,
      submit.count > 1 ? '重複繳交' : '已繳',
      submit.count,
      submit.latestTime,
      submit.score,
      submit.usedSeconds,
      submit.count > 1 ? '同一測驗送出超過一次' : ''
    ]);
  });

  outsideList.forEach(function(item) {
    outputRows.push([
      now,
      item.quizTitle || quizTitle,
      item.className,
      item.seatNo,
      item.studentName,
      '名單外',
      item.count,
      item.latestTime,
      item.score,
      item.usedSeconds,
      '此筆成績不在固定名單中，請檢查班級、座號或姓名'
    ]);
  });

  if (outputRows.length > 0) {
    checkSheet
      .getRange(checkSheet.getLastRow() + 1, 1, outputRows.length, 11)
      .setValues(outputRows);
  }

  formatQuizCheckSheet_(checkSheet);
}

function getQuizCheckSheetName_(className) {
  const name = String(className || '').trim();
  if (!name) {
    return QUIZ_CHECK_SHEET_NAME;
  }

  return sanitizeSheetName_(QUIZ_CHECK_SHEET_NAME + '_' + name);
}

function sanitizeSheetName_(name) {
  const text = String(name || '').replace(/[\\\/\?\*\[\]:]/g, ' ').replace(/\s+/g, ' ').trim();
  return text.slice(0, 99) || QUIZ_CHECK_SHEET_NAME;
}

function removeExistingQuizCheckRows_(sheet, quizTitle, className) {
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return;
  }

  const rows = sheet.getRange(2, 1, lastRow - 1, 11).getValues();

  for (let i = rows.length - 1; i >= 0; i--) {
    const row = rows[i];
    const rQuizTitle = String(row[1] || '').trim();
    const rClass = String(row[2] || '').trim();

    const sameQuiz = quizTitle ? rQuizTitle === quizTitle : true;
    const sameClass = className ? rClass === className : true;

    if (sameQuiz && sameClass) {
      sheet.deleteRow(i + 2);
    }
  }
}

function sortQuestionStatsByQuestionNo_(sheet) {
  const lastRow = sheet.getLastRow();
  if (lastRow <= 2) {
    return;
  }

  sheet
    .getRange(2, 1, lastRow - 1, 10)
    .sort({ column: 1, ascending: true });
}

function normalizeSeatNo_(seatNo) {
  const text = String(seatNo || '').trim();

  if (/^\d+$/.test(text)) {
    return text.padStart(2, '0');
  }

  return text;
}

function makeStudentKey_(className, seatNo, studentName) {
  return [
    String(className || '').trim(),
    normalizeSeatNo_(seatNo || ''),
    String(studentName || '').trim()
  ].join('||');
}

function teacherGetOverview_(ss, className) {
  const rosterSheet = getOrCreateSheet_(ss, ROSTER_SHEET_NAME, getRosterHeaders_());
  const checkSheet = teacherGetCheckSheet_(ss, className);
  const targetClass = String(className || '').trim();
  const rosterRows = rosterSheet.getLastRow() >= 2 ? rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, 3).getValues() : [];
  let rosterCount = 0;
  rosterRows.forEach(function(row) {
    const rClass = String(row[0] || '').trim();
    const rSeat = normalizeSeatNo_(row[1] || '');
    const rName = String(row[2] || '').trim();
    if (!rClass || !rSeat || !rName) { return; }
    if (targetClass && rClass !== targetClass) { return; }
    rosterCount += 1;
  });
  const statusCount = { submitted: 0, missing: 0, duplicate: 0, outside: 0, other: 0 };
  let scoreTotal = 0, scoreCount = 0, maxScore = '', minScore = '', usedSecondsTotal = 0, usedSecondsCount = 0;
  if (checkSheet && checkSheet.getLastRow() >= 2) {
    const rows = checkSheet.getRange(2, 1, checkSheet.getLastRow() - 1, 11).getValues();
    rows.forEach(function(row) {
      const status = String(row[5] || '').trim();
      const scoreRaw = row[8];
      const usedSecondsRaw = row[9];
      const score = Number(scoreRaw);
      const usedSeconds = Number(usedSecondsRaw);
      if (status === '已繳') { statusCount.submitted += 1; }
      else if (status === '未繳') { statusCount.missing += 1; }
      else if (status === '重複繳交') { statusCount.duplicate += 1; }
      else if (status === '名單外') { statusCount.outside += 1; }
      else if (status) { statusCount.other += 1; }
      if (scoreRaw !== '' && !isNaN(score)) {
        scoreTotal += score;
        scoreCount += 1;
        if (maxScore === '' || score > maxScore) { maxScore = score; }
        if (minScore === '' || score < minScore) { minScore = score; }
      }
      if (usedSecondsRaw !== '' && !isNaN(usedSeconds) && usedSeconds > 0) {
        usedSecondsTotal += usedSeconds;
        usedSecondsCount += 1;
      }
    });
  }
  return {
    ok: true,
    className: targetClass,
    rosterCount: rosterCount,
    checkSheetName: checkSheet ? checkSheet.getName() : '',
    hasCheckSheet: !!checkSheet,
    statusCount: statusCount,
    avgScore: scoreCount ? Math.round((scoreTotal / scoreCount) * 10) / 10 : '',
    maxScore: maxScore,
    minScore: minScore,
    avgUsedSeconds: usedSecondsCount ? Math.round(usedSecondsTotal / usedSecondsCount) : ''
  };
}

function teacherGetQuizCheckRows_(ss, className) {
  const checkSheet = teacherGetCheckSheet_(ss, className);
  if (!checkSheet) {
    return { ok: true, rows: [], message: '尚未找到繳交檢核工作表。學生交卷後會自動建立。' };
  }
  const rows = checkSheet.getLastRow() >= 2 ? checkSheet.getRange(2, 1, checkSheet.getLastRow() - 1, 11).getValues() : [];
  return {
    ok: true,
    sheetName: checkSheet.getName(),
    rows: rows.map(function(row) {
      return {
        checkTime: teacherFormatDate_(row[0]),
        quizTitle: row[1] || '',
        className: row[2] || '',
        seatNo: normalizeSeatNo_(row[3] || ''),
        studentName: row[4] || '',
        submitStatus: row[5] || '',
        submitCount: row[6] || '',
        latestSubmitTime: teacherFormatDate_(row[7]),
        score: row[8] || '',
        usedSeconds: row[9] || '',
        note: row[10] || ''
      };
    })
  };
}

function teacherGetScoreDistribution_(ss, className) {
  const scoreSheet = getOrCreateSheet_(ss, SCORE_SHEET_NAME, getScoreHeaders_());
  const targetClass = String(className || '').trim();
  const rows = scoreSheet.getLastRow() >= 2 ? scoreSheet.getRange(2, 1, scoreSheet.getLastRow() - 1, 8).getValues() : [];
  const buckets = { '90-100': 0, '80-89': 0, '70-79': 0, '60-69': 0, '未滿60': 0 };
  let total = 0, count = 0;
  rows.forEach(function(row) {
    const rClass = String(row[2] || '').trim();
    const scoreRaw = row[5];
    const score = Number(scoreRaw);
    if (targetClass && rClass !== targetClass) { return; }
    if (scoreRaw === '' || isNaN(score)) { return; }
    total += score;
    count += 1;
    if (score >= 90) { buckets['90-100'] += 1; }
    else if (score >= 80) { buckets['80-89'] += 1; }
    else if (score >= 70) { buckets['70-79'] += 1; }
    else if (score >= 60) { buckets['60-69'] += 1; }
    else { buckets['未滿60'] += 1; }
  });
  return {
    ok: true,
    className: targetClass,
    totalCount: count,
    avgScore: count ? Math.round((total / count) * 10) / 10 : '',
    buckets: [
      { label: '90–100', count: buckets['90-100'] },
      { label: '80–89', count: buckets['80-89'] },
      { label: '70–79', count: buckets['70-79'] },
      { label: '60–69', count: buckets['60-69'] },
      { label: '未滿60', count: buckets['未滿60'] }
    ]
  };
}

function teacherGetQuestionStats_(ss) {
  const statsSheet = getOrCreateSheet_(ss, QUESTION_STATS_SHEET_NAME, getQuestionStatsHeaders_());
  const rows = statsSheet.getLastRow() >= 2 ? statsSheet.getRange(2, 1, statsSheet.getLastRow() - 1, 10).getValues() : [];
  const output = rows.map(function(row) {
    const correct = Number(row[7]) || 0;
    const wrong = Number(row[8]) || 0;
    const blank = Number(row[9]) || 0;
    const total = correct + wrong + blank;
    return {
      questionNo: row[0] || '',
      questionTitle: row[1] || '',
      correctAnswer: row[6] || '',
      correctCount: correct,
      wrongCount: wrong,
      blankCount: blank,
      totalCount: total,
      wrongRate: total ? Math.round((wrong / total) * 1000) / 10 : 0
    };
  });
  output.sort(function(a, b) { return Number(b.wrongRate) - Number(a.wrongRate); });
  return { ok: true, rows: output };
}

function teacherGetReflections_(ss, className) {
  const scoreSheet = getOrCreateSheet_(ss, SCORE_SHEET_NAME, getScoreHeaders_());
  const reviewSheet = teacherGetOrCreateReviewSheet_(ss);
  const targetClass = String(className || '').trim();
  const scoreRows = scoreSheet.getLastRow() >= 2 ? scoreSheet.getRange(2, 1, scoreSheet.getLastRow() - 1, 8).getValues() : [];
  const reviewMap = teacherGetReviewMap_(reviewSheet);
  const output = [];
  scoreRows.forEach(function(row) {
    const rClass = String(row[2] || '').trim();
    const seatNo = normalizeSeatNo_(row[3] || '');
    const studentName = String(row[4] || '').trim();
    if (targetClass && rClass !== targetClass) { return; }
    const review = reviewMap[teacherMakeReviewKey_(rClass, seatNo, studentName)] || {};
    output.push({
      submitTime: teacherFormatDate_(row[0]),
      quizTitle: row[1] || '',
      className: rClass,
      seatNo: seatNo,
      studentName: studentName,
      score: row[5] || '',
      usedSeconds: row[6] || '',
      learningReflection: row[7] || '',
      reviewStatus: review.reviewStatus || '未審閱',
      teacherNote: review.teacherNote || ''
    });
  });
  return { ok: true, rows: output };
}

function teacherSaveReview_(ss, item) {
  const sheet = teacherGetOrCreateReviewSheet_(ss);
  const className = String(item.className || '').trim();
  const seatNo = normalizeSeatNo_(item.seatNo || '');
  const studentName = String(item.studentName || '').trim();
  const reviewStatus = String(item.reviewStatus || '').trim() || '未審閱';
  const teacherNote = String(item.teacherNote || '').trim();
  if (!className || !seatNo || !studentName) {
    return { ok: false, message: '缺少班級、座號或姓名，無法儲存審閱紀錄。' };
  }
  const key = teacherMakeReviewKey_(className, seatNo, studentName);
  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) {
    const rows = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
    for (let i = 0; i < rows.length; i++) {
      if (teacherMakeReviewKey_(rows[i][0], rows[i][1], rows[i][2]) === key) {
        sheet.getRange(i + 2, 4, 1, 3).setValues([[reviewStatus, teacherNote, new Date()]]);
        return { ok: true, message: '已更新審閱紀錄。' };
      }
    }
  }
  sheet.appendRow([className, seatNo, studentName, reviewStatus, teacherNote, new Date()]);
  return { ok: true, message: '已新增審閱紀錄。' };
}

function teacherGetCheckSheet_(ss, className) {
  const targetClass = String(className || '').trim();
  if (targetClass) { return ss.getSheetByName(getQuizCheckSheetName_(targetClass)); }
  const sheets = ss.getSheets();
  for (let i = 0; i < sheets.length; i++) {
    const name = sheets[i].getName();
    if (name.indexOf(QUIZ_CHECK_SHEET_NAME) === 0) { return sheets[i]; }
  }
  return null;
}

function teacherGetOrCreateReviewSheet_(ss) {
  return getOrCreateSheet_(ss, TEACHER_REVIEW_SHEET_NAME, getTeacherReviewHeaders_());
}

function teacherGetReviewMap_(sheet) {
  const map = {};
  const rows = sheet.getLastRow() >= 2 ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 6).getValues() : [];
  rows.forEach(function(row) {
    const key = teacherMakeReviewKey_(row[0], row[1], row[2]);
    map[key] = { reviewStatus: row[3] || '未審閱', teacherNote: row[4] || '', updatedAt: teacherFormatDate_(row[5]) };
  });
  return map;
}

function teacherMakeReviewKey_(className, seatNo, studentName) {
  return [String(className || '').trim(), normalizeSeatNo_(seatNo || ''), String(studentName || '').trim()].join('||');
}

function teacherFormatDate_(value) {
  if (!value) { return ''; }
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return Utilities.formatDate(value, Session.getScriptTimeZone(), 'yyyy/MM/dd HH:mm:ss');
  }
  return String(value || '');
}

尚未複製 Apps Script 程式碼。

二、開啟 Google Sheet、執行 setupSheets 並部署 Apps Script

按上方「複製 Apps Script 程式碼」。
按「開啟 Google Sheet」建立一份新的試算表。
在 Google Sheet 中點選「擴充功能 → Apps Script」
刪除 Apps Script 編輯器中原本預設的程式碼。
貼上剛剛複製的 Apps Script 程式碼，並儲存專案。
按「執行」，依畫面提示完成授權。
確認工作表建立後，再回到 Apps Script，點選「部署 → 新增部署作業」。
類型選擇「網頁應用程式」。
執行身分選「我」。
誰可以存取選「所有人」。
部署後取得 /exec 結尾的「網頁應用程式」網址。
回到本頁面，貼上 Web App URL。
再到 Google Sheet頁面，複製該 Google Sheet 網址，貼到本頁的「Google Sheet 網址」欄位。
再到第四區填寫測驗設定，產生 AI 輔助出題 Prompt。
把 Prompt 貼到 ChatGPT 或 Gemini，即可取得完整學生端測驗 HTML。
將『學生端測驗 HTML』貼在NewPlus的AI互動教材編輯區，儲存後通知學生點選進行測驗。

開啟新Google Sheet

Google Sheet 網址＊必填

三、貼上 Web App URL. ( 重要：等於個別測驗卷的通行碼 )

Google Sheet Apps Script 網頁應用程式網址。＊必填

請貼上「Google Sheet Apps Script 網頁應用程式網址」，且網址需以 /exec 結尾；不要貼 Google Sheet 分享網址或 Apps Script 編輯器網址。

四、AI 輔助出題設定

這裡不用逐題手動輸入。請填寫測驗需求與出題範圍，系統會產生一份可直接交給 ChatGPT 或 Gemini 的 Prompt，請 AI 直接出題並產生學生端測驗 HTML。

測驗名稱

科目

單元主題

適用年段

學生程度不在此設定，請改由下方「難度」控制題目深淺。

自訂年段

題數

每題分數

測驗時間限制（分鐘）

學生端 HTML 會產生「開始測驗」按鈕；學生須先通過固定名單核對，才會啟動倒數計時，時間到會自動交卷。

題型

難度

交卷後顯示方式

補充教材內容或出題範圍

建議貼上老師希望學生一定要會的重點，AI 出題會更貼近課堂。

其他出題或版面要求

開啟 ChatGPT 開啟 Gemini 尚未產生 Prompt。

一份 Google Sheet 對應一份試卷 → 學生端測驗 HTML 負責作答與回傳 → Google Sheet 儲存成績與統計 → 教師端檢核 HTML 分區更新、圖表分析與審閱備註。

六、產生的 Prompt

Prompt 預設隱藏。按「複製 Prompt」後，直接貼到 ChatGPT 或 Gemini，即可請 AI 直接出題並產生完整學生端測驗 HTML。