// === 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
const SCRIPT_WEBAPP_URL = 'https://script.google.com/macros/s/AKfycbzofw9GcllDpycP6Urig95P4OAnxe-L4_FHvYR1AOzfSZzta-WyTPevwxN1KnkfdOGXBA/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 ===== */
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();
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;
}
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)'],
// PhonePe
['ybl', 'PhonePe (UPI)'], ['ibl', 'PhonePe (UPI)'], ['axl', 'PhonePe (UPI)'],
// Paytm (and user requested)
['paytm', 'Paytm (UPI)'], ['ptyes', 'Paytm (UPI)'], ['ptaxis', 'Paytm (UPI)'], ['pticici', 'Paytm (UPI)'],
// Amazon
['apl', 'Amazon Pay (UPI)'], ['yapl', 'Amazon Pay (UPI)'], ['rapl', 'Amazon Pay (UPI)'],
// WhatsApp
['waaxis', 'WhatsApp (UPI)'], ['wahdfc', 'WhatsApp (UPI)'], ['waicici', 'WhatsApp (UPI)'], ['wasbi', 'WhatsApp (UPI)'],
// Others
['upi', 'BHIM (UPI)'], ['bhim', 'BHIM (UPI)'], ['cred', 'Cred (UPI)'],
['freecharge', 'Freecharge (UPI)'], ['ikwik', 'MobiKwik (UPI)'],
['pingpay', 'Samsung Pay (UPI)'],
// Banks
['icici', 'ICICI (UPI)'], ['kotak', 'Kotak (UPI)'], ['kmbl', 'Kotak (UPI)'],
['axis', 'Axis (UPI)'], ['hdfc', 'HDFC (UPI)'], ['sbi', 'SBI (UPI)'],
['airtel', 'Airtel (UPI)'], ['jio', 'Jio (UPI)'], ['cnrb', 'Canara Bank (UPI)'],
['barodampay', 'Bank of Baroda (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();
var appName = String(data[i][1]);
if (rowHandle === handle) return appName;
}
}
} catch(e) { }
// Fallback Hardcoded
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; // Return null if truly not found
}
/** Helper: Get Display App Name from Mode & VPA */
function _getAppName(mode, vpa) {
var m = (mode || '').toUpperCase();
var appName = '';
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';
// UPI Handling
if (m === 'UPI') {
if (vpa && vpa.includes('@')) {
var detected = _inferUPIApp(vpa);
if (detected) {
return detected; // e.g. "GPay (UPI)"
} else {
var handle = (vpa.split('@')[1] || '').toLowerCase();
return 'UPI (@' + handle + ')';
}
}
return 'UPI';
}
return m; // Fallback to raw mode if unknown
}
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);
// One-click Setup
if (e.parameter.action === 'setupMapping') {
setupUPIMapping();
return _serve({ success: true, message: "UPI Mapping Sheet Created with 40+ Handles!" });
}
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 = {};
// Check Form Data
if (e.parameter && e.parameter.txnid) payload = e.parameter;
// Check JSON
else {
try { payload = JSON.parse(e.postData.contents); } catch(ex){}
}
// Webhook/Redirect Logic
if (payload.txnid && (payload.status || payload.command)) {
// Force verify & Get Exact Time & Details
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; // Use EXACT PayU Time
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 (once) */
/** 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 addedon= (e.parameter.addedon|| '').trim();
var tz = Session.getScriptTimeZone();
var ts = props.getProperty(txnid);
// Force UPI Mapping Sheet Creation immediately
try {
var upiSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName('UPI Mapping');
if (!upiSheet) setupUPIMapping();
} catch(e) {}
// If not processed yet
if (!ts) {
// If status is success but we lack details (Redirect flow), Fetch them!
if (status === 'success' && (!addedon || !e.parameter.field3)) {
var verified = checkPayUStatus(txnid);
if (verified && verified.status === 'success') {
status = 'success'; // confirm
addedon = verified.addedon;
e.parameter.mode = verified.mode;
e.parameter.field3 = verified.field3;
e.parameter.bank_ref_num = verified.bank_ref_num;
}
}
// Exact Time Parsing
if (addedon) {
ts = addedon; // Default
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 {
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 = '';
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;
}
}
// Detailed Logging
var logSheet = ss.getSheetByName(STATUS_LOG_SHEET);
// Infer App
var payAppRaw = (e.parameter.mode || '').toUpperCase();
var vpa = e.parameter.field3 || '';
var appName = _getAppName(payAppRaw, vpa);
var bankRef = e.parameter.bank_ref_num || '';
// UPDATE MAIN SHEET (Col 18 = App Name)
if (rowToUpdate > 0) {
mainSheet.getRange(rowToUpdate, 14).setValue('Paid');
mainSheet.getRange(rowToUpdate, 17).setValue('success');
// Column 18 is the new "App Name" column
mainSheet.getRange(rowToUpdate, 18).setValue(appName);
}
// Ensure 9 cols
if (logSheet.getMaxColumns() < 9) {
logSheet.insertColumnsAfter(logSheet.getMaxColumns(), 9 - logSheet.getMaxColumns());
if (logSheet.getLastRow() === 0) logSheet.getRange(1,1,1,9).setValues([["Time","TxnID","Status","Name","Amount","Mode","UPI ID","Bank Ref","App Name"]]);
}
logSheet.insertRowBefore(2);
logSheet.getRange(2,1,1,9).setValues([[ ts, txnid, status, recName, orderAmt, payAppRaw, vpa, bankRef, appName ]]);
logSheet.getRange("A2:A").setNumberFormat("@");
logSheet.getRange("E2:E").setNumberFormat("0.00");
ensureBlankRows(500);
}
}
var dest = '';
if (status === 'success') {
dest = 'https://mmorder.blogspot.com/p/success.html?txnid=' + encodeURIComponent(txnid) +
'&amount=' + encodeURIComponent(amount) + '&time=' + encodeURIComponent(ts);
} else {
dest = 'https://mmorder.blogspot.com/p/pay.html?status=failed&txnid=' + encodeURIComponent(txnid);
}
var html = '
' +
'' +
'
' +
'' +
'
Payment Processed
Redirecting you back to the store...
' +
'
If not redirected automatically, Click Here
' +
'';
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();
var list = last<2 ? [] : s.getRange(2,1,last-1,1).getValues().join('\n').split('\n').filter(function(v){return v;});
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
= 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);
}
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 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, '' // 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
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 });
}