普普風班級榮譽點數系統

設定 GAS 網址

請貼上您從 Google Apps Script 發布後取得的網頁應用程式網址。

設定說明

步驟 1：建立 Google 試算表

前往 sheets.new 建立一份新的 Google 試算表。
為您的試算表命名，例如「班級點數系統」。
✨ 新功能：系統會自動建立所需的工作表（Students, Behaviors, Log, Config）及預設資料，包含兩位範例學生。
可選：如果要修改班級名稱，可在 Config 工作表的 A1 格輸入您的班級名稱。

步驟 2：建立 Google Apps Script (GAS)

在試算表選單中，點擊「擴充功能」 > 「Apps Script」。
刪除指令碼編輯器中所有預設的程式碼。
將下方的所有程式碼複製並貼到編輯器中。
點擊上方的「儲存專案」圖示 (💾)。


function doPost(e) {
  try {
    const postData = JSON.parse(e.postData.contents);
    const action = postData.action;
    const payload = postData.payload;
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    switch (action) {
      case "loadData":
        return handleLoadData(ss);
      case "addMultipleStudents":
        return handleAddMultipleStudents(ss, payload);
      case "deleteStudent":
        return handleDeleteStudent(ss, payload);
      case "updatePoints":
        return handleUpdatePoints(ss, payload);
      case "getHistory":
        return handleGetHistory(ss, payload);
      case "resetAllPoints":
        return handleResetAllPoints(ss);
      case "manageBehaviors":
        return handleManageBehaviors(ss, payload);
      case "updateMultiplePoints":
        return handleUpdateMultiplePoints(ss, payload);
      case "updateStudentAvatar":
        return handleUpdateStudentAvatar(ss, payload);
      default:
        return createJsonResponse({ status: "error", message: "Unknown action: " + action });
    }
  } catch (err) {
    return createJsonResponse({ status: "error", message: err.message, stack: err.stack });
  }
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

function handleLoadData(ss) {
  // 自動建立所需的工作表
  let studentSheet = ss.getSheetByName("Students");
  let behaviorSheet = ss.getSheetByName("Behaviors");
  let logSheet = ss.getSheetByName("Log");
  let configSheet = ss.getSheetByName("Config");

  // 如果工作表不存在，則建立它們
  if (!studentSheet) {
    studentSheet = ss.insertSheet("Students");
  }
  if (!behaviorSheet) {
    behaviorSheet = ss.insertSheet("Behaviors");
  }
  if (!logSheet) {
    logSheet = ss.insertSheet("Log");
  }
  if (!configSheet) {
    configSheet = ss.insertSheet("Config");
  }

  // 初始化 Config 工作表
  if (configSheet.getLastRow() === 0) {
    configSheet.getRange("A1").setValue("我的班級");
  }

  // 初始化 Students 工作表
  if (studentSheet.getLastRow() === 0) {
    // 設定標題列
    studentSheet.getRange("A1:E1").setValues([["id", "name", "points", "avatar", "group"]]);
    
    // 生成範例學生的預設頭像
    const generateAvatar = (seed) => {
      const colors = ['#ff6384', '#36a2eb', '#ffce56', '#4bc0c0', '#9966ff', '#ff9f40'];
      const bgColor = colors[Math.abs(seed.split('').reduce((a, b) => a + b.charCodeAt(0), 0)) % colors.length];
      return `data:image/svg+xml,${encodeURIComponent(``)}`;
    };
    
    // 新增兩個範例學生
    const sampleStudents = [
      [Utilities.getUuid(), "王小明", 0, generateAvatar("王小明"), "第一組"],
      [Utilities.getUuid(), "陳小華", 0, generateAvatar("陳小華"), "第二組"]
    ];
    studentSheet.getRange(2, 1, sampleStudents.length, 5).setValues(sampleStudents);
  }

  // 初始化 Behaviors 工作表
  if (behaviorSheet.getLastRow() === 0) {
    const defaultBehaviors = [
      ["name", "points", "type"],
      ["積極參與", 1, "positive"], 
      ["幫助同學", 1, "positive"], 
      ["準時交作業", 2, "positive"],
      ["團隊合作", 1, "positive"], 
      ["創意表現", 2, "positive"],
      ["上課分心", -1, "negative"], 
      ["未交作業", -2, "negative"],
      ["干擾秩序", -1, "negative"],
      ["遲到早退", -1, "negative"]
    ];
    behaviorSheet.getRange(1, 1, defaultBehaviors.length, 3).setValues(defaultBehaviors);
  }

  // 初始化 Log 工作表
  if (logSheet.getLastRow() === 0) {
    logSheet.getRange("A1:D1").setValues([["timestamp", "studentId", "behaviorName", "points"]]);
  }

  // 讀取資料
  const students = studentSheet.getLastRow() > 1 ? studentSheet.getRange(2, 1, studentSheet.getLastRow() - 1, 5).getValues() : [];
  const behaviors = behaviorSheet.getLastRow() > 1 ? behaviorSheet.getRange(2, 1, behaviorSheet.getLastRow() - 1, 3).getValues() : [];
  const className = configSheet.getRange("A1").getValue() || "我的班級";

  const data = {
    className: className,
    students: students.map(row => ({ id: row[0], name: row[1], points: Number(row[2]) || 0, avatar: row[3], group: row[4] || '' })),
    behaviors: behaviors.map(row => ({ name: row[0], points: Number(row[1]) || 0, type: row[2] }))
  };
  return createJsonResponse({ status: "success", data: data });
}

function handleAddMultipleStudents(ss, payload) {
  let studentSheet = ss.getSheetByName("Students");
  if (!studentSheet) {
    studentSheet = ss.insertSheet("Students");
  }
  if (studentSheet.getLastRow() === 0) {
    studentSheet.getRange("A1:E1").setValues([["id", "name", "points", "avatar", "group"]]);
  }
  
  const newStudentsPayload = payload.students;
  const newStudentsForSheet = [];
  const newStudentsForState = [];

  newStudentsPayload.forEach(student => {
    const newId = Utilities.getUuid();
    const newStudent = { id: newId, name: student.name, points: 0, avatar: student.avatar, group: student.group || '' };
    newStudentsForSheet.push([newStudent.id, newStudent.name, newStudent.points, newStudent.avatar, newStudent.group]);
    newStudentsForState.push(newStudent);
  });

  if (newStudentsForSheet.length > 0) {
    studentSheet.getRange(studentSheet.getLastRow() + 1, 1, newStudentsForSheet.length, 5).setValues(newStudentsForSheet);
  }
  
  return createJsonResponse({ status: "success", data: newStudentsForState });
}

function handleDeleteStudent(ss, payload) {
  const { studentId } = payload;
  const studentSheet = ss.getSheetByName("Students");
  const studentData = studentSheet.getDataRange().getValues();
  // 從後往前找，避免刪除時影響後續列的索引
  for (let i = studentData.length - 1; i >= 1; i--) {
    if (studentData[i][0] === studentId) {
      studentSheet.deleteRow(i + 1);
      return createJsonResponse({ status: "success", data: { deletedStudentId: studentId } });
    }
  }
  return createJsonResponse({ status: "error", message: "Student not found to delete" });
}

function handleUpdatePoints(ss, payload) {
  const { studentId, behaviorName, points } = payload;
  let studentSheet = ss.getSheetByName("Students");
  let logSheet = ss.getSheetByName("Log");
  
  if (!studentSheet) {
    studentSheet = ss.insertSheet("Students");
  }
  if (!logSheet) {
    logSheet = ss.insertSheet("Log");
  }

  if (logSheet.getLastRow() === 0) {
    logSheet.getRange("A1:D1").setValues([["timestamp", "studentId", "behaviorName", "points"]]);
  }
  logSheet.appendRow([new Date(), studentId, behaviorName, points]);

  const studentData = studentSheet.getDataRange().getValues();
  for (let i = 1; i < studentData.length; i++) {
    if (studentData[i][0] === studentId) {
      const currentPoints = Number(studentData[i][2]) || 0;
      const newPoints = currentPoints + Number(points);
      studentSheet.getRange(i + 1, 3).setValue(newPoints);
      return createJsonResponse({ status: "success", data: { studentId: studentId, newPoints: newPoints } });
    }
  }
  return createJsonResponse({ status: "error", message: "Student not found" });
}

function handleGetHistory(ss, payload) {
    let logSheet = ss.getSheetByName("Log");
    if (!logSheet) {
        logSheet = ss.insertSheet("Log");
        logSheet.getRange("A1:D1").setValues([["timestamp", "studentId", "behaviorName", "points"]]);
    }
    if (logSheet.getLastRow() < 2) {
        return createJsonResponse({ status: "success", data: [] });
    }
    const logData = logSheet.getRange(2, 1, logSheet.getLastRow() - 1, 4).getValues();
    const history = logData
        .filter(row => row[1] === payload.studentId)
        .map(row => ({
            timestamp: new Date(row[0]).toLocaleString('zh-TW'),
            behaviorName: row[2],
            points: row[3]
        }))
        .reverse();
    return createJsonResponse({ status: "success", data: history });
}

function handleResetAllPoints(ss) {
    let studentSheet = ss.getSheetByName("Students");
    let logSheet = ss.getSheetByName("Log");
    
    if (!studentSheet) {
        studentSheet = ss.insertSheet("Students");
    }
    if (!logSheet) {
        logSheet = ss.insertSheet("Log");
    }
    
    if (studentSheet.getLastRow() > 1) {
        studentSheet.getRange(2, 3, studentSheet.getLastRow() - 1, 1).setValue(0);
    }
    if (logSheet.getLastRow() === 0) {
      logSheet.getRange("A1:D1").setValues([["timestamp", "studentId", "behaviorName", "points"]]);
    }
    logSheet.appendRow([new Date(), "SYSTEM", "所有點數已重設", 0]);
    return createJsonResponse({ status: "success", data: { success: true } });
}

function handleManageBehaviors(ss, payload) {
    let behaviorSheet = ss.getSheetByName("Behaviors");
    if (!behaviorSheet) {
        behaviorSheet = ss.insertSheet("Behaviors");
        behaviorSheet.getRange("A1:C1").setValues([["name", "points", "type"]]);
    }
    if (behaviorSheet.getLastRow() > 1) {
        behaviorSheet.getRange(2, 1, behaviorSheet.getLastRow() - 1, 3).clearContent();
    }
    if (payload.behaviors.length > 0) {
        const newBehaviorsData = payload.behaviors.map(b => [b.name, b.points, b.type]);
        behaviorSheet.getRange(2, 1, newBehaviorsData.length, 3).setValues(newBehaviorsData);
    }
    return createJsonResponse({ status: "success", data: { success: true } });
}

function handleUpdateMultiplePoints(ss, payload) {
    const { updates } = payload; // [{ studentId, behaviorName, points }, ...]
    let studentSheet = ss.getSheetByName("Students");
    let logSheet = ss.getSheetByName("Log");
    
    if (!studentSheet) {
        studentSheet = ss.insertSheet("Students");
    }
    if (!logSheet) {
        logSheet = ss.insertSheet("Log");
    }
    
    if (logSheet.getLastRow() === 0) {
        logSheet.getRange("A1:D1").setValues([["timestamp", "studentId", "behaviorName", "points"]]);
    }
    
    // 批量記錄日誌
    const logData = updates.map(update => [new Date(), update.studentId, update.behaviorName, update.points]);
    if (logData.length > 0) {
        logSheet.getRange(logSheet.getLastRow() + 1, 1, logData.length, 4).setValues(logData);
    }
    
    // 批量更新學生分數
    const studentData = studentSheet.getDataRange().getValues();
    const updatedStudents = [];
    
    updates.forEach(update => {
        for (let i = 1; i < studentData.length; i++) {
            if (studentData[i][0] === update.studentId) {
                const currentPoints = Number(studentData[i][2]) || 0;
                const newPoints = currentPoints + Number(update.points);
                studentData[i][2] = newPoints;
                updatedStudents.push({ studentId: update.studentId, newPoints: newPoints });
                break;
            }
        }
    });
    
    // 更新試算表
    studentSheet.getDataRange().setValues(studentData);
    
    return createJsonResponse({ status: "success", data: { updatedStudents: updatedStudents } });
}

function handleUpdateStudentAvatar(ss, payload) {
    const { studentId, avatar } = payload;
    let studentSheet = ss.getSheetByName("Students");
    if (!studentSheet) {
        studentSheet = ss.insertSheet("Students");
        studentSheet.getRange("A1:E1").setValues([["id", "name", "points", "avatar", "group"]]);
    }
    const studentData = studentSheet.getDataRange().getValues();
    
    for (let i = 1; i < studentData.length; i++) {
        if (studentData[i][0] === studentId) {
            studentSheet.getRange(i + 1, 4).setValue(avatar); // 第4欄是avatar欄位
            return createJsonResponse({ status: "success", data: { success: true } });
        }
    }
    
    return createJsonResponse({ status: "error", message: "Student not found for avatar update" });
}

步驟 3：發布網頁應用程式

在 Apps Script 編輯器右上角，點擊「部署」 > 「新增部署作業」。
在「選取類型」旁，點擊齒輪圖示 ⚙️，選擇「網頁應用程式」。
在「說明」欄位輸入「班級點數系統 v4.2 - 自動建表+頭像上傳」。
在「執行身分」選擇「我」。
在「誰可以存取」選擇「任何人」。(這是為了讓您的前端網頁能呼叫它，資料本身仍是安全的)
點擊「部署」。
在彈出的視窗中，點擊「授權存取」，並同意 Google 的權限要求。
最後，您會得到一個「網頁應用程式」的網址。複製這個網址。

步驟 4：設定本網頁

在程式碼中找到 gasUrl: 'demo' 這一行。
將 'demo' 更換為您剛才複製的 GAS 網址。
儲存檔案並重新整理網頁。
完成！您就可以開始使用了！

管理學生

新增學生

請輸入學生姓名，每位學生佔一行。可以只輸入姓名或「姓名，分組」的格式。

設定 GAS 網址

設定說明

步驟 1：建立 Google 試算表

步驟 2：建立 Google Apps Script (GAS)

步驟 3：發布網頁應用程式

步驟 4：設定本網頁

管理學生

新增學生

刪除學生

匯出成績

給予點數

加分項目

待改進項目

歷史紀錄

管理行為項目

家長檢視連結

學生頭像管理