function jsonOutput(obj) {
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}
function parseJsonCell(value, fallback) {
if (!value) return fallback;
try { return JSON.parse(value); } catch (e) { return fallback; }
}
function dateToIso(value) {
if (!value) return "";
if (Object.prototype.toString.call(value) === "[object Date]" && !isNaN(value)) {
return Utilities.formatDate(value, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
return String(value);
}
function readLatestRawLoans(ss) {
const rawSheet = ss.getSheetByName("Hrubá data (Záloha)");
if (!rawSheet || rawSheet.getLastRow() < 2) return [];
const parsed = parseJsonCell(rawSheet.getRange(2, 2).getValue(), []);
return Array.isArray(parsed) ? parsed : (parsed.data || []);
}
function readOverviewLoans(ss, rawLoans) {
const sheet = ss.getSheetByName("Přehled půjček");
if (!sheet || sheet.getLastRow() < 2) return [];
const values = sheet.getDataRange().getValues();
const rawById = {};
(rawLoans || []).forEach(function(l) { if (l && l.id !== undefined) rawById[String(l.id)] = l; });
const rows = [];
for (let i = 1; i < values.length; i++) {
const r = values[i];
if (r.join("") === "") continue;
const id = String(r[1] || ("loan_" + i));
const raw = rawById[id] || {};
rows.push({
id: id,
name: r[2] || raw.name || "",
principal: Number(r[3]) || 0,
rate: Number(r[4]) || 0,
termMonths: Number(r[5]) || 0,
startDate: dateToIso(r[6] || raw.startDate),
manualMonthly: Number(r[7] || raw.manualMonthly) || undefined,
note: r[8] || raw.note || "",
extras: parseJsonCell(r[9], raw.extras || {})
});
}
return rows;
}
function mergeLoans(existingLoans, incomingLoans) {
const incomingById = {};
(incomingLoans || []).forEach(function(l) {
if (l && l.id !== undefined && l.id !== "") incomingById[String(l.id)] = l;
});
const merged = [];
const seen = {};
(existingLoans || []).forEach(function(existing) {
const id = String(existing.id || "");
if (id && incomingById[id]) {
merged.push(Object.assign({}, existing, incomingById[id]));
seen[id] = true;
} else {
merged.push(existing);
if (id) seen[id] = true;
}
});
(incomingLoans || []).forEach(function(l) {
const id = String(l.id || "");
if (!id || !seen[id]) {
merged.push(l);
if (id) seen[id] = true;
}
});
return merged;
}
function doGet() {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const rawLoans = readLatestRawLoans(ss);
const overviewLoans = readOverviewLoans(ss, rawLoans);
const data = overviewLoans.length > 0 ? overviewLoans : rawLoans;
return jsonOutput({ status: "success", data: data, hasData: data.length > 0 });
} catch (error) {
return jsonOutput({ status: "error", message: error.toString() });
}
}
function doPost(e) {
try {
let payload;
if (e && e.postData && e.postData.contents) {
payload = JSON.parse(e.postData.contents);
} else {
return jsonOutput({ status: "error", message: "Chybí data" });
}
if (payload.action !== 'sync_pujcky') {
return jsonOutput({ status: "error", message: "Neznámá akce" });
}
const incomingLoans = Array.isArray(payload.data) ? payload.data : [];
const timestamp = payload.timestamp;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const existingRawLoans = readLatestRawLoans(ss);
const existingOverviewLoans = readOverviewLoans(ss, existingRawLoans);
const existingLoans = existingOverviewLoans.length > 0 ? existingOverviewLoans : existingRawLoans;
if (incomingLoans.length === 0 && existingLoans.length > 0) {
return jsonOutput({ status: "skipped", message: "Prázdný zápis přeskočen kvůli ochraně existujících dat" });
}
const loans = payload.replaceAll === true ? incomingLoans : mergeLoans(existingLoans, incomingLoans);
let rawSheet = ss.getSheetByName("Hrubá data (Záloha)");
if (!rawSheet) {
rawSheet = ss.insertSheet("Hrubá data (Záloha)");
rawSheet.appendRow(["Čas uložení", "Kompletní JSON data"]);
rawSheet.getRange("A1:B1").setFontWeight("bold");
}
rawSheet.insertRowAfter(1);
rawSheet.getRange(2, 1, 1, 2).setValues([[timestamp, JSON.stringify(loans)]]);
if (rawSheet.getMaxRows() > 50) rawSheet.deleteRows(51, rawSheet.getMaxRows() - 50);
let overviewSheet = ss.getSheetByName("Přehled půjček");
if (!overviewSheet) overviewSheet = ss.insertSheet("Přehled půjček");
overviewSheet.clearContents();
overviewSheet.appendRow(["Poslední Sync", "ID", "Název", "Jistina (Kč)", "Úrok p.a. (%)", "Doba (měs.)", "1. splátka", "Měs. splátka", "Poznámka", "Mimořádné splátky JSON"]);
overviewSheet.getRange("A1:J1").setFontWeight("bold").setBackground("#f3f4f6");
if (loans.length > 0) {
const rows = loans.map(function(l) {
return [timestamp, l.id, l.name || "", l.principal || 0, l.rate || 0, l.termMonths || 0, l.startDate || "", l.computedMonthly || l.manualMonthly || 0, l.note || "", JSON.stringify(l.extras || {})];
});
overviewSheet.getRange(2, 1, rows.length, 10).setValues(rows);
}
return jsonOutput({ status: "success", message: "OK" });
} catch (error) {
return jsonOutput({ status: "error", message: error.toString() });
}
}