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:
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
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:
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:
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:
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:
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.
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.
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.
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.
Crypto API Tutorials: