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')
    // Set the endpoint URLs
    .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
    .setTokenUrl('https://accounts.google.com/o/oauth2/token')

    // Set the OAuth credentials from Script Properties
    .setClientId(props.getProperty('CLIENT_ID'))
    .setClientSecret(props.getProperty('CLIENT_SECRET'))

    // Use the script's properties store for token persistence
    .setPropertyStore(props)

    // Set the callback function name (must match the function below)
    .setCallbackFunction('authCallback')

    // Request read-only access to Search Console data
    .setScope('https://www.googleapis.com/auth/webmasters.readonly')

    // Required for Google APIs to return a refresh token
    .setParam('access_type', 'offline')
    .setParam('prompt', 'consent');
}

/**
 * Handles the OAuth callback. Apps Script calls this automatically
 * after the user authorizes access in the browser prompt.
 */
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. Close this tab and try again.'
  );
}

/**
 * Run this function once to trigger the OAuth authorization flow.
 * Check the Apps Script execution log for the authorization URL,
 * then open it in your browser to grant access.
 */
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.
 * Replace SITE_URL with your verified property URL, e.g.:
 *   'https://www.yourdomain.com/'  (for URL-prefix properties)
 *   'sc-domain:yourdomain.com'     (for domain properties)
 */
function fetchTopQueries() {
  const SITE_URL  = 'https://www.yourdomain.com/';
  const service   = getSearchConsoleService();

  // Guard: abort if not authorized
  if (!service.hasAccess()) {
    Logger.log('Not authorized. Run authorizeScript() first.');
    return;
  }

  // Calculate date range: last 28 days (GSC data lags ~2 days)
  const today     = new Date();
  const endDate   = formatDate(new Date(today - 2 * 86400000));
  const startDate = formatDate(new Date(today - 29 * 86400000));

  // Build the API request body
  const payload = {
    startDate:  startDate,
    endDate:    endDate,
    dimensions: ['query'],          // group results by search query
    rowLimit:   10,                // top 10 only for this demo
    startRow:   0                  // pagination offset (0 = first page)
  };

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

  // Make the authenticated POST request
  const response = UrlFetchApp.fetch(endpoint, {
    method:  'POST',
    headers: {
      'Authorization': 'Bearer ' + service.getAccessToken(),
      'Content-Type':  'application/json'
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true     // prevents script crash on API errors
  });

  // Parse and log the response
  const data = JSON.parse(response.getContentText());

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

  // Log each row: query | clicks | impressions | ctr | position
  (data.rows || []).forEach(row => {
    Logger.log(
      '%-40s | clicks: %-5s | impr: %-6s | ctr: %s% | pos: %s',
      row.keys[0],                               // query string
      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,   // multiply by 100 for percentage
      "position":    3.2       // average position (1.0 = top result)
    },
    {
      "keys": ["gsc api tutorial"],
      "clicks":      98,
      "impressions": 2100,
      "ctr":         0.0467,
      "position":    5.8
    }
    // ... more rows
  ],
  "responseAggregationType": "byPage"
}

/**
 * Pulls top 500 query+page combinations from GSC (last 28 days)
 * and writes them to a sheet named 'GSC_Report'.
 * Creates the sheet and headers if they don't exist.
 */
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. Run authorizeScript() first.');
    return;
  }

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

  // ── API request ─────────────────────────────────────────────────
  const payload = {
    startDate,
    endDate,
    dimensions: ['query', 'page'],    // row per query+page combination
    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; }

  // ── Google Sheets setup ─────────────────────────────────────────
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  let   sheet = ss.getSheetByName(SHEET_NAME);

  // Create the sheet if it doesn't exist
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
  }

  // Clear previous data (preserves formatting)
  sheet.clearContents();

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

  // Style the header row
  sheet.getRange(1, 1, 1, headers.length)
       .setFontWeight('bold')
       .setBackground('#1a1a1a')
       .setFontColor('#bf953f');

  // ── Write data rows ─────────────────────────────────────────────
  const exportedAt  = new Date().toISOString();
  const dateRangeStr = `${startDate} to ${endDate}`;

  const rows = (data.rows || []).map(row => [
    row.keys[0],                          // query
    row.keys[1],                          // page URL
    row.clicks,
    row.impressions,
    (row.ctr * 100).toFixed(2) + '%',   // format as percentage
    row.position.toFixed(1),
    dateRangeStr,
    exportedAt
  ]);

  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.
 * Each request returns up to 25,000 rows; loops until exhausted.
 * 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 so far: %s', pageRows.length, allRows.length);

    // If fewer than PAGE_SIZE rows returned, we've reached the last page
    if (pageRows.length < PAGE_SIZE) break;

    startRow += PAGE_SIZE;

    // Polite delay to avoid hitting rate limits on large datasets
    Utilities.sleep(500);
  }

  Logger.log('✅ Total rows fetched: %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 (to filter low-traffic noise)
 *   - 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;    // ignore low-impression queries
  const CTR_DROP_THRESHOLD = 2;       // alert if CTR drops 2+ percentage points

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

  // Calculate date ranges for this week and last week (both lag 2 days)
  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)) };

  // Fetch helper: returns a Map of query → {clicks, impressions, ctr, position}
  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);

  // Find queries with significant CTR drops
  const drops = [];
  currentMap.forEach((curr, query) => {
    const prev = previousMap.get(query);
    if (!prev) return;  // skip queries with no prior week data

    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 this week.');
    return;
  }

  // Sort by largest drop first
  drops.sort((a,b) => b.drop - a.drop);

  // Build email body
  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);

    // Get or create tab for this client
    let sheet = ss.getSheetByName(prop.label);
    if (!sheet) sheet = ss.insertSheet(prop.label);
    sheet.clearContents();

    // Fetch data for this property
    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);

    // Pause between properties to respect rate limits
    Utilities.sleep(1000);
  });
}