Skip to content
Fenn

All posts

Google Sheets Rank Tracker: 3 Ways to Build One

By Fenn9 min readRank trackingGuidesTemplates

You can build a Google Sheets rank tracker three ways. The free way is Search Console: export the Performance report to Google Sheets each week, or pull it automatically with the Search Console API from Apps Script, which returns clicks, impressions, CTR and average position by query, page, country, device and date, up to 25,000 rows per request. This tracks your average position as real searchers saw it, but only for queries where you appeared. For fixed-location daily positions and competitors, feed the sheet from a rank tracker's API instead: SerpRobot includes API access from $4.99 a month, Keyword.com lists a rank tracker API on its plans from $4, SE Ranking Core includes 25,000 API credits, and AccuRanker and Advanced Web Ranking include APIs on higher plans. Scraping Google results into Sheets with formulas is unreliable and not worth building on.

Google Sheets Rank Tracker: 3 Ways to Build One

API details come from Google's Search Console API documentation, and tracker prices from each vendor's own pricing page, read on 11 November 2026 through a US connection. Nothing here needs a download: the sheet layout is shown on this page for you to recreate.

Three ways to track rankings in Google Sheets

MethodCostWhat position meansCompetitorsEffort
Search Console export to SheetsFreeAverage position across real searchesNoLow, manual each week
Search Console API with Apps ScriptFreeAverage position across real searchesNoMedium, one-time script
Rank tracker API into SheetsFrom $4.99 a month (SerpRobot)Position at a set location and deviceYes, if the tracker tracks themMedium, depends on the API

Method 1: export Search Console to Google Sheets

In Search Console's Performance report, set the date range (for example the last 7 days), open the Queries tab and use Export, choosing Google Sheets. Repeat weekly and paste each export into a history tab with the week's date. It takes a few minutes and costs nothing, but it is manual, and the export holds the rows the report shows, not every query.

Method 2: pull the Search Console API with Apps Script

The Search Console API's searchanalytics.query method returns the same data as the Performance report. Google's documentation lists what you can request:

  • Dimensions such as query, page, country, device and date, combined as you need (for example query and date).
  • Clicks, impressions, CTR and average position for each row.
  • Up to 25,000 rows per request (the default is 1,000), with a startRow offset to page through more.
  • A dataState option: finalised data only by default, or 'all' to include fresh data that may still change.
  • Filters by query, page, country or device, and a search type such as web, image or news.

In Google Sheets, Extensions then Apps Script opens a script editor attached to the sheet. A short script can call the API for your verified property each morning, ask for query and date for the previous few days, and append the rows to a history tab. Schedule it with a time-driven trigger. Google notes that the API returns top rows rather than guaranteeing every row, and that days without data are omitted when you group by date.

Method 3: feed the sheet from a rank tracker's API

Search Console cannot tell you where you rank in a specific city on a given morning, or where a competitor ranks. A rank tracker can, and most expose an API you can call from Apps Script or a connector. Options listed on vendors' own pages in November 2026:

TrackerAPI on its pricing pageEntry price with APINotes
SerpRobotAPI access listed among features$4.99 a month per bot (75 daily checks)Google, Bing, Yahoo, YouTube
Keyword.comRank tracker API on Search Visibility plansFrom $4 a month (50 weekly keywords)Also lists an MCP server and a Looker Studio connector
SE Ranking25,000 API credits on Core$129 a month (2,000 daily keywords)100,000 credits on Growth
Advanced Web RankingUnlimited API from Agency$279 a month (14,500 weekly units)Looker Studio integration on Agency
AccuRankerUnlimited read API from Expert$1,089 a month (10,000 keywords)BigQuery on Enterprise

Before you build, check whether you need a sheet at all: Keyword.com, AWR and AccuRanker all list Looker Studio connectors, which may give clients a live dashboard without a script.

A sheet layout to copy

Recreate these three tabs in a new spreadsheet. Column names are suggestions; keep them identical across weeks so formulas keep working.

TabColumnsFilled by
KeywordsKeyword, Target page, Group (brand, product, informational), Priority (daily or weekly), Location, DeviceYou, once
HistoryDate, Keyword, Page, Position, Clicks, Impressions, Source (Search Console or tracker name)Export, script or API, every run
SummaryKeyword, Position this week, Position last week, Change, Best position in 90 days, Page ranking now, Matches target page?Formulas over History

Useful formulas for the Summary tab: AVERAGEIFS over History to average a keyword's position for a date range, a subtraction for the weekly change, MINIFS for the best position, and a comparison of the ranking page with the target page to spot cannibalization. Conditional formatting that colours changes of three or more positions makes the sheet readable at a glance.

Limits of a spreadsheet rank tracker

  • Search Console's average position blends every location and device; it will not match a check from one city.
  • It only reports queries where you appeared, so it cannot show keywords where you rank too low to be seen.
  • Sheets slow down with hundreds of thousands of rows; archive history by quarter.
  • Scripts break when APIs change; someone has to own them.
  • Formulas that scrape Google results pages directly are unreliable and not worth building on.

Past a few hundred keywords or a few clients, a dedicated tracker usually costs less than the time spent maintaining a sheet. Our guides to free rank tracking tools and rank tracking report templates cover the next step.

Where Fenn fits

Fenn does not track keyword rankings. It asks ChatGPT, Claude, Gemini, Perplexity and Grok your buyers' questions when you start a check, shows which brands and sources each answer uses, and turns each gap into a site fix with a ready prompt or a page draft for approval. Its results export to CSV, so they can sit in the same spreadsheet as your rankings. Plans start at $29 a month, set up after a demo.

FAQ

Can I track keyword rankings in Google Sheets for free?

Yes, using Search Console: export the Performance report to Sheets weekly, or pull it with the Search Console API from Apps Script. You get average position for queries where your site appeared.

How many rows can the Search Console API return?

Up to 25,000 rows per request, with a default of 1,000. Use the startRow offset to page through more, as Google's documentation describes.

Can Google Sheets track competitor rankings?

Not from Search Console, which only covers your own verified sites. Pull competitor positions from a rank tracker's API instead.

Is there a Google Sheets rank tracker template?

Use the three-tab layout on this page: Keywords, History and Summary. It works with Search Console exports, an Apps Script pull or a tracker's API.

Why does my sheet's position differ from my rank tracker?

Search Console reports an average across all searchers, locations and devices; a rank tracker checks one location and device at one time. Both are correct for what they measure.

Put this to work on your own website.

Fenn finds what your customers ask, drafts the articles and site fixes, and measures what ChatGPT, Claude, Gemini, Perplexity and Grok say about you — with every change waiting for your approval.