google-apps-script.js

// ============================================================
// Google Apps Script — Conclave Access Backend  (v2)
// ============================================================
//
// Setup steps:
//   1. Open the Google Spreadsheet that your Google Form writes to.
//
//   2. Set FORM_SHEET_NAME below to the exact tab name where form
//      responses land (right-click the tab at the bottom to check).
//      The default is "Form Responses 1".
//
//   3. Leave APP_SHEET_NAME as "visitors" — the script creates it
//      automatically on first use.
//
//   4. Go to Extensions > Apps Script, paste this entire file,
//      replacing any existing code.
//
//   5. Click Deploy > New deployment > Web app.
//         Execute as: Me   |   Who has access: Anyone
//      Authorise and copy the Web App URL.
//
//   6. Paste that URL into the app's Settings → Live Sync field
//      and click "Load Visitors from Sheet".
//
// How it works:
//   • GET (no ?action)   → merges form-sheet registrations with
//                          attendance data from the app sheet.
//   • GET ?action=...    → writes an attendance update to the app
//                          sheet and returns { success: true }.
//   • POST               → kept for backward compatibility.
// ============================================================

const FORM_SHEET_NAME = 'Form Responses 1'; // ← change if your tab is named differently
const APP_SHEET_NAME  = 'visitors';         // created automatically; do not change

// ── Spreadsheet IDs ───────────────────────────────────────────────────────────
//
// Set FORM_SPREADSHEET_ID to the ID of the spreadsheet that contains your
// Google Form responses.  The ID is the long string in the spreadsheet URL:
//   https://docs.google.com/spreadsheets/d/  ← THIS PART →  /edit
//
// APP_SPREADSHEET_ID is where attendance data is written.
// Set it to the same ID as FORM_SPREADSHEET_ID to keep everything in one file,
// or to a different ID if you want a separate attendance sheet.
//
// Leave both as '' to fall back to the bound spreadsheet (original behaviour).

const FORM_SPREADSHEET_ID = '1197WqfIqr5vOrxyF07sut3zPr_OxX7G4CU0lM5PHS3M';
const APP_SPREADSHEET_ID  = '1197WqfIqr5vOrxyF07sut3zPr_OxX7G4CU0lM5PHS3M'; // same sheet

// ── Spreadsheet helpers ───────────────────────────────────────────────────────

function getFormSpreadsheet() {
  return FORM_SPREADSHEET_ID
    ? SpreadsheetApp.openById(FORM_SPREADSHEET_ID)
    : SpreadsheetApp.getActiveSpreadsheet();
}

function getAppSpreadsheet() {
  return APP_SPREADSHEET_ID
    ? SpreadsheetApp.openById(APP_SPREADSHEET_ID)
    : SpreadsheetApp.getActiveSpreadsheet();
}

// ── Entry points ──────────────────────────────────────────────────────────────

function doGet(e) {
  const action   = (e.parameter && e.parameter.action)   || '';
  const callback = (e.parameter && e.parameter.callback) || '';

  // e.parameter values are already URL-decoded by Apps Script — no decodeURIComponent needed
  if (action === 'markPaid') {
    const v = e.parameter.data ? tryParseJSON(e.parameter.data) : null;
    return performUpdate(e.parameter.phone, 'isPaid', true, callback, v);
  }
  if (action === 'checkIn') {
    const v = e.parameter.data ? tryParseJSON(e.parameter.data) : null;
    return performUpdate(e.parameter.phone, 'isCheckedIn', true, callback, v);
  }
  if (action === 'addVisitor') {
    const v = tryParseJSON(e.parameter.data || '{}');
    return performAdd(v, callback);
  }

  // Debug endpoint — open ?action=debug in browser to diagnose merge issues
  if (action === 'debug') {
    return respond(getDebugInfo(), callback);
  }

  // Default — load and return the merged visitor list
  try {
    const visitors = getMergedVisitors();
    return respond({ visitors }, callback);
  } catch (err) {
    return respond({ error: err.message, visitors: [] }, callback);
  }
}

// Kept for backward compatibility with the previous version of the app
function doPost(e) {
  try {
    const body = JSON.parse(e.postData.contents);
    if (body.action === 'markPaid')   return performUpdate(body.id, 'isPaid',      true, '');
    if (body.action === 'checkIn')    return performUpdate(body.id, 'isCheckedIn', true, '');
    if (body.action === 'addVisitor') return performAdd(body.visitor, '');
    return respond({ error: 'Unknown action: ' + body.action }, '');
  } catch (err) {
    return respond({ error: err.message }, '');
  }
}

// ── Debug ─────────────────────────────────────────────────────────────────────

function getDebugInfo() {
  const formSS    = getFormSpreadsheet();
  const appSS     = getAppSpreadsheet();
  const allSheets = formSS.getSheets().map(s => s.getName());

  // Form sheet detection
  const formSheet = formSS.getSheetByName(FORM_SHEET_NAME)
    || formSS.getSheets().find(s => s.getName().toLowerCase() === FORM_SHEET_NAME.toLowerCase())
    || formSS.getSheets().find(s => s.getName().toLowerCase().includes('response'));

  const formHeaders  = formSheet ? formSheet.getRange(1, 1, 1, formSheet.getLastColumn()).getValues()[0] : [];
  const formRaw      = formSheet && formSheet.getLastRow() > 1
    ? formSheet.getRange(2, 1, Math.min(3, formSheet.getLastRow() - 1), formSheet.getLastColumn()).getValues()
    : [];

  // Column detection
  const headers = formHeaders.map(h => String(h).trim().toLowerCase());
  const find = (...keys) => headers.findIndex(h => keys.some(k => h.includes(k)));
  const phoneIdx    = find('phone', 'mobile', 'whatsapp');
  const nameIdx     = find('name');
  const businessIdx = headers.findIndex(h =>
    ['business', 'company', 'org', 'firm', 'brand', 'startup', 'venture'].some(k => h.includes(k)) &&
    !h.includes('phone') && !h.includes('mobile') && !h.includes('number')
  );

  // App sheet
  const appSheet  = appSS.getSheetByName(APP_SHEET_NAME);
  const appHeaders = appSheet ? appSheet.getRange(1, 1, 1, appSheet.getLastColumn()).getValues()[0] : [];
  const appRaw     = appSheet && appSheet.getLastRow() > 1
    ? appSheet.getRange(2, 1, Math.min(5, appSheet.getLastRow() - 1), appSheet.getLastColumn()).getValues()
    : [];

  // Sample form visitors with detected phone values
  const sampleForm = formRaw.map(row => ({
    name:  nameIdx  >= 0 ? row[nameIdx]  : '(nameIdx=-1)',
    phone: phoneIdx >= 0 ? row[phoneIdx] : '(phoneIdx=-1)',
    normalizedPhone: normalizePhone(phoneIdx >= 0 ? row[phoneIdx] : ''),
  }));

  // App sheet phone values
  const h   = appHeaders.map(c => String(c).trim());
  const col = k => h.indexOf(k);
  const sampleApp = appRaw.map(row => ({
    id:     row[col('id')],
    phone:  row[col('phone')],
    normalizedPhone: normalizePhone(row[col('phone')]),
    isPaid: row[col('isPaid')],
  }));

  return {
    allSheets,
    formSheetFound: formSheet ? formSheet.getName() : null,
    formHeaders,
    detectedColumns: { nameIdx, phoneIdx, businessIdx },
    sampleFormRows: sampleForm,
    appSheetFound: appSheet ? appSheet.getName() : null,
    appHeaders,
    sampleAppRows: sampleApp,
  };
}

// ── Load & merge ───────────────────────────────────────────────────────────────
//
// Visitor IDs are derived from the row number in the form sheet:
//   row 2 → id "form-2",  row 3 → id "form-3", etc.
//
// Merging uses PHONE NUMBER as the common key between the form sheet and the
// app sheet, because the row-based "form-N" id can be unreliable if the
// app-sheet rows were ever written incorrectly.  Phone is present in both sheets
// and is stable.  Walk-ins (id starts with "walkin-") are stored only in the
// app sheet and are matched by id as before.

// Normalise a phone number to its last 10 digits for reliable comparison.
function normalizePhone(p) {
  return String(p || '').replace(/\D/g, '').slice(-10);
}

function getMergedVisitors() {
  const formSS = getFormSpreadsheet();
  const appSS  = getAppSpreadsheet();

  // ── 1.  Read registrations from the Google Form sheet ──────────────────────
  // Try exact name first, then case-insensitive, then any sheet whose name
  // contains "response" (covers "Form Responses 1", "Responses", etc.).
  const allSheets = formSS.getSheets();
  const formSheet = formSS.getSheetByName(FORM_SHEET_NAME)
    || allSheets.find(s => s.getName().toLowerCase() === FORM_SHEET_NAME.toLowerCase())
    || allSheets.find(s => s.getName().toLowerCase().includes('response'));
  const formVisitors = [];

  if (formSheet && formSheet.getLastRow() > 1) {
    const rows    = formSheet.getDataRange().getValues();
    const headers = rows[0].map(h => String(h).trim().toLowerCase());

    // Fuzzy column finder — handles "Full Name", "Contact Email", etc.
    const find = (...keys) =>
      headers.findIndex(h => keys.some(k => h.includes(k)));

    const nameIdx     = find('name');
    const emailIdx    = find('email');
    // Use only specific phone keywords — 'contact' and 'number' are too generic
    // and can match "Contact Email" or "Registration Number" etc.
    const phoneIdx    = find('phone', 'mobile', 'whatsapp');
    // Exclude columns that also contain phone-related words to avoid
    // matching "Business Phone Number" as the business name column.
    const businessIdx = headers.findIndex(h =>
      ['business', 'company', 'org', 'firm', 'brand', 'startup', 'venture'].some(k => h.includes(k)) &&
      !h.includes('phone') && !h.includes('mobile') && !h.includes('number')
    );
    const paidIdx     = find('payment', 'paid', 'fee', 'amount');

    for (let i = 1; i < rows.length; i++) {
      if (!rows[i] || rows[i].every(c => !c)) continue;
      const paidStr = paidIdx >= 0 ? String(rows[i][paidIdx] || '').toLowerCase() : '';
      formVisitors.push({
        id:           'form-' + (i + 1),
        name:         nameIdx     >= 0 ? String(rows[i][nameIdx]     || '').trim() : 'Visitor ' + i,
        email:        emailIdx    >= 0 ? String(rows[i][emailIdx]    || '').trim() : '',
        phone:        phoneIdx    >= 0 ? String(rows[i][phoneIdx]    || '').trim() : '',
        businessName: businessIdx >= 0 ? String(rows[i][businessIdx] || '').trim() : '',
        isPaid:       /yes|paid|true|done/.test(paidStr),
        isCheckedIn:  false,
      });
    }
  }

  // ── 2.  Read attendance overrides from the app sheet ────────────────────────
  // Phone is the single unique key.  Every row is indexed by normalised phone.
  // Rows whose phone doesn't match any form registrant are included as extras
  // (walk-ins added manually, or pre-event entries from the previous app version).
  const appSheet   = ensureAppSheet(appSS);
  const appByPhone = {}; // normalizedPhone → full row object

  if (appSheet.getLastRow() > 1) {
    const rows = appSheet.getDataRange().getValues();
    const h    = rows[0].map(c => String(c).trim());
    const col  = k => h.indexOf(k);

    for (let i = 1; i < rows.length; i++) {
      if (!rows[i] || rows[i].every(c => !c)) continue;

      const phone  = normalizePhone(rows[i][col('phone')]);
      const isPaid = rows[i][col('isPaid')]      === true || String(rows[i][col('isPaid')]).toLowerCase()      === 'true';
      const isCI   = rows[i][col('isCheckedIn')] === true || String(rows[i][col('isCheckedIn')]).toLowerCase() === 'true';

      if (phone) {
        appByPhone[phone] = {
          id:           String(rows[i][col('id')]           || '').trim(),
          name:         String(rows[i][col('name')]         || '').trim(),
          email:        String(rows[i][col('email')]        || '').trim(),
          phone:        String(rows[i][col('phone')]        || '').trim(),
          businessName: String(rows[i][col('businessName')] || '').trim(),
          isPaid,
          isCheckedIn: isCI,
        };
      }
    }
  }

  // ── 3.  Merge: apply isPaid / isCheckedIn overrides from app sheet ──────────
  const mergedPhones = new Set();
  const merged = formVisitors.map(v => {
    const phoneKey = normalizePhone(v.phone);
    const override = phoneKey && appByPhone[phoneKey];
    if (phoneKey) mergedPhones.add(phoneKey);
    // Only override attendance fields — keep all registration data from the form
    return override
      ? { ...v, isPaid: override.isPaid, isCheckedIn: override.isCheckedIn }
      : v;
  });

  // App rows with no matching form registrant → extras (walk-ins / legacy entries)
  const extras = Object.entries(appByPhone)
    .filter(([phone]) => !mergedPhones.has(phone))
    .map(([, row]) => row);

  return [...merged, ...extras];
}

// ── Utilities ─────────────────────────────────────────────────────────────────

function tryParseJSON(str) {
  try { return JSON.parse(str); } catch (_) { return null; }
}

// ── Write helpers ──────────────────────────────────────────────────────────────

// Phone is the single unique key — looks up the row by normalised phone number.
function performUpdate(phone, field, value, callback, visitorData) {
  try {
    const normalizedTarget = normalizePhone(phone);
    if (!normalizedTarget) return respond({ error: 'Missing phone number' }, callback);

    const sheet        = ensureAppSheet();
    const data         = sheet.getDataRange().getValues();
    const h            = data[0].map(c => String(c).trim());
    const phoneColIdx  = h.indexOf('phone');
    const fIdx         = h.indexOf(field);

    // Update existing row if found
    for (let i = 1; i < data.length; i++) {
      if (normalizePhone(data[i][phoneColIdx]) === normalizedTarget) {
        sheet.getRange(i + 1, fIdx + 1).setValue(value);
        // Backfill any missing fields from the full visitor object
        if (visitorData) {
          ['id', 'name', 'email', 'businessName'].forEach(key => {
            const idx = h.indexOf(key);
            if (idx >= 0 && !data[i][idx] && visitorData[key]) {
              sheet.getRange(i + 1, idx + 1).setValue(visitorData[key]);
            }
          });
        }
        return respond({ success: true }, callback);
      }
    }

    // Row not found — append a new row using visitorData if available
    sheet.appendRow(h.map(col => {
      if (col === 'phone') return phone;
      if (col === field)   return value;
      if (visitorData && visitorData[col] !== undefined) return visitorData[col];
      return '';
    }));
    return respond({ success: true }, callback);
  } catch (err) {
    return respond({ error: err.message }, callback);
  }
}

function performAdd(v, callback) {
  try {
    const sheet   = ensureAppSheet();
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
                         .map(h => String(h).trim());
    sheet.appendRow(headers.map(h => (v[h] !== undefined ? v[h] : '')));
    return respond({ success: true }, callback);
  } catch (err) {
    return respond({ error: err.message }, callback);
  }
}

// Creates the "visitors" sheet with the correct headers if it doesn't exist yet
function ensureAppSheet(ss) {
  if (!ss) ss = getAppSpreadsheet();
  let sheet = ss.getSheetByName(APP_SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(APP_SHEET_NAME);
    sheet.appendRow(['id', 'name', 'email', 'phone', 'businessName', 'isPaid', 'isCheckedIn']);
  }
  return sheet;
}

function respond(data, callback) {
  const json = JSON.stringify(data);
  if (callback) {
    // JSONP — wraps response so browser <script> tag can read it cross-origin
    return ContentService
      .createTextOutput(`${callback}(${json})`)
      .setMimeType(ContentService.MimeType.JAVASCRIPT);
  }
  return ContentService
    .createTextOutput(json)
    .setMimeType(ContentService.MimeType.JSON);
}