In our quest to find a reliable method for verifying email addresses in Google Sheets, we explored various approaches. Here’s a detailed account of our journey and how we ultimately resolved the issue.
The Challenge
We needed a simple and effective method to verify email addresses directly within Google Sheets. Our goal was to identify invalid email addresses without sending verification emails to users.
Initial Attempts: Scripting Solutions
We started by implementing several scripts. Our initial attempts included basic domain verification scripts that checked the existence of the domain part of the email (e.g., gmail.com). These scripts relied on MX (Mail Exchange) records to verify that the domain could receive emails but couldn’t determine if a specific email address was valid or fake. Here’s the script we used:
MX Record Verification Script
<?php
function verifyEmail($email) {
list($user, $domain) = explode('@', $email);
$mxhosts = dns_get_record($domain, DNS_MX);
if ($mxhosts) {
return 'Valid domain';
} else {
return 'Invalid domain';
}
}
$email = 'test@example.com';
echo verifyEmail($email);
?>
This script only checked if the domain could receive emails by looking up its MX records. It couldn't verify whether the specific email address was valid.
Third-Party Add-Ons and APIs
To enhance our validation process, we considered using third-party add-ons or APIs. These tools offer comprehensive email validation services, but they come with their own challenges:
- Security Concerns: Add-ons often require extensive permissions, posing potential security risks.
- Free Tier Limitations: APIs usually have usage limits on their free plans, which might not be sufficient for our needs.
SMTP Checking Integration
Next, we tried to integrate SMTP checking into our Google Sheet using Apps Script and PHP. This method simulates sending an email to verify if the recipient address exists. However, we encountered issues with cURL timeouts and failed to establish a reliable connection.
SMTP Checking Script
<?php
function verifyEmail($email) {
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
return 'Invalid email format';
}
list($user, $domain) = explode('@', $email);
$mxhosts = dns_get_record($domain, DNS_MX);
if ($mxhosts) {
$mxhost = $mxhosts[0]['target'];
$url = 'smtp://' . $mxhost . ':25';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HEADER, false);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "HELO");
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30); // Increased connection timeout
curl_setopt($ch, CURLOPT_TIMEOUT, 60); // Increased execution timeout
curl_setopt($ch, CURLOPT_VERBOSE, true); // Enable verbose mode for debugging
$response = curl_exec($ch);
$curl_error = curl_error($ch);
curl_close($ch);
if ($response && strpos($response, '250') !== false) {
return 'Valid email';
} elseif ($curl_error) {
return 'cURL error: ' . $curl_error;
} else {
return 'Invalid email';
}
}
return 'Invalid email';
}
$email = 'test@example.com';
echo verifyEmail($email);
?>
Unfortunately, this script encountered cURL timeouts, preventing us from reliably verifying email addresses.
The Realization
After several attempts, we realized that there might not be a foolproof way to programmatically verify email validity without using third-party services or complex server-side configurations. This led us to explore alternative solutions.
The Solution: Manual Verification with Hyperlinks
Leveraging Google Sheets Features
Our final solution leverages the built-in features of Google Sheets to assist with manual email verification. Here’s how it works:
- Hyperlink Method: We used Google Apps Script to create hyperlinks for the email addresses in our sheet. When you hover over these hyperlinks, Google Sheets displays a profile picture if available. If the email is valid, it shows an alphabet in the profile picture if the user hasn't set up any profile pic, along with detailed information at the bottom.
- Visual Inspection: This method allows us to manually inspect each email address. Emails that do not show the expected profile information can be identified as invalid.
Implementing the Hyperlink Method
Script to Create Hyperlinks
- Open Google Sheets and go to Extensions > Apps Script.
- Enter the following code:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Create Hyperlinks', 'createHyperlinks')
.addToUi();
}
function createHyperlinks() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
for (var i = 1; i < values.length; i++) { // Start from row 2 to skip header row
var name = values[i][1]; // Assuming names are in column B
var email = values[i][2]; // Assuming emails are in column C
if (email) {
var hyperlinkFormula = '=HYPERLINK("mailto:' + email + '", "' + name + '")';
sheet.getRange(i + 1, 5).setFormula(hyperlinkFormula); // Column E
}
}
}
- Save the Script and refresh the Google Sheet to see the custom menu.
- Run the Script: Click on "Custom Menu" > "Create Hyperlinks" to generate the hyperlinks.
Conclusion
While our final solution requires manual verification, it provides a practical way to check email validity without sending emails or relying on third-party services. This method is especially useful for small-scale email verification tasks.
Pros:
- No third-party dependencies
- Secure and straightforward implementation
Cons:
- Manual verification required.
- Not scalable for large datasets.
- It can only verify gmail addresses effectively.
Through this journey, we discovered that sometimes a simple, manual approach can be more effective than complex automated solutions, especially when dealing with small-scale tasks. This method, although not fully automated, ensures that we can confidently verify email addresses in Google Sheets.
Leave a Reply