Building a Custom Rank Tracker with Google Search Console API, Google Sheets, and Apps Script

Let’s be honest: the vast majority of off-the-shelf rank tracking tools are either laughably expensive, laughably inaccurate, or both. For the startup marketer who has already internalised the fact that Google My Business, local packs, knowledge panels, and personalised results have turned the old “position 3 for ‘plumbers in Austin’” metric into a ghost, the question isn’t whether to track rankings but how to do it without bleeding budget or sanity. The answer lies in a free, scriptable, and surprisingly robust pipeline: Google Search Console’s API, a Google Sheet, and a few lines of Apps Script. This isn’t a toy. When configured correctly, it gives you daily average position, impression share, click-through rate, and even device-specific breakouts for any set of queries or pages you care about, all without a single scraping request that could land your IP on a watchlist.

The core insight is that Google Search Console’s Performance Report already holds the only rank data Google considers authoritative—the actual average position for queries where your site appeared. The API exposes this data in a raw, filterable format. The challenge is that Search Console’s web interface is limited to 1,000 rows and no direct export of daily granularity beyond the last 16 months. By hitting the API directly, you can pull daily data for any date range, for any set of queries or pages, and for any combination of dimensions (country, device, search appearance). The result is a dataset that makes most third-party rank trackers look like they’re reading tea leaves.

Start by enabling the Search Console API in your Google Cloud Console and creating a service account with appropriate permissions. The API uses OAuth 2.0, but since this is a single-user project, you can bypass the interactive flow by generating a service account key and sharing your Google Sheet with that service account’s email. This sounds like overhead, but it’s a one-time setup that takes about ten minutes. Once the key is stored as a script property in Apps Script, you can authenticate programmatically using the `OAuth2` library. Do not hardcode credentials. Do not share the key. You know this.

The real power comes from the `searchanalytics.query` method. You can request a `date` dimension to get daily numbers, or aggregate up to weekly. The API returns `clicks`, `impressions`, `ctr`, and `position`. The position returned is the average of the topmost ranking across all searches for that query/page combination. Yes, it’s averaged over users, devices, and personalisation—and that is exactly why it’s more reliable than a single IP‑based scrape. If you want to track how a specific landing page performs for a set of keywords, you pass a `dimensionFilterGroups` object that filters by page URL. You can also filter by country, device, or search appearance (web, image, video). For a startup tracking a handful of high‑priority keywords, pulling data for the last 7 days on a daily schedule is trivial. For broader projects, you can paginate through results using `startRow` and `rowLimit` up to 25,000 rows per call.

Now, the sheet architecture. A clean setup has three sheets: one for raw API responses, one for a lookup table of keywords or pages you want to track, and one for a pivot table that presents the latest 30 days of rank trends. In Apps Script, write a function that loops through your lookup list, passes each query or URL to the API, and writes the daily position, clicks, and impressions into a timestamped row. Use `SpreadsheetApp.flush()` only when necessary—batch writes with `setValues()` are far faster than row‑by‑row updates. Schedule the script to run daily via a time‑driven trigger. After a few weeks, you’ll have a dataset that reveals not just rank fluctuations but also seasonality, algorithm updates, and the real impact of content changes.

The edge this gives you is that your data is directly from Google’s own logs. No third‑party tool can claim that. The cost is zero dollars, aside from the negligible Google Cloud API quota (which is far above any startup’s needs). The downside is that you only see data for queries where you already have impressions. You cannot track keyword positions for terms where you aren’t already ranking—that’s a limitation of Search Console itself, not of this method. But for monitoring known target terms, this is the gold standard.

Moreover, because the data lives in a spreadsheet, you can wire it directly into Google Data Studio for a live dashboard, or use conditional formatting to flag positions dropping below a threshold. You can even build a simple notification system: if average position for a critical keyword slips below page three for three consecutive days, send an email via `MailApp.sendEmail()`. This is the kind of lean, high‑signal monitoring that seasoned marketers appreciate—no dashboards for the sake of dashboards, just actionable data pulled from the source.

For those who want to go further, the same API can be used to track search appearance changes (e.g., when a listing moves from web to rich result), or to compare performance across countries and devices. And because you control the scheduling, you can avoid the rate‑limiting headaches that plague public APIs by setting a sensible pause between requests. A 100 ms delay per call is conservative; you can adjust based on your quota.

The takeaway is straightforward: if you’re still paying for a rank tracker that scrapes Google with proxies and then averages out the noise, you are throwing money at a problem that Google already solved for free. The Search Console API, combined with the scripting capabilities of Google Workspace, gives you a fully customisable, no‑BS rank tracking system that any technical marketer can build in an afternoon. It respects the realities of modern search—aggregation, personalisation, and volatility—while giving you raw data you can trust. And in a world where every startup is trying to out‑optimise every other startup, trusting your data is the only sustainable edge.