GS code
// === CONFIG ===
const SPREADSHEET_ID = '1YeKz2a7Q2BugIzMljA2f1hfiZ0f0FkXrkiaDRAj0La4';
const SHEET_NAME = 'Couriers';
const TRACKING_SHEET = 'Tracking';
const STATUS_LOG_SHEET = 'Status Log';
const MERCHANT_KEY = 'oFsOGp';
const SALT = 'MriTXv7syPM4IH6zCy4KvR2nuewkHXr9';
const BLOGGER_FORM_URL = 'https://mmorder.blogspot.com/p/pay.html';
const SCRIPT_WEBAPP_URL = 'https://script.google.com/macros/s/AKfycbxHs1kka7XVA6g4c-0JPKAwMMHzr-5LyuE-JzxZrcd8tDz9bisbbjNLcjWjCYSQQJclhA/exec';
/** JSON / JSONP helper */
function _serve(data, callback) {
const payload = JSON.stringify(data);
if (callback) {
return ContentService
.createTextOutput(callback + '(' + payload + ');')
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
return ContentService
.createTextOutput(payload)
.setMimeType(ContentService.MimeType.JSON);
}
/** ===== Robust Date Parsers ===== */
/**
* Parse the sheet Timestamp (Column A) to a JS Date.
* Supports:
* - Date objects and sheet serials
* - "dd/MM/yyyy", "dd-MM-yyyy", "dd.MM.yyyy"
* - Optional time "HH:mm[:ss]"
* - ISO-like strings as fallback
*/
function _parseSheetTimestampToDate(ts) {
if (!ts) return null;
if (ts instanceof Date && !isNaN(ts)) return ts;
// Google Sheets serial date
if (typeof ts === 'number') {
var ms = Math.round((ts - 25569) * 86400 * 1000);
var d = new Date(ms);
return isNaN(d) ? null : d;
}
ts = String(ts).trim();
// dd/MM/yyyy or dd-MM-yyyy or dd.MM.yyyy with optional time (HH:mm[:ss])
var m = ts.match(/^(\d{1,2})[\/.\-](\d{1,2})[\/.\-](\d{2,4})(?:[ T](\d{1,2}):(\d{2})(?::(\d{2}))?)?$/);
if (m) {
var d = +m[1], mo = +m[2]-1, y = +m[3]; if (y < 100) y += 2000;
var hh = +(m[4]||0), mm = +(m[5]||0), ss = +(m[6]||0);
var out = new Date(y, mo, d, hh, mm, ss);
return isNaN(out) ? null : out;
}
// ISO-ish fallback
var d2 = new Date(ts);
return isNaN(d2) ? null : d2;
}
/**
* Parse Dispatch Date cell (Column J) into a Date.
* Accepts:
* - Date objects and serials
* - yyyy-MM-dd
* - dd/MM/yyyy
* - ISO-ish fallback
*/
function _parseDispatchCell(v) {
if (v === '' || v === null || v === undefined) return null;
if (v instanceof Date && !isNaN(v)) return v;
if (typeof v === 'number') {
if (v > 10000000000) { // epoch ms
var dEpoch = new Date(v);
return isNaN(dEpoch) ? null : dEpoch;
}
var ms = Math.round((v - 25569) * 86400 * 1000); // Sheets serial
var d = new Date(ms);
return isNaN(d) ? null : d;
}
var s = String(v).trim();
var m = s.match(/^(\d{4})-(\d{2})-(\d{2})$/); // yyyy-MM-dd
if (m) {
var d1 = new Date(+m[1], +m[2]-1, +m[3], 0, 0, 0);
return isNaN(d1) ? null : d1;
}
m = s.match(/^(\d{2})\/(\d{2})\/(\d{4})$/); // dd/MM/yyyy
if (m) {
var d2 = new Date(+m[3], +m[2]-1, +m[1], 0, 0, 0);
return isNaN(d2) ? null : d2;
}
var d3 = new Date(s); // ISO-ish
return isNaN(d3) ? null : d3;
}
function _fmtISODate(d) {
if (!d || isNaN(d)) return '';
var y = d.getFullYear();
var m = String(d.getMonth()+1); if (m.length < 2) m = '0' + m;
var day = String(d.getDate()); if (day.length < 2) day = '0' + day;
return y + '-' + m + '-' + day;
}
/** GET entrypoint */
function doGet(e) {
try {
if (e.parameter.action === 'payu') return handlePayURedirect(e);
if (e.parameter.txnid && e.parameter.status) return handlePayUResponse(e);
if (e.parameter.txnid && !e.parameter.status) return lookupLink(e);
switch (e.parameter.action) {
case 'tracking': return handleTracking(e);
case 'getTrackingMap': return handleTrackingMap(e);
case 'search': return handleSearch(e);
case 'edit': return handleEdit(e.parameter);
case 'delete': return handleDelete(e.parameter);
case 'metrics': return handleMetrics(e);
}
return ContentService.createTextOutput("No valid parameters.")
.setMimeType(ContentService.MimeType.TEXT);
} catch (err) {
return _serve({ error: 'doGet error: ' + err.message, stack: String(err.stack || '') });
}
}
/** POST entrypoint */
function doPost(e) {
try {
if (e.parameter.txnid && e.parameter.status) return handlePayUResponse(e);
var params;
try { params = JSON.parse(e.postData.contents); }
catch (parseErr) { return _serve({ error: 'Invalid JSON body: ' + parseErr.message }); }
if (params.action === 'edit') return handleEdit(params);
return handleInsert(params);
} catch (err) {
return _serve({ error: 'doPost error: ' + err.message, stack: String(err.stack || '') });
}
}
/** 0) PayU redirect */
function handlePayURedirect(e) {
var txnid = e.parameter.txnid, amount = e.parameter.amount, name = e.parameter.name,
hash = e.parameter.hash, surl = e.parameter.surl, furl = e.parameter.furl;
return HtmlService
.createHtmlOutput(
'Redirecting… ' +
''
)
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/** 1) PayU response (once) */
function handlePayUResponse(e) {
var props = PropertiesService.getScriptProperties();
var txnid = (e.parameter.txnid || '').trim();
var status = (e.parameter.status || '').toLowerCase();
var amount = (e.parameter.amount || '').trim();
var tz = Session.getScriptTimeZone();
var ts = props.getProperty(txnid);
if (!ts) {
ts = Utilities.formatDate(new Date(), tz, "dd/MM/yyyy HH:mm:ss");
props.setProperty(txnid, ts);
if (status === 'success') {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var mainSheet = ss.getSheetByName(SHEET_NAME);
var data = mainSheet.getDataRange().getValues();
var recName = '', orderAmt = '';
for (var i = 1; i < data.length; i++) {
if ((data[i][14]||'').toString().trim() === txnid) {
mainSheet.getRange(i+1,14).setValue('Paid');
mainSheet.getRange(i+1,17).setValue('success');
recName = data[i][1];
orderAmt = data[i][10];
break;
}
}
var logSheet = ss.getSheetByName(STATUS_LOG_SHEET);
logSheet.insertRowBefore(2);
logSheet.getRange(2,1,1,5).setValues([[ ts, txnid, status, recName, orderAmt ]]);
logSheet.getRange("A2:A").setNumberFormat("@");
logSheet.getRange("E2:E").setNumberFormat("0.00");
ensureBlankRows(500);
}
}
var dest = 'https://mmorder.blogspot.com/p/success.html?txnid=' + encodeURIComponent(txnid) +
'&amount=' + encodeURIComponent(amount) + '&time=' + encodeURIComponent(ts);
var html = '= sd && ts <= ed)) return false;
}
return true;
});
filtered.sort(function(a,b){
function key(o){
var r = o.row;
if (sortBy === 'dispatchDate') return _parseDispatchCell(r[9]) || new Date(0);
return _parseSheetTimestampToDate(r[0]) || new Date(0); // default: timestamp
}
var ka = key(a).getTime(), kb = key(b).getTime();
return (order === 'asc') ? (ka - kb) : (kb - ka);
});
var total = filtered.length;
var page = filtered.slice(offset, offset + limit);
var tz = Session.getScriptTimeZone();
var rows = page.map(function(item){
var out = item.row.slice();
var disp = _parseDispatchCell(out[9]);
out[9] = disp ? Utilities.formatDate(disp, tz, 'yyyy-MM-dd') : ''; // normalized for HTML date input
return [item.sheetRow].concat(out);
});
return _serve({ rows: rows, total: total }, e.parameter.callback);
}
/** Insert */
function handleInsert(params) {
if (!params.recipientName || !params.orderValue) return _serve({ error:'Missing recipientName/orderValue' });
var tz = Session.getScriptTimeZone();
var tsStr = Utilities.formatDate(new Date(), tz, "dd/MM/yyyy HH:mm:ss");
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = ss.getSheetByName(SHEET_NAME);
var dispDate = null;
if (params.dispatchDate) {
var dt = _parseDispatchCell(params.dispatchDate);
if (dt) dispDate = dt;
}
var txnid='', longUrl='', status='';
if ((params.payment||'') === 'Unpaid') {
txnid = 'TXN' + Date.now();
var udf = [ '', '', '', '', '', '', '', '', '', '' ];
var hashArr = [ MERCHANT_KEY, txnid, String(params.orderValue), 'Order Payment', params.recipientName, 'test@example.com' ]
.concat(udf).concat([ SALT ]);
var hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_512, hashArr.join('|'))
.map(function(b){ return ('0'+(b&0xFF).toString(16)).slice(-2); }).join('');
var surl = SCRIPT_WEBAPP_URL + '?txnid=' + txnid + '&status=success&amount=' + encodeURIComponent(params.orderValue);
var furl = SCRIPT_WEBAPP_URL + '?txnid=' + txnid + '&status=failure&amount=' + encodeURIComponent(params.orderValue);
longUrl = BLOGGER_FORM_URL +
'?txnid=' + encodeURIComponent(txnid) +
'&amount=' + encodeURIComponent(params.orderValue) +
'&name=' + encodeURIComponent(params.recipientName) +
'&hash=' + encodeURIComponent(hash) +
'&surl=' + encodeURIComponent(surl) +
'&furl=' + encodeURIComponent(furl) +
'&service_provider=payu_paisa';
status = 'Pending';
} else {
status = 'Paid';
}
sheet.insertRowBefore(2);
var rowValues = [
tsStr, params.recipientName, params.address||'', params.primaryPhone||'',
params.secondaryPhone||'', params.preferredCourier||'', params.courieredVia||'',
params.trackingNumber||'', params.trackingURL||'', '',
params.orderValue, params.courierCharge||'', params.accountPhone||'',
params.payment||'', txnid, longUrl, status
];
sheet.getRange(2,1,1,rowValues.length).setValues([ rowValues ]);
if (dispDate) sheet.getRange(2,10).setValue(dispDate).setNumberFormat("dd/MM/yyyy");
sheet.getRange("A2:A").setNumberFormat("@");
sheet.getRange("K2:K").setNumberFormat("0.00");
ensureBlankRows(500);
return _serve({ success:true, longUrl: longUrl });
}
/** Edit */
function handleEdit(params) {
try {
if (!params.rowIndex) throw new Error('Missing rowIndex');
var row = parseInt(params.rowIndex,10);
var tz = Session.getScriptTimeZone();
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = ss.getSheetByName(SHEET_NAME);
var st = String(sheet.getRange(row,17).getValue()).toLowerCase();
if (st === 'success') { sheet.getRange(row,14).setValue('Paid'); delete params.payment; }
var map = {
courieredVia:7, trackingNumber:8, trackingURL:9, dispatchDate:10,
orderValue:11, courierCharge:12, accountPhone:13, payment:14, status:17
};
for (var f in map) {
if (params[f] !== undefined) {
var v = params[f];
if (f === 'dispatchDate') {
var d = _parseDispatchCell(v);
if (d) sheet.getRange(row, map[f]).setValue(d).setNumberFormat("dd/MM/yyyy");
else sheet.getRange(row, map[f]).setValue(v);
} else {
sheet.getRange(row, map[f]).setValue(v);
}
}
}
if (params.payment!==undefined || params.status!==undefined) {
var rd = sheet.getRange(row,1,1,17).getValues()[0];
var logSheet = ss.getSheetByName(STATUS_LOG_SHEET);
var ts2 = Utilities.formatDate(new Date(), tz, "dd/MM/yyyy HH:mm:ss");
logSheet.insertRowBefore(2);
logSheet.getRange(2,1,1,5).setValues([[ ts2, rd[14], rd[16], rd[1], rd[10] ]]);
logSheet.getRange("A2:A").setNumberFormat("@");
logSheet.getRange("E2:E").setNumberFormat("0.00");
ensureBlankRows(500);
}
sheet.getRange("A2:A").setNumberFormat("@");
ensureBlankRows(500);
return _serve({ success:true }, params.callback);
} catch(e) {
return _serve({ error:e.message }, params.callback);
}
}
/** Delete */
function handleDelete(params) {
try {
if (!params.rowIndex) throw new Error('Missing rowIndex');
var row = parseInt(params.rowIndex,10);
var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
sheet.deleteRow(row);
ensureBlankRows(500);
return _serve({ success:true }, params.callback);
} catch(e) {
return _serve({ error:e.message }, params.callback);
}
}
/** METRICS — STRICTLY by Timestamp (Column A) */
function handleMetrics(e) {
var start = (e.parameter.startDate || '').trim();
var end = (e.parameter.endDate || '').trim();
if (!start || !end) return _serve({ error: 'Missing startDate or endDate' }, e.parameter.callback);
var sd = new Date(start + 'T00:00:00');
var ed = new Date(end + 'T23:59:59.999');
var rows = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME).getDataRange().getValues();
if (rows.length <= 1) {
return _serve({ totalOrders:0, paymentsReceived:0, totalOrderValue:0, totalCourierCharges:0, totalOutstanding:0 }, e.parameter.callback);
}
var totalOrders = 0, paymentsReceived = 0, totalOrderValue = 0, totalCourierCharges = 0;
for (var i=1;i= sd && ts <= ed)) continue;
totalOrders++;
var ov = parseFloat(String(r[10] || '0').replace(/[^0-9.]/g, '')) || 0; // K
var cc = parseFloat(String(r[11] || '0').replace(/[^0-9.]/g, '')) || 0; // L
var pay = String(r[13] || '').trim().toLowerCase() === 'paid';
totalOrderValue += ov;
totalCourierCharges += cc;
if (pay) paymentsReceived += ov;
}
return _serve({
totalOrders: totalOrders,
paymentsReceived: paymentsReceived,
totalOrderValue: totalOrderValue,
totalCourierCharges: totalCourierCharges,
totalOutstanding: totalOrderValue - paymentsReceived
}, e.parameter.callback);
}
/** Ensure plenty of blank rows */
function ensureBlankRows(count) {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheets = ss.getSheets();
for (var i=0;i0) sheet.insertRowsAfter(max, needed);
}
}