SETTLEMENT TRACKER

📋 Google Apps Script Setup Guide

Create a new GAS project at script.google.com, paste the code below, deploy as Web App (Anyone can access), then paste the URL above.

// ════════════════════════════════════════════════════════════
//  IBI Settlement Tracker — Google Apps Script Backend
//  Paste into script.google.com → New Project → Save
//  Deploy → New Deployment → Web App
//    Execute as  : Me
//    Who can access : Anyone   ← IMPORTANT (not "Anyone with Google account")
//  Copy the Web App URL into the tool's GAS Endpoint field
// ════════════════════════════════════════════════════════════

const SHEET_ID  = 'YOUR_GOOGLE_SHEET_ID_HERE';  // ← paste your Sheet ID here
const SHEET_TAB = 'Settlements';

const HEADERS = [
  'Timestamp','Platform','Order ID','Order Date','Product',
  'SKU','Qty','Selling Price (₹)','Courier Charges (₹)','All Fees (₹)','Net Settlement (₹)',
  'Settlement Date','Status','Days Since Order','Remarks'
];

// ── CORS helper — must wrap every response ───────────────────
function corsResponse(data) {
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

// ── Handle POST (data export from the web tool) ──────────────
function doPost(e) {
  try {
    // Works whether browser sends Content-Type: text/plain or application/json
    const raw     = e.postData ? e.postData.contents : '{}';
    const payload = JSON.parse(raw);

    const ss    = SpreadsheetApp.openById(SHEET_ID);
    let   sheet = ss.getSheetByName(SHEET_TAB);

    if (!sheet) {
      sheet = ss.insertSheet(SHEET_TAB);
      const hdrRange = sheet.getRange(1, 1, 1, HEADERS.length);
      hdrRange.setValues([HEADERS]);
      hdrRange.setBackground('#0D1B2A')
              .setFontColor('#00D4F0')
              .setFontWeight('bold')
              .setFontSize(10);
      sheet.setFrozenRows(1);
      sheet.setColumnWidth(3, 160);   // Order ID
      sheet.setColumnWidth(5, 200);   // Product
    }

    const rows = payload.rows || [];
    const ts   = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd-MM-yyyy HH:mm:ss');

    const data = rows.map(r => [
      ts,
      r.platform              || '',
      r.orderId               || '',
      r.orderDate             || '',
      r.product               || '',
      r.sku                   || '',
      r.qty                   || '',
      Number(r.sellingPrice)  || 0,
      Number(r.courierCharge) || 0,
      Number(r.fees)          || 0,
      Number(r.netSettlement) || 0,
      r.settlementDate        || '',
      r.status                || '',
      r.daysSinceOrder        || '',
      r.remarks               || ''
    ]);

    if (data.length > 0) {
      const lastRow = sheet.getLastRow();
      sheet.getRange(lastRow + 1, 1, data.length, HEADERS.length).setValues(data);

      // Colour-code the Status column (col 12)
      data.forEach((row, i) => {
        const cell   = sheet.getRange(lastRow + 1 + i, 13);
        const status = row[12];
        if (status === 'Settled')   { cell.setBackground('#d4edda'); cell.setFontColor('#155724'); }
        if (status === 'Pending')   { cell.setBackground('#fff3cd'); cell.setFontColor('#856404'); }
        if (status === 'Returned')  { cell.setBackground('#f8d7da'); cell.setFontColor('#721c24'); }
        if (status === 'Cancelled') { cell.setBackground('#e2e3e5'); cell.setFontColor('#383d41'); }

        // Bold net settlement column
        sheet.getRange(lastRow + 1 + i, 10).setFontWeight('bold');
      });

      // Auto-resize amount columns
      [8,9,10].forEach(col => sheet.autoResizeColumn(col));
    }

    return corsResponse({ ok: true, rows: data.length, ts: ts });

  } catch(err) {
    return corsResponse({ ok: false, error: err.message });
  }
}

// ── Handle GET (ping / health-check) ────────────────────────
function doGet(e) {
  return corsResponse({ ok: true, msg: 'IBI Settlement Tracker GAS is live', ts: new Date().toISOString() });
}