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

/**
 * 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');
}