You can get Google search results in Google Sheets without running a server: paste a short Google Apps Script program into the spreadsheet and let it call a SERP API with UrlFetchApp. A custom function, =SERP(query, country), shows live results in cells, and a custom menu plus a daily trigger refresh a whole keyword list.
This guide is for SEO and marketing people who live in spreadsheets. The script calls BestSERP, a Google SERP API (what a SERP API is). We tested its logic on November 17, 2026 by running the same Code.gs in Node.js with a small stand-in for Apps Script's services, against the live API. The Apps Script parts follow Google's documentation; run it once in your own sheet first.
You will build a spreadsheet that fetches live Google results two ways: a formula for quick lookups and a menu refresh for keyword lists, which can also run on a schedule.

| Part |
What it does |
Cost |
=SERP(query, country, language, rows) |
Position, site and title for one query, cached for 6 hours |
1 credit per new query |
| BestSERP > Refresh keywords |
Searches each row of a Keywords tab, writes plain values, logs a History row |
1 credit per keyword per day |
| Daily trigger |
Runs that refresh every morning, file open or not |
Same as the refresh |
To find text inside your own spreadsheet, use Ctrl+F or the FIND function instead.
Why IMPORTXML is not enough
#IMPORTXML is not a dependable way to pull Google results, because Google Search stopped showing results to clients that do not run JavaScript.
- Google describes IMPORTXML as importing "structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds" from a URL and running an XPath query on them. Running the page's scripts is not mentioned.
- Since January 2025, Google Search requires JavaScript: a browser without it gets a "Turn on JavaScript to keep searching" notice instead of results (Search Engine Journal, January 30, 2025).
So expect that notice, an error or an empty cell, not ten results (we did not test IMPORTXML on google.com; this is the expected outcome, not a measurement). A SERP API leaves the browser work to the provider, which returns JSON that Apps Script can read.
Step 1: Add the script and store your key
#Paste the script into the spreadsheet's own Apps Script project and keep the API key in Script Properties, not in a cell.
- Sign in to BestSERP for 3,000 free searches (valid 30 days) and create a key under Dashboard > API keys (quickstart).
- In your spreadsheet, choose Extensions > Apps Script, replace the sample code in
Code.gs with the five code blocks below, in order, and click Save. If Project Settings shows an unticked Enable Chrome V8 runtime box, tick it.
- In Project Settings, under Script Properties, click Add script property, enter
BESTSERP_API_KEY and your key, then Save script properties (Google's steps).
- Reload the spreadsheet. A BestSERP menu appears; its first use asks you to authorize the script to call an external URL.
A key in a cell is visible to every viewer and travels with every copy and download. Script properties stay out of the sheet, but they are not a vault: "anyone with permission to edit the spreadsheet can also edit any Apps Script code attached to it" (custom functions guide). Give each shared spreadsheet its own key, so you can revoke one alone.
Part 1 is the settings and the API call:
/**
* Google search results in Google Sheets, with Apps Script and BestSERP.
* Paste this whole file into Extensions > Apps Script (Code.gs) and save.
* The API key lives in Project Settings > Script properties as BESTSERP_API_KEY,
* never in a cell.
*/
// ---- Part 1: settings and the API call ----
const API_URL = 'https://api.bestserp.com';
const FORMULA_WAIT_SECONDS = 20; // =SERP() must finish within 30 s
const BATCH_WAIT_SECONDS = 60; // menu and trigger runs may wait longer
const CACHE_SECONDS = 6 * 60 * 60; // 6 hours, the CacheService maximum
const MAX_RUN_MS = 4.5 * 60 * 1000; // stay inside the 6-minute script limit
/** Calls one BestSERP endpoint. Returns {code, data, headers}; never throws on 4xx/5xx. */
function bestserp_(path, body, waitSeconds) {
const key = PropertiesService.getScriptProperties().getProperty('BESTSERP_API_KEY');
if (!key) throw new Error('add BESTSERP_API_KEY in Project Settings > Script properties');
const options = {
method: body ? 'post' : 'get',
headers: { 'X-API-KEY': key },
muteHttpExceptions: true, // read error responses instead of throwing
timeoutSeconds: waitSeconds + 5, // hard stop shortly after the API's own wait
};
if (body) {
options.contentType = 'application/json';
options.payload = JSON.stringify(Object.assign({ timeout: waitSeconds }, body));
}
const res = UrlFetchApp.fetch(API_URL + path, options);
let data;
try {
data = JSON.parse(res.getContentText());
} catch (e) { // a gateway error page is not JSON
data = { error: { code: 'http_' + res.getResponseCode() } };
}
return { code: res.getResponseCode(), data: data, headers: res.getHeaders() };
}
/** Site name for a result: the domain, or the source label when Google shows no URL. */
function site_(result) {
return result.domain || result.source || '';
}
function errorText_(res) {
return 'Error ' + res.code + ': ' + ((res.data.error || {}).code || 'unknown');
}
Per the UrlFetchApp reference, muteHttpExceptions: true returns 4xx and 5xx responses instead of throwing, so the script can read BestSERP's error code. The body's timeout tells BestSERP when to stop waiting and answer 202 (async requests); timeoutSeconds, UrlFetchApp's own limit (default 360 seconds), sits just above it.
Step 2: Google search results in Google Sheets with =SERP()
#Type =SERP(A2, "us") in an empty cell to get the position, site and title of each result for the query in A2, as Google shows it in the United States.
// ---- Part 2: the =SERP() custom function ----
/**
* Live Google results for a query: position, site and title.
* @param {string} query The search query, or a cell that holds it.
* @param {string} country Two-letter country code (gl), e.g. "us" or "de". Default "us".
* @param {string} language Interface language (hl), for example "en". Default "en".
* @param {number} rows How many results to show, 1 to 10. Default 10.
* @return One row per result, under a header row.
* @customfunction
*/
function SERP(query, country, language, rows) {
if (!query) return 'Enter a query';
const params = {
q: String(query).trim(),
gl: String(country || 'us').trim().toLowerCase(),
hl: String(language || 'en').trim().toLowerCase(),
};
const limit = Math.max(1, Math.min(10, Number(rows) || 10));
const cache = CacheService.getScriptCache();
const cacheKey = ['serp', params.gl, params.hl, params.q].join('|').slice(0, 250);
const cached = cache.get(cacheKey);
if (cached && cached.indexOf('pending:') !== 0) return table_(JSON.parse(cached), limit);
let res;
try {
res = cached
? bestserp_('/requests/' + cached.slice(8), null, 10) // free: poll earlier search
: bestserp_('/search', params, FORMULA_WAIT_SECONDS);
} catch (e) {
return 'Error: ' + e.message;
}
if (res.code === 202) { // still running: remember it, never pay twice
cache.put(cacheKey, 'pending:' + res.data.requestId, 1800);
return 'Still searching. Re-enter the formula in a minute.';
}
if (res.code !== 200) {
cache.remove(cacheKey);
return errorText_(res);
}
const results = (res.data.organic || []).map(r => [r.position, site_(r), r.title]);
cache.put(cacheKey, JSON.stringify(results), CACHE_SECONDS);
return table_(results, limit);
}
function table_(results, limit) {
if (!results.length) return 'No organic results';
return [['Position', 'Site', 'Title']].concat(results.slice(0, limit));
}
Our test of =SERP("google sheets rank tracker", "us", "en", 5) returned these rows in 5.3 seconds; re-entering the formula returned them from the cache without a request:
| Position |
Site |
Title |
| 1 |
codeclever.tech |
Google Rank Tracker in Node.js with the DataForSEO API |
| 2 |
YouTube · Better Sheets |
Easily Make Rank Lists in Google Sheets |
| 3 |
Stack Overflow |
Ranking Scores in Google Sheets |
| 4 |
Reddit · r/googlesheets |
Find the Rank in a row (not column) : r/googlesheets |
| 5 |
ai-tools-for-local-seo.com |
10 Best Google Rank Tracker Software for 2026 |
Three results had no domain, so the function shows the source label; it skips link, which can be a long Google redirect.
"A custom function call must return within 30 seconds," or the cell shows #ERROR! (custom functions guide). Successful searches took 4.1 to 9.9 seconds in our runs, but some take longer, so the formula asks BestSERP to answer within 20 seconds and never polls in a loop.
If the search is not done by then, the API answers 202 with a requestId. The function caches that ID and shows "Still searching"; re-entering the formula polls /requests/{requestId} for free instead of paying for a second search. With the wait forced to 1 second, our first call returned "Still searching" after 1.6 seconds, and a re-entry 10 seconds later returned the results: one credit in total.
Results stay in CacheService for 6 hours, its maximum; BestSERP's own 1-hour cache still charges per request, so this cache is what saves credits.
Avoid recalculation storms
#"Each time a custom function is used in a spreadsheet, Sheets makes a separate call to the Apps Script server," says the custom functions guide. Fill =SERP() down 200 rows and you start 200 executions, each a paid search, against a limit of 30 simultaneous executions per user. A custom function also reruns whenever you edit it or change a cell it references. Use it for a handful of lookups, use the menu for lists, and freeze a result with Paste special > Values only.
For a keyword list, one menu item searches each row and writes the results back as plain values, so the sheet itself becomes the cache and nothing recalculates.
Create a Keywords tab with A Keyword, B Country and C Language (the country and language codes, default us and en) and D Domain to track (optional). The script fills E to H. How gl and hl change results shows what the codes do. onOpen adds the menu each time the file opens (custom menus guide).
// ---- Part 3: menu and batch refresh of a keyword list ----
const KEYWORD_HEADERS = ['Keyword', 'Country', 'Language', 'Domain',
'Position', 'Top result', 'Checked', 'Status'];
const HISTORY_HEADERS = ['Checked', 'Keyword', 'Country', 'Domain',
'Position', 'Top result'];
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('BestSERP')
.addItem('Refresh keywords', 'refreshKeywords')
.addItem('Schedule daily refresh', 'scheduleDailyRefresh')
.addItem('Stop scheduled refresh', 'stopScheduledRefresh')
.addToUi();
}
/** Searches each keyword not checked today; writes results back as plain values. */
function refreshKeywords() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(1000)) return; // another refresh is running
try {
const started = Date.now();
const sheet = sheet_('Keywords', KEYWORD_HEADERS);
const history = sheet_('History', HISTORY_HEADERS);
const rows = sheet.getDataRange().getValues();
const today = day_(new Date());
let searched = 0;
for (let i = 1; i < rows.length; i++) { // row 0 holds the headers
const [keyword, country, language, domain, , , checked] = rows[i];
if (!keyword || (checked instanceof Date && day_(checked) === today)) continue;
if (Date.now() - started > MAX_RUN_MS) break; // the next run continues from here
const gl = String(country || 'us').trim().toLowerCase();
const hl = String(language || 'en').trim().toLowerCase();
const res = searchWithRetry_({ q: String(keyword).trim(), gl: gl, hl: hl });
if (res.code !== 200) {
const status = res.code === 202 ? 'still running, retry later' : errorText_(res);
sheet.getRange(i + 1, 8).setValue(status);
if (res.code === 401 || res.code === 402) break; // wrong key or no credits
continue;
}
const organic = res.data.organic || [];
const position = rankOf_(organic, domain);
const top = organic.length ? site_(organic[0]) : '';
const now = new Date();
sheet.getRange(i + 1, 5, 1, 4).setValues([[position, top, now, 'ok']]);
history.appendRow([now, keyword, gl, domain, position, top]);
searched++;
}
console.log('refreshed ' + searched + ' keyword(s)');
} finally {
lock.releaseLock();
}
}
Rows already checked today are skipped, so a second click costs nothing and a run that stops early continues next time. The loop stops starting searches after 4.5 minutes, inside the 6-minute limit per execution, and a script lock keeps a click and the trigger from overlapping. A 401 or 402 ends the run; other errors go to Status and are retried next run.
The helpers follow BestSERP's error rules: poll 202 every 3 seconds for up to 90 seconds (polling is free), wait for Retry-After on 429 and 503, and retry 502 after 5 seconds. Failed searches are refunded.
/** One search: poll 202 (free), wait on 429, retry 502 and 503 (refunded). */
function searchWithRetry_(params) {
let res;
for (let attempt = 1; attempt <= 3; attempt++) {
res = bestserp_('/search', params, BATCH_WAIT_SECONDS);
for (let polls = 0; res.code === 202 && polls < 30; polls++) {
Utilities.sleep(3000);
res = bestserp_('/requests/' + res.data.requestId, null, 10);
}
if (res.code !== 429 && res.code !== 502 && res.code !== 503) return res;
const wait = res.code === 502 ? 5 : Number(header_(res.headers, 'Retry-After')) || 5;
Utilities.sleep(Math.min(wait, 60) * 1000);
}
return res;
}
/** Position of the first result on `domain` (subdomains count) or 'not in top 10'. */
function rankOf_(organic, domain) {
const want = String(domain || '').trim().toLowerCase().replace(/^www\./, '');
if (!want) return '';
const hit = organic.find(r => {
const d = String(r.domain || '').toLowerCase().replace(/^www\./, '');
return d === want || d.endsWith('.' + want);
});
return hit ? hit.position : 'not in top 10';
}
function sheet_(name, headers) {
const ss = SpreadsheetApp.getActive();
let sheet = ss.getSheetByName(name);
if (!sheet) {
sheet = ss.insertSheet(name);
sheet.appendRow(headers);
}
return sheet;
}
function day_(date) {
return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}
function header_(headers, name) {
const k = Object.keys(headers || {}).find(h => h.toLowerCase() === name.toLowerCase());
return k ? headers[k] : null;
}
Our five-keyword test run took 50 seconds, including an injected 503 and 502 on the first search and a real 502 on the German query (with Retry-After: 60), which succeeded on a retry 5 seconds later. A second click made no requests.
| Keyword (gl) |
Domain |
Position |
Top result |
| apps script custom functions (us) |
developers.google.com |
not in top 10 |
Stack Overflow |
| urlfetchapp (us) |
developers.google.com |
2 |
Stack Overflow |
| importxml (gb) |
support.google.com |
1 |
support.google.com |
| google sheets rank tracker (us) |
(none) |
|
a YouTube channel |
| google tabellen apps script (de, hl de) |
developers.google.com |
4 |
de.scribd.com |
Step 4: Schedule a daily refresh
#A time-driven trigger runs the same refresh every morning, even when nobody has the spreadsheet open.
// ---- Part 4: the daily trigger ----
function scheduleDailyRefresh() {
stopScheduledRefresh(); // never stack duplicate triggers
ScriptApp.newTrigger('refreshKeywords').timeBased().everyDays(1).atHour(6).create();
SpreadsheetApp.getUi().alert(
'Keywords will refresh every day between 6 and 7 AM (script time zone).');
}
function stopScheduledRefresh() {
ScriptApp.getProjectTriggers()
.filter(t => t.getHandlerFunction() === 'refreshKeywords')
.forEach(t => ScriptApp.deleteTrigger(t));
}
- Choose BestSERP > Schedule daily refresh once. With
atHour(6) and everyDays(1) the trigger runs between 6 and 7 AM in the script's time zone (ClockTriggerBuilder).
- It runs as the account that created it; failures arrive by email and in the Executions page (installable triggers).
scheduleDailyRefresh deletes older refresh triggers first, so the refresh is never scheduled twice.
- One run handles about 45 to 50 keywords at our measured 5 to 6 seconds per search. For longer lists, use
.everyHours(1) instead of .everyDays(1).atHour(6): each run continues with rows not yet checked today.
How we tested this script
#We could not run Apps Script itself, so we tested the logic. On November 17, 2026 we loaded the exact Code.gs above into Node.js 22.17.1 with a stub of the services it uses (a synchronous UrlFetchApp, Script Properties, caches, locks, triggers, menus, an in-memory spreadsheet) and sent every search to the live BestSERP API. It cost 12 credits; the 401 checks were free and the two real 502 failures were refunded.
The stub cannot prove Google's side: the authorization prompt, the menu, trigger timing and UrlFetchApp's behavior from Google's servers. Run Refresh keywords on two or three rows first, then check Status and the Executions log.
Quotas, costs and limits
#On a free Gmail account the Apps Script limit you hit first is trigger runtime (90 minutes a day), not URL Fetch calls; on the BestSERP side, each keyword costs one credit per refresh.
| Apps Script limit |
Consumer (gmail.com) |
Google Workspace |
| URL Fetch calls |
20,000 / day |
100,000 / day |
| Triggers total runtime |
90 min / day |
6 hr / day |
| Script runtime |
6 min / execution |
6 min / execution |
| Custom function runtime |
30 sec / execution |
30 sec / execution |
| Simultaneous executions |
30 / user |
30 / user |
Source: Google's Quotas for Google Services, last updated September 3, 2026 and checked November 17, 2026. Quotas are per user and reset 24 hours after the first request.
- Rows per day. At 5 to 6 seconds per keyword, 90 minutes of trigger time is roughly 900 to 1,000 checks a day (3,600 to 4,300 on Workspace): our estimate, not a Google figure.
- Cost. 100 keywords checked daily is about 3,000 searches a month: free in the first month, then $1.50 a month at $0.50 per 1,000 (pricing). Top 20 tracking (
num: 20) costs 2 credits per keyword; our rank tracking cost guide covers bigger budgets. The loop runs 10 to 12 searches a minute, under BestSERP's 60.
- Data. Desktop results, targeted by country, not city. The script reads organic results; BestSERP does not return ads, People Also Ask or AI Overviews.
- Noise. Between our two runs, about four minutes apart, two of four tracked positions changed. Judge trends in the History tab, not single checks.
Common problems and fixes
#
| Problem |
Cause |
Fix |
| "Error: add BESTSERP_API_KEY..." |
Script property missing or misspelled |
Add BESTSERP_API_KEY exactly, then re-enter the formula |
| "Error 401" or "Error 402" |
Wrong or revoked key, or balance too low |
Copy the key again, or top up |
#ERROR! "Exceeded maximum execution time" |
The formula ran past 30 seconds |
Lower FORMULA_WAIT_SECONDS, or use the menu |
| "Too many scripts running simultaneously" |
Many =SERP() cells at once |
Move the list to the Keywords tab |
=SERP() shows an error, not rows |
Cells below or to the right are not empty |
Leave room for 11 rows and 3 columns |
You now have Google search results in Google Sheets with no server: a formula for spot checks, and a menu and trigger that fill a History tab daily.