/**
* ============================================================
* DATABASE SISWA — Liquid Glass Web App (dengan Login & Role)
* ============================================================
* Role yang didukung: admin, user, tamu — semuanya WAJIB login.
*
* Setup:
* 1. Buka Google Sheet database siswa kamu (tab data: "UPDATE").
* 2. Extensions > Apps Script.
* 3. Timpa Code.gs dengan file ini, timpa Index.html dengan file baru.
* 4. Deploy > New deployment > Web app, Execute as: Me, Access: Anyone.
* 5. Buka web app-nya, login pakai akun default:
* Username: admin
* Password: admin123
* -> SEGERA ganti password ini lewat menu Settings > Kelola Akun.
* ============================================================
*/
const SHEET_NAME = 'UPDATE'; // tab data siswa
const USERS_SHEET_NAME = 'Users'; // tab akun (dibuat otomatis kalau belum ada)
const SETTINGS_KEY = 'APP_SETTINGS';
const SESSION_DURATION = 6 * 60 * 60; // 6 jam (maksimum CacheService)
function doGet() {
return HtmlService.createHtmlOutputFromFile('Index')
.setTitle('Database Siswa')
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/* ============================================================
* SHEET HELPERS
* ============================================================ */
function getSheet_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SHEET_NAME);
if (!sheet) throw new Error('Sheet "' + SHEET_NAME + '" tidak ditemukan.');
return sheet;
}
function getUsersSheet_() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(USERS_SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(USERS_SHEET_NAME);
sheet.getRange(1, 1, 1, 4).setValues([['Username', 'PasswordHash', 'Role', 'DibuatPada']]);
sheet.getRange(2, 1, 1, 4).setValues([['admin', hashPassword_('admin123'), 'admin', new Date()]]);
}
return sheet;
}
function hashPassword_(password) {
const bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, String(password), Utilities.Charset.UTF_8);
return bytes.map(b => ('0' + (b & 0xFF).toString(16)).slice(-2)).join('');
}
function dedupeHeaders_(headers) {
const seen = {};
return headers.map(h => {
const key = h || '(kosong)';
if (seen[key] === undefined) { seen[key] = 1; return key; }
seen[key] += 1;
return key + ' (' + seen[key] + ')';
});
}
/* ============================================================
* AUTH
* ============================================================ */
function login(username, password) {
const sheet = getUsersSheet_();
const values = sheet.getDataRange().getValues();
const hash = hashPassword_(password);
for (let i = 1; i < values.length; i++) {
const u = values[i][0], p = values[i][1], role = values[i][2];
if (String(u).trim().toLowerCase() === String(username).trim().toLowerCase() && p === hash) {
const token = Utilities.getUuid();
CacheService.getScriptCache().put(token, JSON.stringify({ username: u, role: role }), SESSION_DURATION);
return { success: true, token: token, username: u, role: role };
}
}
return { success: false, message: 'Username atau password salah' };
}
function logout(token) {
try { CacheService.getScriptCache().remove(token); } catch (e) {}
return { success: true };
}
function validateSession_(token) {
const raw = CacheService.getScriptCache().get(token);
if (!raw) throw new Error('Sesi kamu habis, silakan login ulang.');
return JSON.parse(raw);
}
function changePassword(token, oldPassword, newPassword) {
const session = validateSession_(token);
const sheet = getUsersSheet_();
const values = sheet.getDataRange().getValues();
const oldHash = hashPassword_(oldPassword);
for (let i = 1; i < values.length; i++) {
if (values[i][0] === session.username) {
if (values[i][1] !== oldHash) throw new Error('Password lama salah');
sheet.getRange(i + 1, 2).setValue(hashPassword_(newPassword));
return { success: true };
}
}
throw new Error('User tidak ditemukan');
}
/* ============================================================
* USER MANAGEMENT (admin only)
* ============================================================ */
function listUsers(token) {
const session = validateSession_(token);
if (session.role !== 'admin') throw new Error('Cuma admin yang bisa lihat daftar akun');
const sheet = getUsersSheet_();
const values = sheet.getDataRange().getValues();
const users = [];
for (let i = 1; i < values.length; i++) {
users.push({ username: values[i][0], role: values[i][2] });
}
return users;
}
function addUser(token, username, password, role) {
const session = validateSession_(token);
if (session.role !== 'admin') throw new Error('Cuma admin yang bisa tambah akun');
username = String(username || '').trim();
if (!username || !password) throw new Error('Username dan password wajib diisi');
if (['admin', 'user', 'tamu'].indexOf(role) === -1) throw new Error('Role tidak valid');
const sheet = getUsersSheet_();
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
if (String(values[i][0]).toLowerCase() === username.toLowerCase()) {
throw new Error('Username sudah dipakai');
}
}
sheet.appendRow([username, hashPassword_(password), role, new Date()]);
return { success: true };
}
function deleteUser(token, username) {
const session = validateSession_(token);
if (session.role !== 'admin') throw new Error('Cuma admin yang bisa hapus akun');
if (username === session.username) throw new Error('Ga bisa hapus akun sendiri yang lagi dipakai');
const sheet = getUsersSheet_();
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
if (values[i][0] === username) {
sheet.deleteRow(i + 1);
return { success: true };
}
}
throw new Error('User tidak ditemukan');
}
/* ============================================================
* SETTINGS (kolom per role, judul & warna tema)
* ============================================================ */
function getDefaultSettings_() {
return {
siteTitle: 'Database Siswa',
tagline: 'Liquid glass interface',
logo: '🎓',
loginHeading: 'Database Siswa',
loginSubtitle: 'Masuk untuk melanjutkan',
accentColor: '#4C7EF3',
copyrightText: 'Database Siswa',
columnVisibility: { user: null, tamu: null } // null = semua kolom boleh dilihat
};
}
function getSettingsRaw_() {
const raw = PropertiesService.getScriptProperties().getProperty(SETTINGS_KEY);
if (!raw) return getDefaultSettings_();
try {
return Object.assign(getDefaultSettings_(), JSON.parse(raw));
} catch (e) {
return getDefaultSettings_();
}
}
function getSettings(token) {
const session = validateSession_(token);
const settings = getSettingsRaw_();
const result = {
siteTitle: settings.siteTitle,
tagline: settings.tagline,
logo: settings.logo,
loginHeading: settings.loginHeading,
loginSubtitle: settings.loginSubtitle,
accentColor: settings.accentColor,
copyrightText: settings.copyrightText,
role: session.role,
username: session.username
};
if (session.role === 'admin') {
result.columnVisibility = settings.columnVisibility;
result.allHeaders = getAllHeaders_();
}
return result;
}
/**
* Branding yang boleh diakses TANPA login, buat nge-style halaman login
* sebelum orang berhasil masuk (logo, judul, tagline, teks halaman login, warna).
*/
function getPublicBranding() {
const settings = getSettingsRaw_();
return {
siteTitle: settings.siteTitle,
tagline: settings.tagline,
logo: settings.logo,
loginHeading: settings.loginHeading,
loginSubtitle: settings.loginSubtitle,
accentColor: settings.accentColor,
copyrightText: settings.copyrightText
};
}
function saveSettings(token, newSettings) {
const session = validateSession_(token);
if (session.role !== 'admin') throw new Error('Cuma admin yang bisa ubah pengaturan');
const merged = Object.assign(getSettingsRaw_(), newSettings);
PropertiesService.getScriptProperties().setProperty(SETTINGS_KEY, JSON.stringify(merged));
return { success: true };
}
function getAllHeaders_() {
const sheet = getSheet_();
return dedupeHeaders_(
sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => String(h).trim())
);
}
function getAllowedColumns_(role, allHeaders, settings) {
if (role === 'admin') return allHeaders;
const configured = settings.columnVisibility && settings.columnVisibility[role];
if (!configured || configured.length === 0) return allHeaders; // belum diatur = semua boleh
return allHeaders.filter(h => configured.indexOf(h) !== -1);
}
/* ============================================================
* DATA SISWA (role-aware)
* ============================================================ */
function getStudents(token) {
const session = validateSession_(token);
const sheet = getSheet_();
const values = sheet.getDataRange().getValues();
if (values.length === 0) return { headers: [], rows: [], role: session.role };
const allHeaders = dedupeHeaders_(values[0].map(h => String(h).trim()));
const settings = getSettingsRaw_();
const allowed = getAllowedColumns_(session.role, allHeaders, settings);
const rows = [];
for (let i = 1; i < values.length; i++) {
const rowValues = values[i];
const isEmpty = rowValues.every(v => v === '' || v === null);
if (isEmpty) continue;
const obj = {};
allHeaders.forEach((h, idx) => {
if (allowed.indexOf(h) === -1) return;
let val = rowValues[idx];
if (val instanceof Date) {
val = Utilities.formatDate(val, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}
obj[h] = val;
});
obj.__rowIndex = i + 1;
rows.push(obj);
}
return {
headers: allowed,
rows: rows,
role: session.role,
canAdd: session.role !== 'tamu',
canEdit: session.role !== 'tamu',
canDelete: session.role === 'admin'
};
}
function addStudent(token, data) {
const session = validateSession_(token);
if (session.role === 'tamu') throw new Error('Akun Tamu tidak bisa menambah data');
const sheet = getSheet_();
const headers = dedupeHeaders_(
sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => String(h).trim())
);
const newRow = headers.map(h => (data[h] !== undefined ? data[h] : ''));
sheet.appendRow(newRow);
return { success: true };
}
function updateStudent(token, rowIndex, data) {
const session = validateSession_(token);
if (session.role === 'tamu') throw new Error('Akun Tamu tidak bisa mengubah data');
const sheet = getSheet_();
const headers = dedupeHeaders_(
sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => String(h).trim())
);
headers.forEach((h, idx) => {
if (data[h] !== undefined) sheet.getRange(rowIndex, idx + 1).setValue(data[h]);
});
return { success: true };
}
function deleteStudent(token, rowIndex) {
const session = validateSession_(token);
if (session.role !== 'admin') throw new Error('Cuma admin yang bisa hapus data');
const sheet = getSheet_();
sheet.deleteRow(rowIndex);
return { success: true };
}
/* ============================================================
* REKAP — breakdown Kelas/Rombel dan Status/JK, dihitung live
* dari data di sheet (bukan angka statis), bisa diakses semua role.
* ============================================================ */
function getRekap(token) {
validateSession_(token); // semua role yang login boleh lihat rekap
const sheet = getSheet_();
const values = sheet.getDataRange().getValues();
if (values.length === 0) return { perKelasRombel: [], perStatusJk: [], totalPerStatus: {} };
const allHeaders = dedupeHeaders_(values[0].map(h => String(h).trim()));
const findIdx = (name) => allHeaders.findIndex(h => h.toUpperCase() === name);
const idxKelas = findIdx('KELAS');
const idxRom = findIdx('ROM');
const idxStatus = findIdx('STATUS SISWA');
const idxJk = findIdx('JK');
const VALID_STATUSES = ['aktif', 'pindah', 'keluar']; // status di luar ini (misal salah input) diabaikan dari rekap
const krMap = {}; // "kelas|rombel|status" -> jumlah
const sjMap = {}; // "kelas|rombel|status|jk" -> jumlah
const totalPerStatus = {};
for (let i = 1; i < values.length; i++) {
const row = values[i];
const isEmpty = row.every(v => v === '' || v === null);
if (isEmpty) continue;
const kelas = idxKelas > -1 ? String(row[idxKelas] || '').trim() : '';
const rombel = idxRom > -1 ? String(row[idxRom] || '').trim() : '';
const status = idxStatus > -1 ? String(row[idxStatus] || '').trim() : '';
const jk = idxJk > -1 ? String(row[idxJk] || '').trim() : '';
if (!status || VALID_STATUSES.indexOf(status.toLowerCase()) === -1) continue; // skip status ga valid (misal "Angkatan 14")
if (kelas || rombel) {
const krKey = kelas + '||' + rombel + '||' + status;
krMap[krKey] = (krMap[krKey] || 0) + 1;
}
const sjKey = kelas + '||' + rombel + '||' + status + '||' + jk;
sjMap[sjKey] = (sjMap[sjKey] || 0) + 1;
totalPerStatus[status] = (totalPerStatus[status] || 0) + 1;
}
const perKelasRombel = Object.keys(krMap).map(key => {
const parts = key.split('||');
return { kelas: parts[0], rombel: parts[1], status: parts[2], jumlah: krMap[key] };
});
const perStatusJk = Object.keys(sjMap).map(key => {
const parts = key.split('||');
return { kelas: parts[0], rombel: parts[1], status: parts[2], jk: parts[3], jumlah: sjMap[key] };
});
return { perKelasRombel: perKelasRombel, perStatusJk: perStatusJk, totalPerStatus: totalPerStatus };
}