Import Google Search Console Data to Sheets

In this guide, we’ll walk through how to pull Google Search Console data data directly into Google Sheets, using the API Connector add-on for Sheets.

CONTENTS

• Before You Begin
• Part 1: Connect to the Google Search Console API
• Part 2: Create a Google Search Console API Request
• Part 3: Pull Google Search Console API Data into Sheets
• Part 4: Other Google Search Console Data
• Appendix: Connect with an API Key

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO THE GOOGLE SEARCH CONSOLE API

If you haven’t connected API Connector to Google Search Console before, you’ll first need to initiate the connection as follows:

1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
2. In the list of available connections, find “Google Search Console” and click Connect.
   
   
3. You will be directed to google.com and asked to allow API Connector to view your Google Analytics data.
   
4. You’ll then be returned to your Google Sheet, and can verify that your Google Search Console connection is active in the Connections screen.
   

PART 2: CREATE A GOOGLE SEARCH CONSOLE API REQUEST

For our first request, we’ll pull in summary search performance statistics for your website. Search analytics data from the Google Search Console API is accessed by sending POST requests to this API Request URL:

https://searchconsole.googleapis.com/webmasters/v3/sites/https%3A%2F%2Fmixedanalytics.com/searchAnalytics/query

Where it says https%3A%2F%2Fmixedanalytics.com, substitute in your own URL. Make sure to encode it or the API won’t recognize it.

Breakdown dimensions and dates are specified in the POST body, like this:

{
  "dimensions": [
    "QUERY"
  ],
  "startDate": "2021-06-01",
  "endDate": "2021-06-15"
}

The “QUERY” dimension will return a list of search queries.

PART 3: PULL GOOGLE SEARCH CONSOLE API DATA INTO SHEETS

We can now enter our values into API Connector and start importing Google Search Console data into Google Sheets.

1. In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
   
   
2. Under headers, enter Key = content-type, Value = application/json.
   
   
3. In the POST Body section, paste in the POST body we created above.
   
4. Choose ‘Google Search Console’ from the authentication dropdown.
   
5. Create a new tab and click ‘Set current’ to use that tab as your data destination.
6. Name your request and click Run. A moment later you’ll see search analytics stats for each of your keywords populate your sheet.
   

PART 4: OTHER GOOGLE SEARCH CONSOLE API DATA

View the full list of available search analytics parameters here.

APPENDIX: CONNECT WITH AN API KEY

1. While logged into your Google account, navigate to https://console.developers.google.com/ and click Select a Project.
   
2. A modal will appear, prompting you to choose an existing project or create a new one. You can do either, but here we’ll create a new one.
   
3. You’ll now see a screen asking you to name your project. We’ll call it ‘Search Console’. Click Create.
   
4. We now want to enable the API via the Library. The navigation is pretty complex, but you can find the Library through the ‘APIs and services’ menu.
   
5. Search the Library for “search console” and click to open the result
   
6. Click Enable
   
7. You should now be redirected back into the dashboard. Click Credentials
   
8. We’re looking for an API key, but by default Google only prompts for OAuth Client ID and Service account credentials, so we need to click the “view all” credentials link:
   
9. “API key” will now be available from the dropdown menu. Select it.
   
10. Your API key will now appear in a modal. From this screen, you can also restrict your key to help prevent unauthorized use. Congrats! You can now access the Google Search Console API via API key.