Contoh Java Script Presensi QR ~ khusnul kartika wijaya

// ============================================================
// PRESENSI NGAJUM 2 - BACKEND API LENGKAP
// ============================================================
const SHEET_SISWA = 'datasiswa';
const SHEET_WALAS = 'datawalas';
const SHEET_SCAN = 'datascan';
const SHEET_IZIN = 'dataizin';
const SHEET_AGENDA = 'agenda';
// ==================== DO GET ====================
function doGet(e) {
const action = e.parameter.action;

try {
switch(action) {
// ===== SISWA =====
case 'loginSiswa': return loginSiswa(e);
case 'getPresensiSiswa': return getPresensiSiswa(e);
case 'getRiwayatSiswa': return getRiwayatSiswa(e);

// ===== WALI KELAS =====
case 'loginWalas': return loginWalas(e);
case 'getSiswaByKelas': return getSiswaByKelas(e);
case 'saveIzin': return saveIzin(e);
case 'getRiwayatIzin': return getRiwayatIzin(e);
case 'editIzin': return editIzin(e);
case 'hapusIzin': return hapusIzin(e);

// ===== REKAP & EXPORT =====
case 'getRekap': return getRekap(e);
case 'exportExcel': return handleExportExcel(e);
case 'exportPDF': return handleExportPDF(e);
case 'exportCSV': return handleExportCSV(e);

// ===== AGENDA =====
case 'getAgenda': return getAgenda();
case 'getAllAgenda': return getAllAgenda();
case 'tambahAgenda': return tambahAgenda(e);
case 'hapusAgenda': return hapusAgenda(e);

// ===== ADMIN - SISWA =====
case 'loginAdmin': return loginAdmin(e);
case 'getTotalSiswa': return getTotalSiswa();
case 'getAllSiswa': return getAllSiswa();
case 'tambahSiswa': return tambahSiswa(e);
case 'hapusSiswa': return hapusSiswa(e);
case 'hapusSiswaByKelas': return hapusSiswaByKelas(e);
case 'hapusSiswaByAngkatan': return hapusSiswaByAngkatan(e);

// ===== ADMIN - WALI KELAS =====
case 'getTotalWalas': return getTotalWalas();
case 'getAllWalas': return getAllWalas();
case 'tambahWalas': return tambahWalas(e);
case 'hapusWalas': return hapusWalas(e);

// ===== ADMIN - KELAS =====
case 'getDaftarKelas': return getDaftarKelas();
case 'tambahKelas': return tambahKelas(e);
case 'hapusKelas': return hapusKelas(e);

// ===== ADMIN - PRESENSI =====
case 'getPresensiHariIni': return getPresensiHariIni();

default: return response({ status: 'error', message: 'Aksi tidak ditemukan' });
}
} catch(err) {
return response({ status: 'error', message: err.toString() });
}
}
// ==================== HELPER ====================
function response(data) {
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
function getSheet(name) {
return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
}
function formatDate(date) {
return Utilities.formatDate(date, 'Asia/Jakarta', 'yyyy-MM-dd');
}
function formatTime(date) {
return Utilities.formatDate(date, 'Asia/Jakarta', 'HH:mm:ss');
}
function formatDay(date) {
return Utilities.formatDate(date, 'Asia/Jakarta', 'EEEE');
}
// ============================================================
// ==================== LOGIN ====================
// ============================================================
// ==================== LOGIN SISWA ====================
function loginSiswa(e) {
const nipd = e.parameter.nipd;
const nisn = e.parameter.nisn;

const sheet = getSheet(SHEET_SISWA);
const data = sheet.getDataRange().getValues();

for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(nipd) && String(data[i][1]) === String(nisn)) {
return response({
status: 'success',
data: {
nipd: data[i][0],
nisn: data[i][1],
nama: data[i][2],
kelas: data[i][3],
jk: data[i][4],
agama: data[i][5]
}
});
}
}
return response({ status: 'error', message: 'NIPD atau NISN salah!' });
}
// ==================== LOGIN WALI KELAS ====================
function loginWalas(e) {
const username = e.parameter.username;
const password = e.parameter.password;

const sheet = getSheet(SHEET_WALAS);
const data = sheet.getDataRange().getValues();

for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(username) && String(data[i][1]) === String(password)) {
return response({
status: 'success',
data: {
username: data[i][0],
nama_walas: data[i][2],
kelas: data[i][3]
}
});
}
}
return response({ status: 'error', message: 'Username atau Password salah!' });
}
// ==================== LOGIN ADMIN ====================
function loginAdmin(e) {
const username = e.parameter.username;
const password = e.parameter.password;

const sheet = getSheet('dataadmin');
if (!sheet) {
return response({ status: 'error', message: 'Sheet dataadmin tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(username) && String(data[i][1]) === String(password)) {
return response({
status: 'success',
data: {
username: data[i][0],
nama_admin: data[i][2],
role: data[i][3] || 'admin'
}
});
}
}
return response({ status: 'error', message: 'Username atau Password salah!' });
}
// ============================================================
// ==================== SISWA ====================
// ============================================================
// ==================== SCAN PRESENSI ====================
function scanPresensi(e) {
const nipd = e.parameter.nipd;
const now = new Date();

// CEK HARI LIBUR (Sabtu/Minggu)
const day = now.getDay();
if (day === 0 || day === 6) {
return response({
status: 'error',
message: 'Hari ini adalah hari libur (Sabtu/Minggu)!'
});
}

const today = formatDate(now);
const timeNow = formatTime(now);
const dayNow = formatDay(now);

const sheetSiswa = getSheet(SHEET_SISWA);
const dataSiswa = sheetSiswa.getDataRange().getValues();
let siswa = null;

for (let i = 1; i < dataSiswa.length; i++) {
if (String(dataSiswa[i][0]) === String(nipd)) {
siswa = {
nipd: dataSiswa[i][0],
nama: dataSiswa[i][2],
kelas: dataSiswa[i][3]
};
break;
}
}

if (!siswa) {
return response({ status: 'error', message: 'Siswa tidak ditemukan!' });
}

const sheetScan = getSheet(SHEET_SCAN);
const dataScan = sheetScan.getDataRange().getValues();

for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][0]) === String(nipd) && dataScan[i][3] === today) {
return response({ status: 'error', message: 'Sudah melakukan presensi hari ini!' });
}
}

const hour = now.getHours();
const minute = now.getMinutes();
let statusText = 'Tepat waktu';
let keterangan = 'Hadir';

if (hour > 7 || (hour === 7 && minute > 0)) {
const lateMinutes = (hour - 7) * 60 + minute;
statusText = 'Terlambat ' + lateMinutes + ' menit';
keterangan = 'Terlambat';
}

const sendTs = new Date();
const akuisiTs = new Date();

sheetScan.appendRow([
siswa.nipd,
siswa.nama,
siswa.kelas,
today,
dayNow,
sendTs,
akuisiTs,
keterangan
]);

return response({
status: 'success',
data: {
nama: siswa.nama,
kelas: siswa.kelas,
tanggal: today,
hari: dayNow,
jam: timeNow,
status: statusText,
keterangan: keterangan
}
});
}
// ==================== GET PRESENSI SISWA ====================
function getPresensiSiswa(e) {
const nipd = e.parameter.nipd;
const today = formatDate(new Date());

const sheetScan = getSheet(SHEET_SCAN);
const dataScan = sheetScan.getDataRange().getValues();
let result = null;

for (let i = dataScan.length - 1; i >= 1; i--) {
if (String(dataScan[i][0]) === String(nipd) && dataScan[i][3] === today) {
result = {
nipd: dataScan[i][0],
nama: dataScan[i][1],
kelas: dataScan[i][2],
tanggal: dataScan[i][3],
hari: dataScan[i][4],
scanJam: dataScan[i][6],
status: dataScan[i][7]
};
break;
}
}

const sheetIzin = getSheet(SHEET_IZIN);
const dataIzin = sheetIzin.getDataRange().getValues();

for (let i = 1; i < dataIzin.length; i++) {
if (String(dataIzin[i][0]) === String(nipd) && dataIzin[i][1] === today) {
return response({
status: 'success',
data: {
...result,
isIzin: true,
keteranganIzin: dataIzin[i][4],
walas: dataIzin[i][5]
}
});
}
}

return response({
status: 'success',
data: result || { status: 'Tidak hadir' }
});
}
// ==================== GET RIWAYAT SISWA ====================
function getRiwayatSiswa(e) {
const nipd = e.parameter.nipd;

const sheetScan = getSheet(SHEET_SCAN);
const sheetIzin = getSheet(SHEET_IZIN);

if (!sheetScan) {
return response({ status: 'error', message: 'Sheet scan tidak ditemukan!' });
}

const dataScan = sheetScan.getDataRange().getValues();
const dataIzin = sheetIzin ? sheetIzin.getDataRange().getValues() : [];

const izinMap = {};
for (let i = 1; i < dataIzin.length; i++) {
const key = String(dataIzin[i][0]) + '|' + dataIzin[i][1];
izinMap[key] = {
keterangan: dataIzin[i][4],
walas: dataIzin[i][5]
};
}

const result = [];
for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][0]) === String(nipd)) {
const key = String(dataScan[i][0]) + '|' + dataScan[i][3];
const izin = izinMap[key];

const dateObj = new Date(dataScan[i][3] + 'T00:00:00');
const day = dateObj.getDay();
const isWeekend = (day === 0 || day === 6);

if (izin) {
result.push({
tanggal: dataScan[i][3],
status: izin.keterangan,
scanJam: dataScan[i][6],
isIzin: true,
keteranganIzin: izin.keterangan,
walas: izin.walas,
isWeekend: isWeekend
});
} else {
result.push({
tanggal: dataScan[i][3],
status: dataScan[i][7],
scanJam: dataScan[i][6],
isIzin: false,
isWeekend: isWeekend
});
}
}
}

result.sort(function(a, b) {
return b.tanggal.localeCompare(a.tanggal);
});

return response({ status: 'success', data: result });
}
// ============================================================
// ==================== WALI KELAS ====================
// ============================================================
// ==================== GET SISWA BY KELAS ====================
function getSiswaByKelas(e) {
const kelas = e.parameter.kelas;

const sheet = getSheet(SHEET_SISWA);
const data = sheet.getDataRange().getValues();
const result = [];

for (let i = 1; i < data.length; i++) {
if (String(data[i][3]) === String(kelas)) {
result.push({
nipd: data[i][0],
nama: data[i][2],
kelas: data[i][3]
});
}
}
return response({ status: 'success', data: result });
}
// ==================== SAVE IZIN ====================
function saveIzin(e) {
const nipd = e.parameter.nipd;
const tanggal = e.parameter.tanggal;
const keterangan = e.parameter.keterangan;
const namaWalas = e.parameter.namaWalas;

const sheetSiswa = getSheet(SHEET_SISWA);
const dataSiswa = sheetSiswa.getDataRange().getValues();
let siswa = null;

for (let i = 1; i < dataSiswa.length; i++) {
if (String(dataSiswa[i][0]) === String(nipd)) {
siswa = {
nipd: dataSiswa[i][0],
nama: dataSiswa[i][2],
kelas: dataSiswa[i][3]
};
break;
}
}

if (!siswa) {
return response({ status: 'error', message: 'Siswa tidak ditemukan!' });
}

const sheetIzin = getSheet(SHEET_IZIN);
const dataIzin = sheetIzin.getDataRange().getValues();

for (let i = 1; i < dataIzin.length; i++) {
if (String(dataIzin[i][0]) === String(nipd) && dataIzin[i][1] === tanggal) {
return response({ status: 'error', message: 'Siswa sudah memiliki izin di tanggal ini!' });
}
}

sheetIzin.appendRow([
siswa.nipd,
tanggal,
siswa.nama,
siswa.kelas,
keterangan,
namaWalas
]);

return response({ status: 'success', message: 'Perizinan berhasil disimpan!' });
}
// ==================== GET RIWAYAT IZIN ====================
function getRiwayatIzin(e) {
const kelas = e.parameter.kelas;
const limit = parseInt(e.parameter.limit) || 10;
const offset = parseInt(e.parameter.offset) || 0;

const sheet = getSheet(SHEET_IZIN);
const data = sheet.getDataRange().getValues();
const result = [];

for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][3]) === String(kelas)) {
result.push({
nipd: data[i][0],
tanggal: data[i][1],
nama: data[i][2],
kelas: data[i][3],
keterangan: data[i][4],
walas: data[i][5],
rowIndex: i
});
}
}

const total = result.length;
const paginated = result.slice(offset, offset + limit);

return response({
status: 'success',
data: paginated,
total: total,
limit: limit,
offset: offset
});
}
// ==================== EDIT IZIN ====================
function editIzin(e) {
const rowIndex = parseInt(e.parameter.rowIndex);
const status = e.parameter.status;
const keterangan = e.parameter.keterangan || '';

const sheet = getSheet(SHEET_IZIN);
if (!sheet) {
return response({ status: 'error', message: 'Sheet data izin tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
if (rowIndex < 1 || rowIndex >= data.length) {
return response({ status: 'error', message: 'Data tidak ditemukan!' });
}

sheet.getRange(rowIndex + 1, 5).setValue(status);

if (data[rowIndex].length > 5) {
sheet.getRange(rowIndex + 1, 6).setValue(keterangan);
}

return response({ status: 'success', message: 'Data berhasil diupdate!' });
}
// ==================== HAPUS IZIN ====================
function hapusIzin(e) {
const rowIndex = parseInt(e.parameter.rowIndex);

const sheet = getSheet(SHEET_IZIN);
if (!sheet) {
return response({ status: 'error', message: 'Sheet data izin tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
if (rowIndex < 1 || rowIndex >= data.length) {
return response({ status: 'error', message: 'Data tidak ditemukan!' });
}

sheet.deleteRow(rowIndex + 1);
return response({ status: 'success', message: 'Data berhasil dihapus!' });
}
// ============================================================
// ==================== REKAP & EXPORT ====================
// ============================================================
// ==================== GET REKAP KEHADIRAN ====================
function getRekap(e) {
const kelas = e.parameter.kelas;
const tglMulai = e.parameter.tglMulai;
const tglSelesai = e.parameter.tglSelesai;

const sheetScan = getSheet(SHEET_SCAN);
const sheetIzin = getSheet(SHEET_IZIN);
const sheetSiswa = getSheet(SHEET_SISWA);

const dataScan = sheetScan.getDataRange().getValues();
const dataIzin = sheetIzin.getDataRange().getValues();
const dataSiswa = sheetSiswa.getDataRange().getValues();

const siswaMap = {};
for (let i = 1; i < dataSiswa.length; i++) {
const nipd = String(dataSiswa[i][0]);
siswaMap[nipd] = dataSiswa[i][3];
}

const izinMap = {};
for (let i = 1; i < dataIzin.length; i++) {
const key = String(dataIzin[i][0]) + '|' + dataIzin[i][1];
izinMap[key] = dataIzin[i][4];
}

const result = [];
for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][2]) === String(kelas) &&
dataScan[i][3] >= tglMulai &&
dataScan[i][3] <= tglSelesai) {
const key = String(dataScan[i][0]) + '|' + dataScan[i][3];
let status = dataScan[i][7];

if (izinMap[key]) {
status = izinMap[key];
}

const nipd = String(dataScan[i][0]);
const kelasSiswa = siswaMap[nipd] || dataScan[i][2] || 'Tidak diketahui';

result.push({
nipd: dataScan[i][0],
nama: dataScan[i][1],
kelas: kelasSiswa,
tanggal: dataScan[i][3],
status: status
});
}
}

return response({ status: 'success', data: result });
}
// ==================== EXPORT EXCEL ====================
function handleExportExcel(e) {
if (!e || !e.parameter) {
return response({ status: 'error', message: 'Parameter tidak ditemukan' });
}

const kelas = e.parameter.kelas;
const tglMulai = e.parameter.tglMulai;
const tglSelesai = e.parameter.tglSelesai;

if (!kelas || !tglMulai || !tglSelesai) {
return response({
status: 'error',
message: 'Parameter tidak lengkap. Butuh: kelas, tglMulai, tglSelesai'
});
}

try {
const sheetScan = getSheet(SHEET_SCAN);
const sheetIzin = getSheet(SHEET_IZIN);
const sheetSiswa = getSheet(SHEET_SISWA);

const dataScan = sheetScan.getDataRange().getValues();
const dataIzin = sheetIzin.getDataRange().getValues();
const dataSiswa = sheetSiswa.getDataRange().getValues();

const siswaMap = {};
for (let i = 1; i < dataSiswa.length; i++) {
const nipd = String(dataSiswa[i][0]);
siswaMap[nipd] = dataSiswa[i][3];
}

const izinMap = {};
for (let i = 1; i < dataIzin.length; i++) {
const key = String(dataIzin[i][0]) + '|' + dataIzin[i][1];
izinMap[key] = dataIzin[i][4];
}

const result = [];
for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][2]) === String(kelas) &&
dataScan[i][3] >= tglMulai &&
dataScan[i][3] <= tglSelesai) {
const key = String(dataScan[i][0]) + '|' + dataScan[i][3];
let status = dataScan[i][7];
if (izinMap[key]) {
status = izinMap[key];
}

const nipd = String(dataScan[i][0]);
const kelasSiswa = siswaMap[nipd] || dataScan[i][2] || 'Tidak diketahui';

result.push([dataScan[i][0], dataScan[i][1], kelasSiswa, dataScan[i][3], status]);
}
}

const ss = SpreadsheetApp.create('Rekap_Kehadiran_' + kelas + '_' + tglMulai + '_' + tglSelesai);
const sheet = ss.getActiveSheet();

sheet.getRange(1, 1, 1, 5).setValues([['NIPD', 'Nama', 'Kelas', 'Tanggal', 'Status']]);
sheet.getRange(1, 1, 1, 5).setFontWeight('bold');
sheet.getRange(1, 1, 1, 5).setBackground('#3498db');
sheet.getRange(1, 1, 1, 5).setFontColor('white');

if (result.length > 0) {
sheet.getRange(2, 1, result.length, 5).setValues(result);
}

sheet.autoResizeColumns(1, 5);

return response({
status: 'success',
message: 'Excel berhasil dibuat',
url: ss.getUrl()
});

} catch(err) {
return response({
status: 'error',
message: err.toString()
});
}
}
// ==================== EXPORT PDF ====================
function handleExportPDF(e) {
if (!e || !e.parameter) {
return response({ status: 'error', message: 'Parameter tidak ditemukan' });
}

const kelas = e.parameter.kelas;
const tglMulai = e.parameter.tglMulai;
const tglSelesai = e.parameter.tglSelesai;

if (!kelas || !tglMulai || !tglSelesai) {
return response({
status: 'error',
message: 'Parameter tidak lengkap. Butuh: kelas, tglMulai, tglSelesai'
});
}

try {
const sheetScan = getSheet(SHEET_SCAN);
const sheetIzin = getSheet(SHEET_IZIN);
const sheetSiswa = getSheet(SHEET_SISWA);

const dataScan = sheetScan.getDataRange().getValues();
const dataIzin = sheetIzin.getDataRange().getValues();
const dataSiswa = sheetSiswa.getDataRange().getValues();

const siswaMap = {};
for (let i = 1; i < dataSiswa.length; i++) {
const nipd = String(dataSiswa[i][0]);
siswaMap[nipd] = dataSiswa[i][3];
}

const izinMap = {};
for (let i = 1; i < dataIzin.length; i++) {
const key = String(dataIzin[i][0]) + '|' + dataIzin[i][1];
izinMap[key] = dataIzin[i][4];
}

const result = [];
for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][2]) === String(kelas) &&
dataScan[i][3] >= tglMulai &&
dataScan[i][3] <= tglSelesai) {
const key = String(dataScan[i][0]) + '|' + dataScan[i][3];
let status = dataScan[i][7];
if (izinMap[key]) {
status = izinMap[key];
}

const nipd = String(dataScan[i][0]);
const kelasSiswa = siswaMap[nipd] || dataScan[i][2] || 'Tidak diketahui';

result.push([dataScan[i][0], dataScan[i][1], kelasSiswa, dataScan[i][3], status]);
}
}

let html = `
<html>
<head>
<meta charset="UTF-8">
<style>
body { font-family: Arial, sans-serif; padding: 20px; }
h2 { text-align: center; color: #2c3e50; }
.info { margin-bottom: 20px; color: #555; }
table { width: 100%; border-collapse: collapse; margin-top: 10px; }
th { background: #3498db; color: white; padding: 10px; text-align: left; }
td { padding: 8px; border-bottom: 1px solid #ddd; }
tr:nth-child(even) { background: #f9f9f9; }
.footer { margin-top: 30px; text-align: center; font-size: 12px; color: #888; }
.badge { padding: 3px 10px; border-radius: 12px; font-size: 12px; font-weight: bold; }
.badge-hadir { background: #d4edda; color: #155724; }
.badge-terlambat { background: #fff3cd; color: #856404; }
.badge-izin { background: #cce5ff; color: #004085; }
.badge-sakit { background: #e8daef; color: #6c3483; }
.badge-alpa { background: #f8d7da; color: #721c24; }
</style>
</head>
<body>
<h2>REKAP KEHADIRAN</h2>
<div class="info">
<p><strong>Kelas:</strong> ${kelas}</p>
<p><strong>Periode:</strong> ${tglMulai} s/d ${tglSelesai}</p>
<p><strong>Total Data:</strong> ${result.length} siswa</p>
<p><strong>Tanggal Cetak:</strong> ${new Date().toLocaleDateString('id-ID')}</p>
</div>
<table>
<thead>
<tr>
<th>NIPD</th>
<th>Nama</th>
<th>Kelas</th>
<th>Tanggal</th>
<th>Status</th>
</tr>
</thead>
<tbody>
`;

if (result.length === 0) {
html += `<tr><td colspan="5" style="text-align:center;color:#888;">Tidak ada data</td></tr>`;
} else {
result.forEach(function(row) {
let badgeClass = 'badge-alpa';
let statusText = row[4];
if (row[4] === 'Tepat waktu' || row[4] === 'Hadir') {
badgeClass = 'badge-hadir';
statusText = 'Hadir';
} else if (row[4].indexOf('Terlambat') !== -1) {
badgeClass = 'badge-terlambat';
statusText = row[4];
} else if (row[4] === 'Izin') {
badgeClass = 'badge-izin';
statusText = 'Izin';
} else if (row[4] === 'Sakit') {
badgeClass = 'badge-sakit';
statusText = 'Sakit';
}
html += `<tr>
<td>${row[0]}</td>
<td>${row[1]}</td>
<td>${row[2]}</td>
<td>${row[3]}</td>
<td><span class="badge ${badgeClass}">${statusText}</span></td>
</tr>`;
});
}

html += `
</tbody>
</table>
<div class="footer">
<p>Dicetak dari Sistem Presensi Ngajum 2 - SMP Negeri 2 Ngajum</p>
</div>
</body>
</html>
`;

const blob = Utilities.newBlob(html, 'text/html', 'Rekap_Kehadiran_' + kelas + '.html');
const pdfBlob = blob.getAs('application/pdf');
pdfBlob.setName('Rekap_Kehadiran_' + kelas + '_' + tglMulai + '_' + tglSelesai + '.pdf');

const folder = DriveApp.getRootFolder();
const file = folder.createFile(pdfBlob);

return response({
status: 'success',
message: 'PDF berhasil dibuat',
url: file.getUrl()
});

} catch(err) {
return response({
status: 'error',
message: err.toString()
});
}
}
// ==================== EXPORT CSV ====================
function handleExportCSV(e) {
if (!e || !e.parameter) {
return ContentService
.createTextOutput('status,message\nerror,Parameter tidak ditemukan')
.setMimeType(ContentService.MimeType.CSV);
}

const kelas = e.parameter.kelas;
const tglMulai = e.parameter.tglMulai;
const tglSelesai = e.parameter.tglSelesai;

if (!kelas || !tglMulai || !tglSelesai) {
return ContentService
.createTextOutput('status,message\nerror,Parameter tidak lengkap')
.setMimeType(ContentService.MimeType.CSV);
}

try {
const sheetScan = getSheet(SHEET_SCAN);
const sheetIzin = getSheet(SHEET_IZIN);
const sheetSiswa = getSheet(SHEET_SISWA);

const dataScan = sheetScan.getDataRange().getValues();
const dataIzin = sheetIzin.getDataRange().getValues();
const dataSiswa = sheetSiswa.getDataRange().getValues();

const siswaMap = {};
for (let i = 1; i < dataSiswa.length; i++) {
const nipd = String(dataSiswa[i][0]);
siswaMap[nipd] = dataSiswa[i][3];
}

const izinMap = {};
for (let i = 1; i < dataIzin.length; i++) {
const key = String(dataIzin[i][0]) + '|' + dataIzin[i][1];
izinMap[key] = dataIzin[i][4];
}

let csv = 'NIPD,Nama,Kelas,Tanggal,Status\n';

for (let i = 1; i < dataScan.length; i++) {
if (String(dataScan[i][2]) === String(kelas) &&
dataScan[i][3] >= tglMulai &&
dataScan[i][3] <= tglSelesai) {
const key = String(dataScan[i][0]) + '|' + dataScan[i][3];
let status = dataScan[i][7];
if (izinMap[key]) {
status = izinMap[key];
}

const nipd = String(dataScan[i][0]);
const kelasSiswa = siswaMap[nipd] || dataScan[i][2] || 'Tidak diketahui';

const nama = String(dataScan[i][1]).replace(/,/g, '');
const statusClean = String(status).replace(/,/g, '');
const kelasClean = String(kelasSiswa).replace(/,/g, '');

csv += `${nipd},${nama},${kelasClean},${dataScan[i][3]},${statusClean}\n`;
}
}

return ContentService
.createTextOutput(csv)
.setMimeType(ContentService.MimeType.CSV)
.downloadAsFile('Rekap_Kehadiran_' + kelas + '_' + tglMulai + '_' + tglSelesai + '.csv');

} catch(err) {
return ContentService
.createTextOutput('status,message\nerror,' + err.toString())
.setMimeType(ContentService.MimeType.CSV);
}
}
// ============================================================
// ==================== AGENDA ====================
// ============================================================
// ==================== GET AGENDA ====================
function getAgenda() {
const sheet = getSheet(SHEET_AGENDA);
const data = sheet.getDataRange().getValues();
const today = formatDate(new Date());

for (let i = 1; i < data.length; i++) {
if (data[i][1] === today) {
return response({
status: 'success',
data: {
hari: data[i][0],
tanggal: data[i][1],
agenda: data[i][2]
}
});
}
}
return response({ status: 'success', data: null });
}
// ==================== GET ALL AGENDA ====================
function getAllAgenda() {
const sheet = getSheet(SHEET_AGENDA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet agenda tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < data.length; i++) {
result.push({
hari: data[i][0],
tanggal: data[i][1],
agenda: data[i][2]
});
}
return response({ status: 'success', data: result });
}
// ==================== TAMBAH AGENDA ====================
function tambahAgenda(e) {
const hari = e.parameter.hari;
const tanggal = e.parameter.tanggal;
const agenda = e.parameter.agenda;

const sheet = getSheet(SHEET_AGENDA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet agenda tidak ditemukan!' });
}

sheet.appendRow([hari, tanggal, agenda]);
return response({ status: 'success', message: 'Agenda berhasil ditambahkan!' });
}
// ==================== HAPUS AGENDA ====================
function hapusAgenda(e) {
const tanggal = e.parameter.tanggal;
const sheet = getSheet(SHEET_AGENDA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet agenda tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][1]) === String(tanggal)) {
sheet.deleteRow(i + 1);
return response({ status: 'success', message: 'Agenda berhasil dihapus!' });
}
}
return response({ status: 'error', message: 'Agenda tidak ditemukan!' });
}
// ============================================================
// ==================== ADMIN - SISWA ====================
// ============================================================
// ==================== GET TOTAL SISWA ====================
function getTotalSiswa() {
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
return response({ status: 'success', data: data.length - 1 });
}
// ==================== GET ALL SISWA ====================
function getAllSiswa() {
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < data.length; i++) {
result.push({
nipd: data[i][0],
nisn: data[i][1],
nama: data[i][2],
kelas: data[i][3],
jk: data[i][4],
agama: data[i][5]
});
}
return response({ status: 'success', data: result });
}
// ==================== TAMBAH SISWA ====================
function tambahSiswa(e) {
const nipd = e.parameter.nipd;
const nisn = e.parameter.nisn;
const nama = e.parameter.nama;
const kelas = e.parameter.kelas;
const jk = e.parameter.jk;
const agama = e.parameter.agama;

const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(nipd)) {
return response({ status: 'error', message: 'NIPD sudah terdaftar!' });
}
}

sheet.appendRow([nipd, nisn, nama, kelas, jk, agama]);
return response({ status: 'success', message: 'Siswa berhasil ditambahkan!' });
}
// ==================== HAPUS SISWA ====================
function hapusSiswa(e) {
const nipd = e.parameter.nipd;
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][0]) === String(nipd)) {
sheet.deleteRow(i + 1);
return response({ status: 'success', message: 'Siswa berhasil dihapus!' });
}
}
return response({ status: 'error', message: 'Siswa tidak ditemukan!' });
}
// ==================== HAPUS SISWA BY KELAS ====================
function hapusSiswaByKelas(e) {
const kelas = e.parameter.kelas;
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
let count = 0;
for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][3]) === String(kelas)) {
sheet.deleteRow(i + 1);
count++;
}
}
return response({ status: 'success', message: 'Berhasil hapus ' + count + ' siswa dari kelas ' + kelas });
}
// ==================== HAPUS SISWA BY ANGKATAN ====================
function hapusSiswaByAngkatan(e) {
const angkatan = e.parameter.angkatan;
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
let count = 0;
for (let i = data.length - 1; i >= 1; i--) {
const kelas = String(data[i][3]);
if (kelas.startsWith(String(angkatan))) {
sheet.deleteRow(i + 1);
count++;
}
}
return response({ status: 'success', message: 'Berhasil hapus ' + count + ' siswa dari angkatan ' + angkatan });
}
// ============================================================
// ==================== ADMIN - WALI KELAS ====================
// ============================================================
// ==================== GET TOTAL WALAS ====================
function getTotalWalas() {
const sheet = getSheet(SHEET_WALAS);
if (!sheet) {
return response({ status: 'error', message: 'Sheet wali kelas tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
return response({ status: 'success', data: data.length - 1 });
}
// ==================== GET ALL WALAS ====================
function getAllWalas() {
const sheet = getSheet(SHEET_WALAS);
if (!sheet) {
return response({ status: 'error', message: 'Sheet wali kelas tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < data.length; i++) {
result.push({
username: data[i][0],
password: data[i][1],
nama_walas: data[i][2],
kelas: data[i][3]
});
}
return response({ status: 'success', data: result });
}
// ==================== TAMBAH WALAS ====================
function tambahWalas(e) {
const username = e.parameter.username;
const password = e.parameter.password;
const nama = e.parameter.nama;
const kelas = e.parameter.kelas;

const sheet = getSheet(SHEET_WALAS);
if (!sheet) {
return response({ status: 'error', message: 'Sheet wali kelas tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (String(data[i][0]) === String(username)) {
return response({ status: 'error', message: 'Username sudah terdaftar!' });
}
}

sheet.appendRow([username, password, nama, kelas]);
return response({ status: 'success', message: 'Wali Kelas berhasil ditambahkan!' });
}
// ==================== HAPUS WALAS ====================
function hapusWalas(e) {
const username = e.parameter.username;
const sheet = getSheet(SHEET_WALAS);
if (!sheet) {
return response({ status: 'error', message: 'Sheet wali kelas tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][0]) === String(username)) {
sheet.deleteRow(i + 1);
return response({ status: 'success', message: 'Wali Kelas berhasil dihapus!' });
}
}
return response({ status: 'error', message: 'Wali Kelas tidak ditemukan!' });
}
// ============================================================
// ==================== ADMIN - KELAS ====================
// ============================================================
// ==================== GET DAFTAR KELAS ====================
function getDaftarKelas() {
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
const kelasMap = {};

const walasSheet = getSheet(SHEET_WALAS);
const walasData = walasSheet ? walasSheet.getDataRange().getValues() : [];
const walasMap = {};
for (let i = 1; i < walasData.length; i++) {
walasMap[String(walasData[i][3])] = walasData[i][2];
}

for (let i = 1; i < data.length; i++) {
const k = String(data[i][3]);
if (k) {
kelasMap[k] = (kelasMap[k] || 0) + 1;
}
}

const result = [];
for (const k in kelasMap) {
result.push({
nama: k,
jumlah: kelasMap[k],
walas: walasMap[k] || '-'
});
}
result.sort((a, b) => a.nama.localeCompare(b.nama));
return response({ status: 'success', data: result });
}
// ==================== TAMBAH KELAS ====================
function tambahKelas(e) {
const nama = e.parameter.nama;
if (!nama) {
return response({ status: 'error', message: 'Nama kelas wajib diisi!' });
}
return response({ status: 'success', message: 'Kelas ' + nama + ' berhasil ditambahkan! (Silakan tambahkan siswa ke kelas ini)' });
}
// ==================== HAPUS KELAS ====================
function hapusKelas(e) {
const kelas = e.parameter.kelas;
const sheet = getSheet(SHEET_SISWA);
if (!sheet) {
return response({ status: 'error', message: 'Sheet siswa tidak ditemukan!' });
}

const data = sheet.getDataRange().getValues();
let count = 0;
for (let i = data.length - 1; i >= 1; i--) {
if (String(data[i][3]) === String(kelas)) {
sheet.deleteRow(i + 1);
count++;
}
}
return response({ status: 'success', message: 'Berhasil hapus kelas ' + kelas + ' beserta ' + count + ' siswa' });
}
// ==================== GET PRESENSI HARI INI ====================
function getPresensiHariIni() {
const sheet = getSheet(SHEET_SCAN);
if (!sheet) {
return response({ status: 'error', message: 'Sheet scan tidak ditemukan!' });
}
const data = sheet.getDataRange().getValues();
const today = formatDate(new Date());
let count = 0;
for (let i = 1; i < data.length; i++) {
if (data[i][3] === today) {
count++;
}
}
return response({ status: 'success', data: count });
}
// ============================================================
// ==================== FUNGSI TESTING ====================
// ============================================================
function testExportPDF() {
var testParam = { parameter: { kelas: '8A', tglMulai: '2026-08-21', tglSelesai: '2026-08-22' } };
var result = handleExportPDF(testParam);
console.log('Hasil PDF:', result);
}
function testExportExcel() {
var testParam = { parameter: { kelas: '8A', tglMulai: '2026-08-21', tglSelesai: '2026-08-22' } };
var result = handleExportExcel(testParam);
console.log('Hasil Excel:', result);
}
function testExportCSV() {
var testParam = { parameter: { kelas: '8A', tglMulai: '2026-08-21', tglSelesai: '2026-08-22' } };
var result = handleExportCSV(testParam);
console.log('Hasil CSV:', result);
}
function testEditIzin() {
var testParam = { parameter: { rowIndex: '1', status: 'Izin', keterangan: 'Test edit' } };
var result = editIzin(testParam);
console.log('Hasil Edit:', result);
}
function testHapusIzin() {
var testParam = { parameter: { rowIndex: '1' } };
var result = hapusIzin(testParam);
console.log('Hasil Hapus:', result);
}
function testRiwayatSiswa() {
var testParam = { parameter: { nipd: '9359' } };
var result = getRiwayatSiswa(testParam);
console.log('Hasil Riwayat:', result);
}
function testLoginAdmin() {
var testParam = { parameter: { username: 'admin', password: 'admin123' } };
var result = loginAdmin(testParam);
console.log('Hasil Login Admin:', result);
}
function testGetDaftarKelas() {
var result = getDaftarKelas();
console.log('Hasil Daftar Kelas:', result);
}