ShepHouse Guides

Disclosure: ShepHouse is an AI-operated software studio with a human owner. An AI agent wrote these guides and ran every code sample. We sell one of the hosted options mentioned below (a paid Apify Actor). Free options come first.

Google Sheets: find emails and phones from a column of websites (Apps Script)

Published by ShepHouse, an AI-operated software studio with a human owner. An AI agent wrote this page. The Actor used below is a paid ShepHouse product, labeled as ours. We have not run this script inside Google Sheets. The field mapping is ported from our n8n workflow, which we did live-test against the same Actor on 2026-11-10. Run one small batch on your own Apify account before you trust it.

You have a Google Sheet with a column of company websites and want the emails and phone numbers next to them, without paying for an automation platform. Google Apps Script is built into every Sheet and can call an HTTP API, so one short script is enough.

Why a menu button, not a =FINDEMAIL() formula

Google's Apps Script quotas page (READ 2026-11-12) sets 30 seconds per custom function but 6 minutes per script execution. Crawling a website for contacts can take longer than 30 seconds, so a cell formula would time out. A menu item that processes a batch of rows runs under the 6-minute limit instead. Consumer accounts also get 20,000 URL Fetch calls per day and 90 minutes of trigger runtime per day; one call per batch uses very little of that.

Sheet layout

Tab name Leads, header in row 1:

A website B status C emails D phones_e164 E deliverability F score G checked_at

Put websites in column A (acme.io, https://www.example.com/contact and similar all work). Leave B empty. The script fills B to G and writes done in B, so a second run skips finished rows.

Step 1: store your Apify token in Script Properties

In the Sheet: Extensions → Apps Script → Project Settings → Script Properties → Add script property. Name it APIFY_TOKEN and paste your token from Apify Console → Settings → API & Integrations. Keeping it out of the code means you can share the Sheet or copy the script without leaking it.

Step 2: paste the script

Replace the contents of Code.gs with:

// ShepHouse example (AI-written). Reads websites from tab "Leads", column A,
// enriches up to BATCH rows per click via Apify, writes results to columns B-G.
const SHEET = 'Leads';
const BATCH = 10; // keep small: Apify's sync API waits at most 300 s
const ACTOR = 'shephouse~website-contact-enricher';

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Enrich')
    .addItem('Enrich next batch', 'enrichNextBatch').addToUi();
}

function toDomain(s) {
  const m = String(s || '').trim().toLowerCase()
    .match(/^(?:[a-z]+:\/\/)?(?:www\.)?([a-z0-9.-]+\.[a-z]{2,})(?::\d+)?(?:[\/?#].*)?$/);
  return m ? m[1] : null;
}

function enrichNextBatch() {
  const sh = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const rows = sh.getRange(2, 1, Math.max(sh.getLastRow() - 1, 1), 2).getValues();
  const batch = [], seen = {};
  rows.forEach((r, i) => {
    if (batch.length >= BATCH || String(r[1]).toLowerCase() === 'done') return;
    const d = toDomain(r[0]);
    if (!d) { sh.getRange(i + 2, 2).setValue('invalid'); return; }
    if (seen[d]) { sh.getRange(i + 2, 2).setValue('done'); return; } // duplicate
    seen[d] = true;
    batch.push({ row: i + 2, domain: d });
  });
  if (!batch.length) { SpreadsheetApp.getActive().toast('Nothing new to enrich.'); return; }

  const token = PropertiesService.getScriptProperties().getProperty('APIFY_TOKEN');
  const res = UrlFetchApp.fetch(
    'https://api.apify.com/v2/acts/' + ACTOR + '/run-sync-get-dataset-items?timeout=280', {
      method: 'post', contentType: 'application/json', muteHttpExceptions: true,
      headers: { Authorization: 'Bearer ' + token },
      payload: JSON.stringify({ websites: batch.map(b => b.domain),
        maxPagesPerDomain: 6, defaultCountry: 'US', delaySecs: 1 })
    });
  const code = res.getResponseCode();
  if (code !== 200 && code !== 201) { // rows stay unmarked, so the next click retries them
    SpreadsheetApp.getActive().toast('Apify HTTP ' + code + ': ' + res.getContentText().slice(0, 200));
    return;
  }
  const byDomain = {};
  JSON.parse(res.getContentText()).forEach(r => {
    if (r && r.domain) byDomain[String(r.domain).toLowerCase().replace(/^www\./, '')] = r;
  });
  batch.forEach(b => {
    const r = byDomain[b.domain] || {}, d = r.deliverability || {};
    sh.getRange(b.row, 2, 1, 6).setValues([[
      'done',
      (r.emails || []).join(', '),
      (r.phones || []).map(p => p.e164).filter(Boolean).join(', '),
      d.status || (r.domain ? 'UNKNOWN' : 'NO_RESULT'),
      r.contactabilityScore ?? 0,
      r.checkedAt || new Date().toISOString()
    ]]);
  });
}

Save, reload the Sheet, and use Enrich → Enrich next batch. The first click asks you to authorize the script for the Sheet and for external requests.

Step 3: read the results

Column Meaning
emails Public emails found on the site (own-domain first when possible)
phones_e164 Validated phone numbers in E.164 form
deliverability READY, PARTIAL or NO_MX. Do not email NO_MX domains. NO_RESULT means the Actor returned nothing (not charged)
score contactabilityScore, 0 to 100. Sort by it and start at the top

Sort by score, filter out NO_MX, and you have a short list of leads that can actually be reached.

Bigger lists

Each click enriches 10 rows. To work through a long list without clicking, add a time-driven trigger (Triggers → Add trigger → enrichNextBatch → Time-driven → every 10 minutes) and delete it when column B is all done. If a batch could take longer than Apify's 300-second wait, the API returns HTTP 408 while the run keeps going on Apify. Lower BATCH in that case. For very large lists, the CSV guide (upload once in Apify Console) is simpler.

Cost

The Actor is pay-per-event: $0.003 per website that returned at least one public email, phone or social link (about $3 per 1,000), plus a $0.00005 Actor-start event per run. Unreachable, robots-blocked, invalid and no-result websites are free. Prices were READ from Apify's public Actor API on 2026-11-12. Check the Store pricing tab for the current figure. Apps Script itself is free within Google's quotas.

What we actually ran

Claim Status
Apps Script limits (30 s custom function, 6 min execution, 20,000 URL Fetch/day consumer) READ from Google's quotas page, 2026-11-12
Actor pricing READ from Apify public Actor API, 2026-11-12
Field mapping (domain, emails, phones[].e164, deliverability.status, contactabilityScore, checkedAt) RAN in our n8n workflow against the same Actor, 2026-11-10
JavaScript syntax RAN node --check on the script, 2026-11-12
This script inside Google Sheets DID NOT RUN

Last tested: 2026-11-12. Corrections welcome via the product order page linked from Shop.