Search API Connector Documentation

Print

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.

The Google Search Console API requires authentication for access, through either your own API key or an OAuth2 integration. This article primarily discusses connecting through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you are only accessing public data and would prefer to manage the process manually for free, please skip to the Appendix for step by step instructions on connecting with an API key.

CONTENTS

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.
    google-search-console-img1
  3. You will be directed to google.com and asked to allow API Connector to view your Google Analytics data.
    google-search-console-img2
  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.
    google-search-console-img3
  2. Under headers, enter Key = content-type, Value = application/json.
    google-search-console-img4b
  3. In the POST Body section, paste in the POST body we created above.
    google-search-console-img4
  4. Choose ‘Google Search Console’ from the authentication dropdown.
    google-search-console-img5
  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.
    google-search-console-img6

PART 4: OTHER GOOGLE SEARCH CONSOLE API DATA

View the full list of available search analytics parameters here.

APPENDIX: CONNECT WITH AN API KEY

This section is provided as an alternative to the method described above. Instead of clicking Google Search Console in the Connections manager, you will retrieve your personal API Key yourself. Once you have it, append it to the end of your request URL like this: https://searchconsole.googleapis.com/v1/urlTestingTools/mobileFriendlyTest:run?key=AIz...... Since you’re manually including an API key, leave OAuth2 authentication set to None.

Only public Search Console data is available via API Key. Private data (data that you need to be logged in to view) requires authentication via OAuth2.
  1. While logged into your Google account, navigate to https://console.developers.google.com/ and click Select a Project.
    google-search-console-img10
  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.
    google-search-console-img11
  3. You’ll now see a screen asking you to name your project. We’ll call it ‘Search Console’. Click Create.
    google-search-console-img12
  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.
    google-search-console-img13
  5. Search the Library for “search console” and click to open the result
    google-search-console-img14
  6. Click Enable
    google-search-console-img15
  7. You should now be redirected back into the dashboard. Click Credentials
    google-search-console-img16
  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:
    google-search-console-img17
  9. “API key” will now be available from the dropdown menu. Select it.
    google-search-console-img18
  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.
    google-search-console-img19
Previous Import Google PageSpeed Insights Data to Google Sheets
Next Import Harvest Data to Google Sheets

Leave a Comment