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