vignesh30
TF Buzz
Awesome @sumit_yadav . Is th script for available for public use? I would love to try it.
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
I can share the script as gist soon. I have done it for HDFC, Axis, SBI, Kotak and ICICI.Awesome @sumit_yadav . Is th script for available for public use? I would love to try it.
var sheetUrl = "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=0";
var momentScript = "https://cdn.jsdelivr.net/npm/moment/moment.min.js";
var bankConfig = {
"HDFC": {
"email": "alerts@hdfcbank.net",
"subject": "HDFC Bank Credit Card",
"regex": /Card .+?(\d{4}) for ([A-Za-z]+)\.? ([\d.]+) at (.+?) on (\d{2}-\d{2}-\d{4} \d{2}:\d{2}:\d{2})\.? Authorization code:- (\d+)/,
"dateFormat": 'DD-MM-YYYY HH:mm:ss',
"captureGroups": {
"card": 1,
"currency": 2,
"amount": 3,
"merchant": 4,
"date": 5,
"authcode": 6
},
"sheetName": "HDFC"
},
"Axis": {
"email": "alerts@axisbank.com",
"subject": "Transaction alert on Axis Bank Credit Card",
"regex": /no. XX(\d{4}) for (INR|USD) ([\d.]+) at (.+?) on (\d{2}-\d{2}-(?:\d{2}|\d{4}) \d{2}:\d{2}:\d{2})/,
"dateFormat": 'DD-MM-YYYY HH:mm:ss',
"captureGroups": {
"card": 1,
"currency": 2,
"amount": 3,
"merchant": 4,
"date": 5
},
"sheetName": "Axis"
},
"Kotak": {
"email": "creditcardalerts@kotak.com",
"subject": "Kotak Bank Credit Card Transaction Alert",
"regex": /A Transaction of (INR|USD) ([\d.]+) has been done on your Kotak Bank Credit Card No.xx(\d{4}) at (.+?) on (\d{2}-\w+-\d{4})/,
"dateFormat": 'DD-MMM-YYYY',
"captureGroups": {
"card": 3,
"currency": 1,
"amount": 2,
"merchant": 4,
"date": 5
},
"sheetName": "Kotak"
},
"ICICI": {
"email": "credit_cards@icicibank.com",
"subject": "Transaction alert for your ICICI Bank Credit Card",
"regex": /ICICI Bank Credit Card XX(\d{4}) has been used for a transaction of (INR|USD) ([\d.]+) on (\w{3}\s\d{2},\s\d{4} at \d{2}:\d{2}:\d{2}). Info: ([^.]+)/,
"dateFormat": 'MMM DD, YYYY HH:mm:ss',
"captureGroups": {
"card": 1,
"currency": 2,
"amount": 3,
"merchant": 6,
"date": 4
},
"sheetName": "ICICI"
},
"SBI": {
"email": "onlinesbicard@sbicard.com",
"subject": "Transaction Alert from CASHBACK SBI Card",
"regex": /(Rs.|USD)([\d.,]+) spent on your SBI Credit Card ending (\d{4}) at ([^.]+) on (\d{2}\/\d{2}\/\d{2})/,
"dateFormat": 'DD/MM/YYYY',
"captureGroups": {
"card": 3,
"currency": 1,
"amount": 2,
"merchant": 4,
"date": 5
},
"sheetName": "SBI"
},
"SBI Recurring": {
"email": "onlinesbicard@sbicard.com",
"subject": "SBI Credit Card",
"regex": /(Rs.|USD)([\d.,]+) at ([^.]+) against (?:.+?) SBI Credit Card ending (\d{4}) on (\d{2}-\d{2}-\d{2})/,
"dateFormat": 'DD-MM-YYYY',
"captureGroups": {
"card": 4,
"currency": 1,
"amount": 2,
"merchant": 3,
"date": 5
},
"sheetName": "SBI"
}
};
function processTransactionEmails() {
for (var card in bankConfig) {
var messages = getRelevantMessages(card);
var records = parseMessageData(messages, card);
saveDataToSheet(records, card);
}
return true;
}
function getRelevantMessages(card) {
var config = bankConfig[card];
// Ensure the Moment.js library is loaded
loadMomentJS();
var startOfDay = moment().startOf('day').subtract(1, 'day');
var startOfDayTimestamp = startOfDay.unix(); // Get the timestamp in seconds
//startOfDayTimestamp = 1711929600;
var query = 'from:' + config.email + ' AND subject:"' + config.subject + '" AND after:' + startOfDayTimestamp;
Logger.log("Searching for messages using: " + query);
var messages = [];
var start = 0;
var maxResults = 100;
while (true) {
var threads = GmailApp.search(query, start, maxResults);
if (threads.length == 0) {
break;
}
for (var i = 0; i < threads.length; i++) {
messages = messages.concat(threads[i].getMessages());
}
start += maxResults;
}
return messages;
}
// Dynamically load the script using the URL
function loadMomentJS() {
var response = UrlFetchApp.fetch(momentScript); // Fetch the script from the CDN
eval(response.getContentText()); // Execute the script in the Apps Script environment
}
function parseMessageData(messages, card) {
var config = bankConfig[card];
var records = [];
if (!messages || messages.length === 0) {
Logger.log("No messages found.");
return records;
}
for (var m = 0; m < messages.length; m++) {
var text = messages[m].getPlainBody();
text = text.replace(/\s+/g, " ").trim();
var matches = text.match(config.regex);
Logger.log({ text, matches });
if (!matches) {
continue;
}
var rec = {};
for (var key in config.captureGroups) {
var index = config.captureGroups[key];
rec[key] = index !== null ? matches[index] : null;
}
// Format input date to consistent format.
if (rec.date) {
rec.date = moment(rec.date, config.dateFormat).format("MM/DD/YYYY HH:mm:ss")
}
rec.multiplier = 1;
if (rec.merchant && rec.merchant.toLowerCase().includes("gyftr")) {
rec.multiplier = 5;
} else if (rec.merchant && rec.merchant.includes("smartbuy")) {
if (rec.merchant.includes("jockey") || rec.merchant.includes("hotel") || rec.merchant.includes("pharmeasy")) {
rec.multiplier = 10;
}
}
Logger.log({ rec });
records.push(rec);
}
return records;
}
function saveDataToSheet(records, card) {
var config = bankConfig[card];
var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
var sheet = spreadsheet.getSheetByName(config.sheetName);
Logger.log("Saving " + records.length + " records");
var lastRow = sheet.getLastRow();
for (var r = 0; r < records.length; r++) {
var rowData = [records[r].date, "'" + records[r].card, records[r].merchant, records[r].currency, records[r].amount];
if (records[r].authcode) {
rowData.push(records[r].authcode);
}
if (card === "HDFC") {
rowData.push(records[r].multiplier);
}
sheet.appendRow(rowData);
lastRow = sheet.getLastRow();
sheet.getRange(lastRow, 5).setNumberFormat("0.00");
if (records[r].authcode) {
var authCodeCell = sheet.getRange(lastRow, 6);
authCodeCell.setNumberFormat("@");
authCodeCell.setValue("'" + records[r].authcode);
}
if (card === "HDFC") {
var formula = "=ROUNDDOWN(E" + lastRow + "/150,0)*5*G" + lastRow;
sheet.getRange(lastRow, 8).setFormula(formula);
}
}
}
Legend 🙏🏻 thank youHere's the script that I use. Create a Google sheet and update the sheetUrl in the first line and setup trigger to run this every night between 12 - 1am.
JavaScript:var sheetUrl = "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=0"; var momentScript = "https://cdn.jsdelivr.net/npm/moment/moment.min.js"; var bankConfig = { "HDFC": { "email": "alerts@hdfcbank.net", "subject": "HDFC Bank Credit Card", "regex": /Card .+?(\d{4}) for ([A-Za-z]+)\.? ([\d.]+) at (.+?) on (\d{2}-\d{2}-\d{4} \d{2}:\d{2}:\d{2})\.? Authorization code:- (\d+)/, "dateFormat": 'DD-MM-YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 4, "date": 5, "authcode": 6 }, "sheetName": "HDFC" }, "Axis": { "email": "alerts@axisbank.com", "subject": "Transaction alert on Axis Bank Credit Card", "regex": /no. XX(\d{4}) for (INR|USD) ([\d.]+) at (.+?) on (\d{2}-\d{2}-(?:\d{2}|\d{4}) \d{2}:\d{2}:\d{2})/, "dateFormat": 'DD-MM-YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 4, "date": 5 }, "sheetName": "Axis" }, "Kotak": { "email": "creditcardalerts@kotak.com", "subject": "Kotak Bank Credit Card Transaction Alert", "regex": /A Transaction of (INR|USD) ([\d.]+) has been done on your Kotak Bank Credit Card No.xx(\d{4}) at (.+?) on (\d{2}-\w+-\d{4})/, "dateFormat": 'DD-MMM-YYYY', "captureGroups": { "card": 3, "currency": 1, "amount": 2, "merchant": 4, "date": 5 }, "sheetName": "Kotak" }, "ICICI": { "email": "credit_cards@icicibank.com", "subject": "Transaction alert for your ICICI Bank Credit Card", "regex": /ICICI Bank Credit Card XX(\d{4}) has been used for a transaction of (INR|USD) ([\d.]+) on (\w{3}\s\d{2},\s\d{4} at \d{2}:\d{2}:\d{2}). Info: ([^.]+)/, "dateFormat": 'MMM DD, YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 6, "date": 4 }, "sheetName": "ICICI" }, "SBI": { "email": "onlinesbicard@sbicard.com", "subject": "Transaction Alert from CASHBACK SBI Card", "regex": /(Rs.|USD)([\d.,]+) spent on your SBI Credit Card ending (\d{4}) at ([^.]+) on (\d{2}\/\d{2}\/\d{2})/, "dateFormat": 'DD/MM/YYYY', "captureGroups": { "card": 3, "currency": 1, "amount": 2, "merchant": 4, "date": 5 }, "sheetName": "SBI" }, "SBI Recurring": { "email": "onlinesbicard@sbicard.com", "subject": "SBI Credit Card", "regex": /(Rs.|USD)([\d.,]+) at ([^.]+) against (?:.+?) SBI Credit Card ending (\d{4}) on (\d{2}-\d{2}-\d{2})/, "dateFormat": 'DD-MM-YYYY', "captureGroups": { "card": 4, "currency": 1, "amount": 2, "merchant": 3, "date": 5 }, "sheetName": "SBI" } }; function processTransactionEmails() { for (var card in bankConfig) { var messages = getRelevantMessages(card); var records = parseMessageData(messages, card); saveDataToSheet(records, card); } return true; } function getRelevantMessages(card) { var config = bankConfig[card]; // Ensure the Moment.js library is loaded loadMomentJS(); var startOfDay = moment().startOf('day').subtract(1, 'day'); var startOfDayTimestamp = startOfDay.unix(); // Get the timestamp in seconds //startOfDayTimestamp = 1711929600; var query = 'from:' + config.email + ' AND subject:"' + config.subject + '" AND after:' + startOfDayTimestamp; Logger.log("Searching for messages using: " + query); var messages = []; var start = 0; var maxResults = 100; while (true) { var threads = GmailApp.search(query, start, maxResults); if (threads.length == 0) { break; } for (var i = 0; i < threads.length; i++) { messages = messages.concat(threads[i].getMessages()); } start += maxResults; } return messages; } // Dynamically load the script using the URL function loadMomentJS() { var response = UrlFetchApp.fetch(momentScript); // Fetch the script from the CDN eval(response.getContentText()); // Execute the script in the Apps Script environment } function parseMessageData(messages, card) { var config = bankConfig[card]; var records = []; if (!messages || messages.length === 0) { Logger.log("No messages found."); return records; } for (var m = 0; m < messages.length; m++) { var text = messages[m].getPlainBody(); text = text.replace(/\s+/g, " ").trim(); var matches = text.match(config.regex); Logger.log({ text, matches }); if (!matches) { continue; } var rec = {}; for (var key in config.captureGroups) { var index = config.captureGroups[key]; rec[key] = index !== null ? matches[index] : null; } // Format input date to consistent format. if (rec.date) { rec.date = moment(rec.date, config.dateFormat).format("MM/DD/YYYY HH:mm:ss") } rec.multiplier = 1; if (rec.merchant && rec.merchant.toLowerCase().includes("gyftr")) { rec.multiplier = 5; } else if (rec.merchant && rec.merchant.includes("smartbuy")) { if (rec.merchant.includes("jockey") || rec.merchant.includes("hotel") || rec.merchant.includes("pharmeasy")) { rec.multiplier = 10; } } Logger.log({ rec }); records.push(rec); } return records; } function saveDataToSheet(records, card) { var config = bankConfig[card]; var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl); var sheet = spreadsheet.getSheetByName(config.sheetName); Logger.log("Saving " + records.length + " records"); var lastRow = sheet.getLastRow(); for (var r = 0; r < records.length; r++) { var rowData = [records[r].date, "'" + records[r].card, records[r].merchant, records[r].currency, records[r].amount]; if (records[r].authcode) { rowData.push(records[r].authcode); } if (card === "HDFC") { rowData.push(records[r].multiplier); } sheet.appendRow(rowData); lastRow = sheet.getLastRow(); sheet.getRange(lastRow, 5).setNumberFormat("0.00"); if (records[r].authcode) { var authCodeCell = sheet.getRange(lastRow, 6); authCodeCell.setNumberFormat("@"); authCodeCell.setValue("'" + records[r].authcode); } if (card === "HDFC") { var formula = "=ROUNDDOWN(E" + lastRow + "/150,0)*5*G" + lastRow; sheet.getRange(lastRow, 8).setFormula(formula); } } }
@HumorSimpson . .Here's the script that I use. Create a Google sheet and update the sheetUrl in the first line and setup trigger to run this every night between 12 - 1am.
JavaScript:var sheetUrl = "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=0"; var momentScript = "https://cdn.jsdelivr.net/npm/moment/moment.min.js"; var bankConfig = { "HDFC": { "email": "alerts@hdfcbank.net", "subject": "HDFC Bank Credit Card", "regex": /Card .+?(\d{4}) for ([A-Za-z]+)\.? ([\d.]+) at (.+?) on (\d{2}-\d{2}-\d{4} \d{2}:\d{2}:\d{2})\.? Authorization code:- (\d+)/, "dateFormat": 'DD-MM-YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 4, "date": 5, "authcode": 6 }, "sheetName": "HDFC" }, "Axis": { "email": "alerts@axisbank.com", "subject": "Transaction alert on Axis Bank Credit Card", "regex": /no. XX(\d{4}) for (INR|USD) ([\d.]+) at (.+?) on (\d{2}-\d{2}-(?:\d{2}|\d{4}) \d{2}:\d{2}:\d{2})/, "dateFormat": 'DD-MM-YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 4, "date": 5 }, "sheetName": "Axis" }, "Kotak": { "email": "creditcardalerts@kotak.com", "subject": "Kotak Bank Credit Card Transaction Alert", "regex": /A Transaction of (INR|USD) ([\d.]+) has been done on your Kotak Bank Credit Card No.xx(\d{4}) at (.+?) on (\d{2}-\w+-\d{4})/, "dateFormat": 'DD-MMM-YYYY', "captureGroups": { "card": 3, "currency": 1, "amount": 2, "merchant": 4, "date": 5 }, "sheetName": "Kotak" }, "ICICI": { "email": "credit_cards@icicibank.com", "subject": "Transaction alert for your ICICI Bank Credit Card", "regex": /ICICI Bank Credit Card XX(\d{4}) has been used for a transaction of (INR|USD) ([\d.]+) on (\w{3}\s\d{2},\s\d{4} at \d{2}:\d{2}:\d{2}). Info: ([^.]+)/, "dateFormat": 'MMM DD, YYYY HH:mm:ss', "captureGroups": { "card": 1, "currency": 2, "amount": 3, "merchant": 6, "date": 4 }, "sheetName": "ICICI" }, "SBI": { "email": "onlinesbicard@sbicard.com", "subject": "Transaction Alert from CASHBACK SBI Card", "regex": /(Rs.|USD)([\d.,]+) spent on your SBI Credit Card ending (\d{4}) at ([^.]+) on (\d{2}\/\d{2}\/\d{2})/, "dateFormat": 'DD/MM/YYYY', "captureGroups": { "card": 3, "currency": 1, "amount": 2, "merchant": 4, "date": 5 }, "sheetName": "SBI" }, "SBI Recurring": { "email": "onlinesbicard@sbicard.com", "subject": "SBI Credit Card", "regex": /(Rs.|USD)([\d.,]+) at ([^.]+) against (?:.+?) SBI Credit Card ending (\d{4}) on (\d{2}-\d{2}-\d{2})/, "dateFormat": 'DD-MM-YYYY', "captureGroups": { "card": 4, "currency": 1, "amount": 2, "merchant": 3, "date": 5 }, "sheetName": "SBI" } }; function processTransactionEmails() { for (var card in bankConfig) { var messages = getRelevantMessages(card); var records = parseMessageData(messages, card); saveDataToSheet(records, card); } return true; } function getRelevantMessages(card) { var config = bankConfig[card]; // Ensure the Moment.js library is loaded loadMomentJS(); var startOfDay = moment().startOf('day').subtract(1, 'day'); var startOfDayTimestamp = startOfDay.unix(); // Get the timestamp in seconds //startOfDayTimestamp = 1711929600; var query = 'from:' + config.email + ' AND subject:"' + config.subject + '" AND after:' + startOfDayTimestamp; Logger.log("Searching for messages using: " + query); var messages = []; var start = 0; var maxResults = 100; while (true) { var threads = GmailApp.search(query, start, maxResults); if (threads.length == 0) { break; } for (var i = 0; i < threads.length; i++) { messages = messages.concat(threads[i].getMessages()); } start += maxResults; } return messages; } // Dynamically load the script using the URL function loadMomentJS() { var response = UrlFetchApp.fetch(momentScript); // Fetch the script from the CDN eval(response.getContentText()); // Execute the script in the Apps Script environment } function parseMessageData(messages, card) { var config = bankConfig[card]; var records = []; if (!messages || messages.length === 0) { Logger.log("No messages found."); return records; } for (var m = 0; m < messages.length; m++) { var text = messages[m].getPlainBody(); text = text.replace(/\s+/g, " ").trim(); var matches = text.match(config.regex); Logger.log({ text, matches }); if (!matches) { continue; } var rec = {}; for (var key in config.captureGroups) { var index = config.captureGroups[key]; rec[key] = index !== null ? matches[index] : null; } // Format input date to consistent format. if (rec.date) { rec.date = moment(rec.date, config.dateFormat).format("MM/DD/YYYY HH:mm:ss") } rec.multiplier = 1; if (rec.merchant && rec.merchant.toLowerCase().includes("gyftr")) { rec.multiplier = 5; } else if (rec.merchant && rec.merchant.includes("smartbuy")) { if (rec.merchant.includes("jockey") || rec.merchant.includes("hotel") || rec.merchant.includes("pharmeasy")) { rec.multiplier = 10; } } Logger.log({ rec }); records.push(rec); } return records; } function saveDataToSheet(records, card) { var config = bankConfig[card]; var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl); var sheet = spreadsheet.getSheetByName(config.sheetName); Logger.log("Saving " + records.length + " records"); var lastRow = sheet.getLastRow(); for (var r = 0; r < records.length; r++) { var rowData = [records[r].date, "'" + records[r].card, records[r].merchant, records[r].currency, records[r].amount]; if (records[r].authcode) { rowData.push(records[r].authcode); } if (card === "HDFC") { rowData.push(records[r].multiplier); } sheet.appendRow(rowData); lastRow = sheet.getLastRow(); sheet.getRange(lastRow, 5).setNumberFormat("0.00"); if (records[r].authcode) { var authCodeCell = sheet.getRange(lastRow, 6); authCodeCell.setNumberFormat("@"); authCodeCell.setValue("'" + records[r].authcode); } if (card === "HDFC") { var formula = "=ROUNDDOWN(E" + lastRow + "/150,0)*5*G" + lastRow; sheet.getRange(lastRow, 8).setFormula(formula); } } }
CRED is fine. No need of any scripts
This was created only to track Infinia spends and reward points but ended up adding other banks too that I currently use.CRED is fine. No need of any scripts
I am confident that no one will end up using the script 😉 .This was created only to track Infinia spends and reward points but ended up adding other banks too that I currently use.