# 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/](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**

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/e8b35222-b60f-4068-ab9e-ae21bca4629b.png align="center")

Delete

```javascript
function myFunction() {
  
}
```

and paste this instead :

```javascript
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.

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/4de830e6-1a31-45f9-9701-b54a1f1dc4a9.png align="center")

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.

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/759332a3-045c-4254-afeb-deab58e38a22.png align="center")

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

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/ceb2fa04-755c-4333-89c4-23f3c75e068e.png align="center")

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

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/aa12ef2e-bc30-459a-b9f5-c1c3e2cf2ad8.png align="center")

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

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/336b47a4-7477-4e12-bbd8-eb686dc9c39c.png align="center")

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

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/fcacc3c4-b9d1-4afd-9637-86aa5f3d002e.png align="center")

Google will ask for access. Grant it access.

![](https://cdn.hashnode.com/uploads/covers/5e5e8afd205a551d167f94ef/344d3b6e-54c7-4f58-ba0b-db070e94bbcf.png align="center")

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](https://developers.google.com/speed/public-dns/docs/doh/json)

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

[https://dns.google/resolve?name=johnsmith@yahoo.in&type=MX](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](@yogini)?