Gmail to Google Sheets | How You Can Export Emails and Other Data to Spreadsheets

Are you dreaming of having all your Gmail data as records in a spreadsheet? This may be beneficial in many ways, for example, to analyze your email subscriptions, to track communication history with customers in your self-made Google Sheets CRM, or simply as a backup archive. However, there is no button that will extract Gmail to Google Sheets with one or a couple of clicks. Yet, you have a few options and workarounds to get this job done. Read on and apply the one you like the most. 

How can you extract email from Gmail to Google Sheets?

Let’s start with a mediocre solution that consists of three high-level steps:

• Exporting Gmail data as an MBOX file (email messages) or a JSON file (user settings)
• Converting the exported file to CSV 
• Uploading the CSV file into Google Sheets

Feel free to jump 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 using Google Takeout

Select data

Go to https://takeout.google.com/ and click Deselect all. 

Now you can 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 Delivery 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 files from your Gmail. This process can take a long time to complete, depending on the amount of data to be exported. You’ll receive an email when your export is done. And here is how it looks once completed.

Now you need to download the MBOX file to your device. If you selected the option of exporting to cloud storage, you’ll be able to open the file from it. For example, here 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’ve already tried). 🙂 But this does not mean that you can’t export Gmail emails to Google Sheets.

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 into Google Sheets. Open your spreadsheet, go to File => Import and upload the CSV file with your Gmail data. Here is what the result of our export of Gmail emails 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 (WITHOUT CODING)

The instructions provided in this article are sufficient for creating an automated Gmail to Google Sheets integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API. If you need help with the setup, we can offer this premium integration at an additional fee.

Some readers may be scared of the term API, but it’s the most actionable option for linking Gmail to Google Sheets. Besides, with Coupler.io, you won’t have to do any coding. The solution consists of the following steps:

• Enable the Gmail API and get credentials
• Obtain a Google OAuth Access Token
• Extract data from Gmail to Google Sheets

Enable the Gmail API and get credentials

We need the access token to access Gmail and other Google services (if necessary) via API. This will let us connect Gmail to Google Sheets for a direct data flow. Follow the instructions below to obtain your access token in less than 5 minutes. It may look tricky since Google put in a lot of effort to protect your data under 7 locks. So, while you are following this guide, you can imagine that you are a hacker trying to break into the Pentagon 🙂

Step 1: New project on Google API Console

Use this link to create a new project on Google API Console (log in to your Google account if requested). Name your project and click Create.

Step 2: Enable API

Select your project from the dropdown list, then click Enable APIs 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: Set up OAuth consent screen

Select External as the user type and click Create. 

Configure the following parameters:

• App information
  • App name – insert the name of the app asking for consent (e.g. Coupler.io or Personal)
  • User support email – select your email address
• Developer contact information
  • Email address – insert your email address

Click Save and Continue.

On the Scopes page, you don’t need to configure anything, so just click Save and Continue.

On the Test users page, click Add users and insert your email address. You may also specify other users if necessary => 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 OAuth 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:

https://coupler.io

Click Create. You’ll get your Client ID and Client Secret: 

We’ll need these credentials, so copy them to some text editor and click OK. 

How to get a Google OAuth Access Token to connect Gmail to Google Sheets

Step 1: Get an authorization code

Insert your Client ID and Redirect URI into the following URL:

https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/gmail.readonly&response_type=code&access_type=offline&redirect_uri={insert you-redirect-url}&client_id={insert-your-client-id}

Make sure to insert your credentials without spaces.

You should get something like this:

https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/gmail.readonly&response_type=code&access_type=offline&redirect_uri=https://coupler.io&client_id=221534350000-6964132idj4haiu2vmrmuu58rccgl7k1.apps.googleusercontent.com

Copy the URL and paste it in an incognito window of your browser, then press Enter. You’ll need to log in to your Google account and grant coupler.io permission to View your email messages and settings.

Click Allow and you’ll be redirected to the Coupler.io home page. Copy the code from the URL on the address bar.

Do not copy the entire URL – just extract the code which is located between code= and &.

It may look as follows:

4/0AY0e-g4nBNGM-JV6i2O3arpY-9T5LsfZWGCAvmU9Lzc8kYko7mBvyYmDTNX2Py-NXMtvWg&

The authorization code is needed to obtain a Google OAuth access token.

Step 2: Get a Google OAuth access token 

To get an access token, you need to send a POST request using the following URL template:

https://accounts.google.com/o/oauth2/token?grant_type=authorization_code&code={your-authorization-code}&client_id={your-client-id}&client_secret={your-client-secret}&redirect_uri={your-redirect-url}

• {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

Here is what the URL should look like:

https://accounts.google.com/o/oauth2/token?grant_type=authorization_code&code=4%2F0AY0e-g6N37embD-8RPXRk2D3cg6t8PoTmn37JOYj9TMAKPa5W289Q8hHY7t6pR3kBtRjQg&client_id=221534350000-6964132idj4haiu2vmrmuu58rccgl7k1.apps.googleusercontent.com&client_secret=Ol_dCEoCn1C168Eb91d9ERY7&redirect_uri=https://coupler.io

To send a POST request, we’ll use Postman, a Google Chrome app for interacting with HTTP APIs. Do the following:

• Enter the request URL in the field
• Change the request type from GET to POST
• Click Send

Here is the response with your access token and refresh token:

Note: Google OAuth access tokens expire in one hour! 

Once it expires, you’ll have to repeat steps 1 and 2 to get a new one. Alternatively, you can use the refresh token to update the access token and get it right in your Google Sheets as follows.

Step 3: Automate updates of Google OAuth access token in Google Sheets

Sign in to Coupler.io, click the +Add Importer button and choose JSON Client as a source application. You can also name your importer as you like. 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 into the JSON URL field. Do not forget to specify the highlighted parameters. 

https://accounts.google.com/o/oauth2/token?grant_type=refresh_token&refresh_token={your-refresh-token}&client_id={your-client-id}&client_secret={your-client-secret}&redirect_uri={your-redirect-url}

• {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 the HTTP Method and click Proceed to Destination Settings.

In the Destination section, you need to:

• Select your Google account
• Select an existing spreadsheet and a sheet to import data to

Click Proceed to Schedule Settings.

Since the Google OAuth access token expires in one hour, let’s set the one hour interval for our importer, so it will update your 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 way similar to the above. The following data is available:

Type of data	JSON URL resource
Current user’s Gmail profile.	/profile
Lists the drafts in the user’s mailbox.	/drafts
Gets the specified draft.	/drafts/{id}
Lists the history of all changes to the given mailbox	/history
Lists all labels in the user’s mailbox.	/labels
Gets the specified label.	/labels/{id}
Lists the messages in the user’s mailbox.	/messages
Gets the specified message.	/messages/{id}
Lists the threads in the user’s mailbox.	/threads
Gets the specified thread.	/threads/{id}

Refer to 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 resource from the table:

https://gmail.googleapis.com/gmail/v1/users/{userId}/{resource}

• {userId} – your email address
• {resource} – type of data to export

Insert the JSON URL into the JSON URL field and click Continue. 

Select GET as the HTTP Method and insert the following string into the HTTP Headers field:

Authorization: Bearer {your-access-token}

• {your-access-token} – insert your access token

In the URL query string field, you can use the q: parameter. It allows you to query specific emails. For example, 

q: from:john-doe@example.com is:unread

For more on this, read the API documentation.

Then, you’ll need to choose the destination and importing schedule and run your importer. The data will be extracted from Gmail to Google Sheets. Let’s check this out in the following example.

Export Gmail emails to Google Sheets example

We’ll connect Gmail to Google Sheets to export a list of emails from a specific sender. For this, we’ll need the following parameters:

JSON URL

https://gmail.googleapis.com/gmail/v1/users/{user-id}/messages/

HTTP method

GET

HTTP headers

Authorization: Bearer {your-access-token}

URL query string

q: from:{sender’s-email}

Here is what the flow looks like:

Can you automate Gmail to Google Sheets?

The short expiration period of the OAuth access token does not let you enjoy the full functionality of the automatic data refresh 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 ready to use, tell us about it by filling out this form. We’re always working on new importers, and we need your opinion on which importer to release next.

Export Gmail contacts to Google Sheets

Gmail API does not let you export contacts. For this, you need to enable People API and add a respective scope to your app, and it will work. Here is what you need to do in brief:

Step 1: Enable the 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, since you already have them.

Step 2: Get an authorization code

Insert your Client ID and Redirect URL into the following URL with the updated scope:

https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/contacts&response_type=code&access_type=offline&redirect_uri={insert your-redirect-url}&client_id={insert-your-client-id}

The rest of the flow is the same:

• Copy the URL and paste it in an incognito window of your browser, then press Enter.
• Get the authorization code and use it to obtain the Google OAuth access and refresh tokens as we did 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

https://people.googleapis.com/v1/people/me/connections?personFields=names,emailAddresses

HTTP Method

GET

HTTP headers

Authorization: Bearer {your-access-token}

Click Save and Run, and welcome your Gmail contacts into your spreadsheet. 

Other options to import Gmail to Google Sheets

In this blog post, we did not touch the App Script option, since we promised to explain how you can import Gmail to Google Sheets without coding. However, if you’re interested in this solution, we may add it here later. Let us know if this sounds interesting, and good luck with your data!

Back to Blog