// === 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';
// Update this with your latest Deployment URL if needed
// Update this with your latest Deployment URL if needed
// const SCRIPT_WEBAPP_URL = '...'; // REMOVED: Using dynamic URL extraction
/** 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 ===== */
function _parseSheetTimestampToDate(ts) {
if (!ts) return null;
if (ts instanceof Date && !isNaN(ts)) return ts;
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();
// Support DD/MM/YYYY HH:mm:ss, DD-MM-YYYY, YYYY-MM-DD
// Regex for DD/MM/YYYY or DD-MM-YYYY
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;
}
// Fallback for ISO YYYY-MM-DD
var iso = ts.match(/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?)?$/);
if (iso) {
var y = +iso[1], mo = +iso[2]-1, d = +iso[3];
var hh = +(iso[4]||0), mm = +(iso[5]||0), ss = +(iso[6]||0);
return new Date(y, mo, d, hh, mm, ss);
}
var d2 = new Date(ts);
return isNaN(d2) ? null : d2;
}
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) {
var dEpoch = new Date(v);
return isNaN(dEpoch) ? null : dEpoch;
}
var ms = Math.round((v - 25569) * 86400 * 1000);
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})$/);
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})$/);
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);
return isNaN(d3) ? null : d3;
}
/**
* SETUP FUNCTION: Run this ONCE manually to create the Mapping Sheet
*/
function setupUPIMapping() {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = ss.getSheetByName('UPI Mapping');
if (!sheet) {
sheet = ss.insertSheet('UPI Mapping');
} else {
sheet.clear();
}
var headers = [['UPI Handle (vpa suffix)', 'App Name']];
var data = [
// GPay
['okaxis', 'GPay (UPI)'], ['okicici', 'GPay (UPI)'], ['oksbi', 'GPay (UPI)'], ['okhdfc', 'GPay (UPI)'],
['okhdfcbank', 'GPay (UPI)'], ['okbank', 'GPay (UPI)'],
// PhonePe
['ybl', 'PhonePe (UPI)'], ['ibl', 'PhonePe (UPI)'], ['axl', 'PhonePe (UPI)'],
// Paytm
['paytm', 'Paytm (UPI)'], ['ptyes', 'Paytm (UPI)'], ['ptaxis', 'Paytm (UPI)'],
['pticici', 'Paytm (UPI)'], ['pthdfc', 'Paytm (UPI)'], ['ptsbi', 'Paytm (UPI)'],
// Amazon Pay
['apl', 'Amazon Pay (UPI)'], ['yapl', 'Amazon Pay (UPI)'], ['rapl', 'Amazon Pay (UPI)'],
// WhatsApp Pay
['waaxis', 'WhatsApp (UPI)'], ['wahdfc', 'WhatsApp (UPI)'], ['waicici', 'WhatsApp (UPI)'], ['wasbi', 'WhatsApp (UPI)'],
// BHIM
['upi', 'BHIM (UPI)'], ['bhim', 'BHIM (UPI)'],
// Fintech Apps
['cred', 'Cred (UPI)'], ['yescred', 'Cred (UPI)'], ['yescurie', 'Cred (UPI)'],
['sliceaxis', 'Slice (UPI)'], ['slicepay', 'Slice (UPI)'], ['slc', 'Slice (UPI)'],
['fam', 'Fampay (UPI)'], ['yesfam', 'Fampay (UPI)'],
['jupiteraxis', 'Jupiter (UPI)'],
['bpunity', 'BharatPe (UPI)'],
['pz', 'Payzapp (UPI)'],
['zoicici', 'Zomato (UPI)'],
['trans', 'Cheq (UPI)'],
['payu', 'Citrus (UPI)'],
['yespay', 'YesPay (UPI)'],
['yespop', 'POPClub (UPI)'],
['shriramhdfcbank', 'Shriram One (UPI)'],
['inhdfc', 'IND Money (UPI)'],
['seyes', 'Salary Se (UPI)'],
['timecosmos', 'Timepay (UPI)'],
['abcdicici', 'Aditya Birla (UPI)'],
['mboi', 'BOI Neo (UPI)'],
['freecharge', 'Freecharge (UPI)'],
['ikwik', 'MobiKwik (UPI)'],
['pingpay', 'Samsung Pay (UPI)'],
// Major Banks
['icici', 'ICICI Bank (UPI)'], ['axis', 'Axis Bank (UPI)'], ['axisbank', 'Axis Bank (UPI)'],
['hdfc', 'HDFC Bank (UPI)'], ['hdfcbank', 'HDFC Bank (UPI)'],
['sbi', 'SBI (UPI)'],
['kotak', 'Kotak Bank (UPI)'], ['kmbl', 'Kotak Bank (UPI)'], ['kotak811', 'Kotak 811 (UPI)'],
['pnb', 'Punjab National Bank (UPI)'],
['boi', 'Bank of India (UPI)'],
['cnrb', 'Canara Bank (UPI)'],
['barodampay', 'Bank of Baroda (UPI)'], ['barodapay', 'Bank of Baroda (UPI)'],
['centralbank', 'Central Bank of India (UPI)'],
['mahab', 'Maharashtra Bank (UPI)'],
['indus', 'IndusInd Bank (UPI)'],
['jkb', 'Jammu & Kashmir Bank (UPI)'],
['dlb', 'Delhi Bank (UPI)'],
// Payment Banks
['airtel', 'Airtel Payments Bank (UPI)'],
['jio', 'Jio Payments Bank (UPI)']
];
sheet.getRange(1, 1, 1, 2).setValues(headers).setFontWeight('bold');
sheet.getRange(2, 1, data.length, 2).setValues(data);
sheet.autoResizeColumns(1, 2);
}
/** Helper: Identify App from VPA Handle using Sheet Lookup */
function _inferUPIApp(vpa) {
if (!vpa || !vpa.includes('@')) return '';
var handle = (vpa.split('@')[1] || '').toLowerCase();
try {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = ss.getSheetByName('UPI Mapping');
if (!sheet) { sheet = ss.getSheetByName('UPI Mapping'); }
if (sheet) {
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var rowHandle = String(data[i][0]).toLowerCase().trim();
if (rowHandle === handle) return String(data[i][1]);
}
}
} catch(e) { }
// Fallback
if (['okaxis','okicici','oksbi','okhdfc'].indexOf(handle) > -1) return 'GPay (UPI)';
if (['ybl','ibl','axl'].indexOf(handle) > -1) return 'PhonePe (UPI)';
if (handle.indexOf('paytm') > -1 || ['ptyes','ptaxis','pticici'].indexOf(handle) > -1) return 'Paytm (UPI)';
if (['apl','yapl','rapl'].indexOf(handle) > -1) return 'Amazon Pay (UPI)';
return null;
}
/** Helper: Get Display App Name from Mode & VPA */
function _getAppName(mode, vpa) {
var m = (mode || '').toUpperCase();
if (m === 'CC' || m === 'CREDITCARD') return 'Credit Card';
if (m === 'DC' || m === 'DEBITCARD') return 'Debit Card';
if (m === 'NB' || m === 'NETBANKING') return 'Net Banking';
if (m === 'WALLET') return 'Wallet';
if (m === 'CASH') return 'Cash Card';
if (m === 'EMI') return 'EMI';
if (m === 'UPI') {
if (vpa && vpa.includes('@')) {
var detected = _inferUPIApp(vpa);
if (detected) return detected;
return 'UPI (@' + (vpa.split('@')[1] || '').toLowerCase() + ')';
}
return 'UPI';
}
return m;
}
/** HELPER: Log Transaction to Sheets (Extracted to fix braces/syntax error) */
function _logTransaction(txnid, status, ts, payAppRaw, vpa, appName, payuApp, bankRef, e) {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var mainSheet = ss.getSheetByName(SHEET_NAME);
var logSheet = ss.getSheetByName(STATUS_LOG_SHEET);
var data = mainSheet.getDataRange().getValues();
var recName = '', orderAmt = '';
var rowToUpdate = 0;
for (var i = 1; i < data.length; i++) {
if ((data[i][14]||'').toString().trim() === txnid) {
rowToUpdate = i + 1;
recName = data[i][1];
orderAmt = data[i][10];
break;
}
}
// Update Main Sheet
if (rowToUpdate > 0) {
mainSheet.getRange(rowToUpdate, 14).setValue('Paid');
mainSheet.getRange(rowToUpdate, 17).setValue('success');
mainSheet.getRange(rowToUpdate, 18).setValue(appName);
}
// --- Status Log Logic ---
var existingTxns = logSheet.getRange("B2:B" + (logSheet.getLastRow()||2)).getValues().flat();
var dupIndex = existingTxns.indexOf(txnid);
if (dupIndex !== -1) {
// DUPLICATE FOUND. Backfill missing PayU App
var rowNum = dupIndex + 2;
if (payuApp) {
var currentVal = logSheet.getRange(rowNum, 10).getValue();
if (!currentVal) {
logSheet.getRange(rowNum, 10).setValue(payuApp);
}
}
} else {
// NEW ENTRY
if (logSheet.getMaxColumns() < 10) {
logSheet.insertColumnsAfter(logSheet.getMaxColumns(), 10 - logSheet.getMaxColumns());
var currentHeaders = logSheet.getRange(1,1,1,logSheet.getLastColumn()).getValues()[0];
if (currentHeaders.length < 10 || currentHeaders[9] === '') {
logSheet.getRange(1,1,1,10).setValues([["Time","TxnID","Status","Name","Amount","Mode","UPI ID","Bank Ref","App Name","PayU App"]]);
}
}
logSheet.insertRowBefore(2);
logSheet.getRange(2,1,1,10).setValues([[ ts, txnid, status, recName, orderAmt, payAppRaw, vpa, bankRef, appName, payuApp ]]);
logSheet.getRange("A2:A").setNumberFormat("@");
logSheet.getRange("E2:E").setNumberFormat("0.00");
}
} // End _logTransaction
/** 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);
// One-click Setup
if (e.parameter.action === 'setupMapping') {
setupUPIMapping();
return _serve({ success: true, message: "UPI Mapping Sheet Created with 40+ Handles!" });
}
if (e.parameter.action === 'txnDetails') {
const txnId = (e.parameter.txnId || '').trim();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Status Log');
if (!sheet) return ContentService.createTextOutput(JSON.stringify({error: "Sheet 'Status Log' not found"})).setMimeType(ContentService.MimeType.JSON);
const data = sheet.getDataRange().getValues();
if (data.length < 2) return ContentService.createTextOutput(JSON.stringify({error: "Log is empty"})).setMimeType(ContentService.MimeType.JSON);
const headers = data[0].map(function(h) { return String(h).toLowerCase().trim(); });
let txnCol = headers.indexOf('txnid');
if (txnCol === -1) txnCol = headers.indexOf('txn id');
if (txnCol === -1) txnCol = 1; // Fallback to Index 1
for (let i = 1; i < data.length; i++) {
if (String(data[i][txnCol]).trim() === txnId) {
const row = data[i];
const obj = {
'Payment Time': row[0],
'Txn ID': row[1],
'Status': row[2],
'Payer Name': row[3],
'Amount': row[4],
'Payment mode': row[5],
'UPI ID (VPA)': row[6],
'Bank Ref No.': row[7],
'APP used': row[8],
'PayU App': row[9] || ''
};
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}
}
return ContentService.createTextOutput(JSON.stringify({error: 'Transaction ID not found: ' + txnId})).setMimeType(ContentService.MimeType.JSON);
}
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 {
var payload = {};
if (e.parameter && e.parameter.txnid) payload = e.parameter;
else { try { payload = JSON.parse(e.postData.contents); } catch(ex){} }
if (payload.txnid && (payload.status || payload.command)) {
var verified = checkPayUStatus(payload.txnid);
if (verified && verified.status) {
e.parameter = e.parameter || {};
e.parameter.txnid = payload.txnid;
e.parameter.status = verified.status;
e.parameter.addedon = verified.addedon;
e.parameter.amount = payload.amount || verified.amt;
e.parameter.mode = verified.mode;
e.parameter.field3 = verified.field3;
e.parameter.bank_ref_num = verified.bank_ref_num;
}
return handlePayUResponse(e);
}
if (payload.action === 'edit') return handleEdit(payload);
return handleInsert(payload);
} 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 (Refactored) */
function handlePayUResponse(e) {
var lock = LockService.getScriptLock();
try { lock.waitLock(10000); } catch (e) { Logger.log('Lock timeout'); }
var status = (e.parameter.status || '').toLowerCase();
var txnid = (e.parameter.txnid || '').trim();
var amount = (e.parameter.amount || '').trim();
var addedon= (e.parameter.addedon|| '').trim();
var tz = Session.getScriptTimeZone();
var props = PropertiesService.getScriptProperties();
var ts = props.getProperty(txnid);
try {
// Setup
try {
var upiSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('UPI Mapping');
if (!upiSheet) setupUPIMapping();
} catch(e) {}
// Verification if needed
if (status === 'success' && (!addedon || !e.parameter.field3)) {
var verified = checkPayUStatus(txnid);
if (verified && verified.status === 'success') {
status = 'success';
addedon = verified.addedon;
e.parameter.mode = verified.mode;
e.parameter.field3 = verified.field3;
e.parameter.bank_ref_num = verified.bank_ref_num;
}
}
// TS Parsing
if (addedon) {
ts = addedon;
var m = addedon.match(/^(\d{4})-(\d{2})-(\d{2})\s+(\d{2}:\d{2}:\d{2})$/);
if (m) ts = m[3] + '/' + m[2] + '/' + m[1] + ' ' + m[4];
} else {
if (!ts) ts = Utilities.formatDate(new Date(), tz, "dd/MM/yyyy HH:mm:ss");
}
if (!props.getProperty(txnid)) props.setProperty(txnid, ts);
// Log if Success
var logMsg = '';
if (status === 'success') {
try {
var payAppRaw = (e.parameter.mode || '').toUpperCase();
var vpa = e.parameter.field3 || '';
var appName = _getAppName(payAppRaw, vpa);
var bankRef = e.parameter.bank_ref_num || '';
var payuApp = e.parameter.field8 || e.parameter.Field8 || e.parameter.udf8 || '';
_logTransaction(txnid, status, ts, payAppRaw, vpa, appName, payuApp, bankRef, e);
} catch(logErr) {
logMsg = 'Warning: Logging failed (' + logErr.message + '), but Payment Successful.';
}
}
} finally {
lock.releaseLock();
}
// --- Redirect Logic ---
// Pass details to the Store Page so it can render the receipt
var backToStore = BLOGGER_FORM_URL + '?txnid=' + encodeURIComponent(txnid) +
'&status=' + status +
'&amount=' + encodeURIComponent(amount) +
'&time=' + encodeURIComponent(ts);
// WhatsApp Message
var waMsg = "✅ Payment Receipt\n" +
"Txn ID: " + txnid + "\n" +
"Amount: Rs. " + amount + "\n" +
"Time: " + ts + "\n" +
"Status: " + status.toUpperCase();
var waUrl = "https://wa.me/?text=" + encodeURIComponent(waMsg);
var html = '
' +
'
' +
'' +
'' +
'' +
'
' +
(status === 'success' ?
'
✅
Payment Successful' :
'
❌
Payment Failed') +
'
Transaction ID
' + txnid + '
' +
'
Amount
Rs. ' + amount + '
' +
'
Time
' + ts + '
' +
'
Status
' + status.toUpperCase() + '
' +
((typeof logMsg !== 'undefined' && logMsg) ? '
'+logMsg+'
' : '') +
'
Share receipt or return to app
' +
'
Share Receipt via WhatsApp' +
'
' +
'
' +
'';
return HtmlService.createHtmlOutput(html).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/** 2) Lookup tracking link */
function lookupLink(e) {
var txnid = (e.parameter.txnid||'').trim();
if (!txnid) return _serve({ error: 'Missing txnid' }, e.parameter.callback);
var rows = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME).getDataRange().getValues();
for (var i = 1; i < rows.length; i++) {
if ((rows[i][14]||'').toString().trim() === txnid) return _serve({ url: rows[i][15] }, e.parameter.callback);
}
return _serve({ error: 'Invalid txnid' }, e.parameter.callback);
}
/** Tracking endpoints */
function handleTracking(e) {
var s = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(TRACKING_SHEET);
var last = s.getLastRow();
// Return objects: { name, url, icon }
if (last < 2) return _serve([], e.parameter.callback);
// Fetch A to F (6 columns) to be safe
var params = s.getRange(2, 1, last - 1, 6).getValues();
var list = params.map(function(row) {
if (!row[0]) return null;
var icon = '';
// Check Cols D, E, F, C for a URL that looks like an image
// row indices: A=0, B=1, C=2, D=3, E=4, F=5
var candidates = [row[3], row[4], row[5], row[2]];
for (var i = 0; i < candidates.length; i++) {
var val = String(candidates[i] || '').trim();
if (val.match(/^https?:\/\/.+/i) && (val.indexOf('.png') > -1 || val.indexOf('.jpg') > -1 || val.indexOf('.jpeg') > -1 || val.indexOf('.svg') > -1 || val.indexOf('.webp') > -1 || val.indexOf('googleusercontent') > -1 || val.indexOf('drive.google.com') > -1)) {
icon = val;
break;
}
}
return {
name: String(row[0]),
url: String(row[1]||''),
icon: icon
};
}).filter(function(x) { return x; });
return _serve(list, e.parameter.callback);
}
function handleTrackingMap(e) {
var s = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(TRACKING_SHEET);
var last = s.getLastRow();
if (last<2) return _serve({}, e.parameter.callback);
var map = {};
var vals = s.getRange(2,1,last-1,2).getValues();
for (var i=0;i
eTime) return false;
}
// Query filter (rough pass)
if (q) {
var rowStr = r.join(' ').toLowerCase();
if (rowStr.indexOf(q) === -1) return false;
}
return true;
});
// 2. Score & Sort
// Priority:
// 1. Customer Name STARTS WITH q (Score 100)
// 2. Customer Name CONTAINS q (Score 50)
// 3. Address/Other fields score lower
if (q) {
filtered.forEach(function(item) {
var name = String(item.row[1] || '').toLowerCase(); // Recipient Name is index 1
var addr = String(item.row[2] || '').toLowerCase(); // Address is index 2
var rest = item.row.join(' ').toLowerCase();
item.score = 0;
if (name.startsWith(q)) item.score += 100;
else if (name.indexOf(q) > -1) item.score += 50;
if (addr.indexOf(q) > -1) item.score += 20;
// Tie-breaker: recent dates first if scores equal
});
filtered.sort(function(a, b) {
if (b.score !== a.score) return b.score - a.score; // Descending score
// Secondary sort by date desc
var da = _parseSheetTimestampToDate(a.row[0]) || new Date(0);
var db = _parseSheetTimestampToDate(b.row[0]) || new Date(0);
return db.getTime() - da.getTime();
});
} else {
// Normal Sort (No Query)
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);
}
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') : '';
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 currentScriptUrl = ScriptApp.getService().getUrl();
var surl = currentScriptUrl + '?txnid=' + txnid + '&status=success&amount=' + encodeURIComponent(params.orderValue);
var furl = currentScriptUrl + '?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, '' // Empty Col 18
];
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 */
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;
var cc = parseFloat(String(r[11] || '0').replace(/[^0-9.]/g, '')) || 0;
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);
}
}
/**
* =========================================================
* PAYU VERIFICATION API (Server-to-Server)
* =========================================================
*/
function checkPayUStatus(txnid) {
var command = 'verify_payment';
var hashStr = MERCHANT_KEY + '|' + command + '|' + txnid + '|' + SALT;
var hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_512, hashStr)
.map(function(b){ return ('0'+(b&0xFF).toString(16)).slice(-2); }).join('');
var payload = {
key: MERCHANT_KEY,
command: command,
var1: txnid,
hash: hash
};
var options = { method: 'post', payload: payload };
try {
var response = UrlFetchApp.fetch('https://info.payu.in/merchant/postservice.php?form=2', options);
var json = JSON.parse(response.getContentText());
if (json.status === 1 && json.transaction_details && json.transaction_details[txnid]) {
var det = json.transaction_details[txnid];
return {
status: det.status, // 'success', 'failure'
addedon: det.addedon, // '2023-10-27 10:00:00'
amt: det.amt,
mode: det.mode, // 'UPI', 'CC', etc
field3: det.field3, // UPI ID often here or UDF3
bank_ref_num: det.bank_ref_num,
error_Message: det.error_Message
};
}
} catch (e) {
Logger.log('PayU Check Failed: ' + e.message);
}
return null;
}
/**
* TRIGGER FUNCTION: Run this every 10-15 minutes
*/
function processPendingPayments() {
var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = ss.getSheetByName(SHEET_NAME);
var data = sheet.getDataRange().getValues();
var logSheet = ss.getSheetByName(STATUS_LOG_SHEET);
var tz = Session.getScriptTimeZone();
var updates = 0;
for (var i = 1; i < data.length; i++) {
var status = (data[i][16] || '').toString().trim();
var txnid = (data[i][14] || '').toString().trim();
if (status.toLowerCase() === 'pending' && txnid.startsWith('TXN')) {
var result = checkPayUStatus(txnid);
if (result && result.status === 'success') {
var niceTime = Utilities.formatDate(new Date(), tz, "dd/MM/yyyy HH:mm:ss");
if (result.addedon) {
var m = result.addedon.toString().match(/^(\d{4})-(\d{2})-(\d{2})\s+(\d{2}:\d{2}:\d{2})$/);
if (m) niceTime = m[3] + '/' + m[2] + '/' + m[1] + ' ' + m[4];
else niceTime = result.addedon;
}
var payAppRaw = (result.mode || '').toUpperCase();
var vpa = result.field3 || '';
var appName = _getAppName(payAppRaw, vpa);
sheet.getRange(i + 1, 14).setValue('Paid');
sheet.getRange(i + 1, 17).setValue('success');
sheet.getRange(i + 1, 18).setValue(appName); // Update App Name
// Use the new helper if deduplication is needed or use simple logic here
// The dedup helper is _logTransaction but it needs all params.
// For processing pending, simple dedup we can do here.
var existingTxns = logSheet.getRange("B2:B" + (logSheet.getLastRow()||2)).getValues().flat();
if (!existingTxns.includes(txnid)) {
logSheet.insertRowBefore(2);
// Col A: Time, B: TxnID, C: Status, D: Name, E: Amount, F: Mode/App, G: UPI ID, H: Bank Ref, I: App Name
logSheet.getRange(2,1,1,9).setValues([[ niceTime, txnid, 'success (verified)', data[i][1], data[i][10], payAppRaw, vpa, result.bank_ref_num, appName ]]);
updates++;
}
} else if (result && result.status === 'failure') {
sheet.getRange(i + 1, 17).setValue('failed');
}
}
}
return _serve({ verified: updates });
}