Creating email verification function on Google Sheet using GoogleApp Script

Whether you are managing your customer's data or planning to launch your email marketing campaigns, validating and cleaning customer's email address is a tedious task.

There are many tools available to verify email addresses. But there is nothing as useful as having a 'verify email' function in your Google Sheet. It can save time and make email verification process as easy as using any of the standard Google Sheet's function.

You can use any email verification API or services, but I will be using BigDataCloud's free email verification API. You can access this API for free by creating a free account and can use 1,000 queries per month for free. You can find more details about BigDataCloud's email verification API here.

So if you have the API endpoints and the API key, let's get started.

Note: You need to have a gmail account.

Step 1: Open a new Google Sheet

Step 2: Click on Apps Script from Extension menu

This is a console where you will be defining your email verification function. The function defined in this module will be available by default to only this spreadsheet.

Give the AppsScript a name and define a function.

Step 3: Define Email Verification Function

function emailIsValid(input) {

var url = 'https://api.bigdatacloud.net/data/email-verify'
  + '?emailAddress=' + input
  + '&key=APIKEY';

var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});

// Make request to API and get response before this point.
var json = response.getContentText();
var data = JSON.parse(json);
return data.isValid;

}

The function above takes a single argument called input (email address) and returns a boolean data.

Inside the function, we are calling BigDataCloud's API and parsing through its output data to return the value. If you are using any other API, please use their endpoints and the data objects to parse the result.

Below is the standard output data of BigDataCloud's email verification API.

{
"inputData": "[e-mail]",
"isValid": true,
"isSyntaxValid": true,
"isMailServerDefined": true,
"isKnownSpammerDomain": false,
"isDisposable": false
}

Step 4: Run your code

On the toolbar click on Run button.

Wait for sometime before it starts asking you to provide permission to execute the code. Follow the instruction from the Google and provide necessary permissions.

Once the execution is complete you should see Execution Log as below:

Now you are ready to test the function.

Step 5: Test the function

Go back to your Google sheet.

On the sheet, enter any email address and on different column enter your newly created function as below:

=emailIsValid(B2)

Now you can add more email id and simply drag and drop the function to check the email validation. 😎

Last step: Add more functions

Now that you have a successfully running email verification function, you can create more functions that provide other important information about the email address. May be you want to check if the email address belongs to a known spammer domain or check if the email address is disposable.

Check the function definition example below: