Email validity checker using Google's JSON API for DNS over HTTPS (DoH) in Google Sheets

AutoSend seems to he a promising utility belt for sending out (legit) mass emails be it for a campaign or transactional.

There was a post today by the creator of AutoSend - https://www.linkedin.com/feed/update/urn:li:activity:7452373365497085952/ - and I remembered about a Google Sheet tool I wrote in Google App Script that would validate email ids. Not so robust but enough to weed out bad email ids.

Open a new Google Sheet and goto Extensions > Apps Script

Delete

function myFunction() {
  
}

and paste this instead :

function onOpen()
{
  main();
}

function main()
{
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Domains')
      .addItem('Email Cleaner', 'EmailChecker_OneByOne')      
      .addToUi();
    
  Logger.log("init");

}

function EmailChecker_OneByOne()
{  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();  
  var firstrow = 2;
  var range = sheet.getRange(firstrow, 1, sheet.getLastRow() - firstrow + 1, 1);
  var data = range.getValues();

  sheet.getRange('B1').setValue("Valid Email").setHorizontalAlignment("center").setFontWeight('bold');  
  sheet.getRange('C1').setValue("Status").setHorizontalAlignment("center").setFontWeight('bold');
  sheet.getRange('D1').setValue("Answers").setHorizontalAlignment("center").setFontWeight('bold');
  sheet.getRange('E1').setValue("Is Disposable").setHorizontalAlignment("center").setFontWeight('bold');


  for (var i = 0; i < data.length; i++)
  {
    if (data[i] == "") continue;
    
    var domain = String(data[i]).split('@').pop();
    var response = JSON.parse(UrlFetchApp.fetch("https://dns.google/resolve?name=" + domain + "&type=MX"));

    if (!response.error)
    {      
      if (!response.Answer) answers_length = 0; else answers_length = response.Answer.length;
    }

    disposable = isDisposable(data[i]);

    if (answers_length > 0)
    {
      sheet.getRange('B' + (i + 2)).setValue(data[i]);
    }

    sheet.getRange('C' + (i + 2)).setValue(response.Status);
    sheet.getRange('D' + (i + 2)).setValue(answers_length);
    sheet.getRange('E' + (i + 2)).setValue(disposable);
    
    SpreadsheetApp.flush();

    Logger.log(response);
  }  
}

function isDisposable(email)
{
  // https://open.kickbox.com/v1/disposable/mail%40anjanesh.biz
  var url = "https://open.kickbox.com/v1/disposable/" + encodeURI(email);
  var response = JSON.parse(UrlFetchApp.fetch(url));
  return response.disposable;
}

And save to Drive.

Now, go back to the spreadsheet and reload the page. (F5 or CMD + R). You should see a new menu item called Domains next to Help. Don't click on it yet.

Now Enter Email in A1 and paste a list of email ids you want validated in column A. Now click on Domains > Email Cleaner.

You'll get a popup saying "Authorisation required" - "A script attached to this document needs your permission to run." - Click on Ok.

You'll get one popup - "Google hasn’t verified this app" - click on Advanced.

Continue only if you understand the risks and trust the developer (yourgmail@gmail.com). Click on Go to Untitled project (unsafe)

Google will ask for access. Grant it access.

You should see columns B, C, D and E populated.

https://youtu.be/hcWmCsruG1E

The connect to external service is to connect to Google's JSON API for DNS over HTTPS (DoH) - https://developers.google.com/speed/public-dns/docs/doh/json

For example an email like johnsmith@yahoo.in we cross-check MX records with Google's public DNS checker :

https://dns.google/resolve?name=johnsmith@yahoo.in&type=MX

Many mass mailing solutions will suspend your account if the bounce rate is very high - and this happens even when the collection is legit - like when you have email ids written down on paper material at in-person events.

What would be really cool if AutoSend could be used to send out emails within Google Spreadsheets right Yogini Bende?