Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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/
<!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>
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=[];
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();
}
<!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>
@n0rg

This comment has been minimized.

Copy link

n0rg commented Oct 17, 2019

Great script! One thing I noticed is in getRelevantMessages() for GmailApp.search function, regardless of what filters you put in, it will pull all threads regardless of being in the trash or not. I had to add a check isInInbox() before pushing to the message object.

@wfresch

This comment has been minimized.

Copy link

wfresch commented Jan 22, 2020

same. I ended up just changing the filter to grab messages newer than 1 hour, since the script is running hourly. (ie - "newer_than:1h ...")

@xMQ2RwURb

This comment has been minimized.

Copy link

xMQ2RwURb commented Jan 27, 2020

Prasanth, thanks for this script I have a question though, how can I parse only one variable?
I cannot do it it seems. I only need the phone numbers from a specific sender.

I found the regex var matches = text.match(/Tel.: +\d+/g); which matches Tel.: 01xxxxxxxx
but I'm pulling my hair out and dont know what to modify here in order to make it work:

    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];

Any hints please?

@Wdygrey

This comment has been minimized.

Copy link

Wdygrey commented Feb 10, 2020

Hi, I'm not a professional programmer, please if you could advise me..

I just tried with the first step by displaying those filtered email, but while running the web app I'm getting this msg -"Script function not found: doGet"..

pls help. thanks

@Henrixbor

This comment has been minimized.

Copy link

Henrixbor commented Feb 10, 2020

It as simple as the msg says, you are missing the one function that runs the page. You can fix the issue by adding:

function doGet() {
return getMessagesDisplay();
}

Which is also listed wrote in the end of the more detailed version of Prasanthmj's code.

@Wdygrey

This comment has been minimized.

Copy link

Wdygrey commented Feb 11, 2020

Hi Henrixbor ,

thanks for the advice for me this total program newbie..it works..will continue the rest..

@JusTelor

This comment has been minimized.

Copy link

JusTelor commented Feb 25, 2020

hi Prasanth, can you help me?
function getRelevantMessages were running well and get the email.

but, I have an error here,
TypeError: Cannot read property 'length' of undefined at parseMessageData

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.