How to verify emails in Google Sheets

December 9, 2016 - 4 minutes read

You’ve just received a large sheet of contacts along with their contact information and you plan to include them in your next email outreach campaign. Data collected offline has a high chance of being incorrect. That’s just a fact. There is no way to verify it as you collect it.

Unfortunately, you only know if an email is invalid once you send out your campaign. Now the time sensitive communication you had to send out is delayed as you now have to go back to the ground and correct these email IDs. You lose valuable time and effort in re-verifying them.

Well, what if you could verify them as you get them?

That’s an issue I had with my outreach campaigns. I stumbled upon Hunter.io , an email finder and with email verifier service with a great set of open APIs. Hunter is free to use for upto 150 requests / month. I used Hunter’s APIs to write this quick and easy script.

This saved me hours and hours of time. I hope it helps you too!

How to automatically verify emails

How I verify emails

I use Google sheets to capture unverified data. It makes things very easy. You can use it offline, collect data and then upload it when you’re online. Or if you use Google forms, all the collected data is stored in a Google Sheet.
Google sheets lets you create your own scripts and custom functions. So, I wrote a function that calls the hunter API and verifies the email!

Create a Custom Function

=verifyEmail(Cell)

Where “cell” refers to the cell in the sheet that has the email ID.

All I need to do is drag this formula to the rows I need and as soon as an email is added it verifies it by returning True or False. Perfect!

verify email ID with custom function in google sheets

verify email ID with custom function in google sheets

Automated Script

Unfortunately, the Hunter API is limited by the number of requests you make. Using a function will make unnecessary API calls. So I then write a script that only verifies new emails and it runs only when the sheet is edited.

The script is triggered to run only when the sheet is edited and there are emails that are yet to be verified. Once the script runs, it marks the email as True or False depending on the validity of the email.

Here is the code.

How to use the script

Add code to script editor

Copy this code into the Google script editor. You’ll find it under Tools. If you are unfamiliar with Google scripts, head here for a very quick tutorial. Google scripts tutorial.

verify emails in Google sheets with script

verify emails in Google sheets with script

Edit the script

Modify the script to include your Hunter.io API key and the column number of the column that has the email IDs.

edit the script

Set a Trigger

Set a trigger for the function runVerifyEmail by going to Resources and setting a trigger for onEdit.

add trigger google sheets script

Now just sit back and watch your emails get verified as you add them to your sheet.😀

You’re welcome!

Alternatives

Now Hunter.io is not the only service that let’s you verify emails. It’s just the one I liked most. Feel free to replace the API with any other that your comfortable with and fits in your price range.

Tags: ,