How to prospect with a connected spreadsheet (Google Sheets and the LeadOcean API)

A connected spreadsheet is a sheet that calls a data API and fills its own rows. You write one Apps Script, add a menu, and click. No add-on, no sync tool, no CSV round trip.

TL;DR

• Time: about 20 minutes to set up. A pull runs in seconds.
• Records: 25 for a first test pull, then 1 per enriched row. Sizing the list first is free.
• Result: a sheet menu with three items: Size list, Pull leads, Enrich selected row.

Prerequisites

• A Google account and a blank Google Sheet.
• A LeadOcean API key from app.leadocean.io/register. The free plan has 1,000 records for life, no card, 5 requests a second.
• The key stored as a Script Property named LEADOCEAN_API_KEY (step 1). In the shell examples below it is $LEADOCEAN_API_KEY.
• curl for the one-line test in step 2 (optional).

Step 1: Add the script and store the key

Open Extensions, then Apps Script. Apps Script can store secrets in Script Properties, which are not visible in the sheet (Google, September 2026). Run saveKey once, then delete the key text from the function.

function saveKey() {
  PropertiesService.getScriptProperties()
    .setProperty('LEADOCEAN_API_KEY', 'PASTE_KEY_HERE');
}

function call_(path, body, query) {
  var url = 'https://api.leadocean.io' + path + (query || '');
  var res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': PropertiesService.getScriptProperties()
      .getProperty('LEADOCEAN_API_KEY') },
    payload: JSON.stringify(body),
    muteHttpExceptions: true
  });
  var code = res.getResponseCode();
  if (code >= 300) throw new Error(code + ': ' + res.getContentText());
  return JSON.parse(res.getContentText());
}

UrlFetchApp.fetch takes headers, a JSON payload and muteHttpExceptions, so a 402 or 429 reaches your code as text instead of a stack trace (Google, September 2026).

Step 2: Size the list before you spend anything

Sizing is free. Send count=true as a query parameter, not in the body, with limit 1. meta.total is capped at 100,000 and the data array comes back empty.

curl -s -X POST "https://api.leadocean.io/v1/people/search?count=true" \
  -H "x-api-key: $LEADOCEAN_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"title":["Head of Sales","VP Sales"],"country":["US"],"employeeRange":["51-200"],"limit":1}'

Put the same filters in a shared constant so Size and Pull never drift apart. Filters are arrays of strings, and the body is flat JSON.

var FILTERS = {
  title: ['Head of Sales', 'VP Sales'],
  country: ['US'],
  employeeRange: ['51-200']
};

function sizeList() {
  var body = JSON.parse(JSON.stringify(FILTERS));
  body.limit = 1;
  var r = call_('/v1/people/search', body, '?count=true');
  SpreadsheetApp.getUi().alert('Matches (capped at 100,000): ' + r.meta.total);
}

Step 3: Pull rows into the sheet

Cursor pagination pages through results, 1 to 100 per page. Each person returned is one record, so start with limit: 25. One search pages to 10,000 rows. When meta.depthCapped is true, narrow the filters instead of walking further.

The script writes four columns: the person_id each row carries at meta.source_ids.person_id, the name, the email status, and the raw row as JSON for anything else.

function pullLeads() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var props = PropertiesService.getScriptProperties();
  var body = JSON.parse(JSON.stringify(FILTERS));
  body.limit = 25;
  var cursor = props.getProperty('CURSOR');
  if (cursor) body.cursor = cursor;
  var r = call_('/v1/people/search', body);
  var rows = r.data || [];
  if (!rows.length) { SpreadsheetApp.getUi().alert('No rows.'); return; }
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['person_id', 'name', 'email_status', 'raw']);
  }
  var out = rows.map(function (p) {
    return [
      p.meta.source_ids.person_id,
      (p.profile_data || {}).profile_full_name || '',
      (p.contact_data || {}).email_status || '',
      JSON.stringify(p)
    ];
  });
  sheet.getRange(sheet.getLastRow() + 1, 1, out.length, 4).setValues(out);
  props.setProperty('CURSOR', r.meta.nextCursor || '');
}

Each click pulls the next page, because the script saves meta.nextCursor and sends it back as cursor. Send it back unchanged: an edited cursor is refused with a 400. When it comes back empty, the walk is over. Delete the CURSOR property to start again.

Step 4: Enrich one row on demand

Enrichment takes exactly one of linkedin_url, email, phone or person_id. Add reveal_email: true to get the address back. There is no name plus domain lookup, so enrich by the person_id from your search pull. An enrichment that finds nothing is a 404 and costs nothing.

function enrichSelected() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var idCol = header.indexOf('person_id') + 1;
  if (!idCol) throw new Error('No person_id column in row 1.');
  var id = sheet.getRange(row, idCol).getValue();
  var r = call_('/v1/people/enrich', { person_id: id, reveal_email: true });
  sheet.getRange(row, sheet.getLastColumn() + 1).setValue(JSON.stringify(r));
}

Career history (past roles, education, certifications) now comes back on enrich too, so the cell holds more than contact data.

Step 5: Add the menu

The menu is what makes the sheet usable by someone who never opens the script editor.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('LeadOcean')
    .addItem('Size list (free)', 'sizeList')
    .addItem('Pull leads', 'pullLeads')
    .addItem('Enrich selected row', 'enrichSelected')
    .addToUi();
}

Reload the sheet. Apps Script's Ui class adds custom menus, and the first run asks you to authorize external requests (Google, September 2026).

What you get

This is a real free count run on 21 October 2026 (leadocean_count_leads, title "Head of Sales" or "VP Sales", country US, 51-200 employees, no records spent). It counts only mailable addresses (verified, catch_all_valid or catch_all) by default:

{
  "total": 2639,
  "totalIsExact": true,
  "totalAll": 8926,
  "distinctCompanies": 2253,
  "credits": 0
}

totalAll is the same filters with no email condition. A search record has the shape documented in the API reference, with placeholder values here:

{
  "success": true,
  "data": [
    {
      "profile_data": { "profile_full_name": "Jane Doe" },
      "contact_data": { "has_email": true, "email_status": "verified" },
      "resume_data": {},
      "meta": { "source_ids": { "person_id": "1234567890" } }
    }
  ],
  "meta": { "count": 1, "limit": 25, "nextCursor": "..." }
}

That is the response shape from the API reference, simplified. The raw column keeps every field the live response carries.

Keep the pulls cheap

Treat each click as a purchase. Size first, pull 25, read the rows, then widen. If the 25 rows look wrong, change FILTERS and size again. That loop costs nothing until you pull.

Two habits help. Keep one tab per filter set, so a pull never mixes audiences. And read the email status before you send anything, because bounced emails are not refunded.

Troubleshooting

FAQ