Kode Google Apps Script (Versi Aman Antrean Concurrency & Auto-Sheet Bulanan):
function getMonthlySheet(ss, dateStr) {
var monthNames = ["Januari", "Februari", "Maret", "April", "Mei", "Juni", "Juli", "Agustus", "September", "Oktober", "November", "Desember"];
var targetSheetName = "DATABASE_CS";
if (dateStr && String(dateStr).indexOf("-") > -1) {
var parts = String(dateStr).split("-");
var monthIdx = parseInt(parts[1], 10) - 1;
if (monthIdx >= 0 && monthIdx < 12) targetSheetName = monthNames[monthIdx] + " " + parts[0];
}
var sheet = ss.getSheetByName(targetSheetName);
if (!sheet) {
sheet = ss.insertSheet(targetSheetName);
ss.setActiveSheet(sheet);
ss.moveActiveSheet(1);
}
if (sheet.getLastRow() === 0) {
sheet.appendRow(["ID_KEY", "Tanggal", "CS", "Biaya Iklan", "Klik", "View", "DM", "Real", "Jabo", "Booking", "Datang", "Delay", "Top Up", "Last Updated"]);
sheet.getRange(1, 1, 1, 14).setFontWeight("bold").setBackground("#0d9488").setFontColor("#ffffff");
sheet.setFrozenRows(1);
}
return sheet;
}
function doPost(e) {
var lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var payload = JSON.parse(e.postData.contents);
var date = payload.date || Utilities.formatDate(new Date(), "GMT+7", "yyyy-MM-dd");
var csName = String(payload.csName || "").trim().toUpperCase();
var idKey = date + "_" + csName;
var sheet = getMonthlySheet(ss, date);
var data = sheet.getDataRange().getValues();
var foundRow = -1;
for (var i = 1; i < data.length; i++) {
if (String(data[i][0]) === idKey) { foundRow = i + 1; break; }
}
if (foundRow > -1) {
if (payload.type === "CS_INPUT") {
sheet.getRange(foundRow, 10).setValue(Number(payload.booking || 0));
sheet.getRange(foundRow, 11).setValue(Number(payload.datang || 0));
sheet.getRange(foundRow, 12).setValue(Number(payload.delay || 0));
} else if (payload.type === "PIC_ADS") {
sheet.getRange(foundRow, 4).setValue(Number(payload.biayaIklan || 0));
sheet.getRange(foundRow, 5).setValue(Number(payload.klik || 0));
sheet.getRange(foundRow, 6).setValue(Number(payload.view || 0));
sheet.getRange(foundRow, 7).setValue(Number(payload.dm || 0));
sheet.getRange(foundRow, 8).setValue(Number(payload.real || 0));
sheet.getRange(foundRow, 9).setValue(Number(payload.jabo || 0));
sheet.getRange(foundRow, 13).setValue(Number(payload.topUp || 0));
}
sheet.getRange(foundRow, 14).setValue(new Date());
} else {
sheet.appendRow([idKey, date, csName, Number(payload.biayaIklan || 0), Number(payload.klik || 0), Number(payload.view || 0), Number(payload.dm || 0), Number(payload.real || 0), Number(payload.jabo || 0), Number(payload.booking || 0), Number(payload.datang || 0), Number(payload.delay || 0), Number(payload.topUp || 0), new Date()]);
}
return ContentService.createTextOutput(JSON.stringify({ status: "ok", id: idKey, sheet: sheet.getName() })).setMimeType(ContentService.MimeType.JSON);
} catch (err) {
return ContentService.createTextOutput(JSON.stringify({ status: "error", message: err.toString() })).setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function doGet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var result = {};
for (var s = 0; s < sheets.length; s++) {
var sheet = sheets[s];
if (sheet.getLastRow() <= 1) continue;
var values = sheet.getDataRange().getValues();
for (var i = 1; i < values.length; i++) {
var row = values[i];
var rawDate = row[1];
var date = rawDate instanceof Date ? Utilities.formatDate(rawDate, "GMT+7", "yyyy-MM-dd") : String(rawDate || "").trim();
var cs = String(row[2] || "").trim().toUpperCase();
if (!date || !cs) continue;
if (!result[date]) result[date] = {};
result[date][cs] = {
biayaIklan: Number(row[3] || 0), klik: Number(row[4] || 0), view: Number(row[5] || 0),
dm: Number(row[6] || 0), real: Number(row[7] || 0), jabo: Number(row[8] || 0),
booking: Number(row[9] || 0), datang: Number(row[10] || 0), delay: Number(row[11] || 0), topUp: Number(row[12] || 0)
};
}
}
return ContentService.createTextOutput(JSON.stringify({ status: "ok", db: result })).setMimeType(ContentService.MimeType.JSON);
}
