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.
Compliance basics (not legal advice)
- Public business contacts only. Keep the source website column so you can show where a contact came from.
- GDPR: a named person's work email is personal data. Document a lawful basis and honour objections.
- CAN-SPAM: truthful sender and subject, a postal address, and a working opt-out (FTC guide).
- Check do-not-call registries before phoning.
- No spam: send a few relevant messages, and stop when someone asks.
Related guides
- Zapier: enrich website leads
- Make.com: enrich website leads
- n8n: Sheet websites to contacts
- Clay: emails and phones from a website column
- Find emails from a list of websites
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 |