Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheet script to verify email addresses automatically
/**
* A custom function that verifies an email ID
*
* @param {String} email ID
* @return {Boolean} If the email ID is active
* @customfunction
*/
function verifyEmail(email) {
// Replace this with your Hunter.io API key
var api_key = "<your api key>";
var url = "https://api.hunter.io/v2/email-verifier?api_key=" + api_key;
url += "&email=" + email;
var response = UrlFetchApp.fetch(url);
// Get response code
var responseCode = response.getResponseCode();
if (responseCode != 200) {
return false;
}
// Parse response
var json = response.getContentText();
var data = JSON.parse(json);
if (data["data"]["smtp_check"] != true) {
return false;
}
return true;
}
function runVerifyEmail() {
// Replace the col variable with the column in which your email IDs are stored
// The status will be stored in the column to the right of the email column
var row = 2;
var col = 2;
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(row,col);
var lastRow = sheet.getMaxRows();
var dataRange = sheet.getRange(row, col, lastRow, 3);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; i++) {
var rowData = data[i];
var email = rowData[0]; // First column
var status = rowData[1]; // Second column
if (status == "" && email != "") { // Prevents sending duplicates
response = verifyEmail(email);
sheet.getRange(row + i, col + 1).setValue(response);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment