Stored locally in your browser. See setup at the bottom of the page.
Loading dashboard data…
Loading orders…
ClearSight_Orders.xlsx into it. Right-click → Open with → Google Sheets → File → Save as Google Sheets.const SHEET_NAME = 'Orders';
// Normalize phone to digits-only for consistent matching
function normPhone(p) {
if (!p) return '';
return String(p).replace(/\D/g, '').replace(/^91(\d{10})$/, '$1');
}
function doPost(e) {
const lock = LockService.getScriptLock();
try {
lock.waitLock(10000); // wait up to 10s for concurrent writes
} catch (err) {
return json({ ok: false, error: 'Server busy, please retry' });
}
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
if (!sheet) throw new Error('Sheet "Orders" not found');
const d = JSON.parse(e.postData.contents);
if (d.action === 'update_status') {
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (String(data[i][1]) === String(d.orderNo)) {
sheet.getRange(i + 1, 30).setValue(d.status);
if (d.pickup_date) sheet.getRange(i + 1, 29).setValue(d.pickup_date);
SpreadsheetApp.flush();
return json({ ok: true, updated: true });
}
}
return json({ ok: false, error: 'Order not found' });
}
// Check for duplicate Order No (atomic under lock)
const lastRow = sheet.getLastRow();
if (lastRow >= 2 && d.orderNo) {
const existing = sheet.getRange(2, 2, lastRow - 1, 1).getValues();
for (let i = 0; i < existing.length; i++) {
if (String(existing[i][0]) === String(d.orderNo)) {
return json({ ok: false, error: 'Duplicate order number: ' + d.orderNo });
}
}
}
sheet.appendRow([
new Date(),
d.orderNo, d.date, d.name, normPhone(d.phone),
d.re_sph, d.re_cyl, d.re_axis, d.re_add,
d.le_sph, d.le_cyl, d.le_axis, d.le_add,
d.a_size, d.b_size, d.dbl,
d.rpd, d.lpd, d.fh,
d.pt_vd, d.wa, d.ed,
d.frame, d.lens,
Number(d.total_amount) || 0,
Number(d.advance) || 0,
Number(d.balance) || 0,
d.delivery_date, d.pickup_date,
d.status || 'Pending',
d.signature, d.order_notes
]);
SpreadsheetApp.flush();
return json({ ok: true, orderNo: d.orderNo });
} catch (err) {
return json({ ok: false, error: String(err) });
} finally {
lock.releaseLock();
}
}
function doGet(e) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
if (!sheet) throw new Error('Sheet "Orders" not found');
const data = sheet.getDataRange().getValues();
if (data.length < 2) return json({ ok: true, headers: data[0] || [], rows: [] });
return json({ ok: true, headers: data[0], rows: data.slice(1).filter(r => r[1]) });
} catch (err) {
return json({ ok: false, error: String(err) });
}
}
function json(obj) {
return ContentService.createTextOutput(JSON.stringify(obj))
.setMimeType(ContentService.MimeType.JSON);
}💡 If you ever change the Apps Script, you must deploy a new version (Deploy → Manage deployments → ✏️ Edit → Version: New version → Deploy). The URL stays the same.