Do you dream of having all your Gmail data in spreadsheet files? This can be beneficial in many ways, such as analyzing your email subscriptions, tracking communication history with your clients in your own CRM Google Sheets, or simply as a backup. However, there is no button that allows you to extract Gmail emails to Google Sheets in just one or two clicks. However, you do have a few options and workarounds to accomplish this task. Read on and choose the one that suits you best.
How to Extract Gmail Emails to Google Sheets?
Let’s start with a middle-ground solution that involves three main steps:
- Export Gmail data as MBOX (emails) or JSON (user settings) files.
- Convert the exported file to CSV format.
- Import the CSV file into Google Sheets.
Feel free to skip to the next section, “Export Gmail to Google Sheets Using the API,” if you don’t want to bother with this.
Step 1: Export Gmail Data Using Google Takeout
Select Data
Go to takeout.google.com and click “Deselect All.”
Scroll down and select “Mail.”
If you don’t want to export all the data in your Gmail inbox, click the “All Mail data included” button, uncheck “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 Google Drive
- Add to Dropbox
- Add to OneDrive
- Add to Box
Select “Frequency”:
- Export once
- Export every two months for 1 year
Select the “File Type” (.zip or .tgz) and “Size” (1, 2, 4, 10, or 50 GB).
Create Export
Click the “Create Export” button, and Google will start creating a copy of your Gmail inbox files. This process may take some time depending on the amount of data to export. You will receive an email when your export is complete. And this is what it looks like when it’s done.
Now, you need to download the MBOX file to your device. If you selected the option to export to online storage, you can open it from there. For example, this is what it looks like with Google Drive.
Step 2: Convert the Exported File to CSV
Unfortunately, you cannot directly import MBOX files into Google Sheets. But that doesn’t mean you cannot export Gmail emails to Google Sheets.
Step 3: Import CSV to Extract Gmail Email Dates and Content to Google Sheets
The final step is to import the resulting CSV file into 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 Gmail email export to Google Sheets looks like:
Frankly, we don’t like this option because it is manual, tedious, and dependent on the MBOX to CSV converter. Let’s see some other solutions.
Export Gmail to Google Sheets Using the API (NO CODING)
Some people might be scared of the term “API,” but it’s the most convenient option to connect Gmail and Google Sheets. Plus, with Coupler.io, you don’t need to do any coding. The solution consists of the following steps:
- Activate the Gmail API and obtain the credentials.
- Get a Google OAuth access token.
- Extract Gmail data to Google Sheets.
Activate the Gmail API and Obtain the Credentials
We need the access token to access Gmail and other Google services (if needed) via the API. This will allow us to 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 seem complicated because Google has put a lot of effort into protecting your data, but imagine 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 the Google API Console (log in to your Google account if necessary). Name your project and click “Create.”
Step 2: Activate the API
Select your project from the dropdown list and 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 go to the OAuth API consent screen.
Step 3: Configure the OAuth Consent Screen
Select “External” as the user type and click “Create.”
Configure the following settings:
- “Application Information”
- “Application Name” – insert the name of the application requesting consent (e.g., Coupler.io or Personal)
- “User Support Email Address” – 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 can also specify other users if necessary, then click “Add.” Then, click “Save and Continue.”
On the “Summary” page, click “Back to Dashboard.”
Step 4: Create the 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 URL: https://coupler.io
Click “Create.” You 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 a Google OAuth Access Token to Connect Gmail to Google Sheets
Step 1: Get an Authorization Code
Insert your “Client ID” and “Redirect URL” 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-your-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.comCopy the URL and paste it into a private browsing window of your browser, then press Enter. You will need to sign in to your Google account and grant Coupler.io permission to see your emails and email settings.
Click “Allow,” and you will be redirected to the Coupler.io homepage. Copy the code from the URL in the address bar.
Don’t copy the entire URL – just extract the code between “code=” and “&”.
Here’s an example:
4/0AY0e-g4nBNGM-JV6i2O3arpY-9T5LsfZWGCAvmU9Lzc8kYko7mBvyYmDTNX2Py-NXMtvWgThe authorization code is needed to obtain a Google OAuth access token.
Step 2: Get a Google OAuth Access Token
To obtain 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’s 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.ioTo send a POST request, we’ll use Postman, a Google Chrome application for interacting with HTTP APIs. Follow these steps:
- Enter the request URL into the corresponding field.
- Change the request type from GET to POST.
- Click “Send.”
Here’s the response with your access token and refresh token:
{
"access_token": "ya29.a0ARr...",
"expires_in": 3600,
"refresh_token": "1//09f...N",
"scope": "https://www.googleapis.com/auth/gmail.readonly",
"token_type": "Bearer"
}Note: Google OAuth access tokens expire after one hour!
Once it has expired, you will need to repeat steps 1 and 2 to obtain a new one. You can also use the refresh token to update the access token and obtain it directly in your Google Sheets as follows.
Step 3: Automate the Google OAuth Access Token Update in Google Sheets
Log in to Coupler.io, click the “+ Add Importer” button, and choose “JSON Client” as the application source. You can also name your importer as you wish. Click “Continue.”
Insert the following JSON URL into the “JSON URL” field. Don’t 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 “Continue to Destination Settings.”
In the “Destination” section, you need to:
- Select your Google account
- Select an existing spreadsheet and a sheet to import the data into
Click “Continue to Schedule Settings.”
Since the Google OAuth access token expires in one hour, we will set an interval of one hour for our importer to update your access token every hour.
The last thing to do is click “Save and Run.” Now, you will always have a valid access token in your spreadsheet.
Extract Gmail Data to Google Sheets
Using the Google OAuth access token, we can export Gmail to Google Sheets using Coupler.io, just like we did above. The following data is available:
You can refer to the Gmail API documentation for more information on available resources and parameters.
You will need to create a JSON URL using the Gmail base URL attached to the table resource:
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 “URL Query String”:
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 search for specific emails. For example:
q: from:[email protected] is:unreadFor more details, refer to the API documentation.
Then, you will need to choose the destination and scheduling for the import and execute your importer. The data will be extracted from Gmail to Google Sheets. Let’s see an example below.
Example of Exporting Gmail Emails to Google Sheets
We will connect Gmail to Google Sheets to export a list of emails from a specific sender. For this, we will 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-email-address}
Here’s the workflow:
Can You Automate Gmail Export to Google Sheets?
The short duration of the Google OAuth access token does not allow you to take full advantage of the automatic data update features by Coupler.io. However, you can use our Gmail export to Google Sheets template with the predefined importers.
For this, you need to install the Coupler.io add-on from the Google Workspace Marketplace, obtain the Gmail API credentials and the refresh token, and follow the instructions explained in the documentation.
If you need this ready-to-use Gmail to Google Sheets integration, let us know by filling out this form. We are always working on new importers and need your input on what we should release next.
Export Gmail Contacts to Google Sheets
The Gmail API does not allow you to export contacts. For that, you need to activate the People API and add a corresponding scope to your application, and it will work. Here’s what you need to do briefly:
Step 1: Activate the People API
The process is the same as what we followed for the Gmail API. Select the Google People API in the API library and click “Enable.”
Note: You don’t need to create new credentials because you already have them.
Step 2: Get an Authorization Code
Insert your “Client ID” and “Redirect URL” into this 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={your-redirect-url}&client_id={your-client-id}The rest of the process is the same:
- Copy the URL and paste it into a private browsing window, 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 Gmail Email Addresses to Google Sheets
With a new access token, you can go to Coupler.io, connect Gmail to Google Sheets, and use the following settings 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 in your spreadsheet.
Other Options for Importing Gmail to Google Sheets
In this blog article, we didn’t cover the “Apps Script” option because we promised to explain how to import Gmail to Google Sheets without coding. However, if you are interested in that solution, we can add it later. Let us know if you’re interested, and good luck with your data!
