Skip to content
forumdataapi.com
Integrations/Google Sheets Apps Script

Import Reddit Posts & Sentiment to Google Sheets (Cached Apps Script)

Batch-enrich keyword queries and subreddit feeds directly inside Google Sheets via a safe top-bar menu: ForumData API → Fetch Subreddit & Keyword Mentions (Skip Filled). Includes 6-hour CacheService and PropertiesService key isolation.

⚠ Zero-Waste Design Rule: Never put live API calls inside spreadsheet cell formulas — Google Sheets recalculates formulas automatically on tab reload. This menu-triggered script only processes rows where Column E (Status) is blank and caches responses for 6 hours.

01 · PAY-AS-YOU-GO

$0.10 / 1K items · Never expires

0 Credits charged on empty keyword polls or 4xx/5xx upstream errors.

02 · ZERO OAUTH / ZERO COOKIES

No Reddit account or proxies needed

Skip 6-week commercial OAuth reviews, refresh tokens, and AWS/GCP 403 blocks.

03 · 10×–24× LOWER UNIT COST

Beats Official $0.24/1K calls & $1.50 actors

Up to 100 posts or 200 nested comments per call ($0.04–$0.06/1K with bonus).

04 · INSTANT FREE SANDBOX

2,500 Free Credits ($0.25) on sign-up

No credit card required · 60 RPM (5 RPS) unlocked · Indie PAYG from $7.50 Crypto.

Google Apps Script (Code.gs) · Menu-Triggered + 6h CacheService + PropertiesService

Paste into Extensions → Apps Script, save, reload your sheet, and run ForumData API → Fetch Subreddit & Keyword Mentions (Skip Filled).

/**
 * ForumData API — Google Sheets Reddit Posts & Sentiment Batch Importer
 * Base Endpoint: https://api.forumdataapi.com
 *
 * SAFETY & ZERO-WASTE GUARANTEES:
 * 1. NO custom sheet formulas (=IMPORTDATA / =FORUMDATA) that auto-recalculate and burn credits on reload.
 * 2. Triggered ONLY manually via top menu: "ForumData API -> Fetch Subreddit & Keyword Mentions (Skip Filled)".
 * 3. API key stored securely in PropertiesService (Script Properties), never exposed in cells.
 * 4. 6-Hour CacheService (21,600s TTL) prevents duplicate credit charges on repeated runs.
 * 5. Automatically skips rows where Column E (Status) is already filled ("Skip Filled").
 *
 * Sheet Column Layout (Row 1 = Headers, Data starts on Row 2):
 *   Col A: Mode ("search" or "subreddit")
 *   Col B: Query or Subreddit (e.g. "supabase OR clerk" or "SaaS")
 *   Col C: Sort ("new", "hot", "top", "rising")
 *   Col D: Limit (1–100, defaults to 25)
 *   Col E: Status (Auto-filled — clear cell to force refresh)
 *   Col F: Matched Posts Count
 *   Col G: Sentiment Signal (Positive / Pain-Point / Neutral)
 *   Col H: Top Thread Title
 *   Col I: Top Score & Comments
 *   Col J: Top Thread URL
 *   Col K: Credits Used
 */

const FORUMDATA_BASE_URL = 'https://api.forumdataapi.com';
const CACHE_TTL_SECONDS = 21600; // 6 hours

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ForumData API')
    .addItem('Fetch Subreddit & Keyword Mentions (Skip Filled)', 'fetchRedditMentionsSkipFilled')
    .addSeparator()
    .addItem('Set API Key (PropertiesService)', 'setForumDataApiKey')
    .addToUi();
}

function setForumDataApiKey() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt(
    'ForumData API Key',
    'Paste your rd_live_... API key (get 2,500 free credits at https://forumdataapi.com/signup):',
    ui.ButtonSet.OK_CANCEL
  );
  if (res.getSelectedButton() === ui.Button.OK) {
    const key = res.getResponseText().trim();
    if (!key) return;
    PropertiesService.getScriptProperties().setProperty('FORUMDATA_API_KEY', key);
    ui.alert('Saved FORUMDATA_API_KEY in Script Properties.');
  }
}

function scoreSentimentSignal_(posts) {
  const posRe = /\b(love|great|fast|recommend|solved|helpful|clean|worth|reliable|awesome)\b/i;
  const negRe = /\b(broken|slow|expensive|banned|403|429|hate|bug|issue|frustrating|downtime|overpriced)\b/i;
  let pos = 0;
  let neg = 0;
  for (var i = 0; i < posts.length; i++) {
    var txt = ((posts[i].title || '') + ' ' + (posts[i].selftext || '')).slice(0, 800);
    if (posRe.test(txt)) pos++;
    if (negRe.test(txt)) neg++;
  }
  if (neg > pos && neg > 0) return 'Pain-Point (' + neg + ' neg / ' + pos + ' pos)';
  if (pos > neg && pos > 0) return 'Positive (' + pos + ' pos / ' + neg + ' neg)';
  return 'Neutral (' + pos + ' pos / ' + neg + ' neg)';
}

function cacheKeyDigest_(raw) {
  const bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, raw);
  return 'fd_' + bytes.slice(0, 16).map(function (b) {
    return ('0' + (b & 0xff).toString(16)).slice(-2);
  }).join('');
}

function fetchRedditMentionsSkipFilled() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('FORUMDATA_API_KEY');
  if (!apiKey) {
    SpreadsheetApp.getUi().alert('Missing API Key. Run "ForumData API -> Set API Key (PropertiesService)" first.');
    return;
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  const range = sheet.getRange(2, 1, lastRow - 1, 11);
  const rows = range.getValues();
  const cache = CacheService.getScriptCache();
  let updatedCount = 0;

  for (var i = 0; i < rows.length; i++) {
    var mode = String(rows[i][0] || 'search').trim().toLowerCase();
    var target = String(rows[i][1] || '').trim();
    var sort = String(rows[i][2] || (mode === 'subreddit' ? 'hot' : 'new')).trim().toLowerCase();
    var limit = Math.min(100, Math.max(1, parseInt(rows[i][3], 10) || 25));
    var existingStatus = String(rows[i][4] || '').trim();

    if (!target || existingStatus !== '') {
      continue; // Skip empty inputs and already-filled rows!
    }

    var endpointUrl =
      mode === 'subreddit'
        ? FORUMDATA_BASE_URL + '/v1/subreddit/posts?subreddit=' + encodeURIComponent(target.replace(/^r\//i, '')) + '&sort=' + encodeURIComponent(sort) + '&limit=' + limit
        : FORUMDATA_BASE_URL + '/v1/reddit/search?query=' + encodeURIComponent(target) + '&sort=' + encodeURIComponent(sort) + '&limit=' + limit;

    var cKey = cacheKeyDigest_(endpointUrl);
    var cachedRaw = cache.get(cKey);
    var payload = null;
    var fromScriptCache = false;

    if (cachedRaw) {
      payload = JSON.parse(cachedRaw);
      fromScriptCache = true;
    } else {
      var resp = UrlFetchApp.fetch(endpointUrl, {
        method: 'get',
        muteHttpExceptions: true,
        headers: {
          'x-api-key': apiKey,
          'accept': 'application/json'
        }
      });
      var status = resp.getResponseCode();
      var bodyText = resp.getContentText();
      if (status !== 200) {
        rows[i][4] = 'ERROR HTTP ' + status;
        rows[i][10] = 0;
        updatedCount++;
        continue;
      }
      payload = JSON.parse(bodyText);
      if (bodyText.length < 95000) {
        cache.put(cKey, bodyText, CACHE_TTL_SECONDS);
      }
    }

    var items = (payload && payload.data && payload.data.items) ? payload.data.items : [];
    var top = items[0] || null;
    var creditsUsed = fromScriptCache ? '0 (6h Cache)' : Number((payload.meta && payload.meta.credits_used) || 0);

    rows[i][4] = fromScriptCache ? 'OK (Cached)' : 'OK (' + new Date().toISOString().slice(0, 16) + 'Z)';
    rows[i][5] = items.length;
    rows[i][6] = items.length > 0 ? scoreSentimentSignal_(items) : 'Empty ($0.00)';
    rows[i][7] = top ? String(top.title || '') : '';
    rows[i][8] = top ? ('▲ ' + (top.score || 0) + ' · 💬 ' + (top.num_comments || 0)) : '';
    rows[i][9] = top && top.permalink ? ('https://www.reddit.com' + top.permalink) : '';
    rows[i][10] = creditsUsed;
    updatedCount++;
  }

  if (updatedCount > 0) {
    range.setValues(rows);
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('Processed ' + updatedCount + ' row(s). Filled rows were skipped.');
}

Spreadsheet Column Schema & Exact Credit Cost

ColumnField NameExample Value / Behavior
A (Input)Mode"search" (keyword search) or "subreddit" (feed)
B (Input)Query / Subreddit"supabase OR clerk" or "SaaS"
C–D (Input)Sort & Limit"new" / "hot" · 25 posts ($0.0025 PAYG, $0 if empty)
E (Guard)Status (Skip Filled)Auto-populated timestamp; non-empty rows are skipped on subsequent runs
F–K (Output)Count, Sentiment, Top Post & CreditsMatched count, Pain-Point/Positive/Neutral tag, top thread title, score, URL, and credits billed

Google Sheets Integration FAQ

Why shouldn't I use custom spreadsheet formulas like =IMPORTDATA() for Reddit API data?

Google Sheets re-evaluates custom cell formulas every time a workbook is opened, sorted, or refreshed in the background — silently burning thousands of API credits overnight. Using our menu-triggered Google Apps Script with Skip Filled and 6-hour CacheService ensures credits are only spent when you explicitly click run on new rows.

Where is my ForumData API key stored in Google Sheets?

Your rd_live_... key is stored inside Google Apps Script PropertiesService.getScriptProperties() via 'ForumData API -> Set API Key (PropertiesService)', keeping it out of visible spreadsheet cells and CSV exports.

How much does importing 100 rows of keyword or subreddit feeds into Google Sheets cost?

Subreddit feeds (/v1/subreddit/posts) and keyword searches (/v1/reddit/search) cost 1 Credit per returned post ($0.10 per 1,000 posts PAYG, minimum 2 Credits per non-empty call; $0.0398/1K on Business with +65% bonus). Empty keyword queries cost 0 Credits ($0.00), and single subreddit/user entity lookups cost 2 Credits ($0.20/1K).