Last active
May 2, 2022 20:44
-
-
Save nonsocchi/c19c6f3b42b0a986875071bdbe7569c2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// SET UP ON SHEETS | |
function onOpen(e) { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Get Txns').addItem('Populate Sheets', 'processTxnEmails').addToUi(); | |
} | |
// FILTERING EMAILS... | |
function getBankEmails() { | |
// This function searches for messages from “GeNS” and with emails having subject containing | |
// “GeNS transaction Alert …” | |
var timeframe = 'newer_than:1h'; | |
var filter = timeframe + " AND subject:GeNS transaction Alert AND -label:scripting-emails"; | |
var threads = GmailApp.search(filter, 0, 10); | |
var messages = []; | |
for (var x in threads) { | |
var thread = threads[x] | |
messages.push(thread.getMessages()[0]); | |
} | |
return messages; | |
} | |
function displayEmails() { | |
var templ = HtmlService.createTemplateFromFile('messages'); | |
templ.messages = getBankEmails(); | |
return templ.evaluate(); | |
} | |
// Program Entry Point. | |
function doGet() { | |
// return displayEmails(); | |
console.log('Starting script...'); | |
// return displayParsedData(); | |
return processTxnEmails(); | |
} | |
// PARSING EMAIL MESSAGES... | |
function parseMessageBody(messages) { | |
var txnData = []; | |
for (var m = 0; m < messages.length; m++) { | |
var text = messages[m].getPlainBody(); | |
Logger.log(m); | |
// debit/credit amount and account balance. | |
// var amountMatches = text.match(/NGN\s+(-?[\d\,\.]+)/g); | |
var amountMatches = text.match(/Amount[\s\S]*?Value/); | |
var balanceMatches = text.match(/Balance[\s\S]*?Available/); | |
var txnTypeMatches = text.match(/(Debit|Credit)/); | |
var txnDateMatches = text.match(/\d{2}-[A-z]{3}-\d{4}|\d{2}-?\d{2}-\d{4}/); | |
var txnAccountMatches = text.match(/0147890929/); | |
var txnDescMatches = text.match(/Description[\s\S]*?Amount/); // >>>>>V2.0 | |
Logger.log(txnDescMatches); | |
// Logger.log('regex last index: ' + regDesc.lastIndex); | |
var data = {}; | |
// get the first amount from the match, which is the debit/credit amount. | |
data.txnAmount = amountMatches ? amountMatches[0] : "No Amount Found"; | |
Logger.log(data.txnAmount); | |
data.balance = balanceMatches ? balanceMatches[0] : "No Balance Found"; | |
Logger.log(data.balance); | |
data.txnType = txnTypeMatches ? txnTypeMatches[0]: "No Type Found"; | |
Logger.log(data.txnType); | |
data.txnDesc = txnDescMatches ? txnDescMatches[0] : "No Description Found"; | |
// CLEAN UP DESCRIPTION MATCHES | |
data.txnDesc = data.txnDesc.replace(/(?: *[\n\r])+ */g, ''); | |
data.txnDesc = data.txnDesc.replace('Description', ''); | |
data.txnDesc = data.txnDesc.replace(' :', ''); | |
data.txnDesc = data.txnDesc.replace(':', ''); | |
data.txnDesc = data.txnDesc.replace('Amount', ''); | |
Logger.log(data.txnDesc); | |
// CLEAN UP AMOUNT MATCHES | |
data.txnAmount = data.txnAmount.replace(/(?: *[\n\r])+ */g, ''); | |
data.txnAmount = data.txnAmount.replace('Amount : ', ''); | |
data.txnAmount = data.txnAmount.replace('Value', ''); | |
// CLEAN UP BALAMCE MATCHES | |
data.balance = data.balance.replace(/(?: *[\n\r])+ */g, ''); | |
data.balance = data.balance.replace('Balance:', ''); | |
data.balance = data.balance.replace('Available', ''); | |
data.txnDate = txnDateMatches ? txnDateMatches[0] : "No Date Found"; | |
Logger.log(data.txnDate); | |
data.txnAccount = txnAccountMatches ? txnAccountMatches[0] : "No Account Found"; | |
Logger.log(data.txnAccount); | |
txnData.push(data); | |
} | |
Logger.log(txnData); | |
return txnData; | |
} | |
function displayParsedData() { | |
var templ = HtmlService.createTemplateFromFile('parsed'); | |
templ.txnData = parseMessageBody(getBankEmails()); | |
return templ.evaluate(); | |
} | |
// SAVE DATA TO GOOGLE SHEETS | |
function saveToSheet(txnData){ | |
var expenseSpreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1VZBkiqoTmcnWcxvNCEgHwMLhy_sBof4Cbd0gmKvkxOk/edit#gid=0"); | |
var dataSheet = expenseSpreadsheet.getSheetByName("Data"); | |
Logger.log('Populating spreadsheet...'); | |
for (var txn = 0; txn < txnData.length; txn++) { | |
dataSheet.appendRow([ | |
txnData[txn].txnDate, | |
txnData[txn].txnAmount, | |
txnData[txn].txnDesc, | |
txnData[txn].txnType, | |
txnData[txn].balance, | |
txnData[txn].txnAccount | |
] | |
); | |
} | |
} | |
function processTxnEmails() { | |
var messages = getBankEmails(); | |
var txnData = parseMessageBody(messages); | |
saveToSheet(txnData); | |
labelMessagesAsDone(messages); | |
return true; | |
} | |
function labelMessagesAsDone(messages) { | |
var labelName = 'scripting-emails'; | |
Logger.log('Processing labels...'); | |
var mylabel = GmailApp.getUserLabelByName(labelName); | |
if (!mylabel) { | |
mylabel = GmailApp.createLabel(labelName); | |
Logger.log("Label doesn't exist, creating new " + labelName); | |
} | |
for (var m = 0; m < messages.length; m++) { | |
mylabel.addToThread(messages[m].getThread()); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<title>Message Display Test</title> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
</head> | |
<body style="padding:3em;"> | |
<h1>Messages</h1> | |
<ul> | |
<? for(var m=0;m<messages.length;m++){ ?> | |
<li><?= messages[m].getSubject() ?></li> | |
<p><?= messages[m].getPlainBody() ?></p> | |
<? } ?> | |
</ul> | |
</body> | |
</html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<title>Data parsed from emails</title> | |
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> | |
</head> | |
<body style="padding:3em;"> | |
<h2>Parsed data from Messages</h2> | |
<table style="width:800px"> | |
<thead> | |
<tr> | |
<th>Date</th><th>Amount</th><th>Balance</th><th>Type</th><th>Description</th><th>Acount Number</th> | |
</tr> | |
</thead> | |
<tbody> | |
<? for(var m=0;m<txnData.length;m++){ ?> | |
<tr> | |
<td><?= txnData[m].txnDate ?></td> | |
<td><?= txnData[m].txnAmount ?></td> | |
<td><?= txnData[m].balance ?></td> | |
<td><?= txnData[m].txnType ?></td> | |
<td><?= txnData[m].txnDesc ?></td> | |
<td><?= txnData[m].txnAccount ?></td> | |
</tr> | |
<? }?> | |
</tbody> | |
</table> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Added comments to view scripting progress.