Classifying a list of emails in Google Sheets

To classify a list of emails in Google Sheets, put the addresses in column A, add a short Apps Script that calls a domain lookup API once per unique domain, and write the category and a keep/review/drop action into the next columns. Run it from a custom menu, not as a cell formula, so you can throttle requests and stay inside Apps Script's time limits. The full script is below and works on a free Google account.

This is the setup we'd hand to an ops person who has a few thousand webinar signups or event leads and needs to know which ones are real companies, which are Gmail, and which are throwaway inboxes.

What the script produces

Given a sheet with emails in column A (header in row 1), the script fills three columns:

The rules for action are simple and easy to change: drop anything where verdict.is_spam is true or the input is invalid, mark isp for review (those are real people, but rarely business leads; see why comcast.net is not a company), keep everything else.

Why a menu script and not a custom function

The tempting version is a custom function, =PINGUTIL(A2), filled down the column. Google's own docs explain why that goes badly:

• A custom function call must return within 30 seconds, and each cell is a separate call.
• Sheets evaluates those cells in parallel. You can't put a delay between them, so 500 rows means 500 requests at once against a key that allows 60 per minute.
• Custom functions recalculate when their inputs change, so editing the sheet re-spends your quota.

A function run from a custom menu is an ordinary script execution. It gets the full 6 minute runtime, it can sleep between requests, and it writes values that don't change until you run it again.

Step 1: store the API key in Script Properties

Get a free key from the pingutil dashboard (it starts with pk_live_). Don't paste it into the code.

1. In the sheet, open Extensions > Apps Script.
2. Click Project Settings (the gear icon).
3. Under Script Properties, add a property named PINGUTIL_KEY with your key as the value, then Save script properties.

The script reads it with PropertiesService.getScriptProperties(). This keeps the key out of the source and out of version history if you copy the script around. It does not make it secret from your collaborators: anyone who can edit the spreadsheet can open the bound script project. If that matters, create a dedicated key for this sheet so you can revoke it without touching production.

Step 2: paste the script

Replace the contents of Code.gs with this:

const API_URL = 'https://pingutil.com/v1/lookup';
const MIN_INTERVAL_MS = 1100;        // ~55 requests/min, under the 60/min key limit
const TIME_BUDGET_MS = 5 * 60 * 1000; // stop before the 6 min execution limit
const CACHE_SHEET = '_pingutil_cache';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('pingutil')
    .addItem('Classify emails in column A', 'classifyEmails')
    .addToUi();
}

function classifyEmails() {
  const started = Date.now();
  const key = PropertiesService.getScriptProperties().getProperty('PINGUTIL_KEY');
  if (!key) throw new Error('Set PINGUTIL_KEY in Project Settings > Script Properties');

  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  sheet.getRange(1, 2, 1, 3).setValues([['category', 'reason', 'action']]);
  const emails = sheet.getRange(2, 1, lastRow - 1, 1).getValues().map(r => String(r[0]).trim());
  const out = sheet.getRange(2, 2, lastRow - 1, 3).getValues();
  const cache = loadCache();

  let lastCall = 0;
  let pending = 0;

  for (let i = 0; i < emails.length; i++) {
    if (out[i][0]) continue; // already classified on a previous run
    const domain = domainOf(emails[i]);
    if (!domain) { out[i] = ['unknown', 'invalid', 'drop']; continue; }

    if (!cache.has(domain)) {
      if (Date.now() - started > TIME_BUDGET_MS) { pending++; continue; }
      const wait = MIN_INTERVAL_MS - (Date.now() - lastCall);
      if (wait > 0) Utilities.sleep(wait);
      lastCall = Date.now();
      const result = lookupDomain(key, domain, started);
      if (!result) { pending++; continue; }
      cache.set(domain, result);
      appendCache(domain, result);
    }

    const r = cache.get(domain);
    out[i] = [r.category, r.reason, actionFor(r)];
  }

  sheet.getRange(2, 2, out.length, 3).setValues(out);
  SpreadsheetApp.getActive().toast(
    pending ? pending + ' rows left. Run the menu item again to continue.' : 'All rows classified.',
    'pingutil'
  );
}

// Returns {category, reason, is_spam}, or null if we should stop and resume later.
function lookupDomain(key, domain, started) {
  for (let attempt = 0; attempt < 3; attempt++) {
    const res = UrlFetchApp.fetch(API_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + key },
      payload: JSON.stringify({ input: domain, skip_enrichment: true }),
      muteHttpExceptions: true,
    });
    const code = res.getResponseCode();
    const body = JSON.parse(res.getContentText() || '{}');

    if (code === 200) {
      return { category: body.category, reason: body.verdict.reason || '', is_spam: body.verdict.is_spam };
    }
    if (code === 400) return { category: 'unknown', reason: 'invalid', is_spam: false };
    if (code === 401) throw new Error('pingutil rejected the API key (401). Check PINGUTIL_KEY.');
    if (code === 429) {
      const waitMs = (body.retry_after_seconds || 60) * 1000;
      if (Date.now() - started + waitMs > TIME_BUDGET_MS) return null;
      Utilities.sleep(waitMs);
      continue;
    }
    Utilities.sleep(2000 * (attempt + 1)); // 5xx: short backoff, then retry
  }
  return null;
}

function domainOf(value) {
  const at = value.lastIndexOf('@');
  if (at < 1 || at === value.length - 1) return null;
  return value.slice(at + 1).toLowerCase();
}

function actionFor(r) {
  if (r.is_spam || r.reason === 'invalid') return 'drop';
  if (r.category === 'isp' || r.category === 'unknown') return 'review';
  return 'keep';
}

function loadCache() {
  const ss = SpreadsheetApp.getActive();
  let sh = ss.getSheetByName(CACHE_SHEET);
  if (!sh) {
    sh = ss.insertSheet(CACHE_SHEET);
    sh.appendRow(['domain', 'category', 'reason', 'is_spam']);
    sh.hideSheet();
  }
  const map = new Map();
  const rows = sh.getDataRange().getValues().slice(1);
  rows.forEach(r => map.set(r[0], { category: r[1], reason: r[2], is_spam: r[3] === true }));
  return map;
}

function appendCache(domain, r) {
  SpreadsheetApp.getActive().getSheetByName(CACHE_SHEET)
    .appendRow([domain, r.category, r.reason, r.is_spam]);
}

Save, reload the spreadsheet, and a pingutil menu appears. The first run asks you to authorize the script (it needs to reach an external URL and edit the sheet).

How the script stays inside the limits

One request per domain. pingutil's category and disposable verdict are properties of the registrable domain, so jane@acme.com and bob@acme.com get the same answer. The script sends the bare domain and caches the result in a hidden _pingutil_cache sheet. Lists with lots of webmail or many contacts per company repeat domains heavily, so the number of API calls is often well below the row count.

The local dedupe is by full host, and pingutil collapses subdomains server side (mail.acme.com becomes acme.com, see eTLD+1 explained). That means two hosts on the same company might cost two calls. Harmless, just slightly wasteful.

Throttled to about 55 per minute. MIN_INTERVAL_MS spaces calls 1.1 seconds apart, which keeps a single run under the 60 requests/minute key limit with a little room for anything else using the same key.

429 handling. If the API still returns 429 rate_limited, the response body carries retry_after_seconds. The script sleeps that long and retries, unless the wait would push it past the time budget, in which case it stops cleanly.

Resumable. Apps Script kills any execution at 6 minutes. The script stops itself at 5, writes what it has, and tells you how many rows are left. Rows with a category are skipped on the next run, and cached domains cost nothing. At roughly 55 requests per minute, expect around 270 new domains per run.

Fast mode. The request sets skip_enrichment: true, which returns category and verdict without the LLM company enrichment. That's what you want for list cleaning. If you also need company names and industries, drop that flag and read body.company, but expect first-seen domains to take a few seconds each.

Daily quotas also apply on Google's side. Apps Script allows 20,000 URL Fetch calls per day on consumer accounts and 100,000 on Workspace accounts, per the quotas page. The pingutil rate limit will bind long before that.

What this does not catch

Sending the bare domain gives you the domain verdict only. pingutil's email_signals (randomized local parts like ccjxx90sze@, role addresses like info@) need the full address, which means one request per row instead of per domain. If you need those, change payload to send emails[i] and key the cache on the full address, and budget roughly a minute per 55 rows. The role-based address guide covers whether flagging info@ is worth it for your list.

It also doesn't verify the mailbox exists. A company result means the domain isn't on a free, disposable or ISP list, not that jane@ will receive mail. For that distinction, see email validation vs verification.

Blocklists lag. A disposable domain registered last week may show up as company until the community list picks it up. Treat keep as "not obviously bad", not "verified".

Next step

Try a few addresses in the live demo to see what each category looks like, then run the script on a copy of your sheet first. Once the columns are filled, a filter on action = keep and a pivot on category give you the breakdown most people are after. If the list is too big for Sheets, the same logic as a standalone CSV script runs from your terminal without the 6 minute ceiling.