Google Apps Script to pull data from transaction emails in Gmail, and insert into Spreadsheet

Gmail data into Sheet with Google Apps Script

/*
 * 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');
}