How to verify emails in Google Sheets: three methods compared

You can verify emails in Google Sheets in three ways: a custom function written in Apps Script that calls a verification API from a cell, a Zapier or Make scenario that verifies each new row, or an export to a bulk verifier and an import of the results. For a few hundred addresses the Apps Script function is the quickest, with a formula like =VERIFYRE(A2) returning deliverable, undeliverable, risky or unknown right in the sheet.

Which method should you use?

The rule of thumb: under about 2,000 rows, use the function. Rows arriving one at a time, use the automation. Anything larger or a one-off clean, use a bulk job.

How do you set up the Apps Script function?

1. Get an API key. Sign up, which includes 100 free verifications, and copy a key from the dashboard. Keys start with vfy_live_.
2. Open the script editor. In your sheet: Extensions > Apps Script. Delete the placeholder code.
3. Store the key in Script Properties. In the editor, Project Settings (the gear icon) > Script Properties > Add script property. Name VERIFYRE_KEY, value your key. Keeping it here rather than in the code means it never ends up in a shared copy of the sheet.
4. Paste the function. A compact version that handles single cells, ranges, caching and an optional field argument:

/**
 * Verify an email address with Verifyre.
 * @param {string|string[][]} input A cell or range of addresses.
 * @param {string} field Optional: "status" (default), "score", "reason", "did_you_mean" or "provider".
 * @return The requested field for each address.
 * @customfunction
 */
function VERIFYRE(input, field) {
  field = (field || "status").toLowerCase();
  if (Array.isArray(input)) {
    return input.map(function (row) {
      return row.map(function (cell) { return verifyOne_(cell, field); });
    });
  }
  return verifyOne_(input, field);
}

function verifyOne_(email, field) {
  email = String(email || "").trim().toLowerCase();
  if (!email) return "";
  var cache = CacheService.getScriptCache();
  var cacheKey = "vfy:" + email;
  var hit = cache.get(cacheKey);
  var data = hit ? JSON.parse(hit) : null;
  if (!data) {
    var key = PropertiesService.getScriptProperties().getProperty("VERIFYRE_KEY");
    if (!key) return "missing VERIFYRE_KEY";
    var res = UrlFetchApp.fetch(
      "https://verifyre.co/api/v1/verify?email=" + encodeURIComponent(email),
      { headers: { Authorization: "Bearer " + key }, muteHttpExceptions: true }
    );
    var code = res.getResponseCode();
    if (code === 402) return "out of credits";
    if (code === 429) return "rate limited, retry";
    if (code !== 200) return "error " + code;
    data = JSON.parse(res.getContentText());
    cache.put(cacheKey, JSON.stringify(data), 21600); // 6 hours
  }
  var value = data[field];
  return value === undefined || value === null ? "" : value;
}

1. Save and authorise. Click Save, then use the function in a cell; Sheets will ask you to authorise the script to connect to an external service. Grant it.
2. Use it in the sheet. Put an address in A2 and =VERIFYRE(A2) in B2.

The cache is the important detail. Sheets recalculates custom functions more often than you expect (on every edit to a referenced cell, on reopen, sometimes on a timer), and without the 6 hour CacheService entry every recalculation would call the API again. With it, a sheet of 1,000 addresses costs 1,000 credits the first time and nothing on recalculation. On Verifyre's side, repeat checks of the same address within 24 hours are also free and return cached: true, so even a cache miss after 6 hours is not billed.

What formulas are useful once the function exists?

=VERIFYRE(A2)                      status: deliverable, undeliverable, risky, unknown
=VERIFYRE(A2, "score")             0 to 100
=VERIFYRE(A2, "reason")            short reason code
=VERIFYRE(A2, "did_you_mean")      suggested correction for typos, blank if none
=VERIFYRE(A2:A500)                 a whole column in one call, fills down automatically
=IF(VERIFYRE(A2, "score") >= 90, "send", "hold")
=IF(VERIFYRE(A2, "did_you_mean") <> "", VERIFYRE(A2, "did_you_mean"), A2)
=COUNTIF(B2:B500, "deliverable")
=FILTER(A2:C500, B2:B500 = "undeliverable")

The range form =VERIFYRE(A2:A500) is better than filling a single-cell formula down, because Sheets makes one script call instead of 499, and the whole batch finishes well inside the 30 second limit at typical speeds. For more than roughly 500 rows, split the range into blocks of 300 to 500 so no single call risks the limit.

Conditional formatting makes the result readable at a glance: select column B, Format > Conditional formatting, text is exactly "undeliverable" fills red, "risky" fills amber, "deliverable" fills green. A FILTER in a second tab gives you an export-ready suppression list that updates itself.

When should you switch to a bulk job?

The custom function is a convenience, not a pipeline. Switch to a bulk job when:

• The sheet has more than a few thousand rows. The daily URL fetch quota and the per-call time limit start to bite, and a bulk job finishes the same work in minutes.
• You need the full response (every check, the MX records, the SMTP reason text) rather than one field per cell.
• You want a results file you can import into an ESP or CRM in one go.

Export the column as CSV, upload it to the bulk email verifier, and import the results sheet back; jobs take up to 50,000 addresses. For a sheet that receives new rows all day from a form, an automation is the better fit, and verifying emails with Zapier or Make walks through that. Developers who want the same function inside a larger system should look at the email verification API directly, or the ready-made Google Sheets integration, which installs the function without pasting code.

Key takeaways

• Three routes: Apps Script function for small lists, Zapier or Make for rows arriving one at a time, bulk jobs for thousands.
• Keep the API key in Script Properties, never in the code or a cell.
• Cache results for 6 hours or every recalculation will call the API again.
• Use the range form on blocks of 300 to 500 rows rather than filling a single-cell formula down.
• Above a few thousand rows, export and run a bulk job.

Frequently asked questions

Does the function cost credits every time the sheet recalculates?

Not with the cache in place. Results are held for 6 hours in CacheService, and Verifyre does not charge for repeat checks of the same address within 24 hours, so recalculation is free.

Will the people in the sheet receive an email?

No. Verification is an SMTP conversation that stops before any message is sent, so the mailbox owner sees nothing.

Why do some cells show "Loading..." for a long time?

Each custom function call has a 30 second limit, and a few hundred addresses on slow or greylisting servers can approach it. Split large ranges into blocks of 300 to 500 rows, or move to a bulk job.

Can I share the sheet without sharing my API key?

Yes, if the key is in Script Properties. Anyone with edit access to the script can still read it, so share the sheet as view-only or give collaborators their own key.