app.bettersight.io/appscript/Sync.gs

/**
 * Sheet-initiated pull integration. This is the only place data ever
 * moves between Bettersight and a PM's spreadsheet — always initiated
 * from inside the open Sheet (the sidebar's [Pull latest results]
 * button), never from the dashboard or Flask, since a stateless
 * backend has no way to reach into whichever Sheet a PM has open
 * (CLAUDE.md §15).
 *
 * Layout: TRANSPOSED — one row per field (column A), one column per
 * competitor (starting at column B), row 1 reserved for competitor
 * names. This matches the dashboard's Copy rows / Download Excel
 * export exactly (frontend/src/config/resultFields.js), which was
 * itself verified against the real legacy client reference sheet
 * ("G Compete App V1.xlsx") — NOT the column-per-field/row-per-
 * competitor layout CLAUDE.md §15 originally described, which was
 * never reconciled against that real reference file. ROW_DEFS below
 * is a hand-ported mirror of resultFields.js's STANDARD_ROWS/NGS_ROWS
 * — Apps Script runs in a separate sandbox with no module imports, so
 * this list is duplicated rather than shared. Keep both in sync if
 * either changes.
 */

const DEFAULT_TAB_NAME = 'Bettersight Analysis';

function divOrNull_(a, b) {
  if (a == null || b == null || b === 0) return null;
  return a / b;
}

function mulOrNull_(a, b) {
  if (a == null || b == null) return null;
  return a * b;
}

const STANDARD_ROWS = [
  { key: 'company', label: 'Company', source: 'name' },
  { key: 'link', label: 'Link' },
  { key: 'tripName', label: 'Trip Name' },
  { key: 'tripCode', label: 'Trip Code' },
  { key: 'serviceLevel', label: 'Service Level' },
  { key: 'groupSize', label: 'Group size' },
  { key: 'duration', label: 'Duration (days)' },
  { key: 'meals', label: 'Meals' },
  { key: 'startLocation', label: 'Start Location' },
  { key: 'endLocation', label: 'End Location' },
  { key: 'departures', label: '# Departures Offered per year' },
  { key: 'totalInventory', label: 'Total inventory', compute: function (r) { return mulOrNull_(r && r.departures, r && r.groupSize); } },
  { key: 'seasonality', label: 'Seasonality (what are the price bands in terms of dates?)' },
  { key: 'startDays', label: 'Start days of week' },
  { key: 'targetAudience', label: 'Target audience if known' },
  { key: 'hotels', label: 'Hotels (if applicable)' },
  { key: 'activities', label: 'Included Activities' },
  { key: 'relevancy', label: 'Relevancy rating' },
  { key: 'majorityPrice', label: 'Majority Price (USD)' },
  { key: 'priceLow', label: 'Price low (USD)' },
  { key: 'priceHigh', label: 'Price high (USD)' },
  { key: 'pricePerDay', label: 'Price per day (USD)', compute: function (r) { return divOrNull_(r && r.majorityPrice, r && r.duration); } },
  { key: 'comments', label: 'Suggested changes for {company}/ Competitive Comments' },
  { key: 'dateUsed', label: 'Date Used for Majority Price' },
  { key: 'audPrice', label: 'AUD Majority price' },
  { key: 'cadPrice', label: 'CAD Majority price' },
  { key: 'eurPrice', label: 'EUR Majority price' },
  { key: 'gbpPrice', label: 'GBP Majority price' },
  { key: 'audPricePerDay', label: 'AUD Price/day', compute: function (r) { return divOrNull_(r && r.audPrice, r && r.duration); } },
  { key: 'cadPricePerDay', label: 'CAD Price/day', compute: function (r) { return divOrNull_(r && r.cadPrice, r && r.duration); } },
  { key: 'eurPricePerDay', label: 'EUR Price/day', compute: function (r) { return divOrNull_(r && r.eurPrice, r && r.duration); } },
  { key: 'gbpPricePerDay', label: 'GBP Price/day', compute: function (r) { return divOrNull_(r && r.gbpPrice, r && r.duration); } }
];

// Spliced in after 'activities' — matches resultFields.js's insertAfter().
const NGS_EXTRA_ROWS = [
  { key: 'exclusiveAccess', label: 'Exclusive Access/Special Unique Inclusions' },
  { key: 'groupLeader', label: 'Group Leader and/or Local Expert Description' },
  { key: 'sustainability', label: 'Sustainability Inclusions (if applicable)' }
];

function getRowsForTabType_(tabType) {
  if (tabType !== 'NGS') return STANDARD_ROWS;
  const activitiesIndex = STANDARD_ROWS.findIndex(function (r) { return r.key === 'activities'; });
  return STANDARD_ROWS.slice(0, activitiesIndex + 1)
    .concat(NGS_EXTRA_ROWS, STANDARD_ROWS.slice(activitiesIndex + 1));
}

function normaliseHeader_(text) {
  return String(text || '').toLowerCase().replace(/[^a-z0-9]/g, '');
}

/**
 * Substitutes the '{company}' token in the comments row's label.
 * Apps Script has no direct route to the tenant's display name (only
 * the dashboard's Pinia store carries that) — always falls back to
 * 'your company'. A no-op for every other row (no token present).
 */
function formatLabel_(label, companyName) {
  return label.replace('{company}', companyName || 'your company');
}

function rawValue_(row, item) {
  if (row.compute) return row.compute(item.result || {});
  if (row.source === 'name') return item.name;
  return item.result ? item.result[row.key] : undefined;
}

/**
 * Returns every tab name in the currently open workbook — used by the
 * sidebar to populate its own tab selector. Never called by Flask;
 * this only ever runs inside the open Sheet.
 */
function getWorkbookTabs() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function (s) {
    return s.getName();
  });
}

/**
 * Detects the row index for each field by matching normalised labels
 * in column A (rows 2+, row 1 is reserved for competitor names)
 * against `rows` — never hardcoded row positions, so a PM can reorder
 * or delete rows freely. Falls back to `rows`' own order, starting at
 * row 2, only when column A has no recognisable labels at all (e.g. a
 * brand-new blank tab) — writes those labels in on the spot (and sets
 * the 'Field' corner label in A1) so the sheet becomes self-describing
 * from that point on. A PM never has to hand-type the ~30 row labels
 * themselves — leaving the tab blank and pulling once is enough.
 *
 * Data flow:
 *   sheet + rows + companyName → read column A (rows 2..lastRow) →
 *   normalise each cell → for each row def, match against its
 *   (company-substituted) label → { fieldKey: rowNumber } map →
 *   if nothing matched at all: bootstrap column A from `rows` in
 *   order → same map, now fully populated
 */
function detectFieldRows_(sheet, rows, companyName) {
  const lastRow = sheet.getLastRow();
  const rowMap = {};

  if (lastRow >= 2) {
    const columnA = sheet.getRange(2, 1, lastRow - 1, 1).getValues().map(function (r) { return r[0]; });
    const normalisedColumn = columnA.map(normaliseHeader_);

    rows.forEach(function (row) {
      const target = normaliseHeader_(formatLabel_(row.label, companyName));
      for (let i = 0; i < normalisedColumn.length; i++) {
        if (normalisedColumn[i] && normalisedColumn[i] === target) {
          rowMap[row.key] = i + 2; // +2: back to 1-indexed, offset past row 1
          break;
        }
      }
    });
  }

  const foundAny = Object.keys(rowMap).length > 0;
  if (!foundAny) {
    Logger.log('detectFieldRows_: no recognisable row labels in column A — bootstrapping a fresh template');
    sheet.getRange(1, 1).setValue('Field');
    rows.forEach(function (row, i) {
      const rowNumber = i + 2;
      sheet.getRange(rowNumber, 1).setValue(formatLabel_(row.label, companyName));
      rowMap[row.key] = rowNumber;
    });
  }

  return rowMap;
}

/**
 * Writes one column per competitor into the sheet, using rowMap from
 * detectFieldRows_(). Only writes fields present in rowMap — a row the
 * PM deleted from the template is silently skipped, same resilience
 * rule the old column-based model used.
 *
 * Every pull REPLACES the previously-written competitor columns
 * (clears columns B onward first) rather than appending indefinitely
 * — this tab always reflects the single most recent run, same as the
 * dashboard's results view. Column A's row labels are left untouched.
 */
function writeResultsToSheet_(results, rowMap, rows, sheet) {
  const successItems = (results && results.success) || [];

  const lastColumn = sheet.getLastColumn();
  if (lastColumn > 1) {
    sheet.getRange(1, 2, sheet.getMaxRows(), lastColumn - 1).clearContent();
  }

  successItems.forEach(function (item, index) {
    const col = index + 2; // column A is labels, competitors start at column B
    sheet.getRange(1, col).setValue(item.name);

    rows.forEach(function (row) {
      const rowNumber = rowMap[row.key];
      if (!rowNumber) return;
      const value = rawValue_(row, item);
      sheet.getRange(rowNumber, col).setValue(value == null ? '' : value);
    });
  });

  return successItems.length;
}

/**
 * Fetches the PM's most recent completed analysis from Flask and
 * writes it into the selected tab of the currently open workbook.
 * Sheet-initiated only — cannot be triggered by any external server.
 *
 * Data flow:
 *   validateLicence() → confirms authorisation →
 *   GET {BETTERSIGHT_API_BASE_URL}/research/history/latest?email=... →
 *   stored results + tab_type returned → target tab resolved (creates
 *   it if missing) → getRowsForTabType_(tab_type) selects the
 *   Standard/NGS row list → detectFieldRows_() → writeResultsToSheet_() →
 *   last-pull status saved to UserProperties, sync_from_sheet
 *   onboarding flag flipped on Flask on first success →
 *   { success, rowsWritten, summary } returned to the sidebar
 */
function pullLatestResults(tabName) {
  if (!validateLicence()) {
    return { success: false, message: 'Licence check failed — see the alert for details.' };
  }

  const email = Session.getActiveUser().getEmail();
  let response;
  try {
    response = UrlFetchApp.fetch(
      `${BETTERSIGHT_API_BASE_URL}/research/history/latest?email=${encodeURIComponent(email)}`,
      {
        method: 'get',
        headers: { 'X-API-Key': BETTERSIGHT_API_KEY },
        muteHttpExceptions: true
      }
    );
  } catch (e) {
    return { success: false, message: 'Could not reach Bettersight — check your connection and try again.' };
  }

  if (response.getResponseCode() === 404) {
    return { success: false, message: 'No completed analysis runs yet — run one from the dashboard first.' };
  }
  if (response.getResponseCode() >= 400) {
    return { success: false, message: 'Bettersight returned an error fetching your latest results.' };
  }

  const run = JSON.parse(response.getContentText());

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetTabName = tabName || DEFAULT_TAB_NAME;
  let sheet = spreadsheet.getSheetByName(targetTabName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(targetTabName);
  }

  const rows = getRowsForTabType_(run.tab_type);
  const rowMap = detectFieldRows_(sheet, rows, null);
  const rowsWritten = writeResultsToSheet_(run.results, rowMap, rows, sheet);

  const summary = `${rowsWritten} competitor${rowsWritten === 1 ? '' : 's'}`;
  const timestamp = new Date().toLocaleString();
  const statusLine = `Pulled: ${timestamp} · ${summary}`;
  PropertiesService.getUserProperties().setProperty('LAST_PULL_STATUS', statusLine);

  markOnboardingStepOnce_('sync_from_sheet');

  return { success: true, rowsWritten: rowsWritten, summary: statusLine };
}

/**
 * Returns the last-pull status line saved by pullLatestResults(), for
 * the sidebar to show immediately on open without waiting for a pull.
 */
function getLastPullStatus() {
  return PropertiesService.getUserProperties().getProperty('LAST_PULL_STATUS') || '';
}