/****************************** * CONFIG ******************************/const SHEET_NAME = 'Leads';const EVENTS_SHEET = 'Events';const TIMEZONE = 'America/Los_Angeles'; // for your timestamps// Master column order (add here if you want new columns)const HEADERS = [ "ts", "last_updated", "source", "agent", "callId", "name", "phone", "email", "email_valid", "intent", "appointment_time", "order_items_json", "order_total_cents", "order_currency", "orderId", "payment_url", "notes", "raw_json"];/****************************** * ENTRYPOINT ******************************/function doPost(e) { try { const payload = JSON.parse(e.postData?.contents || "{}"); const action = String(payload.action || 'append'); const lead = payload.lead || {}; const ss = SpreadsheetApp.getActive(); const sh = ensureSheet(ss, SHEET_NAME, HEADERS); const ev = ensureSheet(ss, EVENTS_SHEET, ["ts","event","key","details"]); const result = routeAction({ action, lead, payload, sh, ev }); return jsonOut({ ok: true, result }); } catch (err) { return jsonOut({ ok: false, error: String(err && err.stack || err) }, 500); }}/****************************** * ROUTER ******************************/function routeAction({ action, lead, payload, sh, ev }) { const lock = LockService.getScriptLock(); lock.tryLock(30000); try { const normLead = normalizeLead(lead); const now = new Date(); switch (action) { case 'append': case 'lead_captured': appendLead(sh, normLead, now); logEvent(ev, 'lead_captured', pickKey(normLead), {name:normLead.name, phone:normLead.phone}); break; case 'order_placed': // Expect optional: orderId, payment_url, order_total_cents, order_currency upsertSmart(sh, normLead, now); logEvent(ev, 'order_placed', pickKey(normLead), {orderId:normLead.orderId, payment_url:normLead.payment_url}); break; case 'appointment_scheduled': upsertSmart(sh, normLead, now); logEvent(ev, 'appointment_scheduled', pickKey(normLead), {appointment_time:normLead.appointment_time}); break; case 'call_ended': upsertSmart(sh, normLead, now); logEvent(ev, 'call_ended', pickKey(normLead), {}); break; case 'upsert_by_callId': case 'upsert_by_phone': case 'upsert_by_email': case 'upsert_by_orderId': case 'upsert_smart': upsertSmart(sh, normLead, now, action); logEvent(ev, action, pickKey(normLead), {}); break; case 'reschedule_appointment': { // Accepts: payload.new_time (ISO or plain), or lead.appointment_time const newTime = String(payload.new_time || normLead.appointment_time || ""); if (!newTime) throw new Error("reschedule_appointment requires new_time or lead.appointment_time"); const rowIndex = findRowIndexForSmartKey(sh, normLead, action); if (rowIndex < 2) throw new Error("No matching record found to reschedule"); const map = headerMap(sh); sh.getRange(rowIndex, map["appointment_time"]).setValue(newTime); sh.getRange(rowIndex, map["last_updated"]).setValue(isoNow()); if (normLead.notes) sh.getRange(rowIndex, map["notes"]).setValue(normLead.notes); logEvent(ev, 'reschedule_appointment', pickKey(normLead), {new_time:newTime}); break; } default: throw new Error("Unknown action: " + action); } return { action, key: pickKey(normLead) }; } finally { lock.releaseLock(); }}/****************************** * CORE OPERATIONS ******************************/function appendLead(sh, lead, now) { const map = headerMap(sh); const row = leadToRow(lead, map); row[map["ts"]-1] = lead.ts || isoNow(); row[map["last_updated"]-1] = isoNow(); sh.appendRow(row);}function upsertSmart(sh, lead, now, explicitMode) { const idx = findRowIndexForSmartKey(sh, lead, explicitMode); const map = headerMap(sh); const row = leadToRow(lead, map); // Always update timestamps row[map["last_updated"]-1] = isoNow(); if (!row[map["ts"]-1]) row[map["ts"]-1] = isoNow(); if (idx >= 2) { // Update in place sh.getRange(idx, 1, 1, HEADERS.length).setValues([row]); return idx; } else { // Append new sh.appendRow(row); return sh.getLastRow(); }}/****************************** * LOOKUP / MATCHING ******************************/function findRowIndexForSmartKey(sh, lead, explicitMode) { const values = sh.getDataRange().getValues(); const headers = values[0]; const col = name => headers.indexOf(name); const iCallId = col("callId"); const iOrderId = col("orderId"); const iPhone = col("phone"); const iEmail = col("email"); const wantCall = /callId$/.test(explicitMode || "") || (!explicitMode && lead.callId); const wantOrder = /orderId$/.test(explicitMode || "") || (!explicitMode && lead.orderId); const wantPhone = /phone$/.test(explicitMode || "") || (!explicitMode && lead.phone); const wantEmail = /email$/.test(explicitMode || "") || (!explicitMode && lead.email); // Priority: callId → orderId → phone → email if (wantCall && lead.callId) { const r = values.findIndex((r, i) => i>0 && r[iCallId] === lead.callId); if (r !== -1) return r+1; } if (wantOrder && lead.orderId) { const r = values.findIndex((r, i) => i>0 && r[iOrderId] === lead.orderId); if (r !== -1) return r+1; } if (wantPhone && lead.phone) { const r = values.findIndex((r, i) => i>0 && strip( String(r[iPhone]) ) === strip(lead.phone)); if (r !== -1) return r+1; } if (wantEmail && lead.email) { const r = values.findIndex((r, i) => i>0 && String(r[iEmail]).toLowerCase() === String(lead.email).toLowerCase()); if (r !== -1) return r+1; } return -1;}/****************************** * TRANSFORMS ******************************/function normalizeLead(lead) { const cleaned = Object.assign({}, lead); // Normalize booleans/strings cleaned.email_valid = !!lead.email_valid; // Ensure JSON strings cleaned.order_items_json = lead.order_items_json || JSON.stringify(lead.order_items || []); // Default currency if total provided if (cleaned.order_total_cents && !cleaned.order_currency) cleaned.order_currency = "usd"; // Notes safe if (typeof cleaned.notes !== "string") cleaned.notes = JSON.stringify(cleaned.notes || ""); // Raw cleaned.raw_json = JSON.stringify(lead || {}); // Timestamps cleaned.ts = cleaned.ts || isoNow(); return cleaned;}function leadToRow(lead, map) { const row = new Array(HEADERS.length).fill(""); HEADERS.forEach((h, i) => { if (h in lead) row[i] = lead[h]; }); // Ensure common fields are mapped even if caller used slightly different names row[map["source"]-1] = row[map["source"]-1] || lead.source || ""; row[map["agent"]-1] = row[map["agent"]-1] || lead.agent || ""; row[map["name"]-1] = row[map["name"]-1] || lead.name || ""; row[map["phone"]-1] = row[map["phone"]-1] || lead.phone || ""; row[map["email"]-1] = row[map["email"]-1] || lead.email || ""; row[map["intent"]-1] = row[map["intent"]-1] || lead.intent || ""; return row;}/****************************** * SHEET UTIL ******************************/function ensureSheet(ss, name, headerRow) { const sh = ss.getSheetByName(name) || ss.insertSheet(name); if (sh.getLastRow() === 0) sh.appendRow(headerRow); else ensureHeaders(sh, headerRow); return sh;}function ensureHeaders(sh, headerRow) { const existing = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0]; const missing = headerRow.filter(h => existing.indexOf(h) === -1); if (missing.length) { sh.insertColumnsAfter(existing.length, missing.length); sh.getRange(1, existing.length+1, 1, missing.length).setValues([missing]); }}function headerMap(sh) { const headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0]; const map = {}; headers.forEach((h, i) => map[h] = i+1); return map;}/****************************** * EVENTS LOG ******************************/function logEvent(ev, event, key, details) { ev.appendRow([isoNow(), event, JSON.stringify(key || {}), JSON.stringify(details || {})]);}/****************************** * HELPERS ******************************/function isoNow() { return Utilities.formatDate(new Date(), TIMEZONE, "yyyy-MM-dd'T'HH:mm:ssXXX");}function strip(s) { return String(s || "").replace(/\D/g, ""); }function jsonOut(obj, code) { const out = ContentService.createTextOutput(JSON.stringify(obj)); out.setMimeType(ContentService.MimeType.JSON); if (code) { // Apps Script can't set HTTP status directly; encode in payload return out; } return out;}