Skip to content

Instantly share code, notes, and snippets.

@nonsocchi
Last active May 2, 2022 20:44
Show Gist options
  • Save nonsocchi/c19c6f3b42b0a986875071bdbe7569c2 to your computer and use it in GitHub Desktop.
Save nonsocchi/c19c6f3b42b0a986875071bdbe7569c2 to your computer and use it in GitHub Desktop.
// 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());
}
}
<!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>
<!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>
@nonsocchi
Copy link
Author

Added comments to view scripting progress.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment