const SHEET_ID = SpreadsheetApp.getActiveSpreadsheet().getId(); const API_SECRET = "GANTI_SECRET_KEY_ANDA"; // Samakan dengan APP_CONFIG.apiSecret di index.html function jsonResponse(obj) { return ContentService.createTextOutput(JSON.stringify(obj)) .setMimeType(ContentService.MimeType.JSON); } function doGet(e) { try { const key = e && e.parameter ? e.parameter.secret : ""; if (API_SECRET !== "GANTI_SECRET_KEY_ANDA" && key !== API_SECRET) { return jsonResponse({ status: "unauthorized", message: "Invalid API key" }); } const ss = SpreadsheetApp.openById(SHEET_ID); return jsonResponse({ status: "ok", updatedAt: new Date().toISOString(), transaksi: readSheet(ss.getSheetByName("Transaksi")), rekening: readSheet(ss.getSheetByName("Rekening")), pos: readSheet(ss.getSheetByName("Pos_Anggaran")), investasi: readSheet(ss.getSheetByName("Investasi")), asetTetap: readSheet(ss.getSheetByName("Aset_Tetap")), kewajiban: readSheet(ss.getSheetByName("Kewajiban")) }); } catch (err) { return jsonResponse({ status: "error", message: err.message }); } } function doPost(e) { try { const body = JSON.parse(e.postData.contents || "{}"); // Jika payload berasal dari webhook WhatsApp gateway (misal: { sender, message, secret }) if (body.message && !body.action) { if (API_SECRET !== "GANTI_SECRET_KEY_ANDA" && body.secret !== API_SECRET) { return jsonResponse({ status: "unauthorized" }); } return handleChatCommand(body.message, body.sender || "WhatsApp Bot"); } if (API_SECRET !== "GANTI_SECRET_KEY_ANDA" && body.secret !== API_SECRET) { return jsonResponse({ status: "unauthorized", message: "Invalid API key" }); } const action = body.action; const payload = body.payload || {}; const ss = SpreadsheetApp.openById(SHEET_ID); if (action === "sync_all") { overwriteSheet(ss.getSheetByName("Transaksi"), ["ID","Tanggal","Tipe","Sumber_Income","Pos","Rekening","Nominal","Catatan","Input_Via","Status","Audit_Log"], payload.transaksi || []); overwriteSheet(ss.getSheetByName("Rekening"), ["Nama_Rekening","Tipe","Saldo"], payload.rekening || []); overwriteSheet(ss.getSheetByName("Pos_Anggaran"), ["Nama_Pos","Kategori","Target_Bulanan"], payload.pos || []); overwriteSheet(ss.getSheetByName("Investasi"), ["ID","Instrumen","Modal_Awal","Nilai_Sekarang","Update_Terakhir"], payload.investasi || []); overwriteSheet(ss.getSheetByName("Aset_Tetap"), ["ID","Nama_Aset","Kategori","Tahun_Perolehan","Harga_Perolehan","Nilai_Taksiran","Keterangan_LHKPN"], payload.asetTetap || []); overwriteSheet(ss.getSheetByName("Kewajiban"), ["ID","Nama_Kewajiban","Jenis","Sisa_Pokok","Jatuh_Tempo"], payload.kewajiban || []); return jsonResponse({ status: "ok", message: "Synced to Sheets" }); } if (action === "add_transaction") { const saved = appendTxAndUpdateBalance(ss, payload, payload.via || "Web App"); return jsonResponse({ status: "ok", data: saved }); } return jsonResponse({ status: "error", message: "Unknown action" }); } catch (err) { return jsonResponse({ status: "error", message: err.message }); } } function handleChatCommand(rawText, via) { const clean = String(rawText).trim().replace(/\s+/g, " "); const parts = clean.split(" "); const cmd = (parts[0] || "").toUpperCase(); if (cmd === "KELUAR" && parts.length >= 4) { const nominal = Number(String(parts[3]).replace(/[^0-9]/g, "")); if (!nominal) return jsonResponse({ status: "ignored", reply: "Nominal tidak valid." }); const ss = SpreadsheetApp.openById(SHEET_ID); const tx = { tanggal: Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd"), tipe: "Pengeluaran", sumber: "-", pos: parts[1], rekening: parts[2], nominal: nominal, catatan: parts.slice(4).join(" ") || "-" }; appendTxAndUpdateBalance(ss, tx, via); return jsonResponse({ status: "ok", reply: `Tercatat Pengeluaran ${tx.pos} Rp ${nominal.toLocaleString("id-ID")} (${tx.rekening})` }); } if (cmd === "MASUK" && parts.length >= 5) { const nominal = Number(String(parts[4]).replace(/[^0-9]/g, "")); if (!nominal) return jsonResponse({ status: "ignored", reply: "Nominal tidak valid." }); const ss = SpreadsheetApp.openById(SHEET_ID); const tx = { tanggal: Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd"), tipe: "Pemasukan", sumber: parts[1], pos: parts[2], rekening: parts[3], nominal: nominal, catatan: parts.slice(5).join(" ") || "-" }; appendTxAndUpdateBalance(ss, tx, via); return jsonResponse({ status: "ok", reply: `Tercatat Pemasukan (${tx.sumber}) Rp ${nominal.toLocaleString("id-ID")} ke ${tx.rekening}` }); } return jsonResponse({ status: "ignored", reply: "Format:\nKELUAR [Pos] [Rekening] [Nominal] [Catatan]\nMASUK [Suami/Istri/Tambahan] [Pos] [Rekening] [Nominal] [Catatan]" }); } function appendTxAndUpdateBalance(ss, tx, via) { const txSheet = ss.getSheetByName("Transaksi"); const rekSheet = ss.getSheetByName("Rekening"); const id = tx.id || ("TX-" + new Date().getTime()); const tanggal = tx.tanggal || Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd"); const nominal = Number(tx.nominal || 0); txSheet.appendRow([ id, tanggal, tx.tipe, tx.sumber || "-", tx.pos, tx.rekening, nominal, tx.catatan || "", via, "Aktif", "Dibuat via " + via ]); if (rekSheet) { const rows = rekSheet.getDataRange().getValues(); for (let i = 1; i < rows.length; i++) { if (String(rows[i][0]).toLowerCase() === String(tx.rekening).toLowerCase()) { const lama = Number(rows[i][2]) || 0; const baru = tx.tipe === "Pemasukan" ? lama + nominal : lama - nominal; rekSheet.getRange(i + 1, 3).setValue(baru); break; } } } return { id, tanggal, nominal }; } function readSheet(sheet) { if (!sheet) return []; const values = sheet.getDataRange().getValues(); if (values.length <= 1) return []; const headers = values[0]; return values.slice(1).filter(r => r.some(c => c !== "")).map(r => { const obj = {}; headers.forEach((h, idx) => obj[h] = r[idx]); return obj; }); } function overwriteSheet(sheet, headers, list) { if (!sheet) return; sheet.clearContents(); sheet.appendRow(headers); if (!list || !list.length) return; const rows = list.map(item => headers.map(h => item[h] !== undefined ? item[h] : "")); sheet.getRange(2, 1, rows.length, headers.length).setValues(rows); }