Skip to content
b2bprofileapi

Enrich B2B Company & Executive Data in Google Sheets

Turn any list of corporate domains (stripe.com, openai.com) or LinkedIn company URLs in Google Sheets into verified headcount brackets, industries, headquarters, and decision-maker profiles—at $0.40–$1.00 per 1,000 companies (10 credits/row) and $0.08–$0.20 per 1,000 decision-makers (2 credits/person).

Engineering Guardrail · Why We Never Use Volatile Cell Formulas

Never paste custom cell formulas into paid API spreadsheets: Google Sheets automatically re-evaluates custom functions when tabs reload, rows sort, or filters change—silently burning credits. Our script below installs a top menu (B2BProfile API → Enrich Selected Rows (Skip Filled)), stores your API key in PropertiesService (never in cells), skips rows already marked OK, and caches responses for 6 hours via CacheService.

Extensions → Apps Script · Menu-Driven + 6h CacheService + PropertiesService

Copy or download Code.gs

/**
 * B2BProfile API — Safe Menu-Driven Google Sheets Enrichment (Code.gs)
 *
 * WHY NO CUSTOM FORMULA (=ENRICH_COMPANY)?
 * Google Sheets recalculates custom functions whenever a tab reopens, sorts, or inserts rows,
 * silently re-running paid API calls. This script runs ONLY from the top menu, skips rows
 * already marked "OK", stores your API key safely in PropertiesService, and caches responses
 * for 6 hours in CacheService ($0 repeat lookups).
 *
 * Expected Sheet Columns (Row 1 Header):
 *   Col A: Domain or LinkedIn Company URL (input, e.g. stripe.com)
 *   Col B: Target Role Title (optional input, e.g. VP Engineering — leave blank to skip people search)
 *   Col C: Status ("OK" | "NOT_FOUND_0_CREDITS" | "ERROR")
 *   Col D: Company Name
 *   Col E: LinkedIn Slug
 *   Col F: Industry
 *   Col G: Company Size
 *   Col H: Headquarters
 *   Col I: Top Decision-Maker Name & Title
 *   Col J: Decision-Maker LinkedIn URL
 */

const B2B_API_BASE = 'https://api.b2bprofileapi.com';
const CACHE_TTL_SECONDS = 21600; // 6 hours (Google Apps Script CacheService max TTL)

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('B2BProfile API')
    .addItem('Enrich Selected Rows (Skip Filled)', 'enrichSelectedRowsSkipFilled')
    .addSeparator()
    .addItem('Set API Key (PropertiesService)', 'promptAndSaveApiKey')
    .addItem('Initialize Sheet Headers (Cols A-J)', 'initializeB2bSheetHeaders')
    .addToUi();
}

function promptAndSaveApiKey() {
  const ui = SpreadsheetApp.getUi();
  const res = ui.prompt(
    'B2BProfile API Key',
    'Paste your b2b_... key from https://b2bprofileapi.com/dashboard (stored securely in PropertiesService, never in cells):',
    ui.ButtonSet.OK_CANCEL
  );
  if (res.getSelectedButton() !== ui.Button.OK) return;
  const key = res.getResponseText().trim();
  if (!key) {
    ui.alert('API key was empty — nothing saved.');
    return;
  }
  PropertiesService.getScriptProperties().setProperty('B2BPROFILE_API_KEY', key);
  ui.alert('API key saved securely in Script Properties.');
}

function initializeB2bSheetHeaders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = [[
    'Domain / Company URL',
    'Target Title (Optional)',
    'Status',
    'Company Name',
    'LinkedIn Slug',
    'Industry',
    'Company Size',
    'Headquarters',
    'Decision-Maker (Name — Title)',
    'Decision-Maker LinkedIn URL'
  ]];
  sheet.getRange(1, 1, 1, headers[0].length).setValues(headers).setFontWeight('bold');
}

function cleanDomainOrSlug_(raw) {
  const s = String(raw || '').trim().toLowerCase();
  if (!s) return { param: null, value: '' };
  if (s.indexOf('linkedin.com/company/') !== -1) {
    const slug = s.split('linkedin.com/company/')[1].split(/[/?#]/)[0].trim();
    return { param: 'company', value: slug };
  }
  const stripped = s.replace(/^https?:\/\//, '').replace(/^www\./, '').split(/[/?#]/)[0].trim();
  if (stripped.indexOf('.') !== -1) {
    return { param: 'domain', value: stripped };
  }
  return { param: 'company', value: stripped };
}

function fetchCachedJson_(cacheKey, url, apiKey) {
  const cache = CacheService.getScriptCache();
  const cached = cache.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  const resp = UrlFetchApp.fetch(url, {
    method: 'get',
    muteHttpExceptions: true,
    headers: {
      'X-API-Key': apiKey,
      'Accept': 'application/json'
    }
  });
  const status = resp.getResponseCode();
  const body = JSON.parse(resp.getContentText() || '{}');
  const wrapped = { httpStatus: status, body: body };
  if (status === 200 || status === 404) {
    const serialized = JSON.stringify(wrapped);
    if (serialized.length < 95000) {
      cache.put(cacheKey, serialized, CACHE_TTL_SECONDS);
    }
  }
  return wrapped;
}

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

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const selection = sheet.getActiveRange();
  if (!selection) {
    ui.alert('Select one or more rows in Column A first.');
    return;
  }

  const startRow = Math.max(2, selection.getRow());
  const numRows = selection.getNumRows();
  const range = sheet.getRange(startRow, 1, numRows, 10);
  const values = range.getValues();

  let enrichedCount = 0;
  let skippedCount = 0;

  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const rawTarget = row[0];
    const targetTitle = String(row[1] || '').trim();
    const existingStatus = String(row[2] || '').trim().toUpperCase();

    if (!rawTarget) continue;
    if (existingStatus === 'OK') {
      skippedCount++;
      continue; // Skip already-filled rows so credits are never double-spent
    }

    const parsed = cleanDomainOrSlug_(rawTarget);
    if (!parsed.value) continue;

    try {
      // Step 1: GET /v1/company (10 Credits = $0.0004 Business / $0.001 PAYG; 0 Credits on 404)
      const compUrl = B2B_API_BASE + '/v1/company?' + parsed.param + '=' + encodeURIComponent(parsed.value);
      const compRes = fetchCachedJson_('b2b_comp_' + parsed.value.slice(0, 120), compUrl, apiKey);

      if (compRes.httpStatus === 404) {
        row[2] = 'NOT_FOUND_0_CREDITS';
        continue;
      }
      if (compRes.httpStatus !== 200 || !compRes.body || !compRes.body.data) {
        row[2] = 'HTTP_' + compRes.httpStatus;
        continue;
      }

      const comp = compRes.body.data;
      row[2] = 'OK';
      row[3] = comp.name || '';
      row[4] = comp.slug || '';
      row[5] = comp.industry || '';
      row[6] = comp.company_size || '';
      row[7] = comp.headquarters || '';

      // Optional Step 2: GET /v1/people/search if Column B specifies a target title (2 Credits/person, min 10 on hit, 0 if empty)
      if (targetTitle && comp.slug) {
        const peopleUrl =
          B2B_API_BASE +
          '/v1/people/search?company=' + encodeURIComponent(comp.slug) +
          '&title=' + encodeURIComponent(targetTitle) +
          '&limit=1';
        const cacheId = ('b2b_ppl_' + comp.slug + '_' + targetTitle.toLowerCase()).slice(0, 180);
        const pplRes = fetchCachedJson_(cacheId, peopleUrl, apiKey);
        const lead = pplRes.httpStatus === 200 && pplRes.body?.data?.items?.[0] ? pplRes.body.data.items[0] : null;
        if (lead) {
          row[8] = lead.full_name + (lead.current_title ? ' — ' + lead.current_title : '');
          row[9] = lead.profile_url || '';
        } else {
          row[8] = 'No match (0 credits)';
          row[9] = '';
        }
      }

      enrichedCount++;
      Utilities.sleep(150); // Polite pacing within 30-300 RPM self-serve rate limits
    } catch (err) {
      row[2] = 'ERROR: ' + String(err).slice(0, 80);
    }
  }

  range.setValues(values);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    'Enriched ' + enrichedCount + ' row(s); skipped ' + skippedCount + ' already-filled row(s).',
    'B2BProfile API'
  );
}
Step 01 · Paste Script

Open Extensions → Apps Script

In your Google Sheet, click Extensions → Apps Script, replace the default Code.gs with the script above, press Save, and reload your spreadsheet tab so the B2BProfile API menu appears.

Step 02 · Store Key Safely

Set API Key in Script Properties

Claim 3,000 Free Credits ($0.30) at /signup, then click B2BProfile API → Set API Key. Viewers of your sheet cannot see keys stored in PropertiesService.

Step 03 · Batch Enrich

Select domains & Skip Filled

Put domains in Column A (and an optional target title like VP Sales in Column B), highlight the rows, and click Enrich Selected Rows (Skip Filled).