Private Sheet Sync Setup

const SYNC_URL = 'https://dashmetric.ecomrevup.com/api/sheet-sync/cmo7qr22f0000xyf0e2lzrlg6';
const WEBHOOK_SECRET = 'cmo7qr22g0001xyf0ltss8jt6';

function syncDashboardData() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();
  const tabs = sheets.map((sheet) => sheet.getName());
  const rows = [];
  const metricMap = {};

  sheets.forEach((sheet) => {
    const values = sheet.getDataRange().getDisplayValues();
    if (values.length < 2) return;

    const headers = values[0].map((header) => normalizeKey_(header));
    for (let rowIndex = 1; rowIndex < values.length; rowIndex += 1) {
      const cells = values[rowIndex];
      if (!cells.some((cell) => String(cell).trim() !== '')) continue;

      const record = {
        id: sheet.getName() + '-' + (rowIndex + 1),
        sourceTab: sheet.getName(),
        date: null,
        year: null,
        monthNumber: null,
        weekNumber: null,
        dayOfMonth: null,
        sku: null,
        asin: null,
        metrics: {}
      };

      headers.forEach((header, index) => {
        const value = cells[index];
        if (value === '' || value == null) return;

        if (matches_(header, ['date', 'report_date', 'calendar_date'])) {
          record.date = normalizeDate_(value);
          return;
        }
        if (matches_(header, ['year', 'year_number'])) {
          record.year = toNumber_(value);
          return;
        }
        if (matches_(header, ['month', 'month_number', 'month_no'])) {
          record.monthNumber = toNumber_(value);
          return;
        }
        if (matches_(header, ['week', 'week_number', 'week_no'])) {
          record.weekNumber = toNumber_(value);
          return;
        }
        if (matches_(header, ['day', 'day_of_month', 'day_number'])) {
          record.dayOfMonth = toNumber_(value);
          return;
        }
        if (matches_(header, ['sku', 'seller_sku', 'child_sku'])) {
          record.sku = String(value);
          return;
        }
        if (matches_(header, ['asin', 'child_asin', 'parent_asin'])) {
          record.asin = String(value);
          return;
        }

        const numeric = parseMetric_(value);
        if (numeric == null) return;
        record.metrics[header] = numeric;
        if (!metricMap[header]) {
          metricMap[header] = inferMetric_(header);
        }
      });

      if (Object.keys(record.metrics).length > 0) {
        rows.push(record);
      }
    }
  });

  const payload = {
    spreadsheetId: spreadsheet.getId(),
    spreadsheetTitle: spreadsheet.getName(),
    tabs: tabs,
    metricCatalog: Object.keys(metricMap).map((key) => ({
      key: key,
      label: metricMap[key].label,
      format: metricMap[key].format,
      aggregation: metricMap[key].aggregation
    })),
    rows: rows
  };

  const response = UrlFetchApp.fetch(SYNC_URL, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'x-webhook-secret': WEBHOOK_SECRET
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  Logger.log(response.getResponseCode());
  Logger.log(response.getContentText());
}

function normalizeKey_(value) {
  return String(value || '')
    .trim()
    .toLowerCase()
    .replace(/[\s/()-]+/g, '_')
    .replace(/_+/g, '_')
    .replace(/^_|_$/g, '');
}

function matches_(value, aliases) {
  return aliases.indexOf(value) !== -1;
}

function toNumber_(value) {
  const parsed = Number(String(value).replace(/[^0-9.-]/g, ''));
  return Number.isFinite(parsed) ? parsed : null;
}

function normalizeDate_(value) {
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return Utilities.formatDate(value, 'UTC', 'yyyy-MM-dd');
  }
  const text = String(value).trim();
  if (!text) return null;
  return text;
}

function parseMetric_(value) {
  const text = String(value).trim();
  if (!text) return null;
  const isPercent = text.indexOf('%') !== -1;
  const parsed = Number(text.replace(/[$,₹€£%\s]/g, ''));
  if (!Number.isFinite(parsed)) return null;
  return isPercent ? parsed / 100 : parsed;
}

function inferMetric_(key) {
  if (/(acos|ctr|cvr|rate|share|margin|percent|percentage|tacos|roas_)/i.test(key)) {
    return { label: titleize_(key), format: 'percent', aggregation: 'average' };
  }
  if (/(roas|aov|cpc|cpm|rpc|value|multiplier)/i.test(key)) {
    return { label: titleize_(key), format: 'decimal', aggregation: 'average' };
  }
  if (/(revenue|sales|spend|cost|fees|profit|amount|budget)/i.test(key)) {
    return { label: titleize_(key), format: 'currency', aggregation: 'sum' };
  }
  if (/(orders|units|clicks|impressions|sessions|page_views|purchases)/i.test(key)) {
    return { label: titleize_(key), format: 'integer', aggregation: 'sum' };
  }
  return { label: titleize_(key), format: 'decimal', aggregation: 'sum' };
}

function titleize_(value) {
  return value
    .split('_')
    .filter(Boolean)
    .map((part) => part.charAt(0).toUpperCase() + part.slice(1))
    .join(' ');
}