A Google Sheets SEO rank tracker that records the AI Overview too

HasData's Sheets rank tracker guide makes the point directly: an AI Overview above the results changes what position 1 is worth, so a position alone describes less than it used to. It suggests one column for whether an Overview appeared and a second for whether your domain was cited, and notes that a keyword holding rank 3 can lose visibility by gaining an Overview that does not cite you.

The Linkeddit test project shows both cases. Of 12 keywords checked on 2026-10-28, an AI Overview appeared for 8 and cited the project's domain in 7 of those 8. The keyword cookieless analytics ranked outside the top 10 yet its Overview cited the domain, and umami analytics had an Overview that did not. A rank-only sheet would score the first as a miss and the second as nothing.

Rank data is noisy, and people who watch it say so. An SEO at an agency wrote on Reddit about a page that kept falling out of the results: "Only for that page to drop out of the top 100 rankings for a period of time (sometimes a few days / sometimes a week or so)." That is why a Sheets tracker should keep the check date and the raw result with each row.

The pages that rank for this query offer Marketplace add-ons such as SERPHouse's Keyword Rank Tracker, free sheets from SEO Review Tools and Coefficient, and automation recipes using DataForSEO through n8n. They solve collection: an API key, an Apps Script trigger, a scheduled run. HasData's walkthrough has you add an autoRankScanning trigger under Extensions, Apps Script, Triggers.

What they leave to you is the data source and its upkeep: your own API key and your own script to maintain. Linkeddit's Search tracking already runs the weekly checks and records the Overview. Claude only has to carry the result into a file.

In the test project, change_text says first check for every checked keyword, so there is no previous rank to compare yet. Thirteen keywords read not checked yet, with a null rank. Ask Claude to leave those cells blank. Screpy's Sheets SEO templates guide says the same: a missing result needs a state such as check failed, not an invented position.

Before you save the first report, decide the columns once and keep them. Keyword, rank, previous rank, change, ranking URL, Overview shown, Overview cites us, position 1 domain and check date are all fields the call returns, so a later file can be compared with an earlier one line by line.

Linkeddit also defines rank as the best organic position on the checked results page, 1 to 10, with null meaning not in the top 10. It is a fixed-scope observation, not an average position. Keep it apart from Search Console averages if you add those later.

Google's page lists create_file and read_file_content, so Claude can read last week's report and create this week's. It does not document writing cells into an existing Sheet. Plan for one new dated file per run and compare files, not for a single live tab that updates itself.

Because the connector inherits your permissions, Google advises reviewing every action Claude takes. Approve the file before it is created.

Wait for a second weekly check before acting, because one check is a snapshot: all 12 checked keywords on the test project read first check on 2026-10-28. In the test project every checked keyword reads first check, so there are no movements to interpret yet. Wait for a second weekly check before you call anything a gain or a loss.

Then look at change, which Linkeddit defines as previous rank minus latest rank, so a positive number means the keyword moved up. It is null when either check was outside the top 10. For those keywords, compare the AI Overview columns instead.