How do you integrate Google Search Console Data with Google Sheets?

Google Search Console (GSC) is a free service offered by Google. Whether you are an entrepreneur, SEO specialist, marketer, site administrator, web developer, or app creator, GSC is a key asset. Formerly known as Google Webmaster Tools, it offers valuable SEO insights into website performance. It also provides an extensive suite of tools and an array of reports to enhance your website’s performance.

This free tool provides information on how Google crawls, indexes, and serves websites. Simply put, it provides insights into how search engines perceive your website, allowing you to monitor search performance by identifying search queries leading to your site, optimizing content, testing AMP pages, and much more. Furthermore, it identifies and addresses technical SEO issues boosting website performance and traffic. It is a complex but powerful tool.

Here are a few key benefits of GSC

• Review how your website performs on Google
• Submit sitemaps and new URLs for crawling
• Track search performance and Core Web Vitals
• Resolve spam issues
• Find and fix threats affecting your website
• Analyze metrics based on how Google sees your site
• Increase traffic levels and improve organic search
• See which pages Google is indexing and much more

How to import Google Search Console data into Google Sheets?

There are various ways to connect the Google Search Console to Google Sheets.

Manual Data Extraction

With Google Search Console, you can export data directly to Google Sheets or Excel or choose to download it in CSV format. Since it’s manual, you know the drill — you’ll need to handle each export, organize the data in Sheets, apply any required modifications, and more.

Google Apps Script

Google Apps Script is a built-in coding environment developed by Google — a cloud-based JavaScript platform that allows you to extend the functionality of Google Apps. With Apps Script you can quickly create business applications that integrate with Google Workspace.

You’ll need to write a custom code to call the Google Search Console API, retrieve the necessary data, and import it into Google Sheets. Additionally, the script should automate this process to keep your Sheets updated with the latest Search Console data.

Here is a step-by-step guide to connect your Google Search Console API to Google Sheets:

Step 1: Set up your project in Google Cloud

• Open Google Cloud Console
• Create a new project
• Enable the Google Search Console API in your project settings
• Set up OAuth credentials (OAuth 2.0 Client ID) and keep a record of the client_id and client_secret

Step 2: Enable Google Apps Script in Google Sheets

• Open a new document in Google Sheets
• Click on Extensions -> Apps Script
• Delete any code in the script editor

Step 3: Develop the Code

Here’s an example/sample code:

// Variables to store OAuth credentials

var CLIENT_ID = ‘YOUR_CLIENT_ID’;

var CLIENT_SECRET = ‘YOUR_CLIENT_SECRET’;

var REDIRECT_URI = ‘urn:ietf:wg:oauth:2.0:oob’;

var SCOPE = ‘https://www.googleapis.com/auth/webmasters.readonly';

// Function to get the OAuth2 service

function getOAuthService() {

return OAuth2.createService(‘SearchConsole’)

.setAuthorizationBaseUrl(‘https://accounts.google.com/o/oauth2/auth')

.setTokenUrl(‘https://oauth2.googleapis.com/token')

.setClientId(CLIENT_ID)

.setClientSecret(CLIENT_SECRET)

.setRedirectUri(REDIRECT_URI)

.setScope(SCOPE)

.setPropertyStore(PropertiesService.getUserProperties())

.setCache(CacheService.getUserCache());

}

// Function to authorize

function authorize() {

var service = getOAuthService();

if (!service.hasAccess()) {

var authorizationUrl = service.getAuthorizationUrl();

Logger.log(‘Open the following URL and re-run the script: ‘ + authorizationUrl);

}

}

// Function to fetch data from Google Search Console API

function fetchSearchConsoleData() {

var service = getOAuthService();

if (service.hasAccess()) {

var url = ‘https://www.googleapis.com/webmasters/v3/sites/YOUR_SITE_URL/searchAnalytics/query';

var payload = {

‘startDate’: ‘2024–01–01’, // Adjust the date range as needed

‘endDate’: ‘2024–01–31’,

‘dimensions’: [‘date’, ‘query’],

‘rowLimit’: 5000

};

var response = UrlFetchApp.fetch(url, {

method: ‘post’,

contentType: ‘application/json’,

headers: {

Authorization: ‘Bearer ‘ + service.getAccessToken()

},

payload: JSON.stringify(payload)

});

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

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘SearchConsoleData’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘SearchConsoleData’);

sheet.clear();

// Write the header row

sheet.appendRow([‘Date’, ‘Query’, ‘Clicks’, ‘Impressions’, ‘CTR’, ‘Position’]);

// Write the data rows

data.rows.forEach(function(row) {

sheet.appendRow([

row.keys[0], // Date

row.keys[1], // Query

row.clicks,

row.impressions,

row.ctr,

row.position

]);

});

} else {

Logger.log(‘No access to Google Search Console API.’);

}

}

// Function to schedule the data fetching

function scheduleDailyFetch() {

ScriptApp.newTrigger(‘fetchSearchConsoleData’)

.timeBased()

.everyDays(1)

.atHour(2) // Fetch data daily at 2 AM (Adjust as needed)

.create();

}

// Run this function once to set up the daily trigger

function setup() {

authorize();

scheduleDailyFetch();

}

// Main function to run everything

function main() {

if (getOAuthService().hasAccess()) {

fetchSearchConsoleData();

} else {

authorize();

}

}

Step 4: Run the Script

• Replace ‘YOUR_CLIENT_ID’, ‘YOUR_CLIENT_SECRET’, and ‘YOUR_SITE_URL’ with actual values.
• Run the setup() function to authorize the code and configure a daily trigger.
• Once authorized, the script will fetch data daily and populate it in the designated Google Sheet.

Step 5: Monitor & Adjust

• You can monitor and adjust the script to eliminate errors or add more features, like filtering by specific queries or pages.
• To fetch data for different date ranges, you can adjust the startDate and endDate in the payload object.

This setup will ensure that your Google Sheets is regularly updated with the latest Google Search Console data. It’s a free, lengthy practice.

Google Sheets Extension

Option 1: (Free) Search Analytics for Sheets

Here, you have the option to use Search Analytics for Sheets. With this extension, you can directly import data from Console API to Google Sheets. Below are the steps to be followed:

Step 1: Install Google Sheets Extension

• Open Google Drive workspace
• Create a new Google Sheets file
• Click on extension -> Add-ons -> Get add-ons
• Install Search Analytics for Sheets

Step 2: Import Google Search Console Data Query

• Launch the extension
• Select the website you want to get data from
• Set the date range
• Select Default (Web) under Search Type
• Choose the query under Group By
• Add any filters if required
• Under Aggregation Type, select Default (auto)
• Select rows as per your requirement
• Select Create New Sheet
• Click on Request Data

Now you’ll have the data populated in the Google Sheets file.

Option 2: (14-day free trial) Two Minute Reports

You can export reports for free, but investing a little more can offer more processing power and additional features. For me, Two Minute Reports (TMR) is the best choice — it’s definitely worth the cost.

Two Minute Reports is a quick, efficient, and pretty straightforward tool. It’s compatible with both Google Sheets and Looker Studio. Simply set it up and get instant reports — just like they say Plug, Play, and Report in no time. Also, customize your reports to suit your preferences and schedule and refresh data at any interval — daily, weekly, monthly, or even hourly. Start by:

Step1: Installing Google Sheet Extensions

• Go to Google Drive workspace
• Create a new Google Sheets document
• Click on extensions -> Add-ons -> Get add-ons
• Search for Two Minute Reports and install it

Step 2: Add Search Console Data Source

• Launch the extension — a sidebar appears
• Click on Add+ under Data Sources
• Select Google Search Console after naming your data source
• Sign in with the Google account where Search Console is linked to
• Complete the login process as the tool instructs you to do so

Step 3: Import Search Console Data Query

• Click the hamburger icon -> Data queries -> Add+
• Name your data query and search the imported Console data source
• Choose the Sheet where you want your data to be saved and then add A1 under ‘Cell’.
• Select the Search Console domain under ‘Query’
• Set the date range
• Opt for ‘Web’ under Google Search Type
• Under ‘Filters’, you can select options like country or device type
• Click ‘Run Query’

You see, just a few clicks, and your Google Search Console data is now in Google Sheets. Easy, right? This is just a glimpse, if you want to know more, click the link below.

https://twominutereports.com/google-search-console-to-google-sheets?utm_source=Medium0509&utm_medium=Backlink&utm_campaign=272s