Pull Live Google Search Results Into Google Sheets (a =SERPENT() Custom Function)

You live in spreadsheets. You want a cell to say "we rank #4 for best running shoes in the US today" — without leaving Google Sheets, without writing a server, without babysitting a scraper.

So you reach for the obvious tool: IMPORTXML. You paste in a Google search URL, write an XPath, and… #N/A. Or #ERROR!. Or it works for ten minutes and then quietly stops.

This is not your fault. Both of the "free" ways to get Google results into a sheet are broken in 2026. There is one method that actually holds up: a tiny Google Apps Script custom function backed by a search API.

This guide gives you the complete, copy-paste code for a =SERPENT() function, plus the caching and triggers that keep it from blowing through Google's quotas. Let's build it.

Why IMPORTXML and the old API route both break

The two common methods fail for two different reasons, and knowing why saves you hours.

1. IMPORTXML against google.com. Google's results page is rendered with JavaScript and protected by anti-bot systems. IMPORTXML only fetches raw HTML — it can't run scripts, so it sees almost nothing useful. Google also actively blocks automated requests from spreadsheet imports. The result is #N/A, #ERROR!, or empty cells.

Even on the rare day it returns something, the XPath you wrote is married to Google's exact HTML class names. Google changes those constantly, so your formula is permanently fragile. Google's own docs note that all IMPORT functions also auto-refresh hourly and can be blocked until you re-grant access — not what you want for a tracker.

2. The old "get a Custom Search API key" route. For years the workaround was Google's Custom Search JSON API. That door is now effectively closed to new users. If you've hit a wall trying to provision a key, you're not imagining it — we cover the details and the migration path in the Google Custom Search API shutdown and migration guide.

And remember: directly scraping Google from a spreadsheet is against Google's terms and gets your account flagged. We dig into the legality in is scraping Google legal in 2026. The clean answer is to never touch Google yourself — let an API do it.

The durable plan: an Apps Script custom function

The fix is a Google Apps Script custom function — a JavaScript function you write once that becomes a formula you can type into any cell, like =SERPENT("best running shoes","us").

Under it sits a single HTTPS call to Serpent's Google SERP API. The API does the hard part — there's no proxy pool or headless browser for you to manage, and it handles blocks and CAPTCHAs so you don't. You get back tidy JSON; the function drops the bits you want into cells.

That's the whole architecture. A formula on top, a search API underneath, and a cache in the middle so Google Sheets doesn't hammer the API every time it recalculates. Let's wire it up step by step.

Step 1: Get an API key and your endpoint

First, grab a key — it takes about a minute and you get 10 free Google searches with no card.

Sign up at the dashboard, copy your key (it looks like sk_live_…), and keep it handy. The endpoint we'll call is:

GET https://api.apiserpent.com/api/search?q=best+running+shoes&country=us
Header: X-API-Key: sk_live_your_key

That returns up to 100 organic results in one call as JSON. You can test it instantly in the live playground before writing any code. The exact shape (organic, ads, peopleAlsoAsk, featuredSnippet, aiOverview…) is in the API docs.

Step 2: The basic =SERPENT() custom function

Open your Google Sheet, go to Extensions → Apps Script, delete the placeholder, and paste this in. It returns the rank position of a target domain, or "Not in top 100" if it's not found.

// === Serpent SERP custom function for Google Sheets ===
const SERPENT_KEY = 'sk_live_your_key_here';   // paste your key
const SERPENT_URL = 'https://api.apiserpent.com/api/search';

/**
 * Returns the Google rank of a domain for a keyword.
 * Usage:  =SERPENT_RANK("best running shoes", "nike.com", "us")
 *
 * @param {string} keyword  The search query.
 * @param {string} domain   The domain to find (e.g. "nike.com").
 * @param {string} country  Two-letter country code, default "us".
 * @return The position (1-100) or a "not found" message.
 * @customfunction
 */
function SERPENT_RANK(keyword, domain, country) {
  if (!keyword) return 'Missing keyword';
  country = country || 'us';
  const data = serpentFetch_(keyword, country);

  const organic = (data.results && data.results.organic) || [];
  const needle = String(domain).toLowerCase().replace(/^www\./, '');

  for (let i = 0; i < organic.length; i++) {
    const url = (organic[i].url || '').toLowerCase();
    if (url.indexOf(needle) !== -1) return organic[i].position;
  }
  return 'Not in top ' + organic.length;
}

/** Low-level fetch returning the parsed Serpent JSON. */
function serpentFetch_(keyword, country) {
  const url = SERPENT_URL
    + '?q=' + encodeURIComponent(keyword)
    + '&country=' + encodeURIComponent(country)
    + '&engine=google';

  const res = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: { 'X-API-Key': SERPENT_KEY }
  });

  if (res.getResponseCode() !== 200) {
    throw new Error('Serpent API error ' + res.getResponseCode());
  }
  return JSON.parse(res.getContentText());
}

Save it, go back to the sheet, and type in any cell:

=SERPENT_RANK("best running shoes", "nike.com", "us")

The first time it runs, Google asks you to authorize the script — click through the "Review permissions" prompt (it needs permission to make external requests). After that, the cell shows a number like 4. You just pulled a live Google rank into a spreadsheet.

Want the actual results, not just a rank? Here's a function that spills the top organic results down the rows.

/**
 * Returns the top N organic results as rows: position, title, url.
 * Usage:  =SERPENT_TOP("project management tools", "us", 10)
 * @customfunction
 */
function SERPENT_TOP(keyword, country, n) {
  country = country || 'us';
  n = n || 10;
  const data = serpentFetch_(keyword, country);
  const organic = ((data.results && data.results.organic) || []).slice(0, n);

  const rows = [['Position', 'Title', 'URL']];   // header row
  organic.forEach(function (r) {
    rows.push([r.position, r.title, r.url]);
  });
  return rows;   // array of arrays spills across cells
}

Type =SERPENT_TOP("project management tools","us",10) into a single cell and the results fill a 3-column block automatically.

Step 3: Add caching (this part is not optional)

Custom functions in Sheets recompute far more often than you'd expect — on edits, on reopen, on recalculation — and every recompute re-runs your fetch. Caching turns dozens of identical API calls into one.

Apps Script ships a built-in CacheService for exactly this. We store each query's JSON for up to 6 hours (the max), keyed by keyword + country. Replace the serpentFetch_ function from Step 2 with this cached version:

/** Cached fetch: one API call per keyword+country per 6 hours. */
function serpentFetch_(keyword, country) {
  const cache = CacheService.getScriptCache();
  const key = ('serpent_' + country + '_' + keyword)
    .toLowerCase().slice(0, 240);   // cache keys max 250 chars

  const hit = cache.get(key);
  if (hit) return JSON.parse(hit);

  const url = SERPENT_URL
    + '?q=' + encodeURIComponent(keyword)
    + '&country=' + encodeURIComponent(country)
    + '&engine=google&format=simple';   // smaller payload, fits cache

  const res = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: { 'X-API-Key': SERPENT_KEY }
  });
  if (res.getResponseCode() !== 200) {
    throw new Error('Serpent API error ' + res.getResponseCode());
  }

  const body = res.getContentText();
  // CacheService values max out at 100KB; only cache if it fits.
  if (body.length < 100000) {
    cache.put(key, body, 21600);   // 21600s = 6 hours
  }
  return JSON.parse(body);
}

Two practical notes baked into that code. We use format=simple so the JSON is small enough to fit the 100KB per-value cache limit, and we cap the cache key at 240 characters (Apps Script keys max out at 250). The 21600 is the maximum 6-hour expiry.

Now a column of 200 keywords pulls each unique query once per six hours, no matter how many times the sheet recalculates. For a deeper look at why caching slashes API bills, see building a SERP cache to cut your API bill.

Apps Script quotas, explained honestly

Caching matters because Apps Script has real, hard limits — and custom functions are the most quota-sensitive place to call an API.

Here are the numbers that actually bite, straight from Google's quota docs:

The 30-second ceiling is the big one. A custom function that tries to fetch hundreds of keywords in one formula will time out. The fix is the pattern below: do bulk work in a trigger that has a longer runtime budget and writes results into the sheet, not in a formula that returns them. Also avoid calling volatile functions like NOW() inside a custom-function cell — Sheets blocks that.

Step 4: A daily rank-snapshot trigger + custom menu

For real rank tracking you want history — a row per keyword per day — not a formula that changes under your feet. A time-driven trigger handles this cleanly.

Set up two sheets: a Keywords tab (column A = keyword, column B = target domain, column C = country) and a Snapshots tab where the trigger appends results. Add this to the same Apps Script project:

// Adds a custom menu when the sheet opens.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Rank Tracker')
    .addItem('Run snapshot now', 'runSnapshot')
    .addItem('Schedule daily', 'installDailyTrigger')
    .addToUi();
}

// Reads the Keywords tab, fetches ranks, appends to Snapshots.
function runSnapshot() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const kw = ss.getSheetByName('Keywords');
  const out = ss.getSheetByName('Snapshots');
  const rows = kw.getRange(2, 1, kw.getLastRow() - 1, 3).getValues();
  const today = Utilities.formatDate(new Date(),
    Session.getScriptTimeZone(), 'yyyy-MM-dd');

  rows.forEach(function (row) {
    const keyword = row[0], domain = row[1], country = row[2] || 'us';
    if (!keyword) return;
    const rank = SERPENT_RANK(keyword, domain, country);
    out.appendRow([today, keyword, domain, country, rank]);
    Utilities.sleep(400);   // be gentle; stay under per-second limits
  });
}

// Creates a trigger that runs runSnapshot once a day.
function installDailyTrigger() {
  ScriptApp.newTrigger('runSnapshot')
    .timeBased().everyDays(1).atHour(7).create();   // ~7am daily
  SpreadsheetApp.getUi().alert('Daily snapshot scheduled for ~7am.');
}

Reload the sheet and you'll see a Rank Tracker menu. Click Schedule daily once, and every morning your Snapshots tab grows by one row per keyword. Chart that column and you have a free rank-history dashboard.

Because the trigger runs on its own (not inside a cell), it gets the longer runtime budget — so it can handle far more keywords than a custom function ever could. For very large keyword lists, split the run across batches or move to a server-side job like the one in build a rank tracker in 100 lines of Python.

Use cases: rank tracker, bulk pulls, PAA harvesting

Once =SERPENT() lives in your sheet, the use cases multiply. Three favorites:

A free rank-tracking sheet. The daily-snapshot trigger above is a complete, zero-server rank tracker. Here's what the Snapshots tab looks like after a few days:

Bulk SERP pulls. Need the top 10 URLs for 50 keywords? Run the runSnapshot pattern with SERPENT_TOP and dump everything into a flat tab for analysis — competitive landscape, content gaps, link prospects.

PAA and related-search harvesting. The full Serpent response carries peopleAlsoAsk and relatedSearches. A custom function can spill those into a column — instant question and keyword research. We built a whole workflow around this in the free AnswerThePublic alternative guide.

/**
 * Returns the "People Also Ask" questions for a keyword, one per row.
 * Usage:  =SERPENT_PAA("electric cars", "us")
 * @customfunction
 */
function SERPENT_PAA(keyword, country) {
  const data = serpentFetch_(keyword, country || 'us');
  const paa = (data.results && data.results.peopleAlsoAsk) || [];
  if (!paa.length) return 'No PAA found';
  return paa.map(function (p) { return [p.question]; });
}

If a spreadsheet outgrows you, the same API plugs into no-code and code tools alike: see the Zapier automation guide, the n8n daily rank-tracker tutorial, or graduate to fully automated SEO reports. New to the whole category? Start with what is a SERP API, then compare options against free Google search APIs we tested.

FAQ

Why does IMPORTXML not work for Google search results?

Google search pages are JavaScript-rendered and Google actively blocks automated import, so IMPORTXML returns #N/A, #ERROR! or empty cells. Even when it works for a moment, the HTML structure changes and the formula breaks again. A search API returns clean JSON instead.

Can I get Google search results in Google Sheets for free?

Yes. Serpent API gives 10 free Google searches on signup with no card required, which is enough to build and test the =SERPENT() custom function. After that Google search costs $0.60 per 10,000 calls pay-as-you-go, dropping to $0.03 per 10,000 at the Scale tier.

What are the Apps Script quota limits I should know about?

UrlFetchApp allows about 20,000 calls per day on free Gmail accounts (100,000 on Workspace). Custom functions also have a roughly 30-second run limit and recompute often, so you must cache results with CacheService to avoid burning quota on every recalculation.

Will the =SERPENT() function refresh automatically?

Custom functions only recompute when their inputs change or the sheet recalculates, and cached values are reused for up to 6 hours. For reliable daily snapshots, use a time-driven trigger that writes results into a sheet instead of relying on the formula to refresh.

Is scraping Google search results into a spreadsheet allowed?

Scraping Google directly violates its terms and gets your IP blocked fast. Using a search API that handles access for you is the clean, durable route. Serpent returns results from the live Google SERP as JSON so you never touch Google directly.

Can I pull People Also Ask and related searches into columns?

Yes. The full Serpent response includes peopleAlsoAsk, relatedSearches, featuredSnippet and aiOverview. You can write a custom function that returns those arrays so they spill down a column, the same way the organic results do.