Search Console API & Google Apps Script:
Getting Started Guide

/**
 * Returns an authenticated OAuth2 service for the Search Console API.
 * Call this function from any other function that needs API access.
 */
function getSearchConsoleService() {
  const props = PropertiesService.getScriptProperties();

  return OAuth2.createService('SearchConsole')
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')
    .setClientId(props.getProperty('CLIENT_ID'))
    .setClientSecret(props.getProperty('CLIENT_SECRET'))
    .setPropertyStore(props)
    .setCallbackFunction('authCallback')
    .setScope('https://www.googleapis.com/auth/webmasters.readonly')
    .setParam('access_type', 'offline')
    .setParam('prompt', 'consent');
}

/**
 * Handles the OAuth callback.
 */
function authCallback(request) {
  const service = getSearchConsoleService();
  const authorized = service.handleCallback(request);
  return HtmlService.createHtmlOutput(
    authorized
      ? '✅ Authorized. You can close this tab and return to Apps Script.'
      : '❌ Authorization denied.'
  );
}

/**
 * Run this function once to trigger the OAuth authorization flow.
 */
function authorizeScript() {
  const service = getSearchConsoleService();
  if (!service.hasAccess()) {
    const authUrl = service.getAuthorizationUrl();
    Logger.log('Open this URL to authorize:\n%s', authUrl);
  } else {
    Logger.log('✅ Already authorized.');
  }
}

/**
 * Fetches the top 10 queries by clicks for the last 28 days.
 */
function fetchTopQueries() {
  const SITE_URL  = 'https://www.yourdomain.com/';
  const service   = getSearchConsoleService();

  if (!service.hasAccess()) {
    Logger.log('Not authorized. Run authorizeScript() first.');
    return;
  }

  const today     = new Date();
  const endDate   = formatDate(new Date(today - 2 * 86400000));
  const startDate = formatDate(new Date(today - 29 * 86400000));

  const payload = {
    startDate, endDate,
    dimensions: ['query'],
    rowLimit: 10
  };

  const endpoint =
    'https://searchconsole.googleapis.com/webmasters/v3/sites/' +
    encodeURIComponent(SITE_URL) +
    '/searchAnalytics/query';

  const response = UrlFetchApp.fetch(endpoint, {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + service.getAccessToken(),
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const data = JSON.parse(response.getContentText());

  if (data.error) {
    Logger.log('API Error: %s', JSON.stringify(data.error));
    return;
  }

  (data.rows || []).forEach(row => {
    Logger.log(
      '%-40s | clicks: %-5s | impr: %-6s | ctr: %s% | pos: %s',
      row.keys[0],
      row.clicks,
      row.impressions,
      (row.ctr * 100).toFixed(1),
      row.position.toFixed(1)
    );
  });
}

// Utility: format a Date object as YYYY-MM-DD
function formatDate(date) {
  return Utilities.formatDate(date, 'GMT', 'yyyy-MM-dd');
}

{
  "rows": [
    {
      "keys": ["search console api apps script"],
      "clicks":      142,
      "impressions": 1840,
      "ctr":         0.0771,
      "position":    3.2
    }
  ]
}

/**
 * Pulls top 500 query+page combinations from GSC (last 28 days)
 * and writes them to a sheet named 'GSC_Report'.
 */
function exportGscToSheets() {
  const SITE_URL   = 'https://www.yourdomain.com/';
  const SHEET_NAME = 'GSC_Report';
  const ROW_LIMIT  = 500;

  const service = getSearchConsoleService();
  if (!service.hasAccess()) {
    Logger.log('Not authorized.');
    return;
  }

  const today     = new Date();
  const endDate   = formatDate(new Date(today - 2  * 86400000));
  const startDate = formatDate(new Date(today - 29 * 86400000));

  const payload = {
    startDate, endDate,
    dimensions: ['query', 'page'],
    rowLimit: ROW_LIMIT
  };

  const endpoint =
    'https://searchconsole.googleapis.com/webmasters/v3/sites/' +
    encodeURIComponent(SITE_URL) +
    '/searchAnalytics/query';

  const response = UrlFetchApp.fetch(endpoint, {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + service.getAccessToken(),
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const data = JSON.parse(response.getContentText());
  if (data.error) { Logger.log(data.error); return; }

  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) sheet = ss.insertSheet(SHEET_NAME);
  sheet.clearContents();

  const headers = [
    'Query', 'Page', 'Clicks', 'Impressions',
    'CTR', 'Avg Position', 'Date Range', 'Exported At'
  ];
  sheet.appendRow(headers);

  const rows = (data.rows || []).map(row => [
    row.keys[0], row.keys[1],
    row.clicks, row.impressions,
    (row.ctr * 100).toFixed(2) + '%',
    row.position.toFixed(1),
    `${startDate} to ${endDate}`,
    new Date().toISOString()
  ]);

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length)
         .setValues(rows);
  }

  Logger.log('✅ Written %s rows to sheet "%s"', rows.length, SHEET_NAME);
}

/**
 * Fetches ALL query data from GSC using paginated requests.
 * Returns a flat array of all rows across all pages.
 */
function fetchAllRows(siteUrl, startDate, endDate, dimensions) {
  const service    = getSearchConsoleService();
  const PAGE_SIZE  = 25000;
  const endpoint   =
    'https://searchconsole.googleapis.com/webmasters/v3/sites/' +
    encodeURIComponent(siteUrl) + '/searchAnalytics/query';

  let allRows  = [];
  let startRow = 0;
  let page     = 0;

  while (true) {
    page++;
    Logger.log('Fetching page %s (startRow: %s)...', page, startRow);

    const payload = {
      startDate, endDate, dimensions,
      rowLimit:  PAGE_SIZE,
      startRow
    };

    const response = UrlFetchApp.fetch(endpoint, {
      method: 'POST',
      headers: {
        'Authorization': 'Bearer ' + service.getAccessToken(),
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    const data = JSON.parse(response.getContentText());
    if (data.error) { Logger.log(data.error); break; }

    const pageRows = data.rows || [];
    allRows = allRows.concat(pageRows);

    Logger.log('  → %s rows returned. Total: %s', pageRows.length, allRows.length);

    if (pageRows.length < PAGE_SIZE) break;

    startRow += PAGE_SIZE;
    Utilities.sleep(500);
  }

  Logger.log('✅ Total rows: %s across %s pages', allRows.length, page);
  return allRows;
}

/**
 * Compares top-query CTR between the current week and last week.
 * Sends a Gmail alert for any query where:
 *   - Impressions >= MIN_IMPRESSIONS
 *   - CTR dropped by >= CTR_DROP_THRESHOLD percentage points
 */
function ctrDropAlert() {
  const SITE_URL           = 'https://www.yourdomain.com/';
  const ALERT_EMAIL        = 'youremail@domain.com';
  const MIN_IMPRESSIONS    = 100;
  const CTR_DROP_THRESHOLD = 2;

  const service = getSearchConsoleService();
  if (!service.hasAccess()) { Logger.log('Not authorized.'); return; }

  const today    = new Date();
  const d        = n => new Date(today - n * 86400000);
  const thisWeek = { start: formatDate(d(9)),  end: formatDate(d(2)) };
  const lastWeek = { start: formatDate(d(16)), end: formatDate(d(9)) };

  const fetchQueryMap = (startDate, endDate) => {
    const endpoint =
      'https://searchconsole.googleapis.com/webmasters/v3/sites/' +
      encodeURIComponent(SITE_URL) + '/searchAnalytics/query';
    const resp = UrlFetchApp.fetch(endpoint, {
      method: 'POST',
      headers: { 'Authorization': 'Bearer ' + service.getAccessToken(),
                 'Content-Type': 'application/json' },
      payload: JSON.stringify({ startDate, endDate,
        dimensions: ['query'], rowLimit: 1000 }),
      muteHttpExceptions: true
    });
    const parsed = JSON.parse(resp.getContentText());
    const map = new Map();
    (parsed.rows || []).forEach(r => map.set(r.keys[0], r));
    return map;
  };

  const currentMap = fetchQueryMap(thisWeek.start, thisWeek.end);
  const previousMap = fetchQueryMap(lastWeek.start, lastWeek.end);

  const drops = [];
  currentMap.forEach((curr, query) => {
    const prev = previousMap.get(query);
    if (!prev) return;

    const currCtr = curr.ctr * 100;
    const prevCtr = prev.ctr * 100;
    const drop    = prevCtr - currCtr;

    if (curr.impressions >= MIN_IMPRESSIONS && drop >= CTR_DROP_THRESHOLD) {
      drops.push({ query, currCtr, prevCtr, drop,
        impressions: curr.impressions,
        position:    curr.position.toFixed(1)
      });
    }
  });

  if (drops.length === 0) {
    Logger.log('✅ No CTR drops detected.');
    return;
  }

  drops.sort((a,b) => b.drop - a.drop);

  let body = `GSC CTR DROP ALERT\n
Site: ${SITE_URL}\nPeriod: ${thisWeek.start} → ${thisWeek.end}\n
vs. prior week: ${lastWeek.start} → ${lastWeek.end}\n
Threshold: -${CTR_DROP_THRESHOLD}pp CTR, min ${MIN_IMPRESSIONS} impressions\n\n
QUERIES FLAGGED (${drops.length}):\n${'─'.repeat(60)}\n`;

  drops.forEach(d => {
    body += `\n"${d.query}"\n
  CTR: ${d.prevCtr.toFixed(1)}% → ${d.currCtr.toFixed(1)}%  (-${d.drop.toFixed(1)}pp)\n
  Impressions: ${d.impressions}  |  Avg Pos: ${d.position}\n`;
  });

  GmailApp.sendEmail(
    ALERT_EMAIL,
    `🚨 GSC CTR Alert: ${drops.length} queries dropped — ${SITE_URL}`,
    body
  );
  Logger.log('📧 Alert sent: %s drops found.', drops.length);
}

/**
 * Pulls GSC data for multiple properties and writes each
 * to its own sheet tab. Add client properties to PROPERTIES array.
 */
function exportAllClients() {
  const PROPERTIES = [
    { url: 'https://client-one.com/',    label: 'Client One'  },
    { url: 'https://client-two.com/',    label: 'Client Two'  },
    { url: 'sc-domain:client-three.com', label: 'Client Three' }
  ];

  const service = getSearchConsoleService();
  if (!service.hasAccess()) { Logger.log('Not authorized.'); return; }

  const today     = new Date();
  const endDate   = formatDate(new Date(today - 2  * 86400000));
  const startDate = formatDate(new Date(today - 29 * 86400000));
  const ss        = SpreadsheetApp.getActiveSpreadsheet();

  PROPERTIES.forEach(prop => {
    Logger.log('Processing: %s', prop.label);

    let sheet = ss.getSheetByName(prop.label);
    if (!sheet) sheet = ss.insertSheet(prop.label);
    sheet.clearContents();

    const endpoint =
      'https://searchconsole.googleapis.com/webmasters/v3/sites/' +
      encodeURIComponent(prop.url) + '/searchAnalytics/query';

    const response = UrlFetchApp.fetch(endpoint, {
      method: 'POST',
      headers: { 'Authorization': 'Bearer ' + service.getAccessToken(),
                 'Content-Type': 'application/json' },
      payload: JSON.stringify({ startDate, endDate,
        dimensions: ['query'], rowLimit: 500 }),
      muteHttpExceptions: true
    });

    const data = JSON.parse(response.getContentText());
    if (data.error) {
      Logger.log('Error for %s: %s', prop.label, data.error.message);
      return;
    }

    sheet.appendRow(['Query','Clicks','Impressions','CTR','Position']);

    const rows = (data.rows || []).map(r => [
      r.keys[0], r.clicks, r.impressions,
      (r.ctr*100).toFixed(2)+'%', r.position.toFixed(1)
    ]);
    if (rows.length) {
      sheet.getRange(2,1,rows.length,5).setValues(rows);
    }
    Logger.log('  ✅ %s rows written for %s', rows.length, prop.label);

    Utilities.sleep(1000);
  });
}