How to parse and extract data from Gmail to Google Sheets

Extracting data from Gmail messages and then routing that data to other automation steps is an easy task to do using Google Apps Script.

My bank sends transaction confirmation emails for each transaction in my account. I will parse and collect the expense data into a Google Sheet. 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.

With a little customization for your own email messages, you can automate this process for your own requirements.

Create a standalone web app in Google Apps Script

For this project, we need to create a standalone web app in Google Apps Script. Go to https://script.google.com. Create a new script. Give a name to the script.

Filtering the emails

The search feature in Gmail is quite powerful and you can get to the right emails using the gmail search operators. Read more about the Gmail search operators in this article.

You can use the search operators to filter and get the transaction emails. For the purpose of this project, we need only the transaction confirmation emails and not the marketing emails from the bank.

This function searches for messages from “citicorp.com” and with emails having subject containing “Transaction confirmation …”.
So the search function becomes:

Note that I am passing the second and third parameter to the search() function. This is to limit the number of messages returned and to make the script run faster. If there are too many messages matching the criteria, the search will take a long time to fetch all the messages. Setting a limit makes it faster.

Let us display the messages returned by the search.

First, add an HTML template. Do: File → New and create a new HTML file. Let us name it “messages.html”

Now, here is the script to fetch and display the messages:

You can customize the search filter so that it fetches the right email messages.

Do File → Save.

Go to the menu item: Publish → Deploy as web app.

Then provide the permissions. At the end of the sequence, it will give you the link to open the script in the browser. Open the link. (click on the link for the latest development version)

The web page should display the messages.

Parse the message

The next step is to parse and extract the data we are interested in, from the message. Regular expression is one of the easily available feature in such situations.

There is one tool that will make it quite easy to build the regular expression. It is regexr.com.

parse using regular expression

parse using regular expression

Copy and paste the message text to the text area of the tool and then compose the regular expression step-by-step. The tool will show the matches live. This makes it easier to build the regular expression.

Once the regular expression is ready, parsing out the data is easy.

Let us verify that the parsing actually gets the correct data.

Create another HTML file and name it parsed.html. We will send the parsed records to the HTML template and display it.

Here is the script:

Note: I didn’t parse the date to a Date() object. This is to keep the code simple. It will take only a few extra steps to parse the date.

gmail parsed data

Save the data to a Google Sheet

Now that we have the data extracted, the next step is to save the data to a Google Sheet.

The steps are pretty straight forward. Open the spreadsheet, then append the rows to the sheet.

Preventing the same message from getting processed again and again

If you call processTransactionEmails() multiple times, it will keep adding the same rows multiple times.

One way to prevent that from happening is to add a label to the processed emails.

We add the label payment_processing_done to the messages that are processed. Notice that we updated the search filter -label:payment_processing_done .

This means get the messages, but without the label “payment_processing_done”.

Processing the emails as it arrives in Gmail

In order to completely automate it, this script has to keep running whenever there is a new email. However, there is no easy way to trigger the script when a new email arrives.

The other alternative is to trigger this script on a timer. Let’s run this script every hour.

For each run, we have to check for the emails received in the last one hour. However, there is no direct search filter for that.

This Gmail search: newer_than:1d will get emails in the last one day. Combine it with the other operators and we have a sufficiently narrow filter.

So the updated filter becomes:

Timer Trigger

The next step is to trigger this script every 1 hour. We have to add a timer trigger. Go to the menu item: Edit → Current projects triggers

Click on the new trigger button.

Then create a timer trigger. Select the “processTransactionEmails” function for “the function to run”.

gmail check email timer

See also

Leave a Reply 0 comments

Leave a Reply: