Export Search Console data to Google Sheets past the UI's 1,000-row limit, including the anonymised queries that stop your totals from ever adding up.
The Search Console interface caps exports at 1,000 rows, which for most sites hides the long tail where the actual opportunities are. The API has no such limit, returning 25,000 rows per request and paginating beyond that. This guide covers the free Apps Script route, plus the two data quirks that make Search Console numbers confusing: anonymised queries that stop totals adding up, and a reporting lag of a few days.
Search Console to Google Sheets for free (Apps Script)
Apps Script ships a built-in Search Console service, so there is no OAuth client to configure. In the script editor open Services, add Search Console API, and it authenticates as the signed-in Google account. That account needs access to the property in Search Console.
Property URLs must be written exactly as Search Console stores them. Domain properties take the form sc-domain:example.com, while URL-prefix properties need the full origin including the trailing slash.
function importSearchConsole() { // Domain property: "sc-domain:example.com" // URL-prefix property: "https://example.com/" (trailing slash matters) const SITE = "sc-domain:example.com"; // Data lags 2-3 days and the newest days are incomplete, so end the // window a few days back rather than yesterday. const end = new Date(); end.setDate(end.getDate() - 3); const start = new Date(end); start.setDate(start.getDate() - 28); const fmt = (d) => Utilities.formatDate(d, "UTC", "yyyy-MM-dd"); const rows = []; let startRow = 0; const ROW_LIMIT = 25000; // maximum per request while (true) { const response = SearchConsole.Searchanalytics.query({ startDate: fmt(start), endDate: fmt(end), dimensions: ["query", "page"], rowLimit: ROW_LIMIT, startRow: startRow, }, SITE); if (!response.rows || !response.rows.length) break; response.rows.forEach((r) => { rows.push([ r.keys[0], // query r.keys[1], // page r.clicks, r.impressions, r.ctr, // fraction, format as % in Sheets r.position, ]); }); if (response.rows.length < ROW_LIMIT) break; startRow += ROW_LIMIT; } const header = ["Query","Page","Clicks","Impressions","CTR","Position"]; const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("Search Console") || ss.insertSheet("Search Console"); sheet.clearContents(); sheet.getRange(1, 1, 1, header.length).setValues([header]); if (rows.length) { sheet.getRange(2, 1, rows.length, header.length).setValues(rows); }}A caveat about the numbers this produces. Because Search Console withholds rare queries for privacy, the clicks in a query-dimensioned export will always total less than your site's actual clicks. If someone asks why the spreadsheet disagrees with the Search Console overview, that is the reason, and the fix is to take totals from a request with no dimensions rather than summing rows.
Also note position is an average weighted by impressions across the whole period, so averaging the position column across queries produces a number that means very little. Weight by impressions if you need a site-level figure.
The Brooked method
Brooked handles the pagination, formats CTR and position sensibly, and appends each scheduled pull rather than overwriting, so the sheet becomes a permanent archive that outlives Search Console's 16-month window. Query, page, country and device dimensions can be combined in one import.
Step 1: Install Brooked
Install Brooked from the Google Workspace Marketplace. Free tier includes 100 imports per month, no credit card required.
Step 2: Connect Search Console
Choose Google Search Console in Brooked and authorise with a Google account that has access to the property. Pick the property, the dimensions you want, and a date range.
Step 3: Schedule the refresh
Set the import to re-run daily or weekly. Scheduling matters more here than for most sources: data older than 16 months is deleted permanently, so a regular export is the only way to keep year-on-year history.
Brooked AI, Search ConsoleMethod comparison
| Method | Cost | Setup | Refresh | Two-way | Best for |
|---|---|---|---|---|---|
| Search Console UI export | Free | 1 min | Manual | No | A quick look, capped at 1,000 rows |
| Apps Script + Search Console API | Free | 20 to 30 min | Time-driven trigger | No | Full row counts and scheduled pulls |
| Looker Studio | Free | 5 min | Live | No | Dashboards rather than spreadsheet analysis |
| Brooked | Free tier · Pro $29/user/mo | ~2 min | Scheduled (daily, weekly) | No | Scheduled SEO reporting in Sheets |
Common use cases
- Striking distance: queries ranking 11-20 with impressions worth chasing
- CTR analysis: pages ranking well but under-clicked, usually a title problem
- Cannibalisation: several pages competing for the same query
- Long-term archive: keep data beyond the 16-month retention window
- Content ROI: join query performance against publish dates already in the sheet
Troubleshooting common issues
Related Articles
- How to Connect Metabase to Google Sheets
- How to Connect Looker to Google Sheets
- GA4 Custom Dimensions in Google Sheets: The customEvent API Format Nobody Documents
Frequently asked questions
Why does Search Console only export 1,000 rows?
That is a limit of the web interface, not of your data. The Search Analytics API returns up to 25,000 rows per request and supports pagination beyond that with startRow, so a site with tens of thousands of queries can be exported in full. The UI cap is the single most common reason people move to the API.
Why don't my query clicks add up to the total clicks?
Search Console withholds queries that too few people searched, to protect user privacy. Those impressions and clicks are counted in your site totals but have no query row attached, so summing the query dimension always comes up short. This is expected behaviour and not a bug in your export; use the totals from an unsegmented request when you need accurate site-level numbers.
How far back does Search Console data go?
Sixteen months, and older data is not recoverable once it ages out. That is the main argument for scheduling a regular export into Sheets: it builds a permanent archive that outlives the rolling window, which matters for year-on-year comparisons.
Why is the most recent data missing?
Search Console data lags by roughly two to three days, and the final day or two can be incomplete before settling. A daily export should therefore request a window ending a few days ago rather than yesterday, otherwise you will record artificially low numbers and then wonder why the figures changed later.
Can I connect Search Console to Sheets for free?
Yes. Apps Script includes a built-in Search Console advanced service, so a free script running on a free time-driven trigger can pull the full data set with no third-party tool at all. Looker Studio also connects free, though it produces dashboards rather than a spreadsheet you can manipulate.
What are the API row and rate limits?
25,000 rows per request, paginated with startRow for more. Quotas are applied per site and per user per minute and per day, and are generous for reporting use, but a script looping many dimension combinations across long date ranges can exhaust them, so batch by date rather than requesting everything at once.
Connect Search Console to Google Sheets today.
Free tier: 100 imports per month, no credit card required. Or see the full Search Console integration details.
Install Brooked free →
