function handleRequest(e) {
var lock = LockService.getScriptLock();
lock.tryLock(15000);
try {
var ss = SpreadsheetApp.openById("1YjyJJOmCFeY5E_HYW_uS_mmZAPs4APSakFxxSNIoc0E");
var data = {};
if (e && e.postData && e.postData.contents) {
try { data = JSON.parse(e.postData.contents); } catch (err) { data = e.parameter || {}; }
} else if (e && e.parameter) {
data = e.parameter;
}
// TAB 1: Bookings & Reservations (24 columns)
var sheetBookings = ss.getSheetByName("Bookings & Reservations") || ss.getSheetByName("Bookings");
if (!sheetBookings) {
sheetBookings = ss.insertSheet("Bookings & Reservations");
} else if (sheetBookings.getName() === "Bookings") {
sheetBookings.setName("Bookings & Reservations");
}
var hBookings = [
"Booking ID", "Guest Name", "Passport / ID", "Guest Type", "Contact Info",
"Platform", "Room", "Driver Quarters", "Check-In", "Check-Out", "Nights",
"Total Amount", "Currency", "Converted Base Amount", "Paid Amount",
"Remaining Balance", "Payment Status", "Payment Method", "Card Type",
"Arrival Status", "Cancellation / Refund Info", "Special Requests",
"Damage Log Summary", "Timestamp"
];
formatHeaderRow(sheetBookings, hBookings);
// TAB 2: Room Holds (9 columns)
var sheetHolds = ss.getSheetByName("Room Holds") || ss.insertSheet("Room Holds");
var hHolds = [
"Hold ID", "Room", "Guest Name", "Contact", "Guest Type",
"Check-In Date/Time", "Check-Out Date/Time", "Staff Name", "Status"
];
formatHeaderRow(sheetHolds, hHolds);
// TAB 3: Incidents & Damages (6 columns)
var sheetDamages = ss.getSheetByName("Incidents & Damages") || ss.insertSheet("Incidents & Damages");
var hDamages = [
"Damage ID", "Booking ID", "Description", "Cost (LKR)", "Status", "Date Logged"
];
formatHeaderRow(sheetDamages, hDamages);
// TAB 4: Guest Directory (8 columns)
var sheetGuests = ss.getSheetByName("Guest Directory") || ss.insertSheet("Guest Directory");
var hGuests = [
"Guest ID", "Full Name", "Passport / ID", "Guest Type", "Country", "Phone", "Email", "Notes"
];
formatHeaderRow(sheetGuests, hGuests);
// TAB 5: Deleted Reservations Audit (10 columns)
var sheetDeleted = ss.getSheetByName("Deleted Reservations Audit") || ss.insertSheet("Deleted Reservations Audit");
var hDeleted = [
"Record ID", "Booking ID", "Guest Name", "Passport / ID", "Room",
"Platform", "Total Amount (LKR)", "Deleted By Staff", "Deletion Reason", "Date Deleted"
];
formatHeaderRow(sheetDeleted, hDeleted);
// TAB 6: Performance Analytics & Charts (3 columns)
var sheetAnalytics = ss.getSheetByName("Performance Analytics & Charts") || ss.insertSheet("Performance Analytics & Charts");
var hAnalytics = ["Category / Metric", "Value / Count", "Details"];
formatHeaderRow(sheetAnalytics, hAnalytics);
// Write Data Rows to all 6 sheets
clearSheetDataRows(sheetBookings);
if (data.bookings && Array.isArray(data.bookings) && data.bookings.length > 0) {
var bRows = data.bookings.map(function(b) {
return [
b.bookingId || "", b.guestName || "", b.passportOrId || "", b.guestType || "Foreign",
b.contact || "", b.platform || "Direct", b.room || "Room 1 – Studio", b.driverQuarters || "No",
b.checkIn || "", b.checkOut || "", Number(b.nights) || 0, Number(b.totalAmount) || 0,
b.currency || "LKR", b.convertedBaseAmount || "LKR 0.00", Number(b.paidAmount) || 0,
Number(b.remainingBalance) || 0, b.paymentStatus || "Paid", b.paymentMethod || "Cash",
b.cardType || "N/A", b.arrivalStatus || "Arrived", b.cancellationRefundInfo || "N/A",
b.specialRequests || "None", b.damages || "None", b.timestamp || new Date().toISOString()
];
});
sheetBookings.getRange(2, 1, bRows.length, hBookings.length).setValues(bRows);
}
clearSheetDataRows(sheetHolds);
if (data.roomHolds && Array.isArray(data.roomHolds) && data.roomHolds.length > 0) {
var hRows = data.roomHolds.map(function(h) {
return [
h.holdId || "", h.room || "", h.guestName || "", h.contact || "",
h.guestType || "Foreign", h.checkIn || "", h.checkOut || "", h.staffName || "", h.status || "Reserved"
];
});
sheetHolds.getRange(2, 1, hRows.length, hHolds.length).setValues(hRows);
}
clearSheetDataRows(sheetDamages);
if (data.damages && Array.isArray(data.damages) && data.damages.length > 0) {
var dRows = data.damages.map(function(d) {
return [
d.damageId || "", d.bookingId || "", d.description || "",
Number(d.cost) || 0, d.status || "Unpaid", d.dateLogged || ""
];
});
sheetDamages.getRange(2, 1, dRows.length, hDamages.length).setValues(dRows);
}
clearSheetDataRows(sheetGuests);
if (data.guests && Array.isArray(data.guests) && data.guests.length > 0) {
var gRows = data.guests.map(function(g) {
return [
g.guestId || "", g.fullName || "", g.passportOrId || "", g.guestType || "Foreign",
g.country || "", g.phone || "", g.email || "", g.notes || ""
];
});
sheetGuests.getRange(2, 1, gRows.length, hGuests.length).setValues(gRows);
}
clearSheetDataRows(sheetDeleted);
if (data.deletedBookings && Array.isArray(data.deletedBookings) && data.deletedBookings.length > 0) {
var dbRows = data.deletedBookings.map(function(dbk) {
return [
dbk.recordId || "", dbk.bookingId || "", dbk.guestName || "", dbk.passportOrId || "",
dbk.room || "", dbk.platform || "", Number(dbk.totalAmount) || 0, dbk.deletedByStaff || "",
dbk.deletedReason || "", dbk.deletedAt || ""
];
});
sheetDeleted.getRange(2, 1, dbRows.length, hDeleted.length).setValues(dbRows);
} else {
var defaultDeletedRow = [
"-", "-", "No deleted reservations recorded", "-", "-", "-", 0, "-", "No deletions logged yet", new Date().toISOString()
];
sheetDeleted.getRange(2, 1, 1, hDeleted.length).setValues([defaultDeletedRow]);
}
// 6. Analytics Table & Embedded Real-Time 3D & Bar Charts
clearSheetDataRows(sheetAnalytics);
var analyticsRes = buildSheetAnalyticsSummary(data.bookings || [], data.damages || []);
if (analyticsRes.rows.length > 0) {
sheetAnalytics.getRange(2, 1, analyticsRes.rows.length, hAnalytics.length).setValues(analyticsRes.rows);
// Apply Currency Number Formatting to Column B for numeric rows
sheetAnalytics.getRange("B3:B9").setNumberFormat("LKR #,##0");
sheetAnalytics.getRange("B" + analyticsRes.damageStartRow + ":B" + (analyticsRes.damageStartRow + 2)).setNumberFormat("LKR #,##0");
// Remove existing charts before embedding updated real-time 3D & Bar charts
var existingCharts = sheetAnalytics.getCharts();
for (var c = 0; c < existingCharts.length; c++) {
sheetAnalytics.removeChart(existingCharts[c]);
}
// Chart 1: 3D Pie Chart for Booking Channel Breakdown
if (analyticsRes.channelCount > 0) {
var pieChart = sheetAnalytics.newChart()
.setChartType(Charts.ChartType.PIE)
.addRange(sheetAnalytics.getRange(analyticsRes.channelStartRow, 1, analyticsRes.channelCount, 2))
.setPosition(2, 5, 0, 0)
.setOption("title", "Booking Channel Breakdown (3D)")
.setOption("is3D", true)
.setOption("width", 540)
.setOption("height", 320)
.build();
sheetAnalytics.insertChart(pieChart);
}
// Chart 2: 3D Bar Chart for Revenue by Payment Method
var revenueChart = sheetAnalytics.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheetAnalytics.getRange(6, 1, 4, 2))
.setPosition(2, 12, 0, 0)
.setOption("title", "Revenue by Payment Method (LKR)")
.setOption("is3D", true)
.setOption("colors", ["#10b981"])
.setOption("width", 540)
.setOption("height", 320)
.build();
sheetAnalytics.insertChart(revenueChart);
// Chart 3: 3D Column Bar Chart for Guest Arrival Status Distribution
if (analyticsRes.arrivalStartRow > 0) {
var arrivalChart = sheetAnalytics.newChart()
.setChartType(Charts.ChartType.COLUMN)
.addRange(sheetAnalytics.getRange(analyticsRes.arrivalStartRow, 1, 5, 2))
.setPosition(19, 5, 0, 0)
.setOption("title", "Guest Arrival Status Distribution")
.setOption("is3D", true)
.setOption("colors", ["#0284c7", "#eab308", "#ef4444", "#64748b"])
.setOption("width", 540)
.setOption("height", 320)
.build();
sheetAnalytics.insertChart(arrivalChart);
}
// Chart 4: 3D Column Bar Chart for Incidental Damage Log Audit Metrics
if (analyticsRes.damageStartRow > 0) {
var damageChart = sheetAnalytics.newChart()
.setChartType(Charts.ChartType.COLUMN)
.addRange(sheetAnalytics.getRange(analyticsRes.damageStartRow, 1, 3, 2))
.setPosition(19, 12, 0, 0)
.setOption("title", "Incidental Damage Log Audit Metrics (LKR)")
.setOption("is3D", true)
.setOption("colors", ["#f43f5e"])
.setOption("width", 540)
.setOption("height", 320)
.build();
sheetAnalytics.insertChart(damageChart);
}
}
return ContentService.createTextOutput(JSON.stringify({ "status": "success", "message": "All 6 Google Sheet tabs & 4 3D Charts updated successfully!" }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) {
return ContentService.createTextOutput(JSON.stringify({ "status": "error", "message": error.toString() }))
.setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function buildSheetAnalyticsSummary(bookings, damages) {
var totalRevenueLKR = 0;
var totalNights = 0;
var cashTotalLKR = 0;
var cardTotalLKR = 0;
var qrTotalLKR = 0;
var bankTransferTotalLKR = 0;
var channelCounts = {};
var channelRevenues = {};
var arrivalCounts = { "Arrived": 0, "Late Check-In": 0, "No Show": 0, "Cancelled": 0 };
bookings.forEach(function(b) {
var ch = b.platform || "Direct";
channelCounts[ch] = (channelCounts[ch] || 0) + 1;
var arr = b.arrivalStatus || "Arrived";
arrivalCounts[arr] = (arrivalCounts[arr] || 0) + 1;
if (b.arrivalStatus !== "Cancelled") {
var amt = Number(b.totalAmount) || 0;
totalRevenueLKR += amt;
totalNights += (Number(b.nights) || 1);
channelRevenues[ch] = (channelRevenues[ch] || 0) + amt;
var method = b.paymentMethod || "Cash";
if (method === "Cash") cashTotalLKR += amt;
else if (method === "Card") cardTotalLKR += amt;
else if (method === "QR") qrTotalLKR += amt;
else if (method === "Bank Transfer") bankTransferTotalLKR += amt;
}
});
var adrLKR = totalNights > 0 ? Number((totalRevenueLKR / totalNights).toFixed(2)) : 0;
var revparLKR = Number((totalRevenueLKR / (7 * 30)).toFixed(2));
var damagesTotalLKR = 0;
var damagesUnpaidLKR = 0;
var damagesDeductedLKR = 0;
damages.forEach(function(d) {
var c = Number(d.cost) || 0;
damagesTotalLKR += c;
if (d.status === "Unpaid") damagesUnpaidLKR += c;
else if (d.status === "Deducted from Deposit") damagesDeductedLKR += c;
});
var rows = [
["--- REVENUE & STAY PERFORMANCE ---", "", ""],
["Total Gross Revenue (LKR)", totalRevenueLKR, "Sum of non-cancelled stays"],
["Average Daily Rate (ADR)", adrLKR, "Total Revenue / Occupied Nights"],
["RevPAR", revparLKR, "Revenue / Available Room Capacity"],
["Cash Payments (LKR)", cashTotalLKR, "Cash receipts"],
["Card Payments (LKR)", cardTotalLKR, "Visa / MasterCard / Amex"],
["QR Code Payments (LKR)", qrTotalLKR, "Digital QR payments"],
["Bank Transfer Payments (LKR)", bankTransferTotalLKR, "Wire transfers"],
["", "", ""],
["--- BOOKING CHANNEL BREAKDOWN ---", "", ""]
];
var channelStartRow = 2 + rows.length;
var channelKeys = Object.keys(channelCounts);
if (channelKeys.length === 0) channelKeys = ["Direct Contact"];
channelKeys.forEach(function(ch) {
rows.push([
ch,
channelCounts[ch] || 0,
"LKR " + (channelRevenues[ch] || 0).toLocaleString()
]);
});
rows.push(["", "", ""]);
var arrivalStartRow = 2 + rows.length;
rows.push(["--- ARRIVAL STATUS DISTRIBUTION ---", "", ""]);
rows.push(["Arrived Stays", arrivalCounts["Arrived"], "Confirmed arrivals"]);
rows.push(["Late Check-Ins", arrivalCounts["Late Check-In"], "Delayed arrivals"]);
rows.push(["No Show Stays", arrivalCounts["No Show"], "Did not arrive"]);
rows.push(["Cancelled Stays", arrivalCounts["Cancelled"], "Cancelled bookings"]);
rows.push(["", "", ""]);
var damageStartRow = 2 + rows.length;
rows.push(["--- INCIDENTAL DAMAGE AUDIT METRICS ---", "", ""]);
rows.push(["Total Damage Claims Logged", damagesTotalLKR, damages.length + " incident logs"]);
rows.push(["Unpaid Damage Claims", damagesUnpaidLKR, "Pending settlement"]);
rows.push(["Deducted from Deposit", damagesDeductedLKR, "Covered by guest deposit"]);
return {
rows: rows,
channelStartRow: channelStartRow,
channelCount: channelKeys.length,
arrivalStartRow: arrivalStartRow,
damageStartRow: damageStartRow
};
}
function formatHeaderRow(sheet, headers) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
sheet.getRange(1, 1, 1, headers.length)
.setBackground("#0f172a")
.setFontColor("#ffffff")
.setFontWeight("bold")
.setHorizontalAlignment("center");
sheet.setFrozenRows(1);
}
function clearSheetDataRows(sheet) {
var lastRow = sheet.getLastRow();
if (lastRow > 1) {
sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
}
}
function doPost(e) { return handleRequest(e); }
function doGet(e) { return handleRequest(e); }
