GAS REFACTOR
const ID_SPREADSHEET = "1YLjsqF9wVXT2eGV4hM6K9EYITD6bAX31kpCTlrP8Ifg";
const ID_FOLDER_PRESENSI = "1xOKWuNcPY5OnB-O3gXuxyF2vrrjUBcrr";
/* ======================================================
DO GET: Login, Sync Data, & Dashboard Engine (FULL)
====================================================== */
function doGet(e) {
if (!e || !e.parameter) return outputJSON({ status: "error", message: "Akses Ditolak" });
const ss = SpreadsheetApp.openById(ID_SPREADSHEET);
const action = String(e.parameter.action || "").trim();
// --- HELPER 1: Format Jam agar selalu HH:mm ---
function formatJam(val) {
if (!val || val === "" || val === "-") return "--:--";
if (val instanceof Date) {
return Utilities.formatDate(val, "GMT+7", "HH:mm");
}
if (typeof val === "number") {
var totalSeconds = Math.round(val * 24 * 3600);
var hours = Math.floor(totalSeconds / 3600);
var minutes = Math.floor((totalSeconds % 3600) / 60);
return (hours < 10 ? "0" + hours : hours) + ":" + (minutes < 10 ? "0" + minutes : minutes);
}
var s = String(val).trim();
if (s.includes(":")) {
var parts = s.split(":");
return parts[0].padStart(2, '0') + ":" + (parts[1] || "00").substring(0, 2).padStart(2, '0');
}
return s;
}
// --- HELPER 2: Format Tanggal agar YYYY-MM-DD ---
function formatTanggal(val) {
if (!val) return "";
let d = new Date(val);
if (isNaN(d.getTime())) return String(val);
return Utilities.formatDate(d, "GMT+7", "yyyy-MM-dd");
}
try {
/* 1. AMBIL DATA TABEL (GURU, SISWA, PKL, TAHAP, DUDI) */
const sheetMap = {
"getAllGuru": "Guru",
"getAllSiswa": "SISWA",
"getAllPkl": "PKL",
"getAllTahap": "Tahap",
"getAllDudi": "Dudi"
};
if (sheetMap[action]) {
const sheet = ss.getSheetByName(sheetMap[action]);
if (!sheet) return outputJSON({ status: "error", message: "Sheet tidak ditemukan" });
const data = sheet.getDataRange().getValues();
return outputJSON({ status: "success", data: data.slice(1) });
}
/* 2. GET UNIQUE DATA (UNTUK DROPDOWN) */
if (action === "getUniqueData") {
const sheet = ss.getSheetByName(e.parameter.sheet);
if (!sheet) return outputJSON([]);
const data = sheet.getDataRange().getValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const idx = headers.indexOf(e.parameter.kolom.toUpperCase());
if (idx === -1) return outputJSON([]);
const listUnik = data.slice(1).map(r => r[idx] ? r[idx].toString().trim() : "").filter((v, i, a) => v !== "" && a.indexOf(v) === i).sort();
return outputJSON(listUnik);
}
/* 3. FILTER DUDI BY TAHAP (CEK KUOTA) */
if (action === "getDudiByTahap") {
const tahap = String(e.parameter.tahap || "").trim();
const sheetDudi = ss.getSheetByName("Dudi");
const sheetPKL = ss.getSheetByName("PKL");
if (!sheetDudi || !sheetPKL) return outputJSON({ status: "error", message: "Sheet Dudi/PKL missing" });
const dataDudi = sheetDudi.getDataRange().getValues();
const dataPKL = sheetPKL.getDataRange().getValues();
const hDudi = dataDudi[0].map(h => h.toString().toUpperCase().trim());
const hPKL = dataPKL[0].map(h => h.toString().toUpperCase().trim());
let counts = {};
const idxPklIdDudi = hPKL.indexOf("ID DUDI");
dataPKL.slice(1).forEach(row => {
let id = String(row[idxPklIdDudi]).trim();
if (id) counts[id] = (counts[id] || 0) + 1;
});
const filtered = dataDudi.slice(1).filter(row => {
let id = String(row[hDudi.indexOf("ID")]).trim();
let kuota = parseInt(row[hDudi.indexOf("KUOTA")]) || 0;
return String(row[hDudi.indexOf("TAHAP")]).trim() === tahap && kuota > (counts[id] || 0);
}).map(row => ({
nama: row[hDudi.indexOf("DUDI")],
id: row[hDudi.indexOf("ID")],
sisa: (parseInt(row[hDudi.indexOf("KUOTA")]) || 0) - (counts[String(row[hDudi.indexOf("ID")]).trim()] || 0)
}));
return outputJSON({ status: "success", data: filtered });
}
/* 4. LOGIN & SESSION SYNC (SISWA, GURU, ADMIN) */
if (action === "login" || action === "getLatestSession") {
const inputUser = String(e.parameter.username || "").trim().toLowerCase();
const inputPass = String(e.parameter.password || "").trim();
if (!inputUser) return outputJSON({ status: "error", message: "Username kosong" });
let infoSekolah = {};
const sheetSKLH = ss.getSheetByName("SKLH");
if (sheetSKLH) {
const sklhData = sheetSKLH.getDataRange().getDisplayValues();
if (sklhData.length > 1) {
sklhData[0].forEach((h, i) => { infoSekolah[h.toString().toLowerCase().replace(/\s+/g, "_")] = sklhData[1][i]; });
}
}
const roleSheets = ["Admin", "Guru", "Siswa"];
for (let s = 0; s < roleSheets.length; s++) {
const sheet = ss.getSheetByName(roleSheets[s]);
if (!sheet) continue;
const data = sheet.getDataRange().getDisplayValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const idxU = headers.indexOf("USERNAME");
const idxP = headers.indexOf("PASSWORD");
for (let i = 1; i < data.length; i++) {
const dbUser = String(data[i][idxU]).trim().toLowerCase();
const dbPass = String(data[i][idxP]).trim();
if (dbUser === inputUser && (action === "getLatestSession" || dbPass === inputPass)) {
let profile = {};
headers.forEach((h, idx) => { profile[h.toLowerCase().replace(/\s+/g, "_")] = data[i][idx]; });
// LOGIKA SYNC PKL UNTUK SISWA
if (roleSheets[s] === "Siswa") {
const sheetPKL = ss.getSheetByName("PKL");
const sheetDudi = ss.getSheetByName("Dudi");
if (sheetPKL && sheetDudi) {
const dataPKL = sheetPKL.getDataRange().getValues();
const hPKL = dataPKL[0].map(h => h.toString().toUpperCase().trim());
const userPKLRows = dataPKL.slice(1).filter(r => String(r[hPKL.indexOf("USERNAME")]).trim().toLowerCase() === inputUser);
if (userPKLRows.length > 0) {
const dataDudi = sheetDudi.getDataRange().getDisplayValues();
const hDudi = dataDudi[0].map(h => h.toString().toUpperCase().trim());
let pklList = [];
userPKLRows.forEach(rowPKL => {
const idDudi = String(rowPKL[hPKL.indexOf("ID DUDI")]).trim();
const rowD = dataDudi.find(r => String(r[hDudi.indexOf("ID")]).trim() === idDudi);
if (rowD) {
pklList.push({
id_dudi: idDudi,
pilih_dudi: rowD[hDudi.indexOf("DUDI")],
koordinat: rowD[hDudi.indexOf("KOORDINAT")],
jarak: rowD[hDudi.indexOf("JARAK")],
shift: rowD[hDudi.indexOf("SHIFT")],
alamat: rowD[hDudi.indexOf("ALAMAT")],
pembimbing: rowD[hDudi.indexOf("PEMBIMBING")] || "-",
kuota: rowD[hDudi.indexOf("KUOTA")],
m_shift_1: formatJam(rowD[hDudi.indexOf("M SHIFT 1")]),
p_shift_1: formatJam(rowD[hDudi.indexOf("P SHIFT 1")]),
m_shift_2: formatJam(rowD[hDudi.indexOf("M SHIFT 2")]),
p_shift_2: formatJam(rowD[hDudi.indexOf("P SHIFT 2")]),
m_shift_3: formatJam(rowD[hDudi.indexOf("M SHIFT 3")]),
p_shift_3: formatJam(rowD[hDudi.indexOf("P SHIFT 3")])
});
}
});
profile.pkl_list = pklList;
}
}
}
return outputJSON({ status: "success", jabatan: roleSheets[s], profile: profile, sekolah: infoSekolah });
}
}
}
return outputJSON({ status: "error", message: "Login Gagal" });
}
/* 5. GET LATEST PRESENSI & HISTORY */
if (action === "getLatestPresensi") {
const username = String(e.parameter.username || "").trim().toLowerCase();
const sheetPresensi = ss.getSheetByName("presensi");
if (!sheetPresensi) return outputJSON({ status: "error", message: "Sheet presensi missing" });
const data = sheetPresensi.getDataRange().getValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const rowsUser = data.slice(1).filter(r => String(r[headers.indexOf("USERNAME")]).trim().toLowerCase() === username);
if (rowsUser.length === 0) return outputJSON({ status: "empty" });
const last = rowsUser[rowsUser.length - 1];
const history = rowsUser.map(row => ({
tanggal: formatTanggal(row[headers.indexOf("TANGGAL")]),
jam_masuk: formatJam(row[headers.indexOf("JAM MASUK")]),
jam_pulang: formatJam(row[headers.indexOf("JAM PULANG")]),
status: row[headers.indexOf("STATUS")]
}));
const resPresensi = {
shift: last[headers.indexOf("SHIFT")],
jam_masuk: formatJam(last[headers.indexOf("JAM MASUK")]),
jam_pulang: formatJam(last[headers.indexOf("JAM PULANG")]),
status: last[headers.indexOf("STATUS")],
tanggal: formatTanggal(last[headers.indexOf("TANGGAL")])
};
return outputJSON({ status: "success", presensi: resPresensi, history_presensi: history });
}
return outputJSON({ status: "error", message: "Action Unknown" });
} catch (err) { return outputJSON({ status: "error", message: err.toString() }); }
}
/* ======================================================
DO POST: Presensi, Update Profil, & Pendaftaran PKL
====================================================== */
function doPost(e) {
try {
if (!e || !e.postData || !e.postData.contents) {
return outputJSON({ status: "error", message: "Data kosong" });
}
const contents = JSON.parse(e.postData.contents);
const ss = SpreadsheetApp.openById(ID_SPREADSHEET);
const now = new Date();
const tanggal = Utilities.formatDate(now, "GMT+7", "yyyy-MM-dd");
const jam = Utilities.formatDate(now, "GMT+7", "HH:mm:ss");
const ts = Utilities.formatDate(now, "GMT+7", "dd/MM/yyyy HH:mm:ss");
/* ======================================================
1. DELETE SELECTED (UNTUK SEMUA SHEET)
====================================================== */
if (contents.action === "deleteSelected") {
const sheet = ss.getSheetByName(contents.table);
if (!sheet) return outputJSON({ status: "error", message: "Sheet tidak ditemukan" });
const data = sheet.getDataRange().getValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
// Cari kolom kunci: ID atau TAHAP
let idxKey = headers.indexOf("ID");
if (idxKey === -1) idxKey = headers.indexOf("TAHAP");
if (idxKey === -1) {
return outputJSON({ status: "error", message: "Kolom ID / TAHAP tidak ditemukan" });
}
const idsToDelete = (contents.ids || []).map(v => String(v).trim());
let count = 0;
for (let i = data.length - 1; i >= 1; i--) {
const val = String(data[i][idxKey]).trim();
if (idsToDelete.indexOf(val) !== -1) {
sheet.deleteRow(i + 1);
count++;
}
}
return outputJSON({ status: "success", message: count + " data terhapus" });
}
/* ======================================================
LOGIKA CRUD PENEMPATAN PKL (Kunci: Username & ID Dudi)
====================================================== */
if (contents.action === "savePkl" || contents.action === "editPkl" || contents.action === "deletePkl") {
const sheetPkl = ss.getSheetByName("PKL");
if (!sheetPkl) return outputJSON({ status: "error", message: "Sheet PKL tidak ditemukan" });
const data = sheetPkl.getDataRange().getDisplayValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const idxId = headers.indexOf("ID"); // Mencari berdasarkan ID Gabungan
// --- DELETE PKL ---
if (contents.action === "deletePkl") {
if (!contents.id) return outputJSON({ status: "error", message: "ID tidak disertakan" });
for (let i = 1; i < data.length; i++) {
// Bandingkan ID dari sheet dengan ID yang dikirim dari aplikasi
if (String(data[i][idxId]).trim() === String(contents.id).trim()) {
sheetPkl.deleteRow(i + 1); // i + 1 karena baris di Sheets mulai dari 1
return outputJSON({ status: "success", message: "Data PKL berhasil dihapus" });
}
}
return outputJSON({ status: "not_found", message: "Data tidak ditemukan" });
}
// --- EDIT PKL ---
if (contents.action === "editPkl") {
// ... kode edit Anda yang sudah ada ...
for (let i = 1; i < data.length; i++) {
if (String(data[i][idxId]).trim() === String(contents.id).trim()) {
headers.forEach((h, colIdx) => {
let val;
if (h === "ID") val = contents.id;
if (h === "USERNAME") val = contents.username;
if (h === "ID DUDI") val = contents.id_dudi;
if (h === "PILIH DUDI") val = contents.dudi;
if (h === "NAMA") val = contents.nama;
if (h === "TAHAP") val = contents.tahap;
if (h === "PEMBIMBING") val = contents.pembimbing;
if (h === "KELAS") val = contents.kelas;
if (h === "TIMESTAMP") val = new Date();
if (val !== undefined) sheetPkl.getRange(i + 1, colIdx + 1).setValue(val);
});
return outputJSON({ status: "success", message: "Data PKL diperbarui" });
}
}
}
// --- SAVE NEW PKL ---
if (contents.action === "savePkl") {
// ... kode save Anda yang sudah ada ...
const newRow = headers.map(h => {
if (h === "ID") return contents.id;
if (h === "USERNAME") return contents.username;
if (h === "ID DUDI") return contents.id_dudi;
if (h === "PILIH DUDI") return contents.dudi;
if (h === "NAMA") return contents.nama;
if (h === "TAHAP") return contents.tahap;
if (h === "PEMBIMBING") return contents.pembimbing;
if (h === "KELAS") return contents.kelas;
if (h === "TIMESTAMP") return new Date();
return "";
});
sheetPkl.appendRow(newRow);
return outputJSON({ status: "success", message: "Data PKL tersimpan" });
}
}
const sheetPkl = ss.getSheetByName("PKL");
// LOGIKA IMPORT
if (contents.action === "importPkl") {
if (!sheetPkl) return outputJSON({ status: "error", message: "Sheet PKL tidak ditemukan" });
const rows = contents.data;
const existingData = sheetPkl.getDataRange().getValues();
const headersPkl = existingData[0].map(h => h.toString().toUpperCase().trim());
// Objek Map Indeks Kolom berdasarkan Header Sheets
const col = {
username: headersPkl.indexOf("USERNAME"),
pilihDudi: headersPkl.indexOf("PILIH DUDI"),
tahap: headersPkl.indexOf("TAHAP"),
idDudi: headersPkl.indexOf("ID DUDI"), // Gabungan PILIH DUDI-TAHAP
idGabungan: headersPkl.indexOf("ID"), // Gabungan USERNAME-PILIH DUDI-TAHAP
ts: headersPkl.indexOf("TIMESTAMP")
};
const existingUsernames = existingData.map(r => String(r[col.username]).trim().toUpperCase());
let successCount = 0;
let skipCount = 0;
rows.forEach(function(row) {
const keys = Object.keys(row);
let uVal = String(row[keys.find(k => k.toUpperCase().trim() === "USERNAME")] || "").trim();
let dVal = String(row[keys.find(k => k.toUpperCase().trim() === "PILIH DUDI" || k.toUpperCase().trim() === "DUDI")] || "").trim();
let tVal = String(row[keys.find(k => k.toUpperCase().trim() === "TAHAP")] || "").trim();
if (!uVal || existingUsernames.includes(uVal.toUpperCase())) {
skipCount++;
return;
}
// --- GENERATE ID & TIMESTAMP ---
const cleanDudi = dVal.toUpperCase().replace(/\s+/g, "");
const cleanUser = uVal.toUpperCase().replace(/\s+/g, "");
const cleanTahap = tVal.toUpperCase().replace(/\s+/g, "");
const generatedIdDudi = cleanDudi + "-" + cleanTahap;
const generatedIdGabungan = cleanUser + "-" + cleanDudi + "-" + cleanTahap;
const now = new Date(); // Mencatat Tanggal dan Jam
const rowToAdd = headersPkl.map((h, index) => {
if (index === col.username) return uVal;
if (index === col.pilihDudi) return dVal;
if (index === col.tahap) return tVal;
if (index === col.idDudi) return generatedIdDudi;
if (index === col.idGabungan) return generatedIdGabungan;
if (index === col.ts) return now;
let foundKey = keys.find(k => k.toUpperCase().trim() === h);
return foundKey ? row[foundKey] : "";
});
sheetPkl.appendRow(rowToAdd);
existingUsernames.push(uVal.toUpperCase());
successCount++;
});
return outputJSON({ status: "success", message: successCount + " data berhasil diimport!" });
}
// ======================================================
// 1. LOGIKA CRUD & IMPORT DUDI
// ======================================================
const sheetDudi = ss.getSheetByName("Dudi");
// --- ACTION: SAVE, EDIT, DELETE SINGLE DUDI ---
if (contents.action === "saveDudi" || contents.action === "edit_dudi" || contents.action === "delete_dudi") {
const data = sheetDudi.getDataRange().getDisplayValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const idxId = headers.indexOf("ID");
if (contents.action === "edit_dudi" || contents.action === "delete_dudi") {
for (let i = 1; i < data.length; i++) {
if (String(data[i][idxId]).trim() === String(contents.id).trim()) {
if (contents.action === "delete_dudi") {
sheetDudi.deleteRow(i + 1);
return outputJSON({ status: "success", message: "Terhapus" });
} else {
// EDIT DATA
headers.forEach((h, colIdx) => {
let key = h.toLowerCase().replace(/\s+/g, ""); // membersihkan spasi di header untuk mapping
// Pemetaan khusus jika nama key di HTML berbeda dengan header Sheet
let valueToSet = contents[key];
if (h === "M SHIFT 1") valueToSet = contents.m1;
if (h === "P SHIFT 1") valueToSet = contents.p1;
if (h === "M SHIFT 2") valueToSet = contents.m2;
if (h === "P SHIFT 2") valueToSet = contents.p2;
if (h === "M SHIFT 3") valueToSet = contents.m3;
if (h === "P SHIFT 3") valueToSet = contents.p3;
if (h === "HARI JUMAT") valueToSet = contents.jumat;
if (valueToSet !== undefined) {
sheetDudi.getRange(i + 1, colIdx + 1).setValue(valueToSet);
}
});
return outputJSON({ status: "success", message: "Updated" });
}
}
}
return outputJSON({ status: "error", message: "ID tidak ditemukan" });
}
if (contents.action === "saveDudi") {
const dudiVal = (contents.dudi || "").toString().trim();
const tahapVal = (contents.tahap || "").toString().trim();
// PENTING: Jika tahapVal kosong, script akan menolak simpan
if (!dudiVal || !tahapVal) {
return outputJSON({
status: "error",
message: "Gagal! Nama Dudi dan Tahap wajib diisi untuk membuat ID."
});
}
// Membuat ID Gabungan
const newId = (dudiVal + "-" + tahapVal).toUpperCase().replace(/\s+/g, "");
const existingIds = data.map(r => String(r[idxId]).trim().toUpperCase());
if (existingIds.includes(newId)) {
return outputJSON({ status: "error", message: "Gagal! ID " + newId + " sudah terdaftar." });
}
contents.id = newId;
// Mapping baris baru berdasarkan Header
const newRow = headers.map(h => {
let head = h.toLowerCase().trim();
if (head === "no") return sheetDudi.getLastRow();
if (head === "id") return newId;
if (head === "tahap") return tahapVal;
if (head === "dudi") return dudiVal;
// Mapping Jam Kerja (Sinkronisasi HTML & Sheet)
if (h === "M SHIFT 1") return contents.m1 || "";
if (h === "P SHIFT 1") return contents.p1 || "";
if (h === "M SHIFT 2") return contents.m2 || "";
if (h === "P SHIFT 2") return contents.p2 || "";
if (h === "M SHIFT 3") return contents.m3 || "";
if (h === "P SHIFT 3") return contents.p3 || "";
if (h === "HARI JUMAT") return contents.jumat || "";
let cleanKey = head.replace(/\s+/g, "");
return contents[cleanKey] !== undefined ? contents[cleanKey] : "";
});
sheetDudi.appendRow(newRow);
return outputJSON({ status: "success", message: "Tersimpan dengan ID: " + newId });
}
}
// --- ACTION: IMPORT DARI EXCEL ---
if (contents.action === "importDudi") {
if (!sheetDudi) return outputJSON({ status: "error", message: "Sheet Dudi tidak ditemukan" });
const rows = contents.data;
const existingData = sheetDudi.getDataRange().getValues();
const headersDudi = existingData[0].map(h => h.toString().toUpperCase().trim());
const idxIdDudi = headersDudi.indexOf("ID");
const existingIds = existingData.map(r => String(r[idxIdDudi]).trim().toUpperCase());
let successCount = 0;
let skipCount = 0;
rows.forEach(function(row) {
const keys = Object.keys(row);
// Ambil nilai Dudi dan Tahap dari file Excel
let dudiValue = String(row[keys.find(k => k.toUpperCase().trim() === "DUDI")] || "").trim();
let tahapValue = String(row[keys.find(k => k.toUpperCase().trim() === "TAHAP")] || "").trim();
const generatedId = (dudiValue + "-" + tahapValue).toUpperCase().replace(/\s+/g, "");
if (!dudiValue || !tahapValue || existingIds.includes(generatedId)) {
skipCount++;
return;
}
const rowToAdd = headersDudi.map(h => {
if (h === "ID") return generatedId;
if (h === "DUDI") return dudiValue;
if (h === "TAHAP") return tahapValue;
let foundKey = keys.find(k => k.toUpperCase().trim() === h);
return foundKey ? row[foundKey] : "";
});
sheetDudi.appendRow(rowToAdd);
existingIds.push(generatedId);
successCount++;
});
return outputJSON({ status: "success", message: successCount + " berhasil diimport, " + skipCount + " dilewati." });
}
// ======================================================
// 2. LOGIKA PRESENSI
// ======================================================
if (contents.type === "masuk" || contents.type === "pulang") {
const sheetPresensi = ss.getSheetByName("presensi");
if (!sheetPresensi) return outputJSON({ status: "error", message: "Sheet presensi tidak ditemukan!" });
const dataPresensi = sheetPresensi.getDataRange().getValues();
const headersPresensi = dataPresensi[0].map(h => h.toString().toUpperCase().trim());
const idPresensi = contents.username + "-" + tanggal;
const idxIdPresensi = headersPresensi.indexOf("ID");
let rowIndexPresensi = -1;
for (let i = 1; i < dataPresensi.length; i++) {
if (String(dataPresensi[i][idxIdPresensi]).trim() === idPresensi) {
rowIndexPresensi = i + 1;
break;
}
}
const folder = DriveApp.getFolderById(ID_FOLDER_PRESENSI);
let fileUrl = "";
if (contents.image && contents.image.includes(",")) {
try {
const fileName = idPresensi + "_" + contents.type + ".jpg";
const oldFiles = folder.getFilesByName(fileName);
while (oldFiles.hasNext()) oldFiles.next().setTrashed(true);
const rawData = contents.image.split(",")[1];
const blob = Utilities.newBlob(Utilities.base64Decode(rawData), "image/jpeg", fileName);
fileUrl = folder.createFile(blob).getUrl();
} catch (err) { }
}
if (contents.type === "masuk") {
if (rowIndexPresensi !== -1) return outputJSON({ status: "error", message: "Sudah absen masuk!" });
let newRow = headersPresensi.map(h => {
if (h === "DATETIME") return now;
if (h === "ID") return idPresensi;
if (h === "USERNAME") return contents.username;
if (h === "SHIFT") return contents.shift;
if (h === "JADWAL MASUK") return contents.jadwal;
if (h === "NAMA SISWA") return contents.nama || "";
if (h === "TANGGAL") return tanggal;
if (h === "JAM MASUK") return jam;
if (h === "FOTO MASUK") return fileUrl;
if (h === "LOKASI MASUK") return contents.lokasi || "";
if (h === "KOORDINAT MASUK") return contents.koordinat || "";
if (h === "JARAK MASUK") return contents.jarak || "";
if (h === "STATUS") return contents.status || "";
return "";
});
sheetPresensi.appendRow(newRow);
return outputJSON({ status: "success", message: "Absen Masuk Berhasil!" });
}
if (contents.type === "pulang") {
const updatesPulang = { "LOKASI PULANG": contents.lokasi || "", "KOORDINAT PULANG": contents.koordinat || "", "JARAK PULANG": contents.jarak ||"", "JADWAL PULANG": contents.jadwal || "", "JAM PULANG": jam, "FOTO PULANG": fileUrl, "STATUS": contents.status || "" };
if (rowIndexPresensi === -1) {
let newRow = headersPresensi.map(h => {
if (h === "DATETIME") return now;
if (h === "ID") return idPresensi;
if (h === "USERNAME") return contents.username;
if (h === "NAMA SISWA") return contents.nama;
if (h === "SHIFT") return contents.shift;
if (h === "JADWAL PULANG") return contents.jadwal;
if (h === "TANGGAL") return tanggal;
if (h === "JAM PULANG") return jam;
if (h === "LOKASI PULANG") return contents.lokasi ;
if (h === "KOORDINAT PULANG") return contents.koordinat;
if (h === "JARAK PULANG") return contents.jarak ;
if (h === "FOTO PULANG") return fileUrl;
if (h === "STATUS") return contents.status || "";
return "";
});
sheetPresensi.appendRow(newRow);
return outputJSON({ status: "success", message: "Absen Pulang Berhasil (Baris Baru)!" });
} else {
for (let key in updatesPulang) {
let colIdx = headersPresensi.indexOf(key);
if (colIdx !== -1) sheetPresensi.getRange(rowIndexPresensi, colIdx + 1).setValue(updatesPulang[key]);
}
return outputJSON({ status: "success", message: "Absen Pulang Berhasil Diperbarui!" });
}
}
}
// ======================================================
// 1. LOGIKA CRUD TAHAP
// ======================================================
const sheetTahap = ss.getSheetByName("Tahap");
// --- ACTION: SAVE, EDIT, DELETE SINGLE TAHAP ---
if (contents.action === "saveTahap" || contents.action === "editTahap" || contents.action === "deleteTahap") {
const data = sheetTahap.getDataRange().getDisplayValues();
const headers = data[0].map(h => h.toString().toUpperCase().trim());
const idxId = headers.indexOf("TAHAP");
// ======================================================
// EDIT & DELETE
// ======================================================
if (contents.action === "editTahap" || contents.action === "deleteTahap") {
for (let i = 1; i < data.length; i++) {
if (String(data[i][idxId]).trim() === String(contents.tahap).trim()) {
// DELETE
if (contents.action === "deleteTahap") {
sheetTahap.deleteRow(i + 1);
return outputJSON({
status: "success",
message: "Data Tahap Terhapus"
});
} else {
// EDIT DATA
headers.forEach((h, colIdx) => {
let key = h.toLowerCase().replace(/\s+/g, "");
let valueToSet = contents[key];
// mapping khusus jika ada perbedaan nama field
if (h === "M PRAKERIN") valueToSet = contents.mprakerin;
if (h === "S PRAKERIN") valueToSet = contents.sprakerin;
if (h === "M NILAI") valueToSet = contents.mnilai;
if (h === "S NILAI") valueToSet = contents.snilai;
if (valueToSet !== undefined) {
sheetTahap.getRange(i + 1, colIdx + 1).setValue(valueToSet);
}
});
return outputJSON({
status: "success",
message: "Data Tahap Diperbarui"
});
}
}
}
return outputJSON({
status: "error",
message: "ID Tahap tidak ditemukan"
});
}
// ======================================================
// SAVE NEW
// ======================================================
if (contents.action === "saveTahap") {
const tahapVal = (contents.tahap || "").toString().trim();
if (!tahapVal) {
return outputJSON({
status: "error",
message: "Gagal! Nama Tahap wajib diisi sebagai ID."
});
}
const existingIds = data.map(r => String(r[idxId]).trim().toUpperCase());
if (existingIds.includes(tahapVal.toUpperCase())) {
return outputJSON({
status: "error",
message: "Gagal! Nama Tahap '" + tahapVal + "' sudah ada."
});
}
const newRow = headers.map(h => {
let head = h.toLowerCase().trim();
if (head === "tahap") return tahapVal;
// mapping khusus
if (h === "M PRAKERIN") return contents.mprakerin || "";
if (h === "S PRAKERIN") return contents.sprakerin || "";
if (h === "M NILAI") return contents.mnilai || "";
if (h === "S NILAI") return contents.snilai || "";
let cleanKey = head.replace(/\s+/g, "");
return contents[cleanKey] !== undefined ? contents[cleanKey] : "";
});
sheetTahap.appendRow(newRow);
return outputJSON({
status: "success",
message: "Tahap Berhasil Disimpan"
});
}
}
// ======================================================
// 3. LOGIKA PENDAFTARAN PKL
// ======================================================
if (contents.id_dudi && contents.action !== "update") {
const sheetPKL = ss.getSheetByName("PKL");
const dataPKL = sheetPKL.getDataRange().getValues();
const hPKL = dataPKL[0].map(h => h.toString().toUpperCase().trim());
const inputUser = String(contents.username).trim().toLowerCase();
const userRole = contents.jabatan || "Siswa";
let rowIndexPKL = -1;
const idxUserPKL = hPKL.indexOf("USERNAME");
for (let i = 1; i < dataPKL.length; i++) {
if (String(dataPKL[i][idxUserPKL]).trim().toLowerCase() === inputUser) {
rowIndexPKL = i + 1;
break;
}
}
// Cek Kuota
const dataDudiInfo = sheetDudi.getDataRange().getValues();
const hDudiInfo = dataDudiInfo[0].map(h => h.toString().toUpperCase().trim());
const rowDudiInfo = dataDudiInfo.find(r => String(r[hDudiInfo.indexOf("ID")]).trim() === String(contents.id_dudi).trim());
if (rowDudiInfo) {
const maks = parseInt(rowDudiInfo[hDudiInfo.indexOf("KUOTA")]) || 0;
const terisi = dataPKL.filter(r => String(r[hPKL.indexOf("ID DUDI")]).trim() === String(contents.id_dudi).trim()).length;
if (terisi >= maks && rowIndexPKL === -1) return outputJSON({ status: "fail", message: "Maaf, kuota Dudi sudah penuh!" });
}
if (userRole === "Siswa" && rowIndexPKL !== -1) {
sheetPKL.getRange(rowIndexPKL, hPKL.indexOf("TIMESTAMP") + 1).setValue(ts);
sheetPKL.getRange(rowIndexPKL, hPKL.indexOf("ID DUDI") + 1).setValue(contents.id_dudi);
sheetPKL.getRange(rowIndexPKL, hPKL.indexOf("PILIH DUDI") + 1).setValue(contents.pilih_dudi);
sheetPKL.getRange(rowIndexPKL, hPKL.indexOf("NAMA") + 1).setValue(contents.nama);
sheetPKL.getRange(rowIndexPKL, hPKL.indexOf("TAHAP") + 1).setValue(contents.tahap);
return outputJSON({ status: "success", message: "Data penempatan Anda telah diperbarui!" });
} else {
sheetPKL.appendRow([ts, contents.id_dudi, contents.pilih_dudi, contents.username, contents.nama, contents.tahap]);
return outputJSON({ status: "success", message: "Berhasil Menambah Penempatan PKL" });
}
}
// ======================================================
// 4. LOGIKA UPDATE PROFIL
// ======================================================
if (contents.action === "update") {
const targetSheet = ss.getSheetByName(contents.targetSheet);
if (!targetSheet) return outputJSON({ status: "error", message: "Target sheet profil tidak ditemukan" });
const dataProfil = targetSheet.getDataRange().getValues();
const headersProfil = dataProfil[0].map(h => h.toString().toUpperCase().trim());
const idxUProfil = headersProfil.indexOf("USERNAME");
for (let i = 1; i < dataProfil.length; i++) {
if (String(dataProfil[i][idxUProfil]).trim().toLowerCase() === contents.username.toLowerCase()) {
if (contents.no_hp) targetSheet.getRange(i + 1, headersProfil.indexOf("NO HP") + 1).setValue(contents.no_hp);
if (contents.agama) targetSheet.getRange(i + 1, headersProfil.indexOf("AGAMA") + 1).setValue(contents.agama);
return outputJSON({ status: "success", message: "Profil Berhasil Diperbarui" });
}
}
return outputJSON({ status: "not_found", message: "Username profil tidak ditemukan" });
}
return outputJSON({ status: "error", message: "Aksi tidak dikenal" });
} catch (err) {
return outputJSON({ status: "error", message: err.toString() });
}
}
function outputJSON(data) {
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
function pemicuIzin() {
DriveApp.getFolderById(ID_FOLDER_PRESENSI);
DriveApp.createFile("test.txt", "cek izin");
}