Google Sheets Integration Guide

Quick answer: Use Google Apps Script to call the Surmado REST API from Google Sheets. Store your API key in Script Properties, then define custom functions like =PRESENCE_SCORE("rpt_abc123xyz") that return metrics as spreadsheet values.

Reading time: 12 minutes

---

Prerequisites

1. Surmado API key (sur_live_...) — create at app.surmado.com → Settings → API Keys
2. A Google Sheet with the reports you want to track
3. Familiarity with Google Apps Script (Extensions → Apps Script)

---

How It Works

Surmado reports have two identifiers you’ll see:

• report_id (e.g. rpt_abc123xyz) — the value used in the REST API URL
• token (e.g. SIG-2026-04-A1B2C) — the Intelligence Token, used when chaining reports (passing into a Strategy report from AI Visibility, etc.)

Apps Script functions below take report_id. You get it from the create-report response, from the Surmado app, or from a webhook payload (report.id).

Only AI Visibility (signal) and Site Audit (scan) reports expose public_intelligence over the REST API. Strategy (solutions) reports return only the token and a PDF URL.

---

Step 1: Store Your API Key

Open your sheet → Extensions → Apps Script. In the Apps Script editor:

1. Click the gear icon (Project Settings).
2. Scroll to Script Properties.
3. Add a property named SURMADO_API_KEY with your key (sur_live_...) as the value.

This keeps your key out of the spreadsheet and out of version control.

---

Step 2: Add the Helper Code

In the Apps Script editor, replace the default Code.gs contents with:

const SURMADO_BASE = 'https://api.surmado.com/v1';

function _fetchReport(reportId) {
  const key = PropertiesService.getScriptProperties().getProperty('SURMADO_API_KEY');
  if (!key) throw new Error('SURMADO_API_KEY not set in Script Properties');
  if (!reportId) throw new Error('report_id is required');

  const res = UrlFetchApp.fetch(`${SURMADO_BASE}/reports/${reportId}`, {
    method: 'get',
    headers: { 'Authorization': `Bearer ${key}` },
    muteHttpExceptions: true
  });
  const code = res.getResponseCode();
  if (code !== 200) {
    throw new Error(`Surmado API returned ${code}: ${res.getContentText().slice(0, 200)}`);
  }
  return JSON.parse(res.getContentText());
}

/**
 * Returns the AI Visibility presence score (0-100) for a completed report.
 * Usage: =PRESENCE_SCORE("rpt_abc123xyz")
 */
function PRESENCE_SCORE(reportId) {
  const r = _fetchReport(reportId);
  return r.public_intelligence?.summary?.presence_score ?? null;
}

/**
 * Returns the AI Visibility authority score (0-100).
 * Usage: =AUTHORITY_SCORE("rpt_abc123xyz")
 */
function AUTHORITY_SCORE(reportId) {
  const r = _fetchReport(reportId);
  return r.public_intelligence?.summary?.authority_score ?? null;
}

/**
 * Returns the AI Visibility competitive rank (1 = best).
 * Usage: =COMPETITIVE_RANK("rpt_abc123xyz")
 */
function COMPETITIVE_RANK(reportId) {
  const r = _fetchReport(reportId);
  return r.public_intelligence?.summary?.competitive_rank ?? null;
}

/**
 * Returns a Site Audit score.
 * Scores: "seo", "performance", "accessibility".
 * Usage: =SCAN_SCORE("rpt_abc123xyz", "seo")
 */
function SCAN_SCORE(reportId, which) {
  const r = _fetchReport(reportId);
  const summary = r.public_intelligence?.summary ?? {};
  const map = {
    seo: summary.seo_score,
    performance: summary.performance_score,
    accessibility: summary.accessibility_score
  };
  return map[String(which).toLowerCase()] ?? null;
}

/**
 * Returns the number of critical Site Audit issues.
 * Usage: =SCAN_CRITICAL_ISSUES("rpt_abc123xyz")
 */
function SCAN_CRITICAL_ISSUES(reportId) {
  const r = _fetchReport(reportId);
  return r.public_intelligence?.summary?.critical_issues_count ?? null;
}

/**
 * Returns the report's Intelligence Token (SIG-, SCN-, or SOL-).
 * Usage: =REPORT_TOKEN("rpt_abc123xyz")
 */
function REPORT_TOKEN(reportId) {
  const r = _fetchReport(reportId);
  return r.token ?? null;
}

Save (Ctrl/Cmd + S). The first time you use one of these functions, Google will prompt you to authorize the script.

---

Step 3: Use the Functions in Cells

Example sheet layout:

Brand	Report ID	Presence	Authority	Rank	Token
Acme	rpt_abc123xyz	=PRESENCE_SCORE(B2)	=AUTHORITY_SCORE(B2)	=COMPETITIVE_RANK(B2)	=REPORT_TOKEN(B2)
Other	rpt_def456uvw	=PRESENCE_SCORE(B3)	=AUTHORITY_SCORE(B3)	=COMPETITIVE_RANK(B3)	=REPORT_TOKEN(B3)

Or for Site Audit:

=SCAN_SCORE("rpt_xyz789", "seo")           → 95
=SCAN_SCORE("rpt_xyz789", "performance")   → 87
=SCAN_SCORE("rpt_xyz789", "accessibility") → 92
=SCAN_CRITICAL_ISSUES("rpt_xyz789")        → 8

---

Rate Limits and Caching

Surmado enforces roughly 20 requests per minute per organization. Google Sheets re-evaluates custom functions on cell changes, which can hammer the API. Two mitigations:

1. Snapshot mode: fetch once, paste values. Use Edit → Paste special → Values only to freeze historical rows.
2. Cache in Apps Script with CacheService:

function _fetchReportCached(reportId) {
  const cache = CacheService.getScriptCache();
  const cached = cache.get(reportId);
  if (cached) return JSON.parse(cached);

  const report = _fetchReport(reportId);
  cache.put(reportId, JSON.stringify(report), 3600); // 1 hour
  return report;
}

Then call _fetchReportCached inside each custom function instead of _fetchReport.

---

Automated Refresh via Webhook

For always-fresh data without polling, use the Surmado completion webhook. Pair Google Sheets with the Zapier, Make, or n8n integration — the completion payload already contains the summary, so the automation appends a row with zero API calls from Sheets.

---

Troubleshooting

Surmado API returned 401 — your Script Property SURMADO_API_KEY is missing or wrong. Re-paste the key starting with sur_live_.

Surmado API returned 404 — the report_id doesn’t exist under your organization. Verify the ID in the Surmado app (Reports → select a report). Note: the token (e.g. SIG-2026-04-A1B2C) is not the same as the report_id (e.g. rpt_abc123xyz). The REST API uses report_id.

Surmado API returned 429 — rate limit exceeded. Add caching (above) or reduce how often the sheet refreshes.

Cells show #NAME? — Apps Script saved but not authorized, or the function name was mistyped. Run any function once from the Apps Script editor to trigger the authorization dialog.

Cells show null — the report is still processing (not completed), or it’s a Strategy report (which doesn’t expose public_intelligence). Check =REPORT_TOKEN(B2) — if it starts with SOL-, the summary fields won’t be available.

---

Related: API Integration Guide | Structured Data & Intelligence Tokens | Zapier Integration