/** * ============================================================================ * BACKEND GOOGLE APPS SCRIPT (Code.gs) * Aplikasi Simulasi TKA & ANBK PUSMENDIK * 100% Kompatibel dengan aplikasi_simulasi_tka_anbk_pusmendik.html tanpa mengubah HTML * ============================================================================ */ const SPREADSHEET_ID = ''; // Kosongkan jika script terikat (bound) ke Spreadsheet, atau isi ID Spreadsheet Anda const SCHEMA_TABLES = { pengaturan_sekolah: [ 'id', 'nama_instansi', 'nama_aplikasi', 'nama_sekolah', 'logo_url', 'token_aktif', 'max_pelanggaran', 'updated_at' ], ruang_ujian: [ 'id', 'kode_ruang', 'nama_ruang', 'keterangan', 'created_at' ], sesi_ujian: [ 'id', 'nama_sesi', 'jam_mulai', 'jam_selesai', 'created_at' ], mapel: [ 'id', 'kode_mapel', 'nama_mapel', 'durasi_menit', 'aktif', 'created_at' ], siswa: [ 'id', 'username', 'password', 'nama_lengkap', 'jenis_kelamin', 'tgl_lahir', 'ruang_id', 'created_at' ], jadwal_peserta: [ 'id', 'siswa_id', 'mapel_id', 'ruang_id', 'sesi_id', 'hari', 'tanggal', 'created_at' ], stimulus: [ 'id', 'mapel_id', 'judul', 'konten_teks', 'gambar_url', 'created_at' ], soal: [ 'id', 'mapel_id', 'stimulus_id', 'nomor_urut', 'jenis_soal', 'pertanyaan', 'opsi_a', 'opsi_b', 'opsi_c', 'opsi_d', 'opsi_e', 'kunci_jawaban', 'bobot', 'created_at' ], hasil_ujian: [ 'id', 'siswa_id', 'mapel_id', 'jawaban_siswa', 'skor', 'jumlah_pelanggaran', 'status', 'waktu_mulai', 'waktu_selesai' ], admin_users: [ 'id', 'email', 'password', 'nama_admin', 'created_at' ] }; /** * Mengambil instance Spreadsheet aktif atau berdasarkan ID */ function getDbSpreadsheet() { if (SPREADSHEET_ID && SPREADSHEET_ID.trim() !== '') { return SpreadsheetApp.openById(SPREADSHEET_ID.trim()); } const active = SpreadsheetApp.getActiveSpreadsheet(); if (active) return active; throw new Error('Spreadsheet tidak ditemukan. Pastikan script dibuat dari Google Sheets atau isi SPREADSHEET_ID.'); } /** * Inisialisasi seluruh Sheet dan data awal jika belum ada * Jalankan fungsi ini 1x dari editor Apps Script atau otomatis saat pertama kali dibuka */ function setupDatabase() { const ss = getDbSpreadsheet(); Object.keys(SCHEMA_TABLES).forEach(tableName => { let sheet = ss.getSheetByName(tableName); const headers = SCHEMA_TABLES[tableName]; if (!sheet) { sheet = ss.insertSheet(tableName); sheet.getRange(1, 1, 1, headers.length).setValues([headers]); sheet.getRange(1, 1, 1, headers.length) .setFontWeight('bold') .setBackground('#1E6091') .setFontColor('#FFFFFF'); sheet.setFrozenRows(1); } else if (sheet.getLastRow() === 0) { sheet.getRange(1, 1, 1, headers.length).setValues([headers]); } }); // Seed Data Default Pengaturan Sekolah jika kosong const cfgSheet = ss.getSheetByName('pengaturan_sekolah'); if (cfgSheet.getLastRow() < 2) { cfgSheet.appendRow([ 'cfg-1', 'PUSMENDIK', 'APLIKASI ANBK', 'SMP NEGERI 1 SIMULASI TKA', "data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 120 120'%3E%3Cpolygon points='60,6 110,32 92,98 28,98 10,32' fill='%230055A5' stroke='%23FFD700' stroke-width='4'/%3E%3Ccircle cx='60' cy='56' r='30' fill='%23ffffff'/%3E%3Cpath d='M60 32L32 46l28 14 28-14-28-14zm-18 21v14c0 6 8 11 18 11s18-5 18-11V53l-18 9-18-9z' fill='%231E6091'/%3E%3C/svg%3E", 'TKA26A', 3, new Date().toISOString() ]); } // Seed Akun Admin Default jika kosong const adminSheet = ss.getSheetByName('admin_users'); if (adminSheet.getLastRow() < 2) { adminSheet.appendRow([ 'adm-1', 'admin@pusmendik.id', 'admin123', 'Proktor Utama TKA', new Date().toISOString() ]); } return { status: 'OK', message: 'Seluruh tabel database berhasil disiapkan.' }; } /** * Entry Point Web App Google Apps Script * Menyajikan file index.html tanpa mengubah satu baris pun isinya, * sekaligus menyuntikkan adapter backend Google Sheets agar langsung terhubung. */ function doGet(e) { setupDatabase(); let htmlContent = HtmlService.createHtmlOutputFromFile('index').getContent(); // Adapter runtime yang mengarahkan pemanggilan database ke Google Sheets // tanpa memodifikasi kode asli pada file index.html const gasBridgeScript = ` `; // Menyesuaikan flag runtime di memori output agar terhubung ke Google Sheets // tanpa mengubah isi file index.html asli di proyek Anda htmlContent = htmlContent .replace('const IS_PREVIEW = true;', 'const IS_PREVIEW = false;') .replace('const SUPABASE_URL = "URL_SUPABASE_ANDA";', 'const SUPABASE_URL = "https://gas-backend.local";') .replace('', gasBridgeScript + '\n'); return HtmlService.createHtmlOutput(htmlContent) .setTitle('PUSMENDIK - Aplikasi Simulasi TKA & ANBK') .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) .addMetaTag('viewport', 'width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no'); } /** * ============================================================================ * AUTENTIKASI ADMIN / PROKTOR * ============================================================================ */ function gasAdminLogin(email, password) { try { const admins = readAllRows('admin_users'); const cleanEmail = String(email || '').trim().toLowerCase(); const cleanPass = String(password || '').trim(); const found = admins.find( a => String(a.email || '').trim().toLowerCase() === cleanEmail && String(a.password || '').trim() === cleanPass ); if (!found) { return { data: null, error: { message: 'Email atau Password Admin tidak terdaftar di Sheet admin_users!' } }; } return { data: { user: { id: found.id, email: found.email, nama_admin: found.nama_admin } }, error: null }; } catch (err) { return { data: null, error: { message: err.message } }; } } /** * ============================================================================ * ENGINE DATABASE GOOGLE SHEETS (SELECT, INSERT, UPDATE, UPSERT, DELETE) * ============================================================================ */ function gasExecuteQuery(q) { const lock = LockService.getScriptLock(); try { if (q.action !== 'select') { lock.waitLock(15000); } const table = q.table; if (!SCHEMA_TABLES[table]) { return { data: null, error: { message: 'Tabel tidak dikenal: ' + table } }; } if (q.action === 'select') { let rows = readAllRows(table); rows = applyFilters(rows, q.filters); rows = applyOrder(rows, q.orderBy); if (q.limitCount && Number(q.limitCount) > 0) { rows = rows.slice(0, Number(q.limitCount)); } return formatReturn(rows, q.singleMode); } if (q.action === 'insert') { const inserted = insertRows(table, q.payload || []); return formatReturn(inserted, q.singleMode); } if (q.action === 'update') { const updated = updateRows(table, q.payload || {}, q.filters || []); return formatReturn(updated, q.singleMode); } if (q.action === 'upsert') { const upserted = upsertRows(table, q.payload || [], q.upsertOptions || {}); return formatReturn(upserted, q.singleMode); } if (q.action === 'delete') { deleteRows(table, q.filters || []); return { data: [], error: null }; } return { data: null, error: { message: 'Aksi query tidak didukung: ' + q.action } }; } catch (err) { return { data: null, error: { message: err.message || String(err) } }; } finally { try { lock.releaseLock(); } catch (_) {} } } function formatReturn(rows, singleMode) { if (singleMode === 'single') { if (!rows || rows.length === 0) { return { data: null, error: { message: 'Data tidak ditemukan' } }; } return { data: rows[0], error: null }; } if (singleMode === 'maybeSingle') { return { data: rows && rows.length > 0 ? rows[0] : null, error: null }; } return { data: rows || [], error: null }; } /** * Membaca seluruh baris dari Sheet dan mengonversi tipe data (JSON, Boolean, Number) */ function readAllRows(tableName) { const ss = getDbSpreadsheet(); const sheet = ss.getSheetByName(tableName); if (!sheet) return []; const lastRow = sheet.getLastRow(); const lastCol = sheet.getLastColumn(); if (lastRow < 2 || lastCol < 1) return []; const values = sheet.getRange(1, 1, lastRow, lastCol).getValues(); const headers = values[0].map(h => String(h).trim()); const result = []; for (let i = 1; i < values.length; i++) { const rowArr = values[i]; if (rowArr.every(cell => cell === '' || cell === null)) continue; const obj = {}; headers.forEach((h, colIdx) => { let val = rowArr[colIdx]; if (val instanceof Date) { val = val.toISOString(); } else if (h === 'aktif') { val = (val === true || String(val).toUpperCase() === 'TRUE' || val === 1); } else if (h === 'durasi_menit' || h === 'nomor_urut' || h === 'bobot' || h === 'skor' || h === 'jumlah_pelanggaran' || h === 'max_pelanggaran') { val = val === '' ? 0 : Number(val); } else if (h === 'jawaban_siswa') { if (typeof val === 'string' && val.trim() !== '') { try { val = JSON.parse(val); } catch (_) { val = {}; } } else if (!val || typeof val !== 'object') { val = {}; } } else { val = val === null || val === undefined ? '' : String(val); } obj[h] = val; }); result.push(obj); } return result; } function applyFilters(rows, filters) { if (!filters || filters.length === 0) return rows; return rows.filter(row => { return filters.every(f => { if (f.op === 'eq') { return String(row[f.col]) === String(f.val); } return true; }); }); } function applyOrder(rows, orderBy) { if (!orderBy || !orderBy.col) return rows; const col = orderBy.col; const asc = orderBy.ascending !== false; return rows.slice().sort((a, b) => { const va = a[col]; const vb = b[col]; if (typeof va === 'number' && typeof vb === 'number') { return asc ? va - vb : vb - va; } const sa = String(va || ''); const sb = String(vb || ''); return asc ? sa.localeCompare(sb) : sb.localeCompare(sa); }); } function serializeCellValue(colName, val) { if (val === null || val === undefined) return ''; if (colName === 'jawaban_siswa' && typeof val === 'object') { return JSON.stringify(val); } if (typeof val === 'object') { return JSON.stringify(val); } return val; } function generateRowId(prefix) { return prefix + '-' + Utilities.getUuid().slice(0, 8); } function insertRows(tableName, items) { const ss = getDbSpreadsheet(); const sheet = ss.getSheetByName(tableName); const headers = SCHEMA_TABLES[tableName]; const nowIso = new Date().toISOString(); const insertedRecords = []; const rowsToAppend = items.map(item => { const record = Object.assign({}, item); if (!record.id) { record.id = generateRowId(tableName.slice(0, 3)); } if (headers.includes('created_at') && !record.created_at) { record.created_at = nowIso; } if (headers.includes('waktu_mulai') && !record.waktu_mulai) { record.waktu_mulai = nowIso; } insertedRecords.push(record); return headers.map(h => serializeCellValue(h, record[h])); }); if (rowsToAppend.length > 0) { const startRow = sheet.getLastRow() + 1; sheet.getRange(startRow, 1, rowsToAppend.length, headers.length).setValues(rowsToAppend); } return insertedRecords; } function updateRows(tableName, patchObj, filters) { const ss = getDbSpreadsheet(); const sheet = ss.getSheetByName(tableName); const headers = SCHEMA_TABLES[tableName]; const lastRow = sheet.getLastRow(); if (lastRow < 2) return []; const allValues = sheet.getRange(1, 1, lastRow, headers.length).getValues(); const updatedRecords = []; for (let r = 1; r < allValues.length; r++) { const rowObj = {}; headers.forEach((h, c) => { rowObj[h] = allValues[r][c]; }); const match = applyFilters([rowObj], filters).length > 0; if (match) { const merged = Object.assign({}, rowObj, patchObj); const newRowArr = headers.map(h => serializeCellValue(h, merged[h])); sheet.getRange(r + 1, 1, 1, headers.length).setValues([newRowArr]); updatedRecords.push(merged); } } return updatedRecords; } function upsertRows(tableName, items, options) { const conflictCols = options && options.onConflict ? String(options.onConflict).split(',').map(s => s.trim()).filter(Boolean) : ['id']; const existingRows = readAllRows(tableName); const results = []; items.forEach(item => { const matched = existingRows.find(ex => conflictCols.every(col => item[col] !== undefined && String(ex[col]) === String(item[col])) ); if (matched) { const filters = conflictCols.map(col => ({ op: 'eq', col, val: item[col] })); const upd = updateRows(tableName, item, filters); if (upd.length > 0) results.push(upd[0]); } else { const ins = insertRows(tableName, [item]); if (ins.length > 0) results.push(ins[0]); } }); return results; } function deleteRows(tableName, filters) { const ss = getDbSpreadsheet(); const sheet = ss.getSheetByName(tableName); const headers = SCHEMA_TABLES[tableName]; const lastRow = sheet.getLastRow(); if (lastRow < 2) return; const allValues = sheet.getRange(1, 1, lastRow, headers.length).getValues(); // Iterasi dari bawah ke atas agar indeks baris tidak bergeser saat dihapus for (let r = allValues.length - 1; r >= 1; r--) { const rowObj = {}; headers.forEach((h, c) => { rowObj[h] = allValues[r][c]; }); const match = applyFilters([rowObj], filters).length > 0; if (match) { sheet.deleteRow(r + 1); } } }