Today, I’ll teach you how to use the free emailvalidation.io API and the Apipheny API integrator to validate email addresses in Google Sheets.
emailvalidation.io is a service that verifies and validates email addresses, ensuring deliverability and improving marketing campaign performance by removing invalid, fake, or disposable addresses, ultimately increasing sender reputation and engagement.
To validate data in Google Sheets using emailvalidation.io, follow these steps:
Apipheny is a free API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to API data sources, easily.
1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install
2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.
In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.
Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new
After logging in to your emailvalidation.io account, go to API Keys and copy your API key from there:
Copy and paste your API key in a safe location because you’ll need it in the next steps.
Imagine you have the following table, and you want to replace the question marks with the deliverability of the email address.
To achieve this, we create and save a new Apipheny function:
https://api.emailvalidation.io/v1/info?email=test@test.com
as a placeholder for nowapikey
parameter as a header (2) so it can be re-used when you overwrite the URL laterAfter that, you go into your saved request and copy the request ID:
=APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
The second parameter of the APIPHENY function lets you overwrite the request URL. We will use this functionality to inject the email address of the corresponding row.
Now let’s use the saved function with an URL overwrite and some other Google Sheets functions to build the following:
=INDEX(ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; "https://api.emailvalidation.io/v1/info?email="&A1));100;2);MATCH("state";ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; "https://api.emailvalidation.io/v1/info?email="&A1));100;1);0);2)
This will fetch the data from emailvalidation.io and filter down to only the value of the field with the key state
. You can use any other field specified in the documentation
If we insert this function into B1 and pull it down, it will fill column B with the state of the email address like so:
Schedule requests for automatic updates
Reference cell values in requests
Stack multiple URLs in a single request
Crypto API Tutorials: