Build a Search Console ranking tracker

Google Search Console shows Northwind how its pages rank, but it only keeps a rolling sixteen months of data and its interface is built for browsing, not for tracking a trend. If you want to know whether a keyword has climbed or slipped since the spring, the console alone will not tell you — you need your own running history.

This script builds that history. Once a week it pulls the top keywords from Search Console — clicks, impressions, click-through rate, and average position — and appends them as dated rows to a Sheet. Over a few months the sheet becomes a time series you can chart, so a ranking that is quietly drifting downwards shows up long before it costs real traffic.

What you’ll need

• A Google Sheet for the log, with a header row: date, keyword, clicks, impressions, ctr, position.
• The Search Console API enabled as an Advanced Service: in the editor, Services > add Search Console API (the identifier SearchConsole).
• A verified Search Console property for the site, and an account with at least read access to it.

The script

// The Search Console property to query. Use the sc-domain: form for a
// domain property, or the full https:// URL for a URL-prefix property.
const SITE = 'sc-domain:northwind.studio';

// The spreadsheet that stores the ranking history.
const GSC_SHEET_ID = '1abcGscId';

// How many top keywords to log each run.
const ROW_LIMIT = 100;

// How far back each run looks, in days.
const LOOKBACK_DAYS = 7;

/**
 * Queries Search Console for the top keywords over the lookback window
 * and appends one dated row per keyword to the log sheet.
 */
function logKeywordRankings() {
  // 1. Ask Search Console for the top queries over the last week.
  const res = SearchConsole.Searchanalytics.query({
    startDate: daysAgo(LOOKBACK_DAYS),
    endDate: daysAgo(0),
    dimensions: ['query'],
    rowLimit: ROW_LIMIT,
  }, SITE);

  // 2. Bail out early if the API returned no rows.
  if (!res.rows || !res.rows.length) {
    Logger.log('No Search Console data for this window — nothing logged.');
    return;
  }

  // 3. Shape each result into a dated row for the sheet.
  const today = new Date();
  const rows = res.rows.map((r) => [
    today,        // when this snapshot was taken
    r.keys[0],    // the keyword (the single "query" dimension)
    r.clicks,
    r.impressions,
    r.ctr,
    r.position,   // average position over the window
  ]);

  // 4. Append the batch below the last row of the log.
  const sheet = SpreadsheetApp.openById(GSC_SHEET_ID).getSheets()[0];
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 6)
    .setValues(rows);
  Logger.log(`Logged ${rows.length} keyword(s).`);
}

/**
 * Returns a date n days before today as a yyyy-MM-dd string, the format
 * the Search Console API expects for startDate and endDate.
 */
function daysAgo(n) {
  return new Date(Date.now() - n * 86400000).toISOString().slice(0, 10);
}

How it works

1. logKeywordRankings calls SearchConsole.Searchanalytics.query for the SITE property, asking for the query dimension — one row per keyword — over the lookback window.
2. The window runs from seven days ago to today, both supplied as yyyy-MM-dd strings by the daysAgo helper.
3. If the API returns no rows — a new site, or a window with no traffic — it logs a message and stops without writing anything.
4. It maps each result row into a six-column array: today’s date as the snapshot timestamp, the keyword from keys[0], then clicks, impressions, CTR, and average position.
5. It appends the whole batch in one setValues call starting just below the current last row, so each run adds a fresh dated block.
6. Run weekly, the sheet accumulates a snapshot per keyword per week — the raw material for a trend chart.

Example run

A run on 2025-08-28 appends a block of rows like these to the log:

date	keyword	clicks	impressions	ctr	position
2025-08-28	northwind studio	42	310	0.135	2.4
2025-08-28	photo studio hire	18	540	0.033	8.1
2025-08-28	product photography	9	880	0.010	14.7

A week later another block lands with 2025-08-28 swapped for the new date. Filter the sheet to keyword = "photo studio hire" and you can watch its position move week by week — the trend Search Console will not show you.

Trigger it

Run this weekly so the history builds without anyone remembering to:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for logKeywordRankings, time-driven, Week timer, on a fixed day such as Monday morning.
3. Save and approve the authorisation prompt, including Search Console access.
4. Keep the same day each week so the snapshots are evenly spaced and easy to compare.

Watch out for

• Search Console data lags by two to three days. A window ending today will be partly empty for the most recent days — that is normal, not a bug.
• position is an average over the whole window, not a single ranking. A keyword that appears on page one for some searches and page three for others shows a blended figure.
• The rowLimit of 100 keeps only the top keywords. Long-tail terms below the cut-off are never logged, so the history is of your strongest keywords, not every one.
• The API has daily quota limits. A weekly run is far inside them, but do not loop this over many properties or date ranges in quick succession.
• The SITE format must match the property type exactly — sc-domain: for a domain property, the full https:// URL for a URL-prefix property. A mismatch returns an error or empty results.
• The sheet grows by up to 100 rows a week. After a year that is several thousand rows — fine for Sheets, but archive or summarise old data if it becomes unwieldy.