Track Keyword Ranks in Google Sheets with Apps Script

You can track Google rankings for a keyword list inside Google Sheets — no server, no database, no monthly tracker subscription — with about 50 lines of Apps Script that calls a SERP API once per run and writes each keyword's best rank into a dated column. Set a time-driven trigger and the sheet builds your rank history by itself.

I keep one of these for a 50-word client list. The client gets a live doc they already know how to read, and I get a new column every morning without touching anything.

What you need

• A Google Sheet with one tab named Ranks. Row 1 can hold dates (the script fills it); column A holds your keywords, one per row.
• An API key from a SERP API provider. I use SerpBase — search requests cost 1 credit each, new accounts get 100 free searches, and the entry paid pack is $10 for 20,000 credits with no expiry. Parameter and field definitions live in SerpBase's search endpoint documentation.

The script

Extensions → Apps Script, paste this:

const API_URL = 'https://api.serpbase.dev/google/search';
const DOMAIN  = 'example.com';   // the site you're tracking

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Rank check')
    .addItem('Check now', 'checkRanks')
    .addToUi();
}

function checkRanks() {
  const key = PropertiesService.getScriptProperties().getProperty('SERP_KEY');
  if (!key) throw new Error('Save your key first: Project Settings → Script Properties → SERP_KEY');

  const sheet  = SpreadsheetApp.getActive().getSheetByName('Ranks');
  const values = sheet.getDataRange().getValues();
  const outCol = values[0].length + 1;          // one new column per run

  sheet.getRange(1, outCol).setValue(new Date());   // header = run timestamp

  for (let r = 1; r < values.length; r++) {
    const q = values[r][0];
    if (!q) continue;
    sheet.getRange(r + 1, outCol).setValue(bestRank(q, key));
    Utilities.sleep(500);                        // be gentle on long lists
  }
}

function bestRank(q, key) {
  const resp = UrlFetchApp.fetch(API_URL, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'X-API-Key': key },
    payload: JSON.stringify({ q: q, hl: 'en', gl: 'us' }),
    muteHttpExceptions: true,
  });
  const data = JSON.parse(resp.getContentText());
  if (data.status !== 0) {
    if (data.status === 1020) throw new Error('Out of credits');
    if (data.status === 1029) return 'rate-limited';  // rerun in a minute
    return 'err ' + data.status;
  }
  const hits = (data.organic || [])
    .filter(i => (i.link || '').includes(DOMAIN))
    .map(i => i.rank);                           // 1-based position within this page
  return hits.length ? Math.min(...hits) : '';   // blank = not on page 1
}

Setup, in order:

1. Put your keywords in column A of the Ranks tab (row 1 stays empty for dates).
2. In Apps Script, open Project Settings → Script Properties, add SERP_KEY with your key. The key never lives in the sheet itself, so sharing the doc with a client is safe.
3. Reload the spreadsheet, run Rank check → Check now, and authorize once.
4. Optional: in Apps Script → Triggers, add a time-driven trigger (e.g. every day at 6am) on checkRanks. Each run appends a new dated column — scroll right to see your history.

A blank cell means the domain didn't appear on page 1 for that keyword. If a run hits the rate limit, the cell says rate-limited — rerun and that column gets refreshed.

What this costs

One request per keyword per run, 1 credit each; failed requests come back with credits_charged: 0, so retries and rate-limit hiccups don't burn balance. Fifty words checked daily is about 1,500 credits a month — the $10 / 20k pack covers that pace for over a year. If daily is overkill for your list, trigger weekly instead; weekly is usually enough to spot real movement anyway.

FAQ

Why not just use Search Console? Different question. Search Console shows impressions and clicks for pages it already knows about; this sheet answers "where do I rank today for this specific keyword list" — including keywords you haven't built pages for yet, which is the useful half when you're planning content.

Can I track page 2 and beyond? The script only requests page 1, so it tracks the top 10. The endpoint takes a page parameter if you want to go deeper, at one credit per page — I've never needed it for a health-check sheet.

Can I watch a competitor at the same time? Duplicate the bestRank filter with a second domain and write it to the adjacent column set. Two domains, one request per keyword — the response already contains everyone's positions.

Paste the script, set the trigger, and tomorrow morning your sheet has its first real column.