How to export Google Search Console API data to Google Sheets

There have been countless times I've found myself stuck on a Friday afternoon, painstakingly copying data from Google Search Console in 1,000-row increments. It was always frustrating to realize that crucial performance trends, especially for those long-tail keywords, were hidden in the data I couldn't access due to these limitations. The standard export is simply not built for comprehensive analysis.

This is why I started looking for a way to get more data. By connecting directly to the Google Search Console API, we can overcome these restrictions and pull significantly larger data sets. This approach has been a game-changer for my workflow, allowing me to automate data collection and gain deeper insights without manual intervention.

Unlocking More Data

The 1,000-row limit in the standard export interface is a major bottleneck. It forces teams to work with incomplete datasets, leading to potential blind spots in understanding site performance. The Search Console API, however, allows programmatic requests for up to 25,000 rows in a single call. This unlocks access to a much richer set of site performance metrics that are simply unavailable through the dashboard.

For sites with thousands of landing pages, relying solely on UI exports creates significant gaps in tracking content performance. This often leads to inaccurate SEO audits because the long-tail data is missing. The API acts as a powerful, free alternative to paid rank tracking tools, providing actual click and impression data rather than relying on estimations.

Feature	Standard UI Export	Search Console API
Row limit/request	1,000 rows	25,000 rows
Automation level	Manual	Programmable
Data access	Sampled/Limited	Raw/Comprehensive

Initial Setup: Google Cloud Project

To begin, you'll need a Google Cloud project. This is where you'll enable the Search Console API and set up the necessary credentials for your scripts.

Create a Project: Go to the Google Cloud Console and select 'Create Project'.
Enable API: Search for 'Google Search Console API' in the API Library and enable it.
OAuth Consent Screen: Configure the consent screen with your email and an application name.
Create Credentials: Choose either 'OAuth client ID' or a 'Service Account' for authentication. For automated scripts, a service account is generally preferred.

A common stumbling block here is the complexity of OAuth flows. For this specific use case, you primarily need read-only access to the Search Console property you want to query. If the Cloud Console interface feels overwhelming initially, simply enabling the API is a good first step. You can refine scopes later as you become more familiar with Google's API ecosystem.

Scripting the Connection: Google Apps Script

The core of this process involves using Google Apps Script to interact with the Search Console API. This script will query the searchAnalytics.query method to pull data directly into your Google Sheet.

Open Apps Script: In a new Google Sheet, go to 'Extensions' > 'Apps Script'.
Add API Service: Paste the GSC API service library code into the editor.
Define Parameters: Set your startDate and endDate within the main function.
Set Row Limit: Crucially, set the rowLimit parameter to 25,000.
Run and Authorize: Execute the function and authorize the script to access your Search Console data.

Ensure your script has the correct authentication scopes enabled, specifically 'readonly' access for the Search Console API. Debugging often involves verifying that your JSON request structure aligns with the API's current requirements, as these can be updated by Google.

Handling Script Timeouts

Google Apps Script has a six-minute execution limit per trigger. For large datasets, this can cause scripts to fail. The solution is to implement pagination.

Instead of requesting all 25,000 rows at once, break the request into smaller chunks. The startRow parameter is key here. By incrementing startRow in a loop, you can fetch data in manageable segments (e.g., 5,000 rows per request) that complete within the time limit.

// Example of pagination logic
var startRow = 0;
var rowLimit = 5000; // Smaller chunks for pagination
var totalRowsRequested = 0;
var maxRows = 25000; // Your desired total

while (totalRowsRequested < maxRows) {
  var query = {
    'startDate': 'YYYY-MM-DD',
    'endDate': 'YYYY-MM-DD',
    'dimensions': 'date,query',
    'startRow': startRow,
    'rowLimit': rowLimit
  };
  // ... make API call with query ...
  // ... process results ...
  totalRowsRequested += results.rows.length;
  startRow += rowLimit;
}

This modular approach ensures script stability, even with API latency. I usually add logging to track progress and identify which specific page might fail if a network issue occurs.

Automating Data Refreshes

To keep your reports up-to-date, set up time-driven triggers in Apps Script.

Open Triggers: Navigate to the 'Triggers' tab in the Apps Script dashboard.
Add Trigger: Click '+ Add Trigger', select your main function, and choose 'Time-driven' as the event source.
Set Schedule: Select your preferred interval (e.g., 'Week timer' or 'Month timer').

Automating this process removes the manual labor from reporting, ensuring your team always has current data for meetings and analysis.

When Sheets Isn't Enough

While Google Sheets is excellent for many use cases, it can become slow with hundreds of thousands of rows. For very large historical datasets, consider migrating to BigQuery. It offers superior performance for querying massive amounts of data using SQL.

Metric	Google Sheets	BigQuery
Storage limit	~10 million cells	Petabytes
Calculation speed	Slow with large sets	High-speed SQL
Complexity	Low (Visual)	High (SQL)

If your sheet starts lagging significantly (often around the 50,000-row mark), it's a signal that your data storage layer needs an upgrade. BigQuery enables much more sophisticated time-series analysis for sites generating millions of impressions.

By leveraging the GSC API and Apps Script, you can bypass the export limitations, automate your data collection, and gain a comprehensive view of your search performance. This is essential for serious SEO work in today's landscape. If you're ready to move beyond manual exports, start by setting up your Google Cloud project and experimenting with these scripts.

Originally published at How to export Google Search Console API data to Google Sheets

DEV Community