-
-
Save kapiljhajhria/6b6ab85695d63e1e01ffa0dc696da2ad 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