SERP to Spreadsheet: Google Results Into Sheets, No Code

The exact SERP to spreadsheet setup we give our customers: paste one script into Google Sheets, get =SERPRANK() formulas and a free rank tracker.

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.

Get your free API key on RapidAPI →

100 requests/month free · No credit card required

Now the paste-once part. Three steps:

1

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.

2

Paste the script below and save

The whole block, unchanged except one line: put your RapidAPI key inside setApiKey(). Hit the save icon.

3

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
const API_HOST = "google-serp-search-api.p.rapidapi.com";

// Run this ONCE from the editor, then delete your key from this line.
function setApiKey() {
  const key = "PASTE_YOUR_RAPIDAPI_KEY_HERE";
  PropertiesService.getScriptProperties().setProperty("RAPIDAPI_KEY", key);
}

// Internal helper: calls the API and returns the parsed data object.
function fetchSerp_(keyword, num) {
  const key = PropertiesService.getScriptProperties().getProperty("RAPIDAPI_KEY");
  const url = "https://" + API_HOST + "/search" +
    "?q=" + encodeURIComponent(keyword) +
    "&num=" + (num || 100) + "&hl=en&gl=us";
  const response = UrlFetchApp.fetch(url, {
    headers: { "X-RapidAPI-Key": key, "X-RapidAPI-Host": API_HOST },
    muteHttpExceptions: true
  });
  if (response.getResponseCode() !== 200) {
    throw new Error("API error: HTTP " + response.getResponseCode());
  }
  return JSON.parse(response.getContentText()).data;
}

/**
 * Returns the Google position of a domain for a keyword.
 * Usage: =SERPRANK("best crm for startups", "yourdomain.com")
 * @customfunction
 */
function SERPRANK(keyword, domain) {
  const results = fetchSerp_(keyword, 100).organic_results;
  const hit = results.find(function(r) {
    return r.domain === domain ||
      (r.domain && r.domain.endsWith("." + domain));
  });
  return hit ? hit.position + 1 : "Not in top 100";
}

/**
 * Returns the top N results as rows: position, title, link.
 * Usage: =SERPRESULTS("best crm for startups", 10)
 * @customfunction
 */
function SERPRESULTS(keyword, numResults) {
  const results = fetchSerp_(keyword, numResults || 10).organic_results;
  const rows = [["Position", "Title", "Link"]];
  results.forEach(function(r) {
    rows.push([r.position + 1, r.title, r.link]);
  });
  return rows;
}

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:

1
=SERPRANK("your keyword here", "yourdomain.com")

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:

1
=SERPRESULTS("your keyword here", 10)

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
const MY_DOMAIN = "yourdomain.com";  // change this

function trackRankings() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const keywords = ss.getSheetByName("Keywords")
    .getRange("A2:A").getValues().flat().filter(String);
  const history = ss.getSheetByName("History");
  keywords.forEach(function(kw) {
    try {
      const results = fetchSerp_(kw, 100).organic_results;
      const hit = results.find(function(r) {
        return r.domain === MY_DOMAIN ||
          (r.domain && r.domain.endsWith("." + MY_DOMAIN));
      });
      history.appendRow([
        new Date(), kw,
        hit ? hit.position + 1 : "Not in top 100",
        hit ? hit.link : ""
      ]);
    } catch (e) {
      history.appendRow([new Date(), kw, "Error: " + e.message, ""]);
    }
    Utilities.sleep(1000);
  });
}

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:

PlanPriceRequests/moCovers (weekly checks)Covers (daily checks)
Free$0100~20 keywords3 keywords
Pro$19.99/mo15,000~3,400 keywords~480 keywords
Ultra$49.99/mo50,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 seeWhat it meansFix
#NAME?Sheets doesn't know the formulaThe 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/403Key missing or wrongRe-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 429Quota exhaustedYou'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 errorHit the 30-second custom function capUsually 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.

Try the Google Search API free →

100 requests/month free · No credit card required

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.

Start with 100 free searches →

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.

FAQ

Frequently Asked Questions

Q Can I connect an API to Google Sheets without coding?

Yes. Google Sheets has a built-in scripting layer called Apps Script. You paste a ready-made script once through Extensions > Apps Script, and from then on you use normal spreadsheet formulas like =SERPRANK(). You never write code yourself, you just paste ours.

Q Is there a SerpApi Google Sheets add-on?

Yes, SerpApi publishes an official Sheets extension with a =SERPAPI_RESULT() formula. It works, but it locks you into their pricing: 250 free searches a month, then $25 for 1,000. The Apps Script approach in this guide does the same job against any provider, including one with a $19.99 plan that covers 15,000 requests.

Q How do I extract data from a website into a sheet automatically?

For most websites, IMPORTHTML or IMPORTXML can pull tables and lists. For anything that renders with JavaScript or blocks bots, including Google itself, you need an API that returns structured JSON plus a time-driven Apps Script trigger. The trigger runs on a schedule and appends fresh rows with no human involved.

Q How many keywords can I track for free?

The FlyByAPIs free tier includes 100 requests per month with no credit card. One rank check costs one request, so that covers about 20 keywords checked weekly, or 3 keywords checked daily. Past that, $19.99 a month buys 15,000 requests, enough for roughly 480 keywords checked daily.

Q Why does IMPORTXML not work for Google search results?

Two reasons. Google renders results with JavaScript, and IMPORTXML only reads static HTML, so the parse usually comes back empty. And Google blocks the requests IMPORTXML sends, because they come from identifiable Google-cloud IP ranges. It works on simple static pages, just not on Google itself.

Q Can I get the results into Excel instead of Google Sheets?

Yes, two ways. The lazy way: build everything in Google Sheets, then File > Download > Microsoft Excel. The native way: Excel's Power Query can call the same API endpoint directly with your key in the request header. The JSON fields are identical in both.

Q How often should I check my rankings?

Weekly is enough for most sites. Rankings move slowly outside of algorithm updates, and daily checks mostly record noise. Daily checking makes sense during a site migration, after a big content push, or on money keywords where a two-position drop costs real revenue.
Share this article
Oriol Marti
Oriol Marti
Founder & CEO

Computer engineer and entrepreneur based in Andorra. Founder and CEO of FlyByAPIs, building reliable web data APIs for developers worldwide.

Free tier available

Ready to stop maintaining scrapers?

Production-ready APIs for web data extraction. Whatever you're building, up and running in minutes.

Start for free on RapidAPI