Now that we have the data extracted, the next step is to save the data to a Google Sheet. It will take only a few extra steps to parse the date. Note: I didn’t parse the date to a Date() object. Templ.records = parseMessageData(getRelevantMessages()) Var templ = HtmlService.createTemplateFromFile('parsed') No matches couldn't parse continue with the next message Here is the script: function parseMessageData(messages)
Once the regular expression is ready, parsing out the data is easy. This makes it easier to build the regular expression. It is .Ĭopy and paste the message text to the text area of the tool and then compose the regular expression step-by-step. There is one tool that will make it quite easy to build the regular expression. Regular expression is one of the easily available feature in such situations. The next step is to parse and extract the data we are interested in, from the message. The web page should display the messages. (click on the link for the latest development version) At the end of the sequence, it will give you the link to open the script in the browser. Go to the menu item: Publish → Deploy as web app. You can customize the search filter so that it fetches the right email messages. Var templ = HtmlService.createTemplateFromFile('messages') Var filter = "from: AND subject:Transaction confirmation" Now, here is the script to fetch and display the messages: function getRelevantMessages() Do: File → New and create a new HTML file.
Let us display the messages returned by the search.įirst, add an HTML template. If there are too many messages matching the criteria, the search will take a long time to fetch all the messages. This is to limit the number of messages returned and to make the script run faster. Note that I am passing the second and third parameter to the search() function. So the search function becomes: arch("from: AND subject:Transaction confirmation",0,10) This function searches for messages from “” and with emails having subject containing “Transaction confirmation …”. Var threads = arch("from: AND subject:Transaction confirmation",0,10) For the purpose of this project, we need only the transaction confirmation emails and not the marketing emails from the bank. You can use the search operators to filter and get the transaction emails. Read more about the Gmail search operators in this article. The search feature in Gmail is quite powerful and you can get to the right emails using the gmail search operators. Create a standalone web app in Google Apps Scriptįor this project, we need to create a standalone web app in Google Apps Script. With a little customization for your own email messages, you can automate this process for your own requirements. By the end of the month, I will have a nice overview of all the expenses organized in a Google Sheet ready to process and analyze. I will parse and collect the expense data into a Google Sheet. My bank sends transaction confirmation emails for each transaction in my account. Extracting data from Gmail messages and then routing that data to other automation steps is an easy task to do using Google Apps Script.