Google Search Console API Tutorial [Import To Google Sheets]

How to connect the Google Search Console API to Google Sheets

Warning: this tutorial is one of our most technical and complex tutorials and is for informational purposes only. There are easier ways to connect Google Search Console to Google Sheets.

Google Search Console lets you easily monitor your organic traffic and in some cases resolve server errors, site load issues, and security issues like hacking and malware. You can also use it to ensure any site maintenance or adjustments you make happen smoothly with respect to search performance.

We can use the Google Search Console API to import data from Google Search Console into Google Sheets. In this tutorial, we’ll show you how to connect the Google Search Console REST API to Google Sheets in 6 steps:

Install the Apipheny add-on
Create an OAuth Client ID
Obtain an access token
Choose a Google Search Console API Endpoint
Enter the Google Search Console API request into the Apipheny add-on
Run the Google Search Console API request in your Google Sheets

Step 1.) Install and open the Apipheny add-on for Google Sheets

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Create an OAuth Client ID

Next, go to https://console.cloud.google.com/apis/dashboard and click the arrow next to Google Cloud Platform title label:

The Select a project popup will be displayed. Click the NEW PROJECT button to create a new project:

Complete the Project name field and then click the CREATE button:

Your project will be created:

In the left menu, click the Credentials item:

Click the + CREATE CREDENTIALS button to display a drop-down menu, then click the OAuth client ID item:

On the next screen, click the CONFIGURE CONSENT SCREEN button:

Choose External in the User Type section, then click the CREATE button:

Complete the fields from App information and Developer contact information sections, then click the SAVE AND CONTINUE button:

In the Scopes section, click the ADD OR REMOVE SCOPES button:

In the Update selected scopes popup sheet, choose the scopes you need, don’t forget you check the boxes on their left, not just select the rows. Then, click the UPDATE button

Your scopes are now added and displayed on the screen:

Click the SAVE AND CONTINUE button from the end of this page:

In the next section, Test users, click the + ADD USERS button then complete the user’s email address in the popup sheet and then click the ADD button:

You may need to click the ADD button multiple times to add the user. To check if it worked, the user will be displayed in the Test users section. If it is not, you need to try again until it appears in this table.

This is how it should appear when successfully added:

In the Credentials section, complete all the required fields and then click the CREATE button:

Your OAuth client has been created. Copy the client id and client secret values to a safe location because you’ll need them later:

Step 3.) Obtain an access token

In your browser address bar, enter the following URL:

https://accounts.google.com/o/oauth2/v2/auth?redirect_uri=redirect_URL&prompt=consent&response_type=code&client_id=client_id&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fwebmasters.readonly+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fwebmasters.readonly&access_type=offline

Don’t forget to replace:

redirect_URL with your redirect URL (which can be your company/business website URL or any URL);
client_id with your client id that you previously obtained.

Do not include the brackets around these values.

A sign-in page will be displayed on the screen. Choose the Google account that you used in the previous steps:

Click the Continue button:

If the following screen is displayed, click the Continue button there as well:

The browser will now navigate to the redirect URL that you provided earlier. One query parameter (code) will be added to the URL in your browser’s address bar. The code parameter is a single use login code that we will now use to acquire a refreshable access token:

Open up a terminal and compose the following curl command:

curl -X POST "https://oauth2.googleapis.com/token?code=code&redirect_uri=redirect_URI &client_id=your_client_id&client_secret=your_client_secret&scope=&grant_type=authorization_code" -d ""

You’ll need to fill in a few parts:

code – This should be the value of the code query parameter from the previous step
redirect_URI – This must be the redirect URL that you entered while creating your developer application
your_client_id – This is the client id you previously obtained at Step 2
your_client_secret – This is the client secret you previously obtained at Step 2

Do not include brackets around any of the values in the final command. then, Run the command:

The response access_token may be used in order to access the Google Search Console API for up to one hour. This is the token that you need for Step 5.

The refresh_token may be used to get a new access token when that hour is up.

How to refresh the token

Refreshing the access token can be done with the following command:

curl -X POST "https://oauth2.googleapis.com/token?&redirect_uri=redirect_URI&client_id=your_client_id&client_secret=your_client_secret" -d "refresh_token=your_refresh_token &scope=&grant_type=refresh_token" -d ""

You’ll need to fill in a few parts here as well:

redirect_URL – This must be the redirect URL that you entered while creating your developer application
your_refresh_token – Should be replaced with the value of the refresh_token field from the previous command’s response
your_client_id – This is the client id you previously obtained at Step 2
your_client_secret – This is the client secret you previously obtained at Step 2

Step 4.) Choose a Google Search Console API Endpoint

In this section, I’ll show you how to browse the Google Search Console API documentation to find an API endpoint URL that retrieves the specific information you need from your Google Search Console account. If you already know your API URL, or you want to use the same example URL as us, just skip to Step 5.

First, open the Google Search Console API documentation page: https://developers.google.com/webmaster-tools/search-console-api-original/v3/parameters

The menu on the left contains a list of grouped API endpoints:

An example endpoint group is Sitemaps. This endpoint group contains 4 endpoints.

In this tutorial, we will describe the Sitemaps: get endpoint, which retrieves information about a specific sitemap. The URL for this endpoint is:

https://www.googleapis.com/webmasters/v3/sites/siteUrl/sitemaps/feedpath

The documentation for this endpoint contains a short description of the endpoint’s purpose, its HTTP method and URL path, the accepted path parameters, the necessary scopes, the authorizations used by the endpoint, and the responses it can return:

All API calls to the Google Search Console API should be made using the https://www.googleapis.com/webmasters/v3/ base domain. All endpoints and parameters would be appended to this base domain.

Step 5.) Enter the Google Search Console API Request into Apipheny

Okay, we’re in the home stretch. Now go back to your Google Sheet and make sure that the Apipheny add-on is open on the “Import” tab. With the Import Tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) as required by your Google Search Console API endpoint. For this example, we are using the GET method.

API URL: In Step 4, we explained how you can find the Google Search Console API endpoint that you need. Now copy the complete API URL into the Apipheny add-on, where it says API URL Path, followed by any GET parameters required for your query.

For this example, we are using the Sites list endpoint, which lists the user’s Search Console sites. The corresponding URL for this endpoint is:

https://www.googleapis.com/webmasters/v3/sites

In the Headers of the Apipheny add-on section, add two rows with the following keys and values:

Header 1
Key
Authorization
Value
Bearer your_token

Header 2
Key
Accept
Value
application/json

your_token is the value of the token you obtained previously, in Step 3. There should be a literal space between Bearer and your_token.

Google Search Console API request entered into Apipheny

Step 6.) Run the Google Search Console API request in your Google Sheets

The last step is to click the Run button at the bottom of the Apipheny add-on and then your Google Search Console data will be imported into your Google Sheet.

Google Search Console data imported into Google Sheets

After making a successful request to the Google Search Console API, try querying a different Google Search Console API endpoint, or try using one of the more advanced features in the Apipheny add-on.