Skip to content

Instantly share code, notes, and snippets.

@phillipwilhelm

phillipwilhelm/Email-verifier.js

Forked from augfrank/Email-verifier.js
Created August 11, 2022 20:20
Show Gist options
  • Save phillipwilhelm/a0f68f84fcd31f6ea89abdf3e61d8a44 to your computer and use it in GitHub Desktop.
Save phillipwilhelm/a0f68f84fcd31f6ea89abdf3e61d8a44 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @augfrank augfrank created this gist Dec 9, 2016.
    62 changes: 62 additions & 0 deletions Email-verifier.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,62 @@
    /**
    * 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();
    }
    }
    }