โรงเรียนสิรินธรราชวิทยาลัย
คัดลอกโค้ดด้านล่างไปวางใน Google Apps Script:
// 🎯 Google Apps Script Code สำหรับระบบแจ้งซ่อม
// ✅ ตั้งค่า Google Sheets ID แล้ว: 1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4
function doGet(e) {
return handleRequest(e);
}
function doPost(e) {
return handleRequest(e);
}
function handleRequest(e) {
try {
// เปิด Google Sheets - ใช้ ID ที่ตั้งค่าแล้ว
const SHEET_ID = '1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4';
const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
// ตั้งค่า Headers ถ้ายังไม่มี
if (sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, 7).setValues([
['ID', 'Reporter Name', 'Issue', 'Location', 'Status', 'Created At', 'Completed At']
]);
SpreadsheetApp.flush();
}
const action = e.parameter.action || e.parameters.action?.[0];
// Log ข้อมูลที่ได้รับเพื่อ debug
console.log('=== REQUEST START ===');
console.log('Received action:', action);
console.log('All parameters:', e.parameter);
console.log('All parameters array:', e.parameters);
console.log('Sheet ID:', SHEET_ID);
console.log('Sheet name:', sheet.getName());
console.log('Current rows:', sheet.getLastRow());
if (action === 'addReport') {
// เพิ่มรายการใหม่
const newRow = [
e.parameter.id || e.parameters.id?.[0],
e.parameter.reporterName || e.parameters.reporterName?.[0],
e.parameter.issue || e.parameters.issue?.[0],
e.parameter.location || e.parameters.location?.[0],
e.parameter.status || e.parameters.status?.[0] || 'pending',
e.parameter.createdAt || e.parameters.createdAt?.[0],
e.parameter.completedAt || e.parameters.completedAt?.[0] || ''
];
console.log('Adding new row:', newRow);
// ตรวจสอบว่าข้อมูลครบถ้วน
if (!newRow[0] || !newRow[1] || !newRow[2] || !newRow[3]) {
console.log('ERROR: Missing required data');
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Missing required data (ID, Reporter Name, Issue, or Location)',
received: newRow
}))
.setMimeType(ContentService.MimeType.JSON);
}
// เพิ่มข้อมูลและบังคับบันทึก
sheet.appendRow(newRow);
SpreadsheetApp.flush();
Utilities.sleep(100); // รอให้แน่ใจว่าบันทึกแล้ว
console.log('Row added successfully. New total rows:', sheet.getLastRow());
return ContentService
.createTextOutput(JSON.stringify({
success: true,
action: 'addReport',
addedId: newRow[0],
totalRows: sheet.getLastRow()
}))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'getReports' || action === 'load') {
// โหลดข้อมูลทั้งหมด
console.log('Loading all reports...');
const data = sheet.getDataRange().getValues();
console.log('Raw data from sheet:', data.length, 'rows');
if (data.length <= 1) {
console.log('No data rows found (only headers or empty)');
return ContentService
.createTextOutput(JSON.stringify([]))
.setMimeType(ContentService.MimeType.JSON);
}
const headers = data[0];
const rows = data.slice(1);
console.log('Headers:', headers);
console.log('Data rows:', rows.length);
const reports = rows.map((row, index) => {
const report = {
id: String(row[0] || ''), // แปลงเป็น string เสมอ
reporterName: String(row[1] || ''),
issue: String(row[2] || ''),
location: String(row[3] || ''),
status: String(row[4] || 'pending'),
createdAt: row[5] ? String(row[5]) : '',
completedAt: row[6] ? String(row[6]) : ''
};
console.log(`Row ${index + 1}:`, report);
return report;
});
console.log('Returning', reports.length, 'reports');
return ContentService
.createTextOutput(JSON.stringify(reports))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'updateStatus') {
// อัพเดทสถานะ - ปรับปรุงให้แข็งแกร่งขึ้น
console.log('=== UPDATE STATUS REQUEST ===');
const reportId = e.parameter.reportId || e.parameters.reportId?.[0] ||
e.parameter.id || e.parameters.id?.[0] ||
e.parameter.update_id || e.parameters.update_id?.[0];
const newStatus = e.parameter.status || e.parameters.status?.[0] ||
e.parameter.newStatus || e.parameters.newStatus?.[0];
const completedAt = e.parameter.completedAt || e.parameters.completedAt?.[0];
console.log('Update parameters:');
console.log('- reportId:', reportId, '(type:', typeof reportId, ')');
console.log('- newStatus:', newStatus, '(type:', typeof newStatus, ')');
console.log('- completedAt:', completedAt);
if (!reportId || !newStatus) {
console.log('ERROR: Missing required parameters');
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Missing reportId or status',
received: {reportId, newStatus},
debug: {
hasReportId: !!reportId,
hasNewStatus: !!newStatus,
allParams: e.parameter,
allParamsArray: e.parameters
}
}))
.setMimeType(ContentService.MimeType.JSON);
}
console.log('Getting current data from sheet...');
const data = sheet.getDataRange().getValues();
console.log('Sheet has', data.length, 'total rows (including header)');
let updateDetails = {};
let matchingRowFound = false;
let rowIndex = -1;
// แสดงข้อมูลทั้งหมดใน sheet เพื่อ debug
console.log('=== CURRENT SHEET DATA ===');
for (let i = 0; i < Math.min(data.length, 10); i++) { // แสดงแค่ 10 แถวแรก
if (i === 0) {
console.log('Header row:', data[i]);
} else {
console.log(`Data row ${i} (sheet row ${i + 1}):`, data[i]);
console.log(`- ID: "${data[i][0]}" (type: ${typeof data[i][0]})`);
console.log(`- Status: "${data[i][4]}" (type: ${typeof data[i][4]})`);
}
}
console.log('=== SEARCHING FOR MATCHING ROW ===');
console.log('Looking for ID:', reportId, '(type:', typeof reportId, ')');
// ค้นหาแถวที่ตรงกัน
for (let i = 1; i < data.length; i++) {
const currentId = String(data[i][0] || ''); // แปลงเป็น string เสมอ
const targetId = String(reportId || '');
console.log(`Checking row ${i} (sheet row ${i + 1}):`);
console.log(`- Current ID: "${currentId}"`);
console.log(`- Target ID: "${targetId}"`);
console.log(`- Match: ${currentId === targetId}`);
if (currentId === targetId) {
matchingRowFound = true;
rowIndex = i;
console.log('*** MATCH FOUND! ***');
console.log('Row index:', i, '(sheet row:', i + 1, ')');
break;
}
}
if (!matchingRowFound) {
console.log('=== NO MATCH FOUND ===');
console.log('Searched ID:', reportId);
console.log('Available IDs in sheet:');
const availableIds = data.slice(1).map((row, index) => {
const id = String(row[0] || '');
console.log(`- Row ${index + 2}: "${id}"`);
return id;
});
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Record not found',
searchedId: String(reportId),
availableIds: availableIds,
debug: {
totalRows: data.length,
dataRows: data.length - 1,
searchPerformed: true
}
}))
.setMimeType(ContentService.MimeType.JSON);
}
// ทำการอัพเดท
console.log('=== UPDATING STATUS ===');
const oldStatus = String(data[rowIndex][4] || '');
const oldCompletedAt = String(data[rowIndex][6] || '');
console.log('Before update:');
console.log('- Status (column E, index 4):', oldStatus);
console.log('- CompletedAt (column G, index 6):', oldCompletedAt);
try {
// อัพเดทสถานะ
const statusCell = sheet.getRange(rowIndex + 1, 5); // Column E (Status)
console.log('Setting status in cell E' + (rowIndex + 1) + ' to:', newStatus);
statusCell.setValue(newStatus);
// อัพเดทวันที่เสร็จสิ้น
const completedAtCell = sheet.getRange(rowIndex + 1, 7); // Column G (Completed At)
let newCompletedAtValue = '';
if (newStatus === 'completed') {
if (completedAt) {
newCompletedAtValue = completedAt;
} else {
newCompletedAtValue = new Date().toISOString();
}
console.log('Setting completedAt in cell G' + (rowIndex + 1) + ' to:', newCompletedAtValue);
completedAtCell.setValue(newCompletedAtValue);
} else if (newStatus === 'pending') {
// ถ้าเปลี่ยนกลับเป็น pending ให้ลบวันที่เสร็จสิ้น
newCompletedAtValue = '';
console.log('Clearing completedAt in cell G' + (rowIndex + 1) + ' for pending status');
completedAtCell.setValue('');
}
// บังคับบันทึกและรอ
console.log('=== FORCE SAVE AND VERIFICATION ===');
SpreadsheetApp.flush();
Utilities.sleep(200); // รอให้แน่ใจ
SpreadsheetApp.flush();
console.log('Double flush completed');
// ตรวจสอบผลลัพธ์
const verificationData = sheet.getDataRange().getValues();
const actualStatus = String(verificationData[rowIndex][4] || '');
const actualCompletedAt = String(verificationData[rowIndex][6] || '');
console.log('=== VERIFICATION RESULTS ===');
console.log('After update verification:');
console.log('- Status in sheet:', actualStatus, '(expected:', newStatus, ')');
console.log('- CompletedAt in sheet:', actualCompletedAt, '(expected:', newCompletedAtValue, ')');
const statusMatch = actualStatus === newStatus;
const completedAtMatch = actualCompletedAt === newCompletedAtValue;
const updateSuccess = statusMatch && (newStatus !== 'completed' || completedAtMatch);
console.log('- Status match:', statusMatch);
console.log('- CompletedAt match:', completedAtMatch);
console.log('- Overall success:', updateSuccess);
// ถ้าอัพเดทไม่สำเร็จ ลองอีกครั้ง
if (!updateSuccess) {
console.log('*** RETRY UPDATE - First attempt failed ***');
statusCell.setValue(newStatus);
if (newStatus === 'completed' && newCompletedAtValue) {
completedAtCell.setValue(newCompletedAtValue);
} else if (newStatus === 'pending') {
completedAtCell.setValue('');
}
SpreadsheetApp.flush();
Utilities.sleep(300); // รอนานขึ้น
SpreadsheetApp.flush();
// ตรวจสอบอีกครั้ง
const retryData = sheet.getDataRange().getValues();
const retryStatus = String(retryData[rowIndex][4] || '');
const retryCompletedAt = String(retryData[rowIndex][6] || '');
console.log('After retry verification:');
console.log('- Status in sheet:', retryStatus, '(expected:', newStatus, ')');
console.log('- CompletedAt in sheet:', retryCompletedAt, '(expected:', newCompletedAtValue, ')');
const retryStatusMatch = retryStatus === newStatus;
const retryCompletedAtMatch = retryCompletedAt === newCompletedAtValue;
const retrySuccess = retryStatusMatch && (newStatus !== 'completed' || retryCompletedAtMatch);
updateDetails = {
rowIndex: rowIndex + 1,
oldStatus: oldStatus,
newStatus: newStatus,
actualStatusInSheet: retryStatus,
oldCompletedAt: oldCompletedAt,
newCompletedAt: newCompletedAtValue,
actualCompletedAtInSheet: retryCompletedAt,
updateSuccess: retrySuccess,
statusMatch: retryStatusMatch,
completedAtMatch: retryCompletedAtMatch,
retryAttempted: true
};
} else {
updateDetails = {
rowIndex: rowIndex + 1,
oldStatus: oldStatus,
newStatus: newStatus,
actualStatusInSheet: actualStatus,
oldCompletedAt: oldCompletedAt,
newCompletedAt: newCompletedAtValue,
actualCompletedAtInSheet: actualCompletedAt,
updateSuccess: updateSuccess,
statusMatch: statusMatch,
completedAtMatch: completedAtMatch,
retryAttempted: false
};
}
} catch (updateError) {
console.log('ERROR during update:', updateError.message);
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Update operation failed: ' + updateError.message,
reportId: reportId,
newStatus: newStatus
}))
.setMimeType(ContentService.MimeType.JSON);
}
console.log('=== UPDATE COMPLETE ===');
const response = {
success: updateDetails.updateSuccess,
action: 'updateStatus',
updatedId: reportId,
newStatus: newStatus,
updateDetails: updateDetails,
message: updateDetails.updateSuccess ?
'Status updated successfully in Google Sheets' :
'Status update failed - please check sheet manually and try again',
verification: {
statusUpdated: updateDetails.statusMatch,
completedAtUpdated: updateDetails.completedAtMatch,
overallSuccess: updateDetails.updateSuccess,
retryAttempted: updateDetails.retryAttempted
}
};
console.log('Sending response:', response);
return ContentService
.createTextOutput(JSON.stringify(response))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'deleteReport') {
// ลบรายการ - ปรับปรุงให้แข็งแกร่งขึ้น
console.log('=== DELETE REQUEST ===');
const reportId = e.parameter.reportId || e.parameters.reportId?.[0] ||
e.parameter.id || e.parameters.id?.[0];
console.log('Delete reportId:', reportId);
if (!reportId) {
console.log('ERROR: No reportId provided for delete');
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Missing reportId for delete',
received: e.parameter
}))
.setMimeType(ContentService.MimeType.JSON);
}
const data = sheet.getDataRange().getValues();
console.log('Sheet has', data.length, 'rows for delete operation');
let deleted = false;
let deletedRowIndex = -1;
for (let i = 1; i < data.length; i++) {
const currentId = String(data[i][0] || '');
const targetId = String(reportId || '');
console.log(`Checking row ${i} for delete: "${currentId}" vs "${targetId}"`);
if (currentId === targetId) {
console.log('Match found! Deleting row', i + 1);
deletedRowIndex = i + 1;
try {
sheet.deleteRow(i + 1);
SpreadsheetApp.flush();
Utilities.sleep(100); // รอให้แน่ใจ
deleted = true;
console.log('Row deleted successfully');
} catch (deleteError) {
console.log('ERROR during delete:', deleteError.message);
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Delete operation failed: ' + deleteError.message,
reportId: reportId
}))
.setMimeType(ContentService.MimeType.JSON);
}
break;
}
}
if (!deleted) {
console.log('No matching row found for delete');
const availableIds = data.slice(1).map(row => String(row[0] || ''));
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Record not found for delete',
searchedId: String(reportId),
availableIds: availableIds
}))
.setMimeType(ContentService.MimeType.JSON);
}
return ContentService
.createTextOutput(JSON.stringify({
success: true,
action: 'deleteReport',
deletedId: reportId,
deletedRowIndex: deletedRowIndex,
remainingRows: sheet.getLastRow()
}))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'test') {
// ทดสอบการเชื่อมต่อ
console.log('Test connection request received');
const testResponse = {
success: true,
message: 'Google Apps Script connection successful!',
timestamp: new Date().toISOString(),
sheetInfo: {
sheetId: SHEET_ID,
totalRows: sheet.getLastRow(),
totalColumns: sheet.getLastColumn(),
sheetName: sheet.getName(),
url: SpreadsheetApp.openById(SHEET_ID).getUrl()
},
systemInfo: {
timezone: Session.getScriptTimeZone(),
user: Session.getActiveUser().getEmail(),
locale: Session.getActiveUserLocale()
}
};
console.log('Test response:', testResponse);
return ContentService
.createTextOutput(JSON.stringify(testResponse))
.setMimeType(ContentService.MimeType.JSON);
}
// Default response
console.log('No specific action matched, returning default response');
return ContentService
.createTextOutput(JSON.stringify({
success: true,
message: 'Google Apps Script is working!',
receivedAction: action,
availableActions: ['addReport', 'getReports', 'load', 'updateStatus', 'deleteReport', 'test'],
sheetInfo: {
sheetId: SHEET_ID,
totalRows: sheet.getLastRow(),
sheetName: sheet.getName()
}
}))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
console.log('=== ERROR OCCURRED ===');
console.log('Error message:', error.message);
console.log('Error stack:', error.stack);
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: error.toString(),
errorMessage: error.message,
errorStack: error.stack,
message: 'Error in Google Apps Script - check logs for details',
timestamp: new Date().toISOString()
}))
.setMimeType(ContentService.MimeType.JSON);
}
}
⚠️ สำคัญ: อย่าลืมเปลี่ยน YOUR_GOOGLE_SHEETS_ID_HERE เป็น ID ของ Google Sheets จริงของคุณ (ดูจาก URL ของ Sheets)
https://docs.google.com/spreadsheets/d/1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4/editSHEET_ID ในโค้ดให้เป็น ID ที่คัดลอกมา// 🎯 Google Apps Script Code สำหรับระบบแจ้งซ่อม
// ✅ ตั้งค่า Google Sheets ID แล้ว: 1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4
function doGet(e) {
return handleRequest(e);
}
function doPost(e) {
return handleRequest(e);
}
function handleRequest(e) {
try {
// ✅ ตั้งค่า SHEET_ID แล้ว
const SHEET_ID = '1ZvFlO6AmspfseUAMziEsFxWPIiR-NHo1EIiFjbsjZA4';
const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
// ตั้งค่า Headers ถ้ายังไม่มี
if (sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, 7).setValues([
['ID', 'Reporter Name', 'Issue', 'Location', 'Status', 'Created At', 'Completed At']
]);
SpreadsheetApp.flush();
}
const action = e.parameter.action || e.parameters.action?.[0];
if (action === 'addReport') {
// เพิ่มรายการใหม่
const newRow = [
e.parameter.id || e.parameters.id?.[0],
e.parameter.reporterName || e.parameters.reporterName?.[0],
e.parameter.issue || e.parameters.issue?.[0],
e.parameter.location || e.parameters.location?.[0],
e.parameter.status || e.parameters.status?.[0] || 'pending',
e.parameter.createdAt || e.parameters.createdAt?.[0],
e.parameter.completedAt || e.parameters.completedAt?.[0] || ''
];
sheet.appendRow(newRow);
SpreadsheetApp.flush();
return ContentService
.createTextOutput(JSON.stringify({
success: true,
action: 'addReport',
addedId: newRow[0]
}))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'getReports' || action === 'load') {
// โหลดข้อมูลทั้งหมด
const data = sheet.getDataRange().getValues();
if (data.length <= 1) {
return ContentService
.createTextOutput(JSON.stringify([]))
.setMimeType(ContentService.MimeType.JSON);
}
const headers = data[0];
const rows = data.slice(1);
const reports = rows.map(row => ({
id: String(row[0] || ''),
reporterName: String(row[1] || ''),
issue: String(row[2] || ''),
location: String(row[3] || ''),
status: String(row[4] || 'pending'),
createdAt: row[5] ? String(row[5]) : '',
completedAt: row[6] ? String(row[6]) : ''
}));
return ContentService
.createTextOutput(JSON.stringify(reports))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'updateStatus') {
// อัพเดทสถานะ
const reportId = e.parameter.reportId || e.parameters.reportId?.[0];
const newStatus = e.parameter.status || e.parameters.status?.[0];
const completedAt = e.parameter.completedAt || e.parameters.completedAt?.[0];
if (!reportId || !newStatus) {
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Missing reportId or status'
}))
.setMimeType(ContentService.MimeType.JSON);
}
const data = sheet.getDataRange().getValues();
let updated = false;
for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(reportId)) {
sheet.getRange(i + 1, 5).setValue(newStatus); // Status column
if (newStatus === 'completed' && completedAt) {
sheet.getRange(i + 1, 7).setValue(completedAt); // Completed At column
} else if (newStatus === 'pending') {
sheet.getRange(i + 1, 7).setValue(''); // Clear completed date
}
updated = true;
break;
}
}
SpreadsheetApp.flush();
return ContentService
.createTextOutput(JSON.stringify({
success: updated,
action: 'updateStatus',
updatedId: reportId
}))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'deleteReport') {
// ลบรายการ
const reportId = e.parameter.reportId || e.parameters.reportId?.[0];
if (!reportId) {
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: 'Missing reportId'
}))
.setMimeType(ContentService.MimeType.JSON);
}
const data = sheet.getDataRange().getValues();
let deleted = false;
for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(reportId)) {
sheet.deleteRow(i + 1);
deleted = true;
break;
}
}
SpreadsheetApp.flush();
return ContentService
.createTextOutput(JSON.stringify({
success: deleted,
action: 'deleteReport',
deletedId: reportId
}))
.setMimeType(ContentService.MimeType.JSON);
} else if (action === 'test') {
// ทดสอบการเชื่อมต่อ
return ContentService
.createTextOutput(JSON.stringify({
success: true,
message: 'Google Apps Script connection successful!',
timestamp: new Date().toISOString(),
sheetInfo: {
sheetId: SHEET_ID,
totalRows: sheet.getLastRow(),
sheetName: sheet.getName()
}
}))
.setMimeType(ContentService.MimeType.JSON);
}
return ContentService
.createTextOutput(JSON.stringify({
success: true,
message: 'Google Apps Script is working!'
}))
.setMimeType(ContentService.MimeType.JSON);
} catch (error) {
return ContentService
.createTextOutput(JSON.stringify({
success: false,
error: error.toString(),
message: 'Error in Google Apps Script'
}))
.setMimeType(ContentService.MimeType.JSON);
}
}
URL ควรขึ้นต้นด้วย https://script.google.com/macros/s/
URL ควรขึ้นต้นด้วย https://script.google.com/macros/s/