<!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=[]; | |
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(); | |
} |
<!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> | |
This comment has been minimized.
This comment has been minimized.
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 ...") |
This comment has been minimized.
This comment has been minimized.
Prasanth, thanks for this script I have a question though, how can I parse only one variable? I found the regex
Any hints please? |
This comment has been minimized.
This comment has been minimized.
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 |
This comment has been minimized.
This comment has been minimized.
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() { Which is also listed wrote in the end of the more detailed version of Prasanthmj's code. |
This comment has been minimized.
This comment has been minimized.
Hi Henrixbor , thanks for the advice for me this total program newbie..it works..will continue the rest.. |
This comment has been minimized.
This comment has been minimized.
hi Prasanth, can you help me? but, I have an error here, |
This comment has been minimized.
This comment has been minimized.
Hi, have the same error on parseMessageData function, can you help me? |
This comment has been minimized.
This comment has been minimized.
I have added a check in parseMessageData for messages being empty case. |
This comment has been minimized.
This comment has been minimized.
Hi, I know this is an old thread, any help would be appreciated. My filter is returning returning only 1 of 2 emails when both are visible and present in the inbox. threads are turned off. I stripped the filter down to the basics to try and identify the problem. I'm only filtering on emails send from another email address I own. either the filter is not returning all results or there is a problem with the array. Apologies. very new to this.
|
This comment has been minimized.
This comment has been minimized.
One thing you can do is trying with a partial match and see if it makes any difference:
|
This comment has been minimized.
This comment has been minimized.
thanks - appreciate your quick reply. |
This comment has been minimized.
This comment has been minimized.
hi, apologies, it took some time to come back to this project due to my work commitments. I have tried what you have suggested and unfortunately i am still struggling. I have >300 emails in my inbox, all from the same sender. The search is returning x5 random emails. If i add no filter I get all emails from junk + the 5 random emails. Any ideas? |
This comment has been minimized.
This comment has been minimized.
I do actually have the same problem. The code does not open threads, but only shows the first message within the thread. Even when I deactivate the thread, it does not show the rest of the messages. Can anyone help? EDIT: I was able to solve the problem. What I did is within the getRelevantMessages after you search the threads, I tried to convert the messages-Array to a one-dimensional array. After that, all worked out fine! var arrToConvert=[]; for(var i = threads.length - 1; i >=0; i--){
} for(var i = 0; i < arrToConvert.length; i++) Logger.log(messages); return messages; |
This comment has been minimized.
This comment has been minimized.
thanks @FHaslboeck worked for me too |
This comment has been minimized.
This comment has been minimized.
I am trying to extract name and phone number from email like these Feedback DetailsCaller Name: Mr name can anyone help me? |
This comment has been minimized.
This comment has been minimized.
Hi! How can I get this script to display in google sheets so when I run it the information that populates in the browser instead populates to google sheets? |
This comment has been minimized.
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.