Find a valid email address for any person

Find an email address for any person and validate that it exists. Minimum of 25 requests per month for free 🚀

Check out the GIF to see it in action 🎥

---

How to add the Script to Google Sheets

1. Sign up to Abstract API

Sign up to Abstract Email Validation API, at https://app.abstractapi.com/users/signup?target=/api/email-validation/pricing/select.

Abstract is the best email validation API I found in my search, outside of Hunter. On the free plan on Abstract, you get 100 free requests per month (that’s a minimum of 25 requests per month in my template, as it can request up to 4 times for one person).

The reason I chose it over Hunter is for only $9 per month, you get a whopping 5,000 requests per month. 😲 🔥

---

---

2. Get your API Key

Your API key is visible as soon as you sign up. You should be directed to: https://app.abstractapi.com/api/email-validation/tester where you’ll see your API Key displayed like the image below:

---

3. Copy the script below:

/**
 * Find an email addresses for a person and validate that it exists 
 *
 * @param {"Bill"} first - [OPTIONAL] input the person's first name
 * @param {"Gates"} last - [OPTIONAL] input the person's last name
 * @param {"microsoft.com"} domain - [OPTIONAL] input the domain (without the protocol)
 * @param {"bill.gates@microsoft.com"} email - [OPTIONAL] input an email to be validated 
 * @customfunction
 */

function emailchecker(first, last, domain, email) {

  // var declaration
  const apiKey = '';
  let catchAll;
  let smpt;

  // input validation
  if (apiKey === '') return 'You need to add an API Key'
  if (!first && last) return 'You need to add a first name';
  if (!last && first) return 'You need to add a last name';
  if (!email && !domain) return 'You need to add a domain';


  // fetch email results from abstract api
  const fetch = (email) => {

    const response = UrlFetchApp.fetch(`https://emailvalidation.abstractapi.com/v1/?api_key=${apiKey}&email=${email}`);
    const content = JSON.parse(response.getContentText());

    catchAll = content.is_catchall_email.value;
    smpt = content.is_smtp_valid.value;

  }

  if (!email) {

    // array of popular email formats
    const initial = first.charAt(0);
    const emails = [`${initial}${last}@${domain}`, `${first}${last}@${domain}`, `${first}.${last}@${domain}`, `${first}@${domain}`];

    for (i = 0; i < emails.length; i++) {

      fetch(emails[i]);

      // if there is a catch all, break the loop to save requests
      if (catchAll === true) {
        return 'This domain accepts all emails';
        break;
      }

      // if smpt exists, break the loop to save requests
      if (smpt === true) {
        return emails[i].toLowerCase();
        break;
      }

      // if last item in array and smpt is false, none of the email addresses in the array exist
      if (emails[i] === emails[3] && smpt === false) {
        return 'Cannot find an email address';
      }

      // wait 1 second between fetches (required on free plan)
      Utilities.sleep(1000);

    }

  } else {

    fetch(email);

    if (catchAll === true) return 'This domain accepts all emails';

    return (smpt === true ? 'Email exists' : 'Cannot find the email address');

  }

}

---

4. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and replace line 14 with your API key:

  const apiKey = 'addapikeyhere';

Save it.

---

5. Add the formula to any cell in your sheet

=emailchecker(A2,B2,C2)

Replace A2 with a cell that includes the person’s first name.

Replace B2 with a cell that includes the person’s last name.

Replace C2 with a cell that includes the domain of the company. This needs to be without the protocol eg. example.com not https://www.example.com.

How does it work?

The script loops through four popular variations of email address: flast@example.com, firstlast@example.com, first.last@example.com, first@example.com until it receives a true smpt response from the API.

If it doesn’t receive a response, it doesn’t necessarily mean an email doesn’t exist, it just wasn’t one of the four variations of email. You could make this script even better by adding more email format variations in the array on line 40 but remember custom functions in Google Sheets timeout after 30 seconds.

If you have a list of emails and you want to verify their existence, you can also do that with the formula below.

=emailchecker(,,,"name@example.com")

Just leave the first three arguments of the function empty and add the email in the final argument. This can also reference a cell eg. =emailchecker(,,,A2)

** I haven’t tested this at scale yet, so I’m not sure how it works with a long list of emails. In my tests, the API timed out every so often but worked fine the majority of the time. **

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets