Striking Distance: the Search Console queries sitting just off page one - The Vault

A query sitting at position fourteen with a thousand impressions a month is usually the cheapest ranking win on the whole site: the page already earns real search demand, it just needs a stronger heading, an extra paragraph or a couple of new internal links to cross into page one. Those queries never surface in a normal Search Console glance because they are buried below the top ten. This script pulls the whole striking-distance band, positions eight to twenty, into a Sheet on a schedule.

Before you start

You need a verified Search Console property and a Google account with access to it.
The script authenticates as you, using the account that runs it, so authorise it with the same login that has property access.
Bind the script to a Google Sheet (Extensions, then Apps Script) so SpreadsheetApp.getActiveSpreadsheet() resolves to the right file.
Add the https://www.googleapis.com/auth/webmasters.readonly scope to the script's manifest (appsscript.json, oauthScopes), since it calls the Search Console REST endpoint directly rather than through the built-in advanced service.

The script

JavaScript

/**
 * Striking Distance
 * The SEM Dispatch Vault, semdispatch.com/vault
 *
 * Pulls Search Console query and page data, then lists every query
 * ranking in a chosen position band with real impressions behind it.
 * Read-only: this script only reads from Search Console and writes
 * to a Sheet tab.
 */

var CONFIG = {
  // 'sc-domain:example.com' or 'https://example.com/'
  SITE_URL: 'sc-domain:example.com',

  // Position band to hunt in.
  POSITION_MIN: 8,
  POSITION_MAX: 20,

  // Ignore queries below this many impressions in the lookback window.
  MIN_IMPRESSIONS: 50,

  LOOKBACK_DAYS: 28,

  SHEET_NAME: 'Striking Distance'
};

function main() {
  var end = new Date();
  var start = new Date();
  start.setDate(end.getDate() - CONFIG.LOOKBACK_DAYS);

  var rows = fetchAllRows(formatDate_(start), formatDate_(end));
  var filtered = rows
    .filter(function (r) {
      return (
        r.position >= CONFIG.POSITION_MIN &&
        r.position <= CONFIG.POSITION_MAX &&
        r.impressions >= CONFIG.MIN_IMPRESSIONS
      );
    })
    .sort(function (a, b) {
      return b.impressions - a.impressions;
    });

  writeToSheet_(filtered);
  Logger.log('Striking distance rows written: ' + filtered.length);
}

function fetchAllRows(startDate, endDate) {
  var siteUrl = encodeURIComponent(CONFIG.SITE_URL);
  var url = 'https://searchconsole.googleapis.com/webmasters/v3/sites/' + siteUrl + '/searchAnalytics/query';
  var rowLimit = 25000;
  var startRow = 0;
  var all = [];

  while (true) {
    var payload = {
      startDate: startDate,
      endDate: endDate,
      dimensions: ['query', 'page'],
      rowLimit: rowLimit,
      startRow: startRow
    };
    var response = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });
    var data = JSON.parse(response.getContentText());
    var rows = data.rows || [];

    for (var i = 0; i < rows.length; i++) {
      all.push({
        query: rows[i].keys[0],
        page: rows[i].keys[1],
        clicks: rows[i].clicks,
        impressions: rows[i].impressions,
        ctr: rows[i].ctr,
        position: rows[i].position
      });
    }

    if (rows.length < rowLimit) break;
    startRow += rowLimit;
  }

  return all;
}

function writeToSheet_(rows) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(CONFIG.SHEET_NAME) || ss.insertSheet(CONFIG.SHEET_NAME);
  sheet.clearContents();
  sheet.appendRow(['Query', 'Page', 'Clicks', 'Impressions', 'CTR', 'Position']);

  rows.forEach(function (r) {
    sheet.appendRow([r.query, r.page, r.clicks, r.impressions, r.ctr, r.position]);
  });
}

function formatDate_(date) {
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

Set it up

Create a Google Sheet, then open Extensions, then Apps Script, and paste the code above.
In the manifest, add the webmasters.readonly OAuth scope so the script can call Search Console on your behalf.
Set SITE_URL to your property exactly as it appears in Search Console, accepting either a domain property or a URL-prefix property.
Run main, authorise the script when prompted, and check the log and the new Sheet tab.
Schedule it weekly under Triggers, Search Console's data lags by a couple of days so a daily run adds little.

How to read the output

The Sheet is sorted by impressions, so the top rows are the biggest prizes: real search demand, a page that already ranks, just not high enough. Position eight to eleven queries usually need the lightest touch, a title or heading tweak, or one solid internal link from a related page. Positions further down the band, fifteen to twenty, are worth a heavier content pass before you expect movement.

Two things worth knowing. position from the API is an average across every ranking instance in the window, so a query bouncing between four and thirty will show up around seventeen and look like a mid-band opportunity when it is really volatile. And the account running the script needs Search Console access to the property or every call returns an authorisation error rather than data, check that first if the Sheet comes back empty.