Automated Rank Tracking in Google Sheets: Free Template

Disclosure: We are reader supported, and earn affiliate commissions when you buy through us.

Tired of guessing where your keywords rank? This guide shows you how to set up automated rank tracking in Google Sheets using a free template and a simple Apps Script—no paid tools needed. You’ll get live ranking data every day without lifting a finger.

The messy way I used to check my rankings (and why it sucked)

I used to copy Google search results into a spreadsheet by hand, guessing each position and trying to remember which row belonged to which keyword. Opening an incognito window, typing a phrase, scrolling, and staring at the page felt like a wild goose chase, especially with ten or twenty keywords to track.
The process took at least half an hour per keyword every week, and the data was inconsistent—one day I’d see “maybe 5?” and the next day “looks like 9”. Inconsistent timing, location bias, and search‑history noise turned my DIY tracker into a guessing game rather than a reliable tool.
That inconsistency pushed me to look for a solution that could pull rankings automatically, without manual copy‑pasting.

The free Google Sheet that actually does the work for you

I built a free rank tracking spreadsheet template that anyone can clone and start using in minutes. The file lives on Google Drive, so you only need a Google account and a few minutes of setup.

What the sheet looks like

The first tab lists your keywords. Column A holds the keyword itself, column B displays the current rank, and column C is a notes field for observations such as “ranking dropped after site redesign”. Additional tabs are hidden and handle the behind‑the‑scenes work.

The magic formulas

In column B for observations like “ranking dropped after site redesign”. All other tabs are hidden and manage the automated lookup.

The core formula

Column B uses a custom function =GETRANK(A2). This function pulls the first page of Google results, scans for your domain, and returns the position number—or “—” if you’re not on the first page. Drag the formula down to apply it to every keyword.

The tiny Apps Script (≈30 lines)

function GETRANK(keyword) {
  var url = 'https://www.googleapis.com/customsearch/v1?key=YOUR_API_KEY&cx=YOUR_CX&q=' + encodeURIComponent(keyword);
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  var items = json.items || [];
  for (var i = 0; i < items.length; i++) {
    if (items[i].displayLink.indexOf('yourdomain.com') > -1) {
      return i + 1;
    }
  }
  return '—';
}

Replace YOUR_API_KEY and YOUR_CX with your free Custom Search Engine credentials, and swap yourdomain.com for your actual site. A short video walkthrough on [Blog Name] shows exactly where to click.

Setting it up in minutes

1. Copy the template – Open the shared link and click “Make a copy”.
2. Add your domain – In the script editor, replace yourdomain.com with your website URL.
3. Insert the API key – Follow the quick guide on [Blog Name] to generate a free Custom Search API key and paste it into the script.
4. Schedule the script – In Apps Script, go to “Triggers” → “Add Trigger”, choose GETRANK, set the event source to “Time‑driven”, and pick “Daily”. The script will now run once every 24 hours and update your ranks automatically.

After these four steps, open your sheet the next morning and you’ll see fresh numbers next to every keyword—no more manual copy‑pasting, no more guessing.

Why this works

Because the script runs on Google’s own infrastructure, it’s fast, reliable, and stays within daily usage limits. The sheet itself is completely free—no hidden fees, no subscription, just a simple spreadsheet that lives in your Drive.
Add more keywords by pasting them into column A; the GETRANK function will handle the rest. For trend tracking, add a second tab that logs daily ranks with a =NOW() timestamp. The possibilities are endless, and you never have to spend a dime.

Wrap up & Thoughts

You now have a zero‑cost, automated rank tracking in Google Sheets that works while you sleep. No more manual copy‑pastes, no more pricey dashboards—just a clean spreadsheet that tells you exactly where you stand. I hope the free rank tracking spreadsheet template helps you stay on top of your SEO game without breaking the bank.

If you found this useful, feel free to subscribe to the [Blog Name] newsletter for more free DIY SEO tricks. Share this post with fellow small‑biz owners—good tools are meant to be shared.