Hey there! I’m Dorothi Viki, and today I’m going to walk you through a super practical way to verify emails directly in Google Sheets using a custom function. Whether you're managing a newsletter, running a business, or just need to clean up your email list, verifying email addresses can save you from bounces and spam traps. From my experience, integrating email verification into Google Sheets can massively improve your email deliverability and keep your contacts clean. And guess what? We’re going to do this with MillionVerifier’s API!
Why Verify Emails in Google Sheets?
If you work with email lists, you already know the importance of verifying them. Here are some key benefits:
Reduces bounce rates: Sending emails to invalid addresses harms your sender reputation.
Prevents spam traps: Some fake emails are actually traps designed to catch spammers.
Saves money: Many email marketing tools charge based on the number of emails sent.
Increases engagement: A clean email list means higher open rates and better email marketing performance.
With Google Sheets and a little scripting magic, we can verify emails in bulk, right inside the spreadsheet. Let's dive into it!
Setting Up MillionVerifier API for Google Sheets
Before we create the custom function, we need to get an API key from MillionVerifier.
Step 1: Get Your MillionVerifier API Key
Go to MillionVerifier and sign up for an account.
Once logged in, navigate to the API section.
Copy your API key. We’ll need this in our script.
Step 2: Open Google Apps Script
Open a new or existing Google Sheet.
Click on Extensions > Apps Script.
Delete any existing code and paste the script below.
function VERIFY_EMAIL(email) {
var apiKey = "tfXNVowLxOW5PaRYUWo76FE96";
var url = "https://api.millionverifier.com/api/v3/?api=" + apiKey + "&email=" + encodeURIComponent(email) + "&timeout=10";
try {
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
var json = JSON.parse(response.getContentText());
var resultMapping = {
1: "ok",
2: "catch_all",
3: "unknown",
4: "error",
5: "disposable",
6: "invalid"
};
var resultValue = resultMapping[json.resultcode] || "Unknown Result";
return [json.result];
} catch (e) {
return "API Error";
}
}
How to Use the Custom Function in Google Sheets
Once you've added the script, follow these steps to use it:
Close the script editor and return to your Google Sheet.
Use the function like any other built-in function.
- Example: In any cell, type
=VERIFY_EMAIL(A2) where A2 contains an email address.
Wait for the result – Google Sheets will call the API and return one of these:
ok (Valid email)
catch_all (Might be valid but uncertain)
unknown (Could not determine)
error (Something went wrong)
disposable (Temporary email address)
invalid (Fake or mistyped email)
Troubleshooting Common Issues
1. Getting "API Error"
This usually happens if:
Your API key is incorrect or expired.
MillionVerifier's service is down.
You've exceeded your API quota.
Solution: Double-check your API key and ensure you have available requests left in your MillionVerifier account.
2. Slow Response Time
If you notice slow responses:
Google Sheets functions run one at a time, so verifying thousands of emails might take longer.
You can optimize by verifying smaller batches of emails.
3. Getting "Unknown Result"
Some email servers block verification requests. In such cases, you might get catch_all or unknown. You may need to manually check these addresses.
Enhancing Your Email Verification Workflow
Now that you can verify emails in Google Sheets, here are a few ways to improve your workflow:
1. Filter Out Invalid Emails
Use Google Sheets filters to remove emails marked as invalid, error, or disposable.
2. Automate with Google Apps Script
You can modify the script to verify an entire column automatically without manually entering formulas.
3. Export Cleaned Data
Once you clean your email list, export it as a CSV and import it into your email marketing tool.
Need Help?
If you need any help, feel free to reach out to us at makeinfo.co/contacts. We’re happy to assist you with any issues or questions!
Conclusion
By implementing this Google Sheets custom function with MillionVerifier, you can save time and ensure that your email lists are accurate and up-to-date. I’ve used this approach for email marketing, client outreach, and internal data verification—and it works like a charm.
If you found this guide useful, share it with your team or anyone struggling with email verification. And if you have any questions, feel free to drop them in the comments below!
