Semrush wants $249.95 a month to track your keyword rankings. Ahrefs wants $129. A VA checking positions by hand costs you an afternoon a week and comes back with numbers skewed by their own search history.
Meanwhile, the entire SERP to spreadsheet pipeline is a script you paste into Google Sheets once. After that, checking a ranking looks like this: =SERPRANK("best crm for startups", "yourdomain.com"). Type formula, get position.
The fastest way to get Google results into a spreadsheet without code is to paste a ready-made Apps Script into Google Sheets, connect it to a Google Search API such as FlyByAPIs, and use custom formulas like =SERPRANK() and =SERPRESULTS(). Setup takes about 10 minutes and the first 100 searches per month are free.
10 min
One-time setup
$0
100 free searches/month
20
Keywords tracked weekly, free
$230
Monthly savings vs Semrush Guru
We run FlyByAPIs, the SERP infrastructure behind this setup, serving millions of Google search requests a month, and the script below is the exact one we hand to customers who ask for a Sheets integration. Nothing here requires you to write a line of code. You copy, you paste, you get formulas.
By the end of this guide you’ll have working =SERPRANK() and =SERPRESULTS() formulas, plus a scheduled tracker that logs your rankings into a history tab every week while you sleep.
Why the free methods break before you finish reading about them
I know why you searched this. The videos ranking for this topic all promise “FREE” in capital letters. So let’s deal with the free options honestly, because two of them almost work.
IMPORTXML / IMPORTHTML
Great for static pages, dead on Google. Google blocks the requests, which arrive from known Google-cloud IPs, and the results render with JavaScript that IMPORTXML can't read anyway. You get #N/A or an empty cell.
Google Search Console export
Real data, wrong scope. GSC only shows queries where your own site already appears, with positions averaged over devices and days. No competitor visibility, no keywords you don't rank for yet.
Manual checking is the third option, and it’s worse than both. Your logged-in browser personalizes results, so the position you see isn’t the position your customers see. Incognito helps less than people think, because location still leaks in.
That leaves the method that actually works: a structured Google SERP API that returns every result as clean JSON, called from inside your sheet. “API” sounds like developer territory. It isn’t, and the next section proves it.
Bottom line:
Free scraping formulas can't see Google, and Search Console can't see your competitors. An API sees everything, and its free tier costs the same as IMPORTXML: nothing.
The SERP to spreadsheet setup, step by step
You need exactly two things: a Google Sheet and a key for a Google search results API . The key takes two minutes. Sign up on RapidAPI, subscribe to the FlyByAPIs free plan (100 requests a month, no credit card), and copy the key from the code snippet panel.
100 requests/month free · No credit card required
Now the paste-once part. Three steps:
Open the script editor
In your sheet: Extensions > Apps Script. A code editor opens in a new tab. Delete the placeholder function you see there.
Paste the script below and save
The whole block, unchanged except one line: put your RapidAPI key inside setApiKey(). Hit the save icon.
Run setApiKey once
Select setApiKey in the toolbar dropdown, press Run, and approve the permission prompt. Then delete your key from the code. It's now stored safely in Script Properties.
Here’s the full script:
| |
That’s the only time you’ll see code in this workflow. From here on, everything happens in normal spreadsheet cells.
One design note, because it matters for security: the key lives in Script Properties , not in a cell. Anyone you share the sheet with can use your formulas, but nobody can read your key out of the spreadsheet.
One thing to change if you’re not targeting the US: the script pins results to gl=us and hl=en. Selling in Germany? Make it gl=de&hl=de inside fetchSerp_, or the tracker will happily report your US rankings forever.
Your two new formulas, and what they pull
Back in the sheet, type this into any cell:
| |
A few seconds later the cell shows a number. That’s your live Google position, pulled from the top 100 results. If you’re not there, it says “Not in top 100”, which stings but at least it’s honest.
The second formula dumps the whole first page:
| |
That one spills three columns: position, title, link. Point it at any keyword you’re chasing and you get the current top 10 as rows you can sort and filter. The underlying Google Search API also returns each result’s description, domain, and displayed link, plus People Also Ask questions and related searches, so you can extend the script to spill those columns too if you want them.
Pro tip:
Those People Also Ask questions are H2 headings waiting to happen. One =SERPRESULTS() call on your target keyword hands you both the competition and the outline for the post that beats them.
Now, the important part. There’s a catch with custom formulas that no tutorial I’ve seen bothers to mention, and it will silently eat your quota:
Custom formulas recalculate, and every recalculation is a billed request
Sheets caches results while a formula's arguments stay the same, but edits, copies, and cache evictions make cells fire again. In the worst case, twenty formula cells re-firing five times is 100 requests: your entire free month. After a formula returns, copy the cells and Paste special > Values only. Keep live formulas only where you want live data.
This recalculation problem is also why formulas alone can’t track rankings over time. A formula shows you now: it has no memory. For history you need the scheduler, and that’s where this setup starts beating the paid tools.
Turn the sheet into a rank tracker that keeps history
This is the payoff section. The Reddit thread ranking first for this topic asks for exactly this: a spreadsheet that checks rankings on its own. Here’s the recipe.
Create two tabs: Keywords (your keyword list in column A, starting at A2) and History (leave it empty, with headers Date | Keyword | Position | URL in row 1). Then add this function to the same Apps Script project, below the existing code:
| |
Then schedule it: in the Apps Script editor, click the clock icon in the left sidebar (Triggers), then Add Trigger. Choose trackRankings, event source “Time-driven”, pick weekly, and you’re done. Every week, one new row per keyword lands in History with a timestamp.
Why the trigger instead of formulas? Because appendRow writes permanent rows: formulas recalculate and forget, the trigger remembers. Six months from now you’ll have a real ranking timeline you can chart keyword by keyword, and unlike rank tracker suites
, the data lives in your sheet, exportable, forever.
Bottom line:
Formulas answer "where am I today?" The weekly trigger answers "am I winning?" You want both, and together they're a working rank tracking setup built on the Google Search API for a fraction of any SEO suite's price.
Scheduled functions also dodge the 30-second execution cap that applies to custom functions : triggers get 6 minutes. With the polite 1-second pause plus API time, that covers roughly 100 to 150 keywords per run. Tracking more? Split the list across two or three triggers spaced an hour apart.
What the quota math actually looks like
Every tutorial in this space hides the cost conversation. Let’s not. One rank check costs one request, one =SERPRESULTS() call costs one request, and the math from there is simple:
| Plan | Price | Requests/mo | Covers (weekly checks) | Covers (daily checks) |
|---|---|---|---|---|
| Free | $0 | 100 | ~20 keywords | 3 keywords |
| Pro | $19.99/mo | 15,000 | ~3,400 keywords | ~480 keywords |
| Ultra | $49.99/mo | 50,000 | ~11,500 keywords | ~1,600 keywords |
For context: Semrush’s Guru plan is $249.95 a month and caps tracking at 1,500 keywords. The Pro plan here covers roughly 480 keywords checked daily for $19.99. You give up the pretty dashboards, you keep $230 a month, and full details on every FlyByAPIs tier are on the Google Search API pricing page .
The free tier’s 100 requests sound small until you remember most sites seriously track 10 to 20 keywords. Weekly checks on 20 keywords burn 80 requests a month. That’s a real rank tracker, permanently free.
When it breaks: the four errors you’ll actually hit
Something will go wrong at some point. It always does. These four cover roughly every support conversation we’ve had about Sheets setups:
| What you see | What it means | Fix |
|---|---|---|
| #NAME? | Sheets doesn't know the formula | The script isn't saved, or you're in a different spreadsheet. Scripts belong to one sheet unless you deploy them as an add-on. |
| API error: HTTP 401/403 | Key missing or wrong | Re-run setApiKey with the key pasted correctly. Check you're subscribed to the API on RapidAPI (even the free plan needs a subscribe click). |
| API error: HTTP 429 | Quota exhausted | You've used your 100 free requests, probably via recalculation. Paste-values over old formula results, then upgrade or wait for the monthly reset. |
| Loading... forever, then error | Hit the 30-second custom function cap | Usually a huge =SERPRESULTS() range or a slow moment. Retry, or move heavy work into the trackRankings trigger, which gets 6 minutes. |
If a formula returns something weird instead of erroring, compare what you got against the response schema in the search endpoint reference . Positions are 0-based in the raw JSON, which is why the script adds 1 before showing you anything.
100 requests/month free · No credit card required
The same trick works far beyond Google search
Once UrlFetchApp plus a JSON API clicks, your spreadsheet stops being a place where you paste things and becomes a place where data arrives. The pattern is identical for any structured data source. A few that our customers wire into Sheets with the same ten lines:
E-commerce
Track your products' search positions on Amazon with the Amazon rank tracking API: same formula idea, different marketplace.
Local & lead gen
Pull local business listings into rows with the Google Maps data API: names, ratings, phones, websites, one row per lead.
Market research
Enrich a target-account list with funding data via the Crunchbase company data API, straight into the columns your CRM imports.
Recruiting & content
Monitor openings with the jobs search API, or run international keyword lists through the translation API before checking foreign-market results.
Same sheet, same script editor. Only the endpoint changes.
Which route should you take?
The decision guide, including the options that aren’t ours:
This script + free tier
You track up to ~20 keywords and want them checked weekly, free, in a sheet you fully control. This is most people reading this post.
This script + Pro plan
You track hundreds of keywords, or client portfolios, and $19.99 replaces a $230+ tool. Agencies live here.
A vendor's Sheets add-on
You want zero setup, even the paste. SerpApi's extension is genuinely convenient. You pay for it: $25/mo covers 1,000 searches vs 15,000 here.
A full SEO suite
You need backlink data, site audits, and dashboards for stakeholders, not just positions. Then Semrush or Ahrefs earn their price. Different job.
Ship the sheet
The gap between “I should track my rankings” and actually tracking them is usually one intimidating word: API. I hope this post shrank that word down to what it really is here, a URL, a key, and a paste.
Ten minutes from now you can have live positions in cells. One trigger later, you have a rank history that builds itself. And the whole thing runs on a Google Search API free tier until the day 100 requests stop being enough.
100 requests/month free · No credit card required
P.S. If you extend the script in a fun direction (someone recently wired the People Also Ask output into a content calendar tab, which I thought was brilliant), I’d genuinely like to hear about it.
Oriol.
