"선생님, 저 이거 못 들으면 졸업 못해요? 😭"
이런 말 나오지 않게 미리미리 챙겨야죠! 고등학교 교육과정 편성의 구세주, [과목 선택 검증 프로그램] 등판! 🦸♂️
수백 명 학생의 과목 선택, 일일이 엑셀 눈빠지게 검토하고 계신가요? 기초 교과 총점, 탐구 영역 필수 이수, 위계성 위반... 🤯 하나라도 놓치면 큰일 나는 교육과정 검증, 이제 프로그램에 맡기세요.
🛡️ 완벽한 검증 시스템
졸업 요건 자동 체크: 필수 이수 단위 부족 여부 즉시 확인
규정 위반 알림: 위계성, 영역별 선택 제한 등 복잡한 조건 완벽 필터링
클릭 한 번으로 끝: 학생 데이터만 넣으면 검증 결과가 1초 만에!
[구글 앱스크립트 코드 pdf에서 복사 안되면 아래에서 복사해서 쓰세요.]
/**
* Google Apps Script for Curriculum Planner Backend
* Updated with deleteResponse capability
*/
function setup() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (!ss.getSheetByName('Config')) ss.insertSheet('Config');
if (!ss.getSheetByName('Settings')) ss.insertSheet('Settings');
if (!ss.getSheetByName('Responses')) ss.insertSheet('Responses');
if (!ss.getSheetByName('Registry')) ss.insertSheet('Registry');
if (ss.getSheetByName('Sheet1')) ss.deleteSheet(ss.getSheetByName('Sheet1'));
}
function doGet(e) {
const action = e.parameter.action;
if (action === 'getConfig') {
return getConfig();
} else if (action === 'getResponses') {
return getResponses();
} else if (action === 'getSettings') {
return getSettings();
} else if (action === 'getRegistry') {
return getRegistry();
}
return ContentService.createTextOutput(JSON.stringify({ status: 'error', message: 'Invalid action' }))
.setMimeType(ContentService.MimeType.JSON);
}
function doPost(e) {
try {
const request = JSON.parse(e.postData.contents);
const action = request.action;
const data = request.data;
if (action === 'saveConfig') {
return saveConfig(data);
} else if (action === 'submitResponse') {
return submitResponse(data);
} else if (action === 'saveSettings') {
return saveSettings(data);
} else if (action === 'saveRegistry') {
return saveRegistry(data);
} else if (action === 'deleteResponse') { // [NEW] Delete Handler
return deleteResponse(data);
}
return createJSONOutput({ status: 'error', message: 'Invalid action' });
} catch (error) {
return createJSONOutput({ status: 'error', message: error.toString() });
}
}
function getConfig() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Config');
const data = sheet.getDataRange().getValues();
if (data.length <= 1) return createJSONOutput([]);
const headers = data[0];
const rows = data.slice(1);
const result = rows.map(row => {
const obj = {};
headers.forEach((header, index) => {
obj[header] = row[index];
});
return obj;
});
return createJSONOutput(result);
}
function saveConfig(data) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Config');
sheet.clear();
if (!data || data.length === 0) return createJSONOutput({ status: 'success' });
const headers = Object.keys(data[0]);
sheet.appendRow(headers);
const rows = data.map(obj => headers.map(header => obj[header]));
// Batch write for performance
if (rows.length > 0) {
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}
return createJSONOutput({ status: 'success' });
}
function submitResponse(data) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Responses');
// If sheet is empty, add headers
if (sheet.getLastRow() === 0) {
const headers = ['Timestamp', 'Grade', 'Class', 'Number', 'Name', 'Major', 'SelectedCourses', 'JointCourses', 'TotalCredits', 'ValidationResult', 'AiRecommendation'];
sheet.appendRow(headers);
} else {
// Check if new headers exist, if not add them
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const missingHeaders = [];
if (!headers.includes('ValidationResult')) missingHeaders.push('ValidationResult');
if (!headers.includes('AiRecommendation')) missingHeaders.push('AiRecommendation');
if (missingHeaders.length > 0) {
// Append missing headers
sheet.getRange(1, headers.length + 1, 1, missingHeaders.length).setValues([missingHeaders]);
}
}
// Re-fetch headers to ensure correct mapping
const currentHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const row = [];
// Construct row based on headers
currentHeaders.forEach(header => {
if (header === 'Timestamp') row.push(new Date());
else if (header === 'Grade') row.push(data.Grade || data.grade);
else if (header === 'Class') row.push(data.Class || data.classNum);
else if (header === 'Number') row.push(data.Number || data.studentNum);
else if (header === 'Name') row.push(data.Name || data.name);
else if (header === 'Major') row.push(data.Major || data.major);
else if (header === 'SelectedCourses') row.push(data.SelectedCourses || data.selectedCourses);
else if (header === 'JointCourses') row.push(Array.isArray(data.JointCourses || data.jointCourses) ? (data.JointCourses || data.jointCourses).map(c => c.subjectName).join(', ') : (data.JointCourses || data.jointCourses || ''));
else if (header === 'TotalCredits') row.push(data.TotalCredits || data.totalCredits);
else if (header === 'ValidationResult') row.push(data.ValidationResult || data.validationResult || '');
else if (header === 'AiRecommendation') row.push(data.AiRecommendation || data.aiRecommendation || '');
else row.push(''); // Unknown header placeholder
});
sheet.appendRow(row);
return createJSONOutput({ status: 'success' });
}
// [NEW] Delete Response Function
function deleteResponse(idsToDelete) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Responses');
// If empty, nothing to delete
if (sheet.getLastRow() <= 1) return createJSONOutput({ status: 'success', message: 'No data to delete' });
const data = sheet.getDataRange().getValues();
let rowsDeleted = 0;
// Iterate backwards to safely delete rows
// Skipping header (index 0), so stopping at i=1
for (let i = data.length - 1; i >= 1; i--) {
const row = data[i];
// Column Index Assumptions based on submitResponse:
// 0: Timestamp (Date object)
// 1: Grade, 2: Class, 3: Number
const timestamp = row[0];
// Determine unique ID for this row to compare with idsToDelete
// 1. Try Timestamp match
let timestampStr = String(timestamp);
let isoStr = "";
try { isoStr = timestamp.toISOString(); } catch(e) {}
// 2. Try Grade-Class-Number match (fallback)
const uniqueId = row[1] + '-' + row[2] + '-' + row[3];
// Check if this row matches any ID in the deletion list
if (idsToDelete.includes(timestamp) ||
idsToDelete.includes(timestampStr) ||
(isoStr && idsToDelete.includes(isoStr)) ||
idsToDelete.includes(uniqueId)) {
sheet.deleteRow(i + 1); // deleteRow uses 1-based index
rowsDeleted++;
}
}
return createJSONOutput({ status: 'success', message: rowsDeleted + ' rows deleted' });
}
function getResponses() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Responses');
const data = sheet.getDataRange().getValues();
if (data.length <= 1) return createJSONOutput([]);
const headers = data[0];
const rows = data.slice(1);
const result = rows.map(row => {
const obj = {};
headers.forEach((header, index) => {
obj[header] = row[index];
});
return obj;
});
return createJSONOutput(result);
}
function getSettings() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Settings');
if (sheet.getLastRow() === 0) return createJSONOutput({}); // Empty settings
const settingsJson = sheet.getRange(1, 1).getValue();
if (!settingsJson) return createJSONOutput({});
try {
return createJSONOutput(JSON.parse(settingsJson));
} catch (e) {
return createJSONOutput({});
}
}
function saveSettings(data) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Settings');
sheet.clear();
// Save as JSON string in A1
sheet.getRange(1, 1).setValue(JSON.stringify(data));
return createJSONOutput({ status: 'success' });
}
function getRegistry() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Registry');
const data = sheet.getDataRange().getValues();
if (data.length <= 1) return createJSONOutput([]);
const headers = data[0];
const rows = data.slice(1);
const result = rows.map(row => {
const obj = {};
headers.forEach((header, index) => {
obj[header] = row[index];
});
return obj;
});
return createJSONOutput(result);
}
function saveRegistry(data) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Registry');
sheet.clear();
if (!data || data.length === 0) return createJSONOutput({ status: 'success' });
const headers = Object.keys(data[0]);
sheet.appendRow(headers);
const rows = data.map(obj => headers.map(header => obj[header]));
// Batch write
if (rows.length > 0) {
sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}
return createJSONOutput({ status: 'success' });
}
function createJSONOutput(data) {
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
