Orders

// Helix Research — Google Apps Script v3 (secured) // Must match SHEET_API_SECRET in the dashboard HTML. const API_SECRET = 'hx_71OKYJvychKpcoE0Evz7pk-6'; // Fields customers are allowed to see via public order lookup. // Deliberately excludes: Name, Venmo, Address, Zip, affiliate + commission data. const PUBLIC_FIELDS = ['Order Number', 'Timestamp', 'Items', 'Total', 'Status', 'Tracking', 'Shipping']; function doPost(e) { // Lock prevents two simultaneous requests from double-appending const lock = LockService.getScriptLock(); lock.waitLock(10000); try { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const d = JSON.parse(e.postData.contents); const rows = sheet.getDataRange().getValues(); const headers = rows[0].map(function(h){ return String(h).trim(); }); const onCol = headers.indexOf('Order Number'); // ── STATUS / TRACKING / NOTES UPDATE (dashboard only — requires secret) ── if (d.action === 'update_order') { if (d.secret !== API_SECRET) { return ContentService.createTextOutput("FORBIDDEN").setMimeType(ContentService.MimeType.TEXT); } if (!d.orderNumber) { return ContentService.createTextOutput("MISSING_ORDER").setMimeType(ContentService.MimeType.TEXT); } const sCol = headers.indexOf('Status'); const nCol = headers.indexOf('Notes'); const tCol = headers.indexOf('Tracking'); for (var i = 1; i < rows.length; i++) { if (String(rows[i][onCol]).trim().toUpperCase() === String(d.orderNumber).trim().toUpperCase()) { if (d.status !== undefined && sCol > -1) sheet.getRange(i+1, sCol+1).setValue(String(d.status).toUpperCase()); if (d.notes !== undefined && nCol > -1) sheet.getRange(i+1, nCol+1).setValue(d.notes); if (d.tracking !== undefined && tCol > -1) sheet.getRange(i+1, tCol+1).setValue(d.tracking); return ContentService.createTextOutput("UPDATED").setMimeType(ContentService.MimeType.TEXT); } } return ContentService.createTextOutput("NOT_FOUND").setMimeType(ContentService.MimeType.TEXT); } // ── NEW ORDER (public — storefront checkout logs here) ── // Idempotent: same order number never logs twice. if (d.orderNumber && onCol > -1) { for (var j = 1; j < rows.length; j++) { if (String(rows[j][onCol]).trim() === String(d.orderNumber).trim()) { return ContentService.createTextOutput("DUPLICATE").setMimeType(ContentService.MimeType.TEXT); } } } // Email lives in its own column, found by header so it never shifts the // existing columns. Header is auto-created the first time an order arrives. var eCol = headers.indexOf('Email'); if (eCol === -1) { eCol = headers.length; sheet.getRange(1, eCol + 1).setValue('Email'); } var newRow = [ d.timestamp, d.orderNumber||'', d.name, d.venmo, d.address, d.zip, d.items, d.subtotal, d.shipping, d.total, d.discountCode, d.affiliateCode, d.affiliateName, d.affiliateVenmo, d.commissionOwed, 'PENDING', '', d.tracking||'', d.paymentMethod||'' ]; while (newRow.length <= eCol) newRow.push(''); newRow[eCol] = d.email || ''; sheet.appendRow(newRow); return ContentService.createTextOutput("OK").setMimeType(ContentService.MimeType.TEXT); } finally { lock.releaseLock(); } } function doGet(e) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const rows = sheet.getDataRange().getValues(); const headers = rows[0].map(function(h){ return String(h).trim(); }); const p = (e && e.parameter) ? e.parameter : {}; // ── PUBLIC: single-order lookup for the customer tracking page ── // Returns ONLY safe fields for the one matching order. No key needed. if (p.orderNumber) { const onCol = headers.indexOf('Order Number'); const want = String(p.orderNumber).trim().toUpperCase(); for (var i = 1; i < rows.length; i++) { if (String(rows[i][onCol]).trim().toUpperCase() === want) { const obj = {}; PUBLIC_FIELDS.forEach(function(f) { const c = headers.indexOf(f); if (c > -1) obj[f] = rows[i][c]; }); // tracking page expects an array it can .find() on return ContentService.createTextOutput(JSON.stringify([obj])) .setMimeType(ContentService.MimeType.JSON); } } return ContentService.createTextOutput(JSON.stringify([])) .setMimeType(ContentService.MimeType.JSON); } // ── PRIVATE: full dump for the dashboard — requires key ── if (p.key !== API_SECRET) { return ContentService.createTextOutput(JSON.stringify({error:'forbidden'})) .setMimeType(ContentService.MimeType.JSON); } const data = rows.slice(1).map(function(r) { const obj = {}; headers.forEach(function(h, i) { obj[h] = r[i]; }); return obj; }); return ContentService.createTextOutput(JSON.stringify(data)) .setMimeType(ContentService.MimeType.JSON); }

Timestamp | Order Number | Name | Venmo | Address | Zip | Items | Subtotal | Shipping | Total | Discount Code | Affiliate Code | Affiliate Name | Affiliate Venmo | Commission Owed | Status | Notes | Tracking | Payment