Build a =VERIFY() Custom Function in Google Sheets With Apps Script

You can verify an email address from inside Google Sheets by writing a short Apps Script that sends the address to an email verification API and returns the verdict. Create a function called VERIFY, store your API key in Script Properties, and a cell like =VERIFY(A2) shows valid, risky or invalid. For a whole column, use a menu script instead of thousands of formula cells, so you stay under the rate limit and the results stick.

This guide builds both, using the SimpleVerifier API. If you only need to clean one list once, the Google Sheets integration or a CSV upload is quicker. The script is for sheets that keep filling up: a lead tracker, a form responses tab, a scraper's output.

Why a formula alone cannot do it

A Sheets formula can test whether text looks like an email address. It cannot tell you whether the mailbox exists, because that needs a conversation with the recipient's mail server, which a spreadsheet cannot open. Apps Script gets around this the only way it can: it sends the address over HTTPS to a service that does the check. We cover the limits of formulas in verify emails in Google Sheets. Here we go one step further and call the API.

What you need

• A SimpleVerifier plan with API access: Pro, Business or Agency. Pro is $39 a month. It includes 10,000 API calls a month, then $1.50 per 1,000 on monthly billing. Pro email verification also runs the first 100,000 checks a month at full speed, then slower, never cut off. See pricing.
• An API key from the API keys page. It starts with sv_live_ and is shown once.
• A Google Sheet with emails in one column, with a header in row 1.

The API is one endpoint: GET or POST https://simpleverifier.com/api/v1/verify with the address as email and the key as a Bearer token. The full reference is in the API docs.

Step 1: Store the key in Script Properties

1. In the sheet, open Extensions, then Apps Script.
2. Open Project Settings (the gear icon).
3. Under Script Properties, add a property named SV_KEY with your key as the value.

Reading the key from a property keeps it out of the cells and out of the code. Anyone who can edit the sheet can open the script, so share edit access carefully. A viewer cannot see the script.

Step 2: The custom function

Replace the contents of Code.gs with this.

const SV_ENDPOINT = "https://simpleverifier.com/api/v1/verify";

/**
 * Verifies an email address with the SimpleVerifier API.
 * @param {string} email The address to check, for example A2.
 * @param {string} field Optional: status (default), reason, action or send_advice.
 * @return {string} The requested field, or a short error message.
 * @customfunction
 */
function VERIFY(email, field) {
  const address = String(email || "").trim().toLowerCase();
  if (!address) return "";
  if (address.indexOf("@") < 1) return "invalid: no @";

  const key = PropertiesService.getScriptProperties().getProperty("SV_KEY");
  if (!key) return "ERROR: add SV_KEY in Script Properties";

  const outcome = svCall_(address, key);
  if (outcome.error) return outcome.error;
  return String(outcome.body[field || "status"] ?? "");
}

function svCall_(address, key) {
  let response;
  try {
    response = UrlFetchApp.fetch(SV_ENDPOINT + "?email=" + encodeURIComponent(address), {
      method: "get",
      headers: { Authorization: "Bearer " + key },
      muteHttpExceptions: true,
    });
  } catch (err) {
    return { error: "ERROR: request failed" };
  }

  const code = response.getResponseCode();
  if (code === 200) return { body: JSON.parse(response.getContentText()) };
  if (code === 401) return { error: "ERROR 401: key missing or revoked" };
  if (code === 402) return { error: "ERROR 402: plan has no API access or quota used", fatal: true };
  if (code === 429) return { error: "ERROR 429: rate limited", retryAfter: retryAfterSeconds_(response) };
  if (code === 503) return { error: "ERROR 503: try again", retry: true };
  return { error: "ERROR " + code };
}

function retryAfterSeconds_(response) {
  const headers = response.getHeaders();
  const name = Object.keys(headers).find((h) => h.toLowerCase() === "retry-after");
  const seconds = name ? Number(headers[name]) : NaN;
  return Number.isFinite(seconds) ? seconds : 30;
}

Save, return to the sheet and type =VERIFY(A2). The first time, Sheets may take several seconds. The second argument picks another field from the reply: =VERIFY(A2, "send_advice") returns safe, care, retry or do_not_send.

What the function can and cannot do

Google's custom functions guide lists the constraints that matter here:

• A custom function must return within 30 seconds. A mailbox check can take up to about 30 seconds on a slow mail server, so an occasional error cell is normal. The API docs suggest a 30 second timeout everywhere else, but inside a cell you do not get that room.
• Custom functions never ask for authorization, so they can only use services that do not touch personal data. UrlFetchApp is on the supported list.
• Each cell is its own call to the Apps Script server, so a few hundred of them is slow.

There is one more cost that the guide cannot warn you about: every run of the function is an API call. If Sheets recalculates the column, you pay for the calls again. Once a column has its results, select it, copy, and use Paste special, then Values only.

Step 3: A menu script for a whole column

For more than a handful of rows, use a script that runs with your authorization, walks the sheet one row at a time and writes plain values. This has three advantages: it can sleep between calls, it can stop and resume, and it does not recalculate.

Add this to the same file.

const SV_EMAIL_COLUMN = 1;        // A
const SV_FIRST_RESULT_COLUMN = 2; // B: status, C: reason, D: action
const SV_PAUSE_MS = 650;          // about 92 calls a minute, under the 120 limit
const SV_RUN_BUDGET_MS = 5 * 60 * 1000; // stop before Google's 6 minute script limit

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("SimpleVerifier")
    .addItem("Verify empty rows", "verifyEmptyRows")
    .addToUi();
}

function verifyEmptyRows() {
  const key = PropertiesService.getScriptProperties().getProperty("SV_KEY");
  if (!key) {
    SpreadsheetApp.getUi().alert("Add SV_KEY in Project Settings, Script Properties.");
    return;
  }

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

  const emails = sheet.getRange(2, SV_EMAIL_COLUMN, lastRow - 1, 1).getValues();
  const done = sheet.getRange(2, SV_FIRST_RESULT_COLUMN, lastRow - 1, 1).getValues();
  const started = Date.now();
  let checked = 0;

  for (let i = 0; i < emails.length; i++) {
    if (done[i][0] !== "" || !emails[i][0]) continue;
    if (Date.now() - started > SV_RUN_BUDGET_MS) break;

    const address = String(emails[i][0]).trim().toLowerCase();
    let outcome = svCall_(address, key);

    if (outcome.retryAfter) {
      Utilities.sleep(outcome.retryAfter * 1000);
      outcome = svCall_(address, key);
    } else if (outcome.retry) {
      Utilities.sleep(5000);
      outcome = svCall_(address, key);
    }

    if (outcome.fatal) {
      SpreadsheetApp.getUi().alert(outcome.error);
      return;
    }

    const row = i + 2;
    const values = outcome.body
      ? [[outcome.body.status, outcome.body.reason, outcome.body.action]]
      : [[outcome.error, "", ""]];
    sheet.getRange(row, SV_FIRST_RESULT_COLUMN, 1, 3).setValues(values);
    checked++;
    Utilities.sleep(SV_PAUSE_MS);
  }

  SpreadsheetApp.getActive().toast("Checked " + checked + " rows. Run again to continue.");
}

Reload the sheet. A SimpleVerifier menu appears. Click Verify empty rows and Apps Script asks for permission the first time (it needs to read the sheet and call an external service).

How it behaves:

• It only checks rows where column B is empty, so you can run it again after it stops.
• It stops after five minutes of work, inside Google's six minute limit per execution. At the pause above that is a few hundred rows a run. Click the menu item again to continue.
• A 429 makes it wait the Retry-After seconds from the reply and try once more.
• A 402 stops the run with an alert, because retrying cannot fix a plan or quota problem.
• Failed rows hold the error text instead of a verdict, so they are not silently marked valid.

If you have thousands of rows, use a CSV upload. It runs the same checks in bulk and returns valid, risky and invalid files. The sheet script is for the steady trickle.

Quotas you will meet

Google's quota table lists URL Fetch calls at 20,000 a day for consumer accounts and 100,000 a day for Google Workspace accounts, with a 6 minute limit per script execution and 30 seconds per custom function call. Those are Google's limits and they are separate from yours with the API.

On the API side, one key can make 120 calls in a sliding minute. The menu script paces itself well under that. If you also run a workflow tool on the same key, they share the 120.

Branch on the result

The API returns three statuses plus a machine-friendly action:

status	action	What to do in the sheet
valid	send	Keep, ready for your sequence
risky	send_with_care or retry_later	Do not drop. Put in a review tab, or retry the retry_later ones another day
invalid	do_not_send	Move to suppression

Add conditional formatting on column D to colour the rows, and a filter view on do_not_send. A risky result is not a failure. It means delivery was not confirmed, for example on a catch-all domain. The catch-all explainer covers what to do with those.

Errors you will see

What you see	Cause	Fix
ERROR 401	Key missing from Script Properties, mistyped, or revoked	Create a new key on the API keys page
ERROR 402	Starter plan, inactive subscription, or a yearly plan that used this month's included calls	Upgrade or renew. Retrying does not help. See errors
ERROR 429	More than 120 calls a minute	The menu script waits for you. If it keeps happening, another tool is using the key
ERROR 503	The service was briefly unavailable	Run the menu item again
Exceeded maximum execution time in a cell	The mailbox check took more than 30 seconds	Use the menu script for that row

What it costs

Each address is one call. Pro includes 10,000 calls a month, then $1.50 per 1,000 on monthly billing. On yearly billing the included calls are a fixed monthly quota, and further calls return 402 until the 1st (UTC). Business and Agency include more. Check the pricing page for today's numbers.

The short version

Put your key in Script Properties, paste the two scripts, and use =VERIFY(A2) for the odd cell and the Verify empty rows menu for a column. Pace calls under 120 a minute, stop on 402, wait on 429, and paste results as values so recalculation never spends calls. Start from the quickstart if you want to test the API in curl first.