How to use the Google Search Console API in Google Sheets

Google Sheets offers extensive possibilities for SEO reporting. The Search Console API supports countless customizations. Combining the two lets you query all your Search Console data directly in a spreadsheet – no BigQuery or email storage needed. Below are step-by-step instructions for setting this up.

Requirements

• Search Console property and Google Sheets using the same Google account.

In the appendix, you will find only one method and a script that can be used as a basis for such automation.

Before you can use the script, you need to authenticate to Google via Cloud Console. I have tried several methods and this one seems to work best.

To create a new project, click on the drop-down menu to the right of 'Google Cloud' and then click on 'New Project'.

Google Cloud Settings

• Choose a name for the project. Click 'Create'.

• Then simply type "search console api" in the search box. A drop-down menu will open and you will find the API you want. Activate the API.

• Then go back to the left menu and click on 'OAuth consent screen'. Then click on 'Edit application' and select the internal or external type (internal is recommended – you need to be a Google Workspace user for internal). Fill in the required fields, including the application name, contact email and your domain.

• Add the section "/auth/webmasters.readonly" to allow read-only access to Search Console data. To do this, click 'Add or remove scopes' on page 2. You'll find it on the third page under Google Search Console API. When the confirmation screen is complete, return to the left-hand menu and click on 'Credentials'.

• Then click 'Add Credentials' and select 'OAuth Client ID'. Enter the following value in "Authorised redirect URIs":

https://script.google.com/macros/d/{SCRIPTID}/usercallback

• Replace "{SCRIPTID}" including the brackets with the Apps Script ID from your sheet. You can find this in the project settings under Apps Script (see screenshot after next).

• Make a note of the Google Cloud project number and return to the Apps Script project. Go to the Project Settings page and set the GCP project to the project number you noted above. On this page, also enable the option "Show the appsscript.json manifest file in the editor".

Google Apps Script Settings

• Open the appsscript.json file in your code editor and add the following line (you may need to enable the display of appsscript.json in the project settings first):

"oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/webmasters.readonly", "https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/script.send_mail"],

Google Apps Script Code

If you have done everything correctly, you should now be verified with the form.

Below is a sample script that you can use to make API requests:

function getSearchAnalyticsData() {

// Replace with your own values

var propertyUrl = 'YOUR_WEBSITE_URL'; // <-- Placeholder here

var startDate = '2023-06-06'; // for example

var endDate = '2023-10-06'; // for example

var dimensions = ['query'];

var metrics = ['clicks', 'impressions', 'ctr', 'position'];

var url = 'https://www.googleapis.com/webmasters/v3/sites/' +

encodeURIComponent(propertyUrl) + '/searchAnalytics/query';

var request = {

'startDate': startDate,

'endDate': endDate,

'dimensions': dimensions,

'rowLimit': 10000,

'searchType': 'web',

'fields': 'rows',

'aggregationType': 'auto',

'metrics': metrics

};

var response = UrlFetchApp.fetch(url, {

headers: {

Authorization: 'Bearer ' + ScriptApp.getOAuthToken()

},

'method' : 'post',

'contentType' : 'application/json',

'payload' : JSON.stringify(request, null, 2)

});

var json = JSON.parse(response.getContentText());

var rows = json.rows;

// Write the results to a sheet

var sheet = SpreadsheetApp.getActiveSheet();

sheet.clearContents();

sheet.appendRow(['Query', 'Clicks', 'Impressions', 'CTR', 'Position']);

for (var i = 0; i < rows.length; i++) {

var row = rows[i];

sheet.appendRow([row.keys[0], row.clicks, row.impressions, row.ctr, row.position]);

}

}

Replace "YOUR_WEBSITE_URL" with the actual Search Console URL when you need to use this script.

As an alternative to 'query', you can also use 'page'. All customisation options can be found in the official Google documentation: https://developers.google.com/webmaster-tools/v1/searchanalytics/query?hl=de

Troubleshooting

When you run the script for the first time, Google may not trust it. Here, simply click on Advanced and then continue with the email.