How to convert Emails to Google Sheets
In this tutorial, we will describe how to extract text from emails and add to Google Sheets using Parseur. In fact, many businesses rely on data from emails on a day-to-day basis and want to track them in Google Sheets. And Google spreadsheets are great because they can be accessed from anywhere and allow real time updates and collaboration. With Parseur, take your Google Sheets to the next level and have your emails data directly uploaded into your spreadsheet!
The problem: track emails data in Google Sheets
There are many reasons why you would need to get your emails to Google Sheets.
For example, this is why some of our users need to convert their emails to into Google Sheets:
- Transform Leads or user inquiries received from their website contact form: by tracking all emails in a spreadsheet, you make sure no request is left behind and unanswered. It is also a cheap but super efficient way to create your own custom CRM
- Track purchase orders (POs) and shipping fulfillment notifications: if you're a medium-sized merchant selling products on various marketplaces, you will want to have an overview of all your purchase orders and shipping fulfillment emails in a consolidated Google Spreadsheet
- Automatically build a consolidated audit trail for tax and accounting purposes: for small to medium companies, accounting is a well-known time sink. Building the list of all expenses and revenues for a tax audit is often quite consuming and has no immediate return value for the business. Hence, automating it is a must. With Parseur, you can send all your expenses and customer purchases to a single email address, create a template for each format and then let Parseur in auto pilot for building your tax and accounting audit trail.
Convinced ? Let's automate everything with Parseur and Google Sheets!
Step 1: Create a Parseur Account
You can create your Parseur account here.
Step 2: Setup up your first mailbox
We have written an extensive step by step article on how to set up your Parseur account. In case this is your first time using Parseur, we recommend you to go read it and come back here once you have a working mailbox.
Once done, you will have a mailbox that takes emails as input and automatically transform them into structured data, extracting exactly what you asked for.
Step 3: Export your emails to Google Sheets
Exports to Google Sheets are very straight forward. By default, Parseur gives you 3 links to get your data in 3 different format: Excel, CSV and JSON.
Fortunately, Google Sheets has a very handy formula named
IMPORTDATA() (see documentation) that takes a URL with CSV format and automatically imports it in a Sheet. This is the formula we are going to use.
We have already prepared that formula for you. Just go in the Export menu and copy the formula.
Next, all you have to do is:
- Connect to your Google account
- Go to your Google Sheets dashboard
- Create a new Sheet (or open an existing one)
- Paste the
IMPORTDATA()formula we gave you in one of the sheet (typically in cell
- Wait for the data to finish loading
That's it ! Your data is now in Google Sheets.
Google Sheets will now be automatically updated every hour or so with new data processed by Parseur.
Step 4: Refining your data
Filter export by date
By default, Parseur will export all the data of your mailbox to Google Sheets. If you only want to get a subset of the data, you can use the filters by year, month and day on the export page.
Export Table Field data
If you use Table Fields, you will see that you have additional formula to use on the export screen.
- The default document formula will create one row per document processed. Table field rows will be added as columns. This is usually not the desired result when having table fields.
- The Table Field formulas will create one row per table field row processed. Non table fields will be repeated on each row. This is usually the preferred option.
Correctly format numbers
If you use Parseur Number format, it will send number fields using a stop (
.) as decimal separator. If your Google Sheets is set in a locale that uses comma (
,) as separator, numbers may not be properly formated. You can fix this by changing your Spreadsheet locale configuration.
To change your Google Sheets locale config:
- open your spreadsheet
- click on File
- then click on Spreadsheet settings
- in the drop-down for Locale, choose United States
Step 5: Going further with Zapier
The default export is using
IMPORTDATA() and has a few limitations:
- Data is not added in real time but refreshed every hour or so
- You cannot change the order of the columns
- You cannot add new columns to the right of the data (they will be overwritten at the next refresh)
If you find those limitations a problem, you can use Zapier to send your parsed data to Google Sheets. Using the Zapier integration to Google Sheets, you'll get:
- Data added to your spreadsheet in real time
- You'll be free to decide on the column ordering
- You'll be able to add columns on top of the ones sent by Zapier
- You'll be able to post process your parsed data in Zapier
If you would like to know how to connect Parseur to your Google Spreadsheet, check out this article.