// automation and data tools
Via APIGoogle Sheets email verification with pingutil
Lead lists, webinar exports and event signups often live in a sheet before they go anywhere else, and a sheet will happily hold a thousand temp-mail and gibberish addresses. An Apps Script custom function that calls pingutil puts a category and a keep or drop verdict next to each row, and a menu-run script handles mailbox verification for longer lists.
Where it connects: Apps Script custom function using UrlFetchApp (plus a menu-run script for long lists)
What goes wrong without it
- Someone hands me a 2,000-row sheet of event leads and asks which ones are real companies.
- My =VERIFY() formula worked for ten rows and then every cell turned into #ERROR.
- I do not want to paste our API key into a script every collaborator can read.
What you can do with Google Sheets and pingutil
Tag a short list inline
Use =PINGUTIL_CLASSIFY(A2:A40) on a small range to get category and verdict reason per row without leaving the sheet.
Clean a long list from a menu
For hundreds of rows, run a menu script that throttles to about 55 requests a minute and resumes where it stopped; the full version is in our Google Sheets tutorial.
Verify mailboxes before an import
A menu script can send up to 50 addresses per call to /v1/verify/batch and write status and reason columns before you upload the sheet to your email tool.
Set it up
- 1
Open Extensions > Apps Script in the sheet.
- 2
In Project Settings add a Script Property named PINGUTIL_KEY with your pingutil API key.
- 3
Paste the custom function below into Code.gs and save.
- 4
In the sheet, enter =PINGUTIL_CLASSIFY(A2:A40) in an empty column; it fills category and reason for each row.
- 5
Once values look right, copy and paste them as values so the sheet does not recalculate and re-spend your lookups.
- 6
For lists longer than a few dozen rows, switch to a menu-run script that can sleep between calls.
/**
* Classifies email addresses with pingutil (fast mode, no mailbox check).
* @param {A2:A40} range Cells containing email addresses.
* @return Category and verdict reason for each row.
* @customfunction
*/
function PINGUTIL_CLASSIFY(range) {
const key = PropertiesService.getScriptProperties().getProperty('PINGUTIL_KEY');
if (!key) return [['set PINGUTIL_KEY', '']];
const rows = Array.isArray(range) ? range : [[range]];
if (rows.length > 40) return [['use the menu script for more than 40 rows', '']];
const started = Date.now();
return rows.map(function (row) {
const email = String(row[0] || '').trim();
if (!email) return ['', ''];
if (Date.now() - started > 25000) return ['not checked (time limit)', ''];
try {
const res = UrlFetchApp.fetch('https://pingutil.com/v1/lookup', {
method: 'post',
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + key },
payload: JSON.stringify({ input: email, skip_enrichment: true }),
muteHttpExceptions: true,
});
const body = JSON.parse(res.getContentText() || '{}');
if (res.getResponseCode() !== 200) return ['error', body.error || String(res.getResponseCode())];
return [body.category, body.verdict.is_spam ? 'drop: ' + body.verdict.reason : (body.verdict.reason || 'keep')];
} catch (e) {
return ['error', 'network'];
}
});
}What to do with each result
| pingutil result | Action in Google Sheets |
|---|---|
| verdict.is_spam = true | Write "drop" plus the reason (disposable_domain, randomized_local_part) |
| category = "company" | Keep, and filter on it for the sales hand-off |
| category = "free" or "isp" | Keep as a consumer contact, or mark for review on B2B lists |
| status = "invalid" (menu script with /v1/verify/batch) | Delete the row or move it to an Invalid tab before importing |
Limits to plan for
- A custom function call must return within 30 seconds, and cells recalculate, so keep ranges small and paste results as values.
- pingutil allows 60 requests a minute and 2,000 lookups and 300 verified addresses a day (30,000 and 3,000 a month) per account; a column of single-cell formulas will burst past the per-minute limit. Those are Free plan limits; Enterprise has custom limits.
- Apps Script has daily UrlFetch quotas, and anyone who can edit the spreadsheet can open the bound script and its properties, so use a dedicated key.
Google Sheets documentation
- developers.google.com/apps-script/guides/sheets/functions
- developers.google.com/apps-script/guides/services/quotas
Google Sheets is a trademark of its owner. This integration uses Google Sheets's documented extension points; it is not an official Google Sheets app.
Common questions
- Can the custom function check whether the mailbox exists?
- Not reliably. A mailbox check can take up to about 25 seconds per address, which collides with the 30 second custom function limit. Run verification from a menu script with /v1/verify/batch instead.
- Why does the function take a range instead of one cell?
- Google recommends it: one call fills many cells, so Sheets makes fewer parallel executions and you stay closer to the API rate limit.
Stop bad addresses in Google Sheets today
Sign in with your email, create an API key and paste it into the step above. Test on a few real addresses first: the live demo shows exactly what your Google Sheets setup will receive.