Last active
January 29, 2024 17:50
-
-
Save prasanthmj/ec804de3b6355c3ca26984a892ad550d to your computer and use it in GitHub Desktop.
Parse and extract data from Gmail to Google Sheet. Read full article here: http://blog.gsmart.in/parse-and-extract-data-from-gmail-to-google-sheets/
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
function getRelevantMessages() | |
{ | |
var threads = GmailApp.search("newer_than:1d AND from:citicorp.com AND subject:Transaction confirmation AND -label:payment_processing_done",0,100); | |
var messages=[]; | |
threads.forEach(function(thread) | |
{ | |
messages.push(thread.getMessages()[0]); | |
}); | |
return messages; | |
} | |
function parseMessageData(messages) | |
{ | |
var records=[]; | |
if(!messages) | |
{ | |
//messages is undefined or null or just empty | |
return records; | |
} | |
for(var m=0;m<messages.length;m++) | |
{ | |
var text = messages[m].getPlainBody(); | |
var matches = text.match(/Rs\.\s+([\d\,\.]+)\s+(?:spent on card)\s+(\d+)\s+(?:on)\s+([\d\-A-Z]+)\s+(?:at)\s+([\w\s]+)\./); | |
if(!matches || matches.length < 5) | |
{ | |
//No matches; couldn't parse continue with the next message | |
continue; | |
} | |
var rec = {}; | |
rec.amount = matches[1]; | |
rec.card = matches[2]; | |
rec.date= matches[3]; | |
rec.merchant = matches[4]; | |
//cleanup data | |
rec.amount = parseFloat(rec.amount.replace(/,/g, '')); | |
records.push(rec); | |
} | |
return records; | |
} | |
function getMessagesDisplay() | |
{ | |
var templ = HtmlService.createTemplateFromFile('messages'); | |
templ.messages = getRelevantMessages(); | |
return templ.evaluate(); | |
} | |
function getParsedDataDisplay() | |
{ | |
var templ = HtmlService.createTemplateFromFile('parsed'); | |
templ.records = parseMessageData(getRelevantMessages()); | |
return templ.evaluate(); | |
} | |
function saveDataToSheet(records) | |
{ | |
var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ql-S5TjH5KoekRvRqiwuyequyxhgas/edit#gid=0"); | |
var sheet = spreadsheet.getSheetByName("Sheet1"); | |
for(var r=0;r<records.length;r++) | |
{ | |
sheet.appendRow([records[r].date,records[r].card, records[r].merchant, records[r].amount ] ); | |
} | |
} | |
function processTransactionEmails() | |
{ | |
var messages = getRelevantMessages(); | |
var records = parseMessageData(messages); | |
saveDataToSheet(records); | |
labelMessagesAsDone(messages); | |
return true; | |
} | |
function labelMessagesAsDone(messages) | |
{ | |
var label = 'payment_processing_done'; | |
var label_obj = GmailApp.getUserLabelByName(label); | |
if(!label_obj) | |
{ | |
label_obj = GmailApp.createLabel(label); | |
} | |
for(var m =0; m < messages.length; m++ ) | |
{ | |
label_obj.addToThread(messages[m].getThread() ); | |
} | |
} | |
function doGet() | |
{ | |
return getParsedDataDisplay(); | |
//return getMessagesDisplay(); | |
} |
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:420px"> | |
<thead> | |
<tr> | |
<th>Date</th><th>Merchant</th><th>Card</th><th>Amount</th> | |
</tr> | |
</thead> | |
<tbody> | |
<? for(var m=0;m<records.length;m++){ ?> | |
<tr> | |
<td><?= records[m].date ?></td> | |
<td><?= records[m].merchant ?></td> | |
<td><?= records[m].card ?></td> | |
<td><?= records[m].amount ?></td> | |
</tr> | |
<? }?> | |
</tbody> | |
</table> | |
<div class="block"> | |
<button id="save_to_sheet" class="blue">Save Data to Sheet</button> | |
<span id="saving_label" style="visibility:hidden">Saving ...</span> | |
</div> | |
<script> | |
document.querySelector("#save_to_sheet").addEventListener("click", | |
function(e) | |
{ | |
var self=this; | |
self.style.visibility = 'hidden'; | |
var saving_label = document.querySelector("#saving_label"); | |
saving_label.style.visibility = 'visible'; | |
google.script.run.withSuccessHandler(function() | |
{ | |
alert("Saved expense data"); | |
self.style.visibility = 'visible'; | |
saving_label.style.visibility = 'hidden'; | |
}).withFailureHandler(function() | |
{ | |
alert("an error occured while saving"); | |
self.style.visibility = 'visible'; | |
saving_label.style.visibility = 'hidden'; | |
}).processTransactionEmails(); | |
}); | |
</script> | |
</body> | |
</html> | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have determined it is skipping emails that are received at the same time. Because they are considered part of the same thread ( though not displayed that way in the gmail browser UI). So getRelevantMessages() should be updated with something like this :
var messages=[];
threads.forEach(function(thread)
{
I am new to this but it feels like a google apps script bug.