Google Sheets Integration Guide

Quick answer: Use Google Apps Script to fetch Intelligence Token data directly in Google Sheets. Custom functions like =PRESENCE_RATE("SIG-2025-11-A1B2C") return metrics as spreadsheet values. Build tracking dashboards, competitive comparisons, client reports without Zapier. Copy/paste provided Apps Script code, authorize API access, use formulas in cells. Works with Signal, Scan, Solutions.

Reading time: 16 minutes

In this guide:

• Create custom Google Sheets functions by pasting provided Apps Script code (fetchTokenData, PRESENCE_RATE, AUTHORITY_SCORE, COMPETITOR_RATE, SCAN_SCORE, etc.) into Extensions → Apps Script, authorizing once, then using formulas like =PRESENCE_RATE(“SIG-2025-11-A1B2C”) returning 42 directly in cells
• Build quarterly tracking dashboards with columns for Date/Token/Presence Rate %/Authority Score/Top Competitor using formulas (=PRESENCE_RATE(B2), =AUTHORITY_SCORE(B2), =COMPETITOR_NAME(B2, 1)) that automatically populate when you paste new tokens in column B, creating line charts showing 28% → 48% annual growth
• Use bulk import scripts by adding importSignalData() and importScanData() functions to custom Surmado menu (Extensions → Surmado → Import Signal Data), prompting for token, then auto-populating entire row with business name/metrics/competitors/platform variance vs manual cell-by-cell entry
• Set up scheduled auto-refresh using refreshAllTokens() function with Google Apps Script time-driven triggers (daily 9-10am) to update all tokens in column B automatically, though Intelligence Token data is static post-report so refresh only useful for adding new tokens regularly
• Create advanced dashboards including competitive comparison tables (you vs top 3 competitors side-by-side), platform variance heatmaps (ChatGPT: 52%, Claude: 38% color-coded), multi-client agency tracking (20 clients with Status column IF formulas showing “At Risk” < 30%), and historical trends with SPARKLINE mini charts

No coding experience required (copy/paste provided scripts)

---

Method 1: Custom Functions (Recommended)

Use Case

Fetch individual metrics with spreadsheet formulas:

• =PRESENCE_RATE("SIG-2025-11-A1B2C") → 42
• =AUTHORITY_SCORE("SIG-2025-11-A1B2C") → 78
• =COMPETITOR_RATE("SIG-2025-11-A1B2C", 1) → 54 (top competitor)

Setup Steps

Step 1: Open Apps Script Editor

1. Open your Google Sheet
2. Click Extensions → Apps Script
3. Delete default function myFunction() code

---

Step 2: Add Intelligence Token API Code

Copy/paste this code:

/**
 * Fetch Intelligence Token data from Surmado API
 * @param {string} token - Intelligence Token (e.g., "SIG-2025-11-A1B2C")
 * @return {object} JSON data from API
 */
function fetchTokenData(token) {
  const url = `https://api.surmado.com/intelligence/${token}`;

  try {
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());
    return data;
  } catch (error) {
    Logger.log(`Error fetching token ${token}: ${error}`);
    return null;
  }
}

/**
 * Get Presence Rate from Signal token
 * @param {string} token - Signal Intelligence Token
 * @return {number} Presence Rate as percentage (0-100)
 * @customfunction
 */
function PRESENCE_RATE(token) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }
  return data.metrics.presence_rate * 100;
}

/**
 * Get Authority Score from Signal token
 * @param {string} token - Signal Intelligence Token
 * @return {number} Authority Score (0-100)
 * @customfunction
 */
function AUTHORITY_SCORE(token) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }
  return data.metrics.authority_score || 0;
}

/**
 * Get Ghost Influence from Signal token
 * @param {string} token - Signal Intelligence Token
 * @return {number} Ghost Influence as percentage (0-100)
 * @customfunction
 */
function GHOST_INFLUENCE(token) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }
  return data.metrics.ghost_influence * 100;
}

/**
 * Get competitor Presence Rate from Signal token
 * @param {string} token - Signal Intelligence Token
 * @param {number} rank - Competitor rank (1 = top competitor, 2 = second, etc.)
 * @return {number} Competitor Presence Rate as percentage (0-100)
 * @customfunction
 */
function COMPETITOR_RATE(token, rank) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }

  const competitors = data.competitors || [];
  if (rank < 1 || rank > competitors.length) {
    return 'N/A';
  }

  return competitors[rank - 1].presence_rate * 100;
}

/**
 * Get competitor name from Signal token
 * @param {string} token - Signal Intelligence Token
 * @param {number} rank - Competitor rank (1 = top competitor, 2 = second, etc.)
 * @return {string} Competitor name
 * @customfunction
 */
function COMPETITOR_NAME(token, rank) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }

  const competitors = data.competitors || [];
  if (rank < 1 || rank > competitors.length) {
    return 'N/A';
  }

  return competitors[rank - 1].name;
}

/**
 * Get overall score from Scan token
 * @param {string} token - Scan Intelligence Token
 * @return {number} Overall score (0-100)
 * @customfunction
 */
function SCAN_SCORE(token) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'scan') {
    return 'ERROR';
  }
  return data.overall_score;
}

/**
 * Get Scan category score
 * @param {string} token - Scan Intelligence Token
 * @param {string} category - Category name: "technical_seo", "performance", "accessibility", "security"
 * @return {number} Category score (0-100)
 * @customfunction
 */
function SCAN_CATEGORY(token, category) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'scan') {
    return 'ERROR';
  }

  const validCategories = ['technical_seo', 'performance', 'accessibility', 'security'];
  if (!validCategories.includes(category)) {
    return 'INVALID_CATEGORY';
  }

  return data.category_scores[category] || 0;
}

/**
 * Get Scan issues count
 * @param {string} token - Scan Intelligence Token
 * @param {string} severity - Optional severity filter: "high", "medium", "low", or "all"
 * @return {number} Number of issues
 * @customfunction
 */
function SCAN_ISSUES(token, severity = 'all') {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'scan') {
    return 'ERROR';
  }

  const issues = data.issues || [];

  if (severity === 'all') {
    return issues.length;
  }

  return issues.filter(issue => issue.severity === severity).length;
}

/**
 * Get platform-specific Presence Rate from Signal token
 * @param {string} token - Signal Intelligence Token
 * @param {string} platform - Platform name: "chatgpt", "claude", "gemini", "deepseek", "meta_ai", "grok", "chatgpt_search", "perplexity"
 * @return {number} Platform Presence Rate as percentage (0-100)
 * @customfunction
 */
function PLATFORM_RATE(token, platform) {
  const data = fetchTokenData(token);
  if (!data || data.product !== 'signal') {
    return 'ERROR';
  }

  const platformData = data.platform_variance[platform];
  if (!platformData) {
    return 'INVALID_PLATFORM';
  }

  return platformData.presence_rate * 100;
}

---

Step 3: Save and Authorize

1. Click Save (disk icon)
2. Name project: Surmado Intelligence Token Functions
3. Close Apps Script editor
4. Return to Google Sheet
5. Type =PRESENCE_RATE("SIG-2025-11-A1B2C") in any cell (use your actual token)
6. Click Allow when prompted for authorization
7. Authorize access to external services (Surmado API)

---

Step 4: Use Custom Functions

Signal metrics:

=PRESENCE_RATE("SIG-2025-11-A1B2C")          → 42
=AUTHORITY_SCORE("SIG-2025-11-A1B2C")        → 78
=GHOST_INFLUENCE("SIG-2025-11-A1B2C")        → 28
=COMPETITOR_RATE("SIG-2025-11-A1B2C", 1)     → 54 (top competitor)
=COMPETITOR_NAME("SIG-2025-11-A1B2C", 1)     → "Competitor A"
=PLATFORM_RATE("SIG-2025-11-A1B2C", "chatgpt") → 52

Scan metrics:

=SCAN_SCORE("SCAN-2025-11-D4E5F")                   → 68
=SCAN_CATEGORY("SCAN-2025-11-D4E5F", "performance") → 58
=SCAN_ISSUES("SCAN-2025-11-D4E5F", "high")          → 12
=SCAN_ISSUES("SCAN-2025-11-D4E5F", "all")           → 62

---

Example Dashboard

Sheet layout:

A	B	C	D	E
Date	Token	Presence Rate	Authority Score	Top Competitor
2025-11-10	SIG-2025-11-A1B2C	=PRESENCE_RATE(B2)	=AUTHORITY_SCORE(B2)	=COMPETITOR_NAME(B2, 1)

Cell formulas:

• C2: =PRESENCE_RATE(B2)
• D2: =AUTHORITY_SCORE(B2)
• E2: =COMPETITOR_NAME(B2, 1)

Copy row 2 down for historical tracking:

A	B	C	D	E
2025-02-15	SIG-2025-02-XXXXX	28	68	Competitor A
2025-05-20	SIG-2025-05-XXXXX	34	72	Competitor A
2025-08-18	SIG-2025-08-XXXXX	42	78	Competitor A
2025-11-10	SIG-2025-11-A1B2C	48	82	Competitor B

Add chart: Select columns C-D, Insert → Chart → Line chart

Result: Visual trend of Presence Rate and Authority Score over time

---

Method 2: Bulk Import with Apps Script

Use Case

Import all fields from a token at once (not just individual metrics).

Setup

Step 1: Add Bulk Import Function

Add this to your Apps Script (same project as Method 1):

/**
 * Import all Signal data from token to current row
 * Place cursor in any cell, run this function from Apps Script menu
 */
function importSignalData() {
  const ui = SpreadsheetApp.getUi();

  // Prompt for token
  const response = ui.prompt('Import Signal Data', 'Enter Intelligence Token:', ui.ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  const token = response.getResponseText().trim();
  const data = fetchTokenData(token);

  if (!data || data.product !== 'signal') {
    ui.alert('Error: Invalid Signal token or API error');
    return;
  }

  // Get active sheet and current row
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  // Write data to columns
  sheet.getRange(row, 1).setValue(new Date(data.created_at)); // Date
  sheet.getRange(row, 2).setValue(token); // Token
  sheet.getRange(row, 3).setValue(data.business.name); // Business Name
  sheet.getRange(row, 4).setValue(data.metrics.presence_rate * 100); // Presence Rate
  sheet.getRange(row, 5).setValue(data.metrics.authority_score || 0); // Authority Score
  sheet.getRange(row, 6).setValue(data.metrics.ghost_influence * 100); // Ghost Influence
  sheet.getRange(row, 7).setValue(data.metrics.category_share * 100); // Category Share

  // Competitor data (top 3)
  const competitors = data.competitors || [];
  for (let i = 0; i < 3; i++) {
    if (i < competitors.length) {
      sheet.getRange(row, 8 + i * 2).setValue(competitors[i].name);
      sheet.getRange(row, 9 + i * 2).setValue(competitors[i].presence_rate * 100);
    }
  }

  ui.alert('Success', `Imported data for ${data.business.name}`, ui.ButtonSet.OK);
}

---

Step 2: Add Custom Menu

Add this to create menu item:

/**
 * Add custom menu when sheet opens
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Surmado')
    .addItem('Import Signal Data', 'importSignalData')
    .addItem('Import Scan Data', 'importScanData')
    .addToUi();
}

---

Step 3: Create Header Row

Manually add headers (row 1):

A	B	C	D	E	F	G	H	I	J	K	L	M
Date	Token	Business	Presence Rate	Authority	Ghost Influence	Category Share	Comp 1 Name	Comp 1 Rate	Comp 2 Name	Comp 2 Rate	Comp 3 Name	Comp 3 Rate

---

Step 4: Import Data

1. Click in row 2, column A
2. Click Surmado menu → Import Signal Data
3. Enter token: SIG-2025-11-A1B2C
4. Click OK

Result: Entire row populated with Signal data

---

Scan Bulk Import Function

Add this for Scan tokens:

/**
 * Import all Scan data from token to current row
 */
function importScanData() {
  const ui = SpreadsheetApp.getUi();

  const response = ui.prompt('Import Scan Data', 'Enter Scan Intelligence Token:', ui.ButtonSet.OK_CANCEL);

  if (response.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  const token = response.getResponseText().trim();
  const data = fetchTokenData(token);

  if (!data || data.product !== 'scan') {
    ui.alert('Error: Invalid Scan token or API error');
    return;
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  // Write data to columns
  sheet.getRange(row, 1).setValue(new Date(data.created_at)); // Date
  sheet.getRange(row, 2).setValue(token); // Token
  sheet.getRange(row, 3).setValue(data.website.url); // Website
  sheet.getRange(row, 4).setValue(data.overall_score); // Overall Score
  sheet.getRange(row, 5).setValue(data.category_scores.technical_seo); // Technical SEO
  sheet.getRange(row, 6).setValue(data.category_scores.performance); // Performance
  sheet.getRange(row, 7).setValue(data.category_scores.accessibility); // Accessibility
  sheet.getRange(row, 8).setValue(data.category_scores.security); // Security

  // Issues count
  const issues = data.issues || [];
  const highCount = issues.filter(i => i.severity === 'high').length;
  const mediumCount = issues.filter(i => i.severity === 'medium').length;
  const lowCount = issues.filter(i => i.severity === 'low').length;

  sheet.getRange(row, 9).setValue(issues.length); // Total Issues
  sheet.getRange(row, 10).setValue(highCount); // High Severity
  sheet.getRange(row, 11).setValue(mediumCount); // Medium Severity
  sheet.getRange(row, 12).setValue(lowCount); // Low Severity

  ui.alert('Success', `Imported Scan data for ${data.website.url}`, ui.ButtonSet.OK);
}

Scan header row:

A	B	C	D	E	F	G	H	I	J	K	L
Date	Token	Website	Overall	Tech SEO	Performance	Accessibility	Security	Total Issues	High	Medium	Low

---

Method 3: Scheduled Auto-Update

Use Case

Automatically refresh dashboard data daily without manual action.

Setup

Step 1: Add Refresh Function

Add to Apps Script:

/**
 * Refresh all token data in sheet
 * Assumes tokens are in column B, starting row 2
 */
function refreshAllTokens() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return; // No data to refresh
  }

  // Get all tokens from column B
  const tokensRange = sheet.getRange(2, 2, lastRow - 1, 1);
  const tokens = tokensRange.getValues();

  // Refresh each token
  for (let i = 0; i < tokens.length; i++) {
    const token = tokens[i][0];
    if (!token || token === '') continue;

    const data = fetchTokenData(token);
    if (!data) continue;

    const row = i + 2; // Row number in sheet

    if (data.product === 'signal') {
      sheet.getRange(row, 4).setValue(data.metrics.presence_rate * 100);
      sheet.getRange(row, 5).setValue(data.metrics.authority_score || 0);
      sheet.getRange(row, 6).setValue(data.metrics.ghost_influence * 100);
    } else if (data.product === 'scan') {
      sheet.getRange(row, 4).setValue(data.overall_score);
      sheet.getRange(row, 5).setValue(data.category_scores.technical_seo);
      sheet.getRange(row, 6).setValue(data.category_scores.performance);
    }

    // Avoid API rate limits (if future rate limits imposed)
    Utilities.sleep(500); // 500ms delay between requests
  }

  Logger.log(`Refreshed ${tokens.length} tokens`);
}

---

Step 2: Set Up Daily Trigger

1. In Apps Script editor, click Triggers (clock icon, left sidebar)
2. Click + Add Trigger
3. Configuration:
   • Function: refreshAllTokens
   • Event source: Time-driven
   • Type: Day timer
   • Time of day: 9am to 10am (or preferred time)
4. Click Save

Result: Sheet data refreshes daily automatically

Note: Intelligence Token data doesn’t change after report creation, so daily refresh is only useful if you’re adding new tokens regularly. For static historical tracking, refresh is unnecessary.

---

Method 4: Competitive Comparison Table

Use Case

Compare your Signal metrics to top 3 competitors side-by-side.

Setup

Sheet layout:

A	B	C	D
Metric	You	Competitor 1	Competitor 2
Token	SIG-2025-11-A1B2C	=COMPETITOR_NAME(B2, 1)	=COMPETITOR_NAME(B2, 2)
Presence Rate	=PRESENCE_RATE(B2)	=COMPETITOR_RATE(B2, 1)	=COMPETITOR_RATE(B2, 2)
Gap vs You	0	=C3-B3	=D3-B3

Formulas:

• B2: Your token (manual entry)
• C2: =COMPETITOR_NAME(B2, 1)
• D2: =COMPETITOR_NAME(B2, 2)
• B3: =PRESENCE_RATE(B2)
• C3: =COMPETITOR_RATE(B2, 1)
• D3: =COMPETITOR_RATE(B2, 2)
• C4: =C3-B3 (gap: positive means competitor ahead)
• D4: =D3-B3

Conditional formatting:

• Row 4 (Gap): Red if positive (competitor ahead), green if negative (you ahead)

Add chart:

• Select B3:D3 (Presence Rates)
• Insert → Chart → Bar chart
• Visual comparison of you vs competitors

---

Method 5: Platform Variance Heatmap

Use Case

Visualize which AI platforms favor you vs competitors.

Setup

Sheet layout:

A	B	C	D	E	F	G	H	I
Platform	ChatGPT	Claude	Gemini	DeepSeek	Meta AI	Grok	ChatGPT Search	Perplexity
Your Token	SIG-2025-11-A1B2C
Presence Rate	=PLATFORM_RATE($B$2, B1)	=PLATFORM_RATE($B$2, C1)	…	…	…	…	…	=PLATFORM_RATE($B$2, I1)

Formulas:

• B1: chatgpt (platform name, lowercase)
• C1: claude
• D1: gemini
• (etc. for all 8 platforms)
• B3: =PLATFORM_RATE($B$2, B1) (absolute reference to token in B2, relative to platform name in B1)
• Copy formula across row 3

Conditional formatting:

• Select B3:I3
• Format → Conditional formatting
• Color scale: Red (0%) → Yellow (50%) → Green (100%)

Result: Heatmap showing which platforms give you best visibility

---

Advanced Patterns

Pattern 1: Historical Trend with SPARKLINE

Use case: Mini chart showing Presence Rate trend inline

Setup:

• Column A-B: Dates and tokens for last 6 months
• Column C: Presence Rates
• Column D: =SPARKLINE(C2:C7) (mini line chart)

Result: Inline trend visualization

---

Pattern 2: Conditional Alerts with Apps Script

Add notification function:

/**
 * Check if Presence Rate dropped below threshold, send email alert
 */
function checkPresenceAlert() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const token = sheet.getRange('B2').getValue(); // Latest token
  const data = fetchTokenData(token);

  if (!data || data.product !== 'signal') return;

  const presenceRate = data.metrics.presence_rate * 100;
  const threshold = 30; // Alert if below 30%

  if (presenceRate < threshold) {
    MailApp.sendEmail({
      to: 'you@yourcompany.com',
      subject: 'Alert: Presence Rate Below Threshold',
      body: `Your latest Signal report shows ${presenceRate.toFixed(1)}% Presence Rate, below ${threshold}% threshold.\n\nToken: ${token}\n\nReview report for details.`
    });
  }
}

Set up trigger: Run checkPresenceAlert daily

---

Pattern 3: Multi-Client Dashboard (Agencies)

Sheet layout:

A	B	C	D	E	F
Client	Latest Token	Presence Rate	Authority	Last Updated	Status
Phoenix Cool Air	SIG-2025-11-A1B2C	=PRESENCE_RATE(B2)	=AUTHORITY_SCORE(B2)	=fetchDate(B2)	=IF(C2<30,"At Risk","Good")

Add date fetch function:

/**
 * Get report date from token
 * @param {string} token - Intelligence Token
 * @return {string} Report date
 * @customfunction
 */
function fetchDate(token) {
  const data = fetchTokenData(token);
  if (!data) return 'ERROR';
  return new Date(data.created_at).toLocaleDateString();
}

Conditional formatting:

• Column F (Status): Red for “At Risk”, green for “Good”

---

Error Handling

Common Errors

#ERROR! in cell:

• Token format incorrect (check for spaces, typos)
• API request failed (check internet connection)
• Token doesn’t exist (verify token in email)

Fix: Check formula syntax, verify token in browser: https://api.surmado.com/intelligence/{TOKEN}

---

“Loading…” indefinitely:

• Apps Script authorization pending
• Re-authorize: Extensions → Apps Script → Run any function → Allow

---

“Service invoked too many times”:

• Apps Script quota exceeded (UrlFetchApp limit: 20,000/day)
• Reduce refresh frequency or use caching

---

Best Practices

1. Use Absolute References for Tokens

Good:

=PRESENCE_RATE($B$2)

Why: Copy formula down without changing token reference

---

2. Cache API Responses

Pattern: Fetch once, store in hidden column, reference stored value

Why: Avoid hitting API multiple times for same token (faster, respects future rate limits)

Example:

• Column Z (hidden): =fetchTokenData(B2) (full JSON as text)
• Column C: Extract from cached JSON instead of re-fetching

---

3. Separate Raw Data and Visualizations

Sheet 1: Data (tokens, formulas, raw metrics) Sheet 2: Dashboard (charts, conditional formatting, summary)

Why: Cleaner organization, easier maintenance

---

4. Use Named Ranges

Example:

• Select B2, name it CurrentToken (Data → Named ranges)
• All formulas use =PRESENCE_RATE(CurrentToken) instead of B2

Why: Easier to read, update token in one place

---

5. Document Custom Functions

Add comments in Apps Script:

/**
 * Get Presence Rate from Signal token
 * @param {string} token - Signal Intelligence Token (format: SIG-YYYY-MM-XXXXX)
 * @return {number} Presence Rate as percentage (0-100)
 * @example =PRESENCE_RATE("SIG-2025-11-A1B2C") returns 42
 * @customfunction
 */

Why: Helps collaborators understand usage

---

Frequently Asked Questions

Do formulas auto-update when data changes?

No. Intelligence Token data is static (doesn’t change after report creation). Use refreshAllTokens() function if you want to manually refresh (e.g., if Surmado updates historical data).

Can I use custom functions in Google Sheets mobile app?

No. Custom functions require Apps Script, which only runs in desktop browser version. Values will display on mobile but can’t be edited/recalculated.

How many API calls can I make per day?

Apps Script UrlFetchApp quota: 20,000 requests/day (free tier). More than enough for typical use (even 100 tokens × 10 refreshes/day = 1,000 requests).

Can I share sheets with clients without exposing API code?

Yes. Share sheet in view-only mode. Custom functions work for viewers (they see calculated values, not Apps Script code). To fully hide code, copy values and paste as “Values only” before sharing.

Does this work with Scan and Solutions tokens?

Yes. Use SCAN_SCORE(), SCAN_CATEGORY(), SCAN_ISSUES() for Scan tokens. Solutions tokens require custom function (not included in base code, but follow same pattern: fetch JSON, parse recommendations array).

---

Need custom Google Sheets integration? Contact hi@surmado.com with your use case. We can provide tailored Apps Script templates for complex dashboards or multi-client reporting.