Key Takeaways

  • Google Apps Script lets you verify emails directly inside Google Sheets, so you clean a list where it already lives without exporting a CSV.
  • A custom VERIFYEMAIL function checks a single cell, which is convenient but fires one API call per cell and is best for small sheets.
  • For real lists, a menu-driven batch run with UrlFetchApp verifies all rows, writes results back per row, and handles rate limits and caching.
  • Store the API key in Script Properties, never in the sheet or the code, and throttle requests so a large sheet does not trip rate limits.

A huge amount of real-world email data lives in spreadsheets: lead lists from a trade show, exports from a CRM, signup forms piped into a sheet. Cleaning that data usually means exporting a CSV, running it through a tool, and importing it back, which is friction nobody enjoys. Verifying emails in Google Sheets with Apps Script removes that friction by cleaning the list where it already lives. This guide builds two things: a custom VERIFYEMAIL function for quick checks and a menu-driven batch cleaner for real lists, both calling the v2 API. For other environments, see the email verification integrations hub.

Apps Script is JavaScript that runs inside Google Workspace, and it can make outbound HTTP calls with UrlFetchApp, which is all we need to reach the verification API.

Store the API Key Safely First

Before any code, put your API key in Script Properties, not in a cell and not hardcoded in the script. Open Extensions, then Apps Script, then Project Settings, and add a script property named EMAILVERIFIER_API_KEY. This keeps the key out of the spreadsheet, so sharing the sheet never leaks it.

Important Never put your API key in a spreadsheet cell or paste it directly into the script body. Anyone you share the sheet with can read both. Script Properties keeps the key server-side within your Apps Script project, where collaborators cannot see it.

The Custom VERIFYEMAIL Function

A custom function lets you type =VERIFYEMAIL(A2) in a cell and get a status back. It is the most convenient option for small sheets, with one caveat we will cover: it fires a call per cell.

/**
 * Verify one email address.
 * @param {string} email The address to check.
 * @return {string} The verification status.
 * @customfunction
 */
function VERIFYEMAIL(email) {
  if (!email) return "";
  const key = PropertiesService.getScriptProperties()
    .getProperty("EMAILVERIFIER_API_KEY");

  // Cache results so re-calculation does not re-bill the same address
  const cache = CacheService.getScriptCache();
  const hit = cache.get(email);
  if (hit) return hit;

  const url = "https://emailverifierapi.com/v2/verify"
    + "?api_key=" + encodeURIComponent(key)
    + "&email=" + encodeURIComponent(email);

  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() !== 200) return "error";

  const data = JSON.parse(res.getContentText());
  const label = data.isDisposable ? "disposable"
    : data.isRoleAccount ? "role"
    : data.status;                    // passed / failed / unknown

  cache.put(email, label, 21600);     // cache 6 hours
  return label;
}

The CacheService call is what makes this usable. Google recalculates custom functions often, and without caching every recalculation would fire a fresh API call and burn credits on addresses you already checked. Caching the result for six hours means each address costs one call, not one per recalculation.

Cache every result, or Google will re-bill the same address on every recalculation. Source: Apps Script custom function behavior, 2026

The Menu-Driven Batch Cleaner

For a real list of hundreds or thousands of rows, a custom function per cell is slow and fragile. A batch run is better: add a menu, verify every row in one pass, and write the results back. The onOpen trigger adds the menu, and the batch function does the work with throttling.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Verify")
    .addItem("Verify column A", "verifyColumnA")
    .addToUi();
}

function verifyColumnA() {
  const key = PropertiesService.getScriptProperties()
    .getProperty("EMAILVERIFIER_API_KEY");
  const sheet = SpreadsheetApp.getActiveSheet();
  const last = sheet.getLastRow();
  const emails = sheet.getRange(2, 1, last - 1, 1).getValues();

  const out = [];
  for (let i = 0; i < emails.length; i++) {
    const email = String(emails[i][0]).trim();
    if (!email) { out.push([""]); continue; }

    const url = "https://emailverifierapi.com/v2/verify"
      + "?api_key=" + encodeURIComponent(key)
      + "&email=" + encodeURIComponent(email);
    const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

    if (res.getResponseCode() === 429) {   // rate limited: wait, retry once
      Utilities.sleep(2000);
      out.push([verifyOne(url)]);
    } else if (res.getResponseCode() === 200) {
      const d = JSON.parse(res.getContentText());
      out.push([d.status]);
    } else {
      out.push(["error"]);
    }
    Utilities.sleep(150);                  // gentle throttle between calls
  }
  // Write all results back to column B in one operation
  sheet.getRange(2, 2, out.length, 1).setValues(out);
}

function verifyOne(url) {
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  return res.getResponseCode() === 200
    ? JSON.parse(res.getContentText()).status : "error";
}

Three choices make this production-ready. It reads all emails in one getValues call and writes results in one setValues call, which is far faster than touching cells individually. It handles a 429 rate-limit response by waiting and retrying. And the small sleep between calls keeps a large sheet from tripping limits. Apps Script has a six-minute execution cap, so for very large sheets, process in chunks across multiple runs rather than one long pass.

Pro Tip For sheets bigger than a few thousand rows, store a cursor in Script Properties marking the last verified row, and run the batch in chunks under the six-minute limit. A time-driven trigger can then clean a very large sheet automatically over several runs.

From Sheet to Clean List

Once the status column is populated, filter the sheet by result: keep the passed rows, delete or suppress the failed ones, and review the risky and role-labeled rows. You now have a cleaned list in the same place you started, ready to import into your email platform.

New developers can get an API key and test both patterns on a real sheet with 100 free email verification credits, and the email verification API documentation covers the full response schema and rate limits so you can extend the script to surface disposable, role, and catch-all flags per row.

Frequently Asked Questions

Can I verify emails directly in Google Sheets?

Yes, with Google Apps Script. You can build a custom VERIFYEMAIL function that checks a single cell, or a menu-driven batch script that verifies an entire column and writes the results back. Both call the verification API with UrlFetchApp, so you clean the list where it lives without exporting a CSV.

Why should I cache results in the custom function?

Because Google recalculates custom functions frequently, and without caching, every recalculation fires a fresh API call and bills you again for addresses you already checked. Using CacheService to store each result for a few hours means every address costs a single call rather than one per recalculation.

How do I handle large sheets within the Apps Script time limit?

Apps Script caps execution at about six minutes, so for large sheets process in chunks. Store a cursor in Script Properties marking the last verified row, verify a batch under the limit, and use a time-driven trigger to continue across multiple runs until the whole sheet is clean.

Where should I store my API key in Apps Script?

In Script Properties, accessed with PropertiesService, never in a spreadsheet cell or hardcoded in the script body. Script Properties keeps the key inside your Apps Script project where collaborators who can see the sheet cannot read it, which protects the key when you share the spreadsheet.