Google Apps Script Code สำหรับใช้งานหลายเครื่อง (รหัส.gs)
คัดลอกโค้ดด้านล่างนี้ นำไปวางแทนที่ใน Apps Script (รหัส.gs) ของ Google Sheets เพื่อบันทึกข้อมูลและผู้จัดทำรายงานสรุปลงฐานข้อมูล:
// --- Google Apps Script สำหรับระบบรายงานการอบรม โรงเรียนมหาไถ่ศึกษากุมภวาปี ---
function doGet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// 1. อ่านข้อมูลรายชื่อครู (Teachers Sheet)
var tSheet = getOrCreateSheet(ss, "Teachers", ["ID", "Name", "Department"]);
var tData = tSheet.getDataRange().getValues();
var teachers = [];
for (var i = 1; i < tData.length; i++) {
if (tData[i][0]) {
teachers.push({ id: String(tData[i][0]), name: String(tData[i][1]), department: String(tData[i][2]) });
}
}
// 2. อ่านข้อมูลประวัติการอบรม (Records Sheet)
var rSheet = getOrCreateSheet(ss, "Records", ["ID", "ReportDate", "AcademicYear", "TeacherName", "Topic", "Speaker", "Organizer", "Location", "TrainingDateStr", "Hours", "HourUnit", "CertStatus", "Summary", "DocsReceived", "Benefits"]);
var rData = rSheet.getDataRange().getValues();
var records = [];
for (var j = 1; j < rData.length; j++) {
if (rData[j][0]) {
records.push({
id: String(rData[j][0]),
reportDate: formatDate(rData[j][1]),
academicYear: String(rData[j][2]),
teacherName: String(rData[j][3]),
topic: String(rData[j][4]),
speaker: String(rData[j][5]),
organizer: String(rData[j][6]),
location: String(rData[j][7]),
trainingDateStr: String(rData[j][8]),
hours: parseFloat(rData[j][9]) || 0,
hourUnit: String(rData[j][10] || "ชั่วโมง"),
certStatus: String(rData[j][11]),
summary: String(rData[j][12]),
docsReceived: String(rData[j][13]),
benefits: String(rData[j][14])
});
}
}
// 3. อ่านการตั้งค่าระบบ (Settings Sheet)
var sSheet = getOrCreateSheet(ss, "Settings", ["Key", "Value"]);
var sData = sSheet.getDataRange().getValues();
var settingsMap = {};
for (var k = 1; k < sData.length; k++) {
if (sData[k][0]) {
settingsMap[sData[k][0]] = sData[k][1];
}
}
var activeYear = settingsMap["activeYear"] || "2567";
var academicYears = settingsMap["academicYears"] ? JSON.parse(settingsMap["academicYears"]) : ["2567", "2566", "2565"];
var principal = {
name: settingsMap["principalName"] || "นางสาวอารี โชคอรุณ",
position: settingsMap["principalPosition"] || "ผู้อำนวยการโรงเรียนมหาไถ่ศึกษากุมภวาปี"
};
var preparers = settingsMap["preparers"] ? JSON.parse(settingsMap["preparers"]) : [
{ id: "P1", name: "นายสมชาย ใจตรง", position: "หัวหน้างานพัฒนาบุคลากร" }
];
var activePreparerId = settingsMap["activePreparerId"] || "P1";
var response = {
status: "success",
teachers: teachers,
records: records,
activeYear: activeYear,
academicYears: academicYears,
principal: principal,
preparers: preparers,
activePreparerId: activePreparerId
};
return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = JSON.parse(e.postData.contents);
if (data.action === "syncAll") {
saveTeachersSheet(ss, data.teachers);
saveRecordsSheet(ss, data.records);
saveSettingsSheet(ss, data.activeYear, data.academicYears, data.principal, data.preparers, data.activePreparerId);
}
return ContentService.createTextOutput(JSON.stringify({ status: "success" })).setMimeType(ContentService.MimeType.JSON);
}
function getOrCreateSheet(ss, name, headers) {
var sheet = ss.getSheetByName(name);
if (!sheet) {
sheet = ss.insertSheet(name);
if (headers && headers.length > 0) {
sheet.appendRow(headers);
}
}
return sheet;
}
function saveTeachersSheet(ss, teachers) {
var sheet = getOrCreateSheet(ss, "Teachers", ["ID", "Name", "Department"]);
sheet.clearContents();
sheet.appendRow(["ID", "Name", "Department"]);
if (teachers && teachers.length > 0) {
teachers.forEach(function(t) {
sheet.appendRow([t.id, t.name, t.department]);
});
}
}
function saveRecordsSheet(ss, records) {
var sheet = getOrCreateSheet(ss, "Records", ["ID", "ReportDate", "AcademicYear", "TeacherName", "Topic", "Speaker", "Organizer", "Location", "TrainingDateStr", "Hours", "HourUnit", "CertStatus", "Summary", "DocsReceived", "Benefits"]);
sheet.clearContents();
sheet.appendRow(["ID", "ReportDate", "AcademicYear", "TeacherName", "Topic", "Speaker", "Organizer", "Location", "TrainingDateStr", "Hours", "HourUnit", "CertStatus", "Summary", "DocsReceived", "Benefits"]);
if (records && records.length > 0) {
records.forEach(function(r) {
sheet.appendRow([r.id, r.reportDate, r.academicYear, r.teacherName, r.topic, r.speaker, r.organizer, r.location, r.trainingDateStr, r.hours, r.hourUnit, r.certStatus, r.summary, r.docsReceived, r.benefits]);
});
}
}
function saveSettingsSheet(ss, activeYear, academicYears, principal, preparers, activePreparerId) {
var sheet = getOrCreateSheet(ss, "Settings", ["Key", "Value"]);
sheet.clearContents();
sheet.appendRow(["Key", "Value"]);
sheet.appendRow(["activeYear", activeYear]);
sheet.appendRow(["academicYears", JSON.stringify(academicYears || [])]);
sheet.appendRow(["principalName", principal ? principal.name : ""]);
sheet.appendRow(["principalPosition", principal ? principal.position : ""]);
sheet.appendRow(["preparers", JSON.stringify(preparers || [])]);
sheet.appendRow(["activePreparerId", activePreparerId || ""]);
}
function formatDate(dateVal) {
if (!dateVal) return "";
if (dateVal instanceof Date) {
var y = dateVal.getFullYear();
var m = ("0" + (dateVal.getMonth() + 1)).slice(-2);
var d = ("0" + dateVal.getDate()).slice(-2);
return y + "-" + m + "-" + d;
}
return String(dateVal);
}
