Skip to content

Instantly share code, notes, and snippets.

@davemoz
Last active November 1, 2023 18:48
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save davemoz/dd024c64e9c63167b9bd7278c5d419fb to your computer and use it in GitHub Desktop.
Save davemoz/dd024c64e9c63167b9bd7278c5d419fb to your computer and use it in GitHub Desktop.
Google Apps Script to pull data from transaction emails in Gmail, and insert into Spreadsheet
/*
* This Google Apps Script works by simply scraping info from transaction emails
* sent to your Gmail account.
* My credit cards are Capital One, Chase, and Bank of America so the email-scraping
* regexes will need to change to work with the format of your emails.
*
* Once installed, you'll need to set a monthly trigger to run the createMonthlySheet()
* function, but then it will run automatically and sort your transactions into monthly sheets.
*
* Any reference to the getActiveSpreadsheet() method will create a new sheet
* within the context of whatever spreadsheet you add the script to.
*
*/
// This function creates a new monthly sheet
function createMonthlySheet() {
// Get a new formatted date object
var currentMonthYear = Utilities.formatDate(new Date(), 'GMT-5', 'MM/yyyy');
// Insert formatted date string from above
var sheetName = currentMonthYear;
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Create the new sheet
ss.insertSheet(sheetName, 0);
// Now get the newly-create currently active sheet
var newSheet = ss.getActiveSheet();
// Get all sheets
var allSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
// Get the next oldest sheet
var oldSheet = allSheets[1];
// Hide the old one
oldSheet.hideSheet();
// Set our monthly budget amount
var budgetAmt = 1510;
// Set H1 to the active cell
var budgetCell = newSheet.setActiveSelection('H1');
// Set H1 to our monthly budget
budgetCell.setValue(budgetAmt);
// Set the number format to US-style currency
budgetCell.setNumberFormat('$#,##0.00');
// Set F1 as the active cell
var sumCell = newSheet.setActiveSelection('F1');
// Set the formula to the sum of column D
sumCell.setFormula('=SUM(D:D)');
// Set L1 as the active cell
var diffCell = newSheet.setActiveSelection('L1');
// Set the formula to the sum of budgetCell - sumCell
diffCell.setFormula('=SUM(H1,-(F1))');
// Set the number format to US-style currency
diffCell.setNumberFormat('$#,##0.00');
// Get column B
var range_B = newSheet.getRange('B:B');
// Set column B as the active range
var acctCol = newSheet.setActiveRange(range_B);
// Set the number format to US-style currency
acctCol.setNumberFormat('0000').setHorizontalAlignment('right');
// Get column D
var range_D = newSheet.getRange('D:D');
// Set column D as the active range
var moneyCol = newSheet.setActiveRange(range_D);
// Set the number format to US-style currency
moneyCol.setNumberFormat('$#,##0.00');
}
// This function fires different functions for each email label
function getEmails() {
getCapitalOneEmails();
getChaseEmails();
getBofAEmails();
}
// This function fetches CapitalOne transaction emails and parses the data
function getCapitalOneEmails() {
// Set a variable to our "CapitalOne Transaction" label in Gmail
var label = GmailApp.getUserLabelByName('CapitalOne Transaction');
if (label) {
var threads = label.getThreads(); // Get threads of label above
for (var i in threads) {
var messages = threads[i].getMessages(); // Get messages in threads
for (var j in messages) {
if (messages[j].isUnread()) {
// Only get unread messages
var emailBody = messages[j].getBody(); // Get HTML email body
Logger.log('******Email body: ' + emailBody);
// This sets out data variables to an empty string initially
var message_account = '',
message_date = '',
message_vendor = '',
message_amount = '';
// Get account number
var regExpAcct = /RE\: Account ending in (\d{4})/; // regex to find 4-digit account number
var CO_message_account = regExpAcct.exec(emailBody);
if (CO_message_account) {
message_account = CO_message_account[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message accnt: ' + message_account);
}
// Get date of transaction
// var regExpDate = /notifying you that on (...+), at/; // regex to find date
// var rough_message_date = regExpDate.exec(emailBody);
// var CO_message_date = JSON.parse(rough_message_date);
var CO_message_date = messages[j].getDate();
if (CO_message_date) {
message_date = CO_message_date; // Insert got data to final variable
Logger.log('******Email message date: ' + message_date);
}
// Get vendor name
var regExpVendor = /, at (...+),/; // regex to find transaction vendor name
var CO_message_vendor = regExpVendor.exec(emailBody);
if (CO_message_vendor) {
message_vendor = CO_message_vendor[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message vendor: ' + message_vendor);
}
// Get transaction amount
var regExpAmount = /purchase in the amount of \$(\S+) was/; // regex to find transaction amount
var CO_message_amount = regExpAmount.exec(emailBody);
if (CO_message_amount) {
message_amount = CO_message_amount[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message amount: ' + message_amount);
}
if(message_date && message_account && message_vendor && message_amount){
// Calls the function, below, that adds the data to the spreadsheet, with our data inserted as parameters
addDataToSpreadsheet(
message_date,
message_account,
message_vendor,
message_amount
);
messages[j].markRead(); // Mark the message as read to end
} else {
Logger.log('******CapitalOne email vars are empty******');
}
}
}
}
} // End if(label)
}
// This function fetches Chase(Amazon) transaction emails and parses the data
function getChaseEmails() {
// Set a variable to our "Chase Transaction" label in Gmail
var label = GmailApp.getUserLabelByName('Chase Transaction');
if (label) {
var threads = label.getThreads(); // Get threads of label above
for (var i in threads) {
var messages = threads[i].getMessages(); // Get messages in threads
for (var j in messages) {
if (messages[j].isUnread()) {
// Only get unread messages
var emailBody = messages[j].getPlainBody(); // Get email body in plaintext, no HTML
Logger.log('******Email body: ' + emailBody);
// This sets out data variables to an empty string initially
var message_account = '',
message_date = '',
message_vendor = '',
message_amount = '';
// Get account number
var regExpAcct = /account ending in \n(\d{4})./; // regex to find 4-digit account number
var CH_message_account = regExpAcct.exec(emailBody);
if (CH_message_account) {
message_account = CH_message_account[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message accnt: ' + message_account);
}
// Get date of transaction
var regExpDate = /has been authorized on \n(...+) EST./; // regex to find date
var CH_message_date = regExpDate.exec(emailBody);
if (CH_message_date) {
message_date = CH_message_date[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message date: ' + message_date);
}
// Get vendor name
var regExpVendor = / at (...+) has been authorized /; // regex to find transaction vendor name
var CH_message_vendor = regExpVendor.exec(emailBody);
if (CH_message_vendor) {
message_vendor = CH_message_vendor[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message vendor: ' + message_vendor);
}
// Get transaction amount
var regExpAmount = /This charge of \(\$USD\) (\S+) at /; // regex to find transaction amount
var CH_message_amount = regExpAmount.exec(emailBody);
if (CH_message_amount) {
message_amount = CH_message_amount[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message amount: ' + message_amount);
}
if(message_date && message_account && message_vendor && message_amount){
// Calls the function, below, that adds the data to the spreadsheet, with our data inserted as parameters
addDataToSpreadsheet(
message_date,
message_account,
message_vendor,
message_amount
);
messages[j].markRead(); // Mark the message as read to end
} else {
Logger.log('******Chase email vars are empty******');
}
}
}
}
} // End if(label)
}
// This function fetches Bank of America transaction emails and parses the data
function getBofAEmails() {
// Set a variable to our "BofA Transaction" label in Gmail
var label = GmailApp.getUserLabelByName('BofA Transaction');
if (label) {
var threads = label.getThreads(); // Get threads of label above
for (var i in threads) {
var messages = threads[i].getMessages(); // Get messages in threads
for (var j in messages) {
if (messages[j].isUnread()) {
// Only get unread messages
var emailBody = messages[j].getPlainBody(); // Get HTML email body
Logger.log('******Email body: ' + emailBody);
// This sets out data variables to an empty string initially
var message_account = '',
message_date = '',
message_vendor = '',
message_amount = '';
// Get account number
var regExpAcct = /Rewards Visa ending in - \*(\d{4})\*/; // regex to find 4-digit account number
var BA_message_account = regExpAcct.exec(emailBody);
if (BA_message_account) {
message_account = BA_message_account[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message accnt: ' + message_account);
}
// Get date of transaction
var regExpDate = /Transaction date: (...+)\nView details/; // regex to find date
var BA_message_date = regExpDate.exec(emailBody);
if (BA_message_date) {
message_date = BA_message_date[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message date: ' + message_date);
}
// Get vendor name
var regExpVendor = /Where\: at (...+)\nType/; // regex to find transaction vendor name
var BA_message_vendor = regExpVendor.exec(emailBody);
if (BA_message_vendor) {
message_vendor = BA_message_vendor[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message vendor: ' + message_vendor);
}
// Get transaction amount
var regExpAmount = /Amount\: \n\$ (\S+) \nCredit card\:/; // regex to find transaction amount
var BA_message_amount = regExpAmount.exec(emailBody);
if (BA_message_amount) {
message_amount = BA_message_amount[1]; // Insert Group 1 got data from regex execution to variable
Logger.log('******Email message amount: ' + message_amount);
}
if(message_date && message_account && message_vendor && message_amount){
// Calls the function, below, that adds the data to the spreadsheet, with our data inserted as parameters
addDataToSpreadsheet(
message_date,
message_account,
message_vendor,
message_amount
);
messages[j].markRead(); // Mark the message as read to end
} else {
Logger.log('******BofA email vars are empty******');
}
}
}
}
} // End if(label)
}
function addDataToSpreadsheet(date, account, vendor, amount) {
var rowData = [date, account, vendor, amount];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
sheet
.insertRowBefore(2)
.getRange(2, 1, 1, rowData.length)
.setValues([rowData]);
SpreadsheetApp.flush();
} finally {
lock.releaseLock();
}
var range = sheet.getRange('D:D');
var moneyCol = sheet.setActiveRange(range);
moneyCol.setNumberFormat('$#,##0.00');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment