How to connect the Google Search Console API to Google Sheets

Google Search Console lets you easily monitor 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:

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

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

To pull data from the Google Search Console API to Google Sheets, first install and open the Apipheny Google Sheets add-on.

Apipheny is a no-code API integrator for Google Sheets that you can use to make unlimited API requests, connect to unlimited APIs, save API requests, schedule API requests, and more. 

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

2. After you’ve installed Apipheny, open a Google Sheet and then click on the Add-Ons or Extensions option in the top menu. In the add-ons menu, you should see Apipheny.

Click Apipheny > Import API to open the Apipheny sidebar in your Google Sheets.

open apipheny

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;
  • <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:

Sign In page
Sign In page

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_URL – 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.

The refresh_token may be used to get a new access token when that hour is up. Refreshing the access token can be done with the following command:

curl -X POST "https://oauth2.googleapis.com/token?&redirect_uri=redirect_URL&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:

Google Search Console API documentation page
Google Search Console API documentation page

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:

Sitemaps endpoint documentation
Sitemaps endpoint documentation

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
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
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, such as:

  • Save your Google Search Console API request
  • Make a POST request to the Google Search Console API (if available)
  • Use the custom =APIPHENY() function to call the Google Search Console API request inside your spreadsheet
  • Create an API request by referencing the value of a cell in the API URL with three curly braces eg. {{{Sheet1!A1}}}

More API Tutorials

Related Posts