Do you dream of having all your gmail data as records in a spreadsheet? This can be beneficial in many ways, for example, to analyze your email subscriptions, to track customer communication history in your DIY Google Sheets CRM, or simply as a backup file. however, there is no button that will extract gmail to google sheets with one or a couple of clicks. however, you have some options and workarounds to get this job done. read on and apply the one you like best.
how can you extract email from gmail to google sheets?
Let’s start with a mediocre solution consisting of three high-level steps:
- export gmail data as mbox file (email messages) or json file (user settings)
- convert exported file to csv
- upload the csv file in google sheets
feel free to skip to the next section, export gmail to google sheets using the api, if you don’t want to deal with this.
step 1: export gmail data with google takeout
select data
You can now scroll down and select mail.
If you don’t want to export all data from your Gmail, then click the All Mail data included button, deselect Include all messages in Mail, and select the specific label for your export. Click OK in the top-right corner.
After that, you can click the next step button at the bottom.
select file type, frequency and destination
select the shipping method:
- send download link via email
- add to gdrive
- add to dropbox
- add to onedrive
- add to box
select the frequency:
- export once
- export every two months for 1 year
select the file type (.zip or .tgz) and its size (1, 2, 4, 10 or 50 gb).
create the export
click the create export button and google will start creating a copy of your gmail files. this process can take a long time to complete, depending on the amount of data to be exported. You will receive an email when the export is complete. and this is what it looks like when completed.
now you need to download the mbox file to your device. if you selected the option to export to cloud storage, you will be able to open the file from there. for example, this is how it looks with gdrive:
Step 2: Convert the exported file into CSV
Unfortunately, you can’t import mbox files into google sheets (we tried that already). 🙂 but this doesn’t mean you can’t export gmail emails to google spreadsheets.
So, you need to convert MBOX to CSV. This can be done using a dedicated tool, such as an online MBOX to CSV converter or Sysinfo MBOX Exporter Tool. In our example, we used the latter option. Although it requires installation, it worked well and transformed the MBOX file exported from Gmail into CSV.
Step 3: Import CSV to extract Gmail date and body to Google Sheets
the last step is to import the resulting csv to google sheets. open your spreadsheet, go to file => import and upload the csv file with your gmail data. this is what the result of our export of emails from gmail to google sheets looks like:
Frankly, we don’t like this option because it’s manual, burdensome, and dependent on the MBOX to CSV converter. Let’s check out some other solutions.
export gmail to google sheets using the api (no coding)
some readers may be afraid of the term api, but it is the most practical option to link gmail with google sheets. plus, with coupler.io, you won’t have to do any coding. The solution consists of the following steps:
- enable gmail api and get credentials
- get google oauth access token
- extract data from gmail to google sheets
enable gmail api and get credentials
we need the access token to access gmail and other google services (if needed) via api. this will allow us to connect gmail to google sheets for direct data flow. follow the instructions below to get your access token in less than 5 minutes. It may seem complicated as Google went to great lengths to protect your data under 7 lockdowns. so while following this guide you can imagine that you are a hacker trying to break into the pentagon 🙂
step 1: new project in the google api console
use this link to create a new project in the google api console (sign in to your google account if prompted). name your project and click create.
Step 2: Enable API
select your project from the dropdown list, then click enable api and services.
Select the Gmail API in the API library, since we need to export Gmail to Google Sheets. Then click Enable.
Click Create Credentials and then go to the OAuth consent screen.
step 3: configure the authentication consent screen
select external as the user type and click create.
set the following parameters:
- application information
- application name: insert the name of the application requesting consent (eg coupler.io or personal)
- email user support: select your email address
- email address: insert your email address
click save and continue.
On the scopes page, you don’t need to configure anything, just click save and continue.
On the test users page, click add users and enter your email address. you can also specify other users if needed => click add. after that, click save and continue.
On the summary page, click back to dashboard.
step 4: create credentials
Go to the credentials menu and click create credentials. choose authenticated client id.
Choose Web application as the application type, name it if you want (or you can leave the default name), and add an Authorized redirect URI:
click create. will get your client id and client secret:
We will need these credentials, so copy them into a text editor and click ok.
how to get google oauth access token to connect gmail to google sheets
step 1: get an authorization code
insert your client id and redirect uri to the following url:
make sure to insert your credentials without spaces.
you should get something like this:
Copy the url and paste it into an incognito window of your browser, then hit enter. You will need to log in to your google account and give coupler.io permission to see your emails and settings.
click allow and you will be redirected to the coupler.io home page. copy the url code into the address bar.
Do not copy the entire URL – just extract the code which is located between code= and &.
may look like this:
authorization code is needed to get a google oauth access token.
step 2: get a google auth access token
To get an access token, you need to send a post request using the following url template:
- {your-authorization-code} – insert the authorization code obtained in the previous step
- {your-client-id} – insert your client id
- {your-client-secret} – insert your client secret
- {your-redirect- url} – insert your redirect url
This is what the url should look like:
To send a post request, we’ll use postman, a google chrome application for interacting with http apis. do the following:
- enter the request url in the field
- change request type from get to post
- click submit
Here is the response with your access token and refresh token:
Note: Google OAuth access tokens expire in one hour!
once it expires, you will need to repeat steps 1 and 2 to get a new one. alternatively you can use refresh token to refresh access token and get it right in your google sheets as follows.
step 3: automate google oauth access token updates in google sheets
Log in to coupler.io, click the +add importer button and choose the json client as the source application. you can also name your importer whatever you want. click continue.
Coupler.io is an out-of-the-box solution to synchronize data between your source and destination on a schedule. It provides ready-to-use connections, such as Google Sheets to Google Sheets, as well as customizable integrations that you can configure yourself.
insert the following url in the json url field. don’t forget to specify the highlighted parameters.
- {your-refresh-token} – insert the refresh token obtained in the previous step
- {your-client-id} – insert your client id
- {your-client-secret} – insert your client secret
- {your-redirect- url} – insert your redirect url
Click Continue.
select post as http method and click continue with destination configuration.
In the Destination section, you need to:
- select your google account
- select an existing spreadsheet and a sheet to import data
click continue to schedule setup.
Since the google oauth access token expires in an hour, let’s set the interval to one hour for our importer so that it updates its access token every hour.
The last thing to do is click Save and Run. Now you’ll always have a valid access token in your spreadsheet.
extract data from gmail to google sheets
Using the oauth access token, we can export gmail to google sheets using coupler.io, in a similar way as above. the following data is available:
see the gmail api documentation for more information on available resources and parameters.
you will need to create a json url using the basic gmail url attached with the table resource:
- {userid} – your email address
- {resource} – type of data to export
insert the json url in the json url field and click continue.
select get as the http method and insert the following string into the http headers field:
- {your-access-token} – insert your access token
in the url query string field, you can use the q: parameter. allows you to check specific emails. for example,
for more information on this, please read the api documentation.
then you will need to choose the import destination and program and run your importer. the data will be pulled from gmail to google sheets. Let’s see this in the following example.
example of exporting emails from gmail to google sheets
we will connect gmail with google sheets to export a list of emails from a specific sender. for this, we will need the following parameters:
json url
http method
http headers
url query string
This is what the flow looks like:
Can you automate Gmail to Google Sheets?
The short expiry period of the authentication access token does not allow you to enjoy the full functionality of automatic data updating by coupler.io. however, you can use our gmail to google sheets template with the preset importers.
For this, you need to install the Coupler.io add-on from the Google Workspace Marketplace, obtain the Gmail API credentials, as well as the refresh token, and follow the instructions explained in the Readme sheet.
If you need this gmail to google sheets integration out of the box, please let us know about it by filling out this form. we’re always working on new importers and need your feedback on which importer to launch next.
export gmail contacts to google sheets
the gmail api does not allow you to export contacts. for this you need to enable people api and add a respective scope to your app, and it will work. this is what you need to do shortly:
step 1: enable people api
the flow is the same as we did with the gmail api. select the google people api in the api library and click enable.
note: you don’t need to create credentials as you already have them.
step 2: get an authorization code
insert your client id and redirect the url to the following url with the updated scope:
the rest of the flow is the same:
- copy the url and paste it into an incognito window of your browser, then press enter.
- get the authorization code and use it to get the google oauth access and refresh tokens like we did it ourselves before.
step 3: import email addresses from gmail to google sheets
With a new access token, you can go to coupler.io, connect gmail to google sheets, and use the following parameters to get your gmail contacts:
json-url
http method
http headers
click save and run and welcome your gmail contacts to your spreadsheet.
other options to import gmail to google sheets
In this blog post, we didn’t touch the app script option as we promised to explain how you can import gmail to google sheets without coding. however, if you are interested in this solution, we may add it here later. let us know if this sounds interesting to you, and good luck with your data!