Google Sheets Email Validation With Apps Script

A spreadsheet is useful for reviewing a small list of addresses, but it should not contain your API key in a cell or send a verification request every time the sheet recalculates. A custom Apps Script menu avoids both problems.

This tutorial adds two commands to Google Sheets:

• Set API key saves a project key in the current user's script properties.
• Verify selected email makes one API request and writes the result into the four empty cells to its right.

The script checks one selected address at a time. That makes credit use visible and keeps the workflow suitable for manual review instead of bulk list cleaning.

Create a key for the sheet

Create an emailverifier.dev account and project, then copy the project key when it is shown. Each authenticated check uses one project credit.

Open a Google Sheet and choose Extensions → Apps Script. Replace the contents of Code.gs with the complete script below.

Add the Apps Script

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Email verification')
    .addItem('Set API key', 'setApiKey')
    .addItem('Verify selected email', 'verifySelectedEmail')
    .addToUi()
}

function setApiKey() {
  const ui = SpreadsheetApp.getUi()
  const response = ui.prompt(
    'Set API key',
    'Paste the project key from your emailverifier.dev dashboard.',
    ui.ButtonSet.OK_CANCEL
  )

  if (response.getSelectedButton() !== ui.Button.OK) return

  const apiKey = response.getResponseText().trim()
  if (!apiKey.startsWith('ev_')) {
    ui.alert('That does not look like an emailverifier.dev project key.')
    return
  }

  PropertiesService.getUserProperties().setProperty(
    'EMAILVERIFIER_API_KEY',
    apiKey
  )
  ui.alert('API key saved for your Google account in this script.')
}

function verifySelectedEmail() {
  const ui = SpreadsheetApp.getUi()
  const sheet = SpreadsheetApp.getActiveSheet()
  const cell = sheet.getActiveCell()
  const email = String(cell.getValue()).trim()
  const apiKey = PropertiesService.getUserProperties()
    .getProperty('EMAILVERIFIER_API_KEY')

  if (!apiKey) {
    ui.alert('Choose Email verification → Set API key first.')
    return
  }

  if (!email || !email.includes('@')) {
    ui.alert('Select a cell that contains an email address.')
    return
  }

  if (cell.getColumn() + 4 > sheet.getMaxColumns()) {
    sheet.insertColumnsAfter(
      sheet.getMaxColumns(),
      cell.getColumn() + 4 - sheet.getMaxColumns()
    )
  }

  const outputRange = cell.offset(0, 1, 1, 4)
  const outputValues = outputRange.getDisplayValues()[0]
  if (outputValues.some(value => value !== '')) {
    ui.alert('Keep the four cells to the right of the email empty.')
    return
  }

  let response
  try {
    response = UrlFetchApp.fetch(
      'https://emailverifier.dev/api/v1/verify',
      {
        method: 'post',
        contentType: 'application/json',
        headers: { 'X-API-Key': apiKey },
        payload: JSON.stringify({ email }),
        muteHttpExceptions: true
      }
    )
  } catch (error) {
    ui.alert(`The verification service could not be reached: ${error.message}`)
    return
  }

  const httpStatus = response.getResponseCode()
  let body
  try {
    body = JSON.parse(response.getContentText())
  } catch {
    ui.alert(`The verification service returned HTTP ${httpStatus}.`)
    return
  }

  if (httpStatus !== 200) {
    ui.alert(body.message || body.error || `Verification failed with HTTP ${httpStatus}.`)
    return
  }

  outputRange.setValues([[
    body.status,
    body.action,
    (body.signals || []).join(', '),
    body.suggestion || ''
  ]])
  outputRange.setNotes([[
    'Verification status',
    'Recommended signup action',
    'Evidence and risk signals',
    'Suggested correction, when available'
  ]])
}

Save the project and return to the sheet. Reload the page so the Email verification menu appears. Choose Set API key, paste the key, and approve the Google permissions prompt the first time the script runs.

PropertiesService.getUserProperties() stores the key for the user running the script instead of displaying it in the grid. Each editor who wants to run the menu should save their own key. Anyone who can edit the Apps Script project can still change the code, so use this with people you trust to edit the sheet.

Verify an address

1. Enter an email address in a cell.
2. Leave the next four cells in that row empty.
3. Select the email cell.
4. Choose Email verification → Verify selected email.

The script sends exactly one POST /api/v1/verify request. It writes the status, recommended action, signals, and typo suggestion into the four cells to the right. Hover over those cells to see the column meaning in their notes.

Read the possible statuses

A timeout, policy block, or inconclusive SMTP result can produce unknown. The script preserves that state instead of changing it to “invalid.” The validation, verification, and confirmation guide explains when to use each layer.

Why this is a menu action instead of a custom function

A formula such as =VERIFY_EMAIL(A2) can recalculate when the sheet changes. That makes API usage less predictable and can repeat checks without an explicit decision from the person reviewing the list. The menu action runs only when selected.

Google documents the storage behavior in Properties Service and outbound HTTP options in UrlFetchApp. For response fields and HTTP errors, use the emailverifier.dev API reference.