Google Apps Script to Run MX Lookups on domains from Google Sheets

Google Sheets MX Lookup

function MXLookup(domain) {
  
  try {
    var url = "https://dns.google.com/resolve?name=%FQDN%&type=MX".replace("%FQDN%",domain);
  
    //var url = "https://dns.google.com/resolve?name=e-mercy.com&type=MX"; // USED FOR TESTING ONLY
    
    Utilities.sleep(100);
    var result = UrlFetchApp.fetch(url,{muteHttpExceptions:true});
    var rc = result.getResponseCode();
    var response = JSON.parse(result.getContentText());
  
    if (rc !== 200) {
      throw new Error( response.message );
    }
    
    if (response.Answer[0].data == null) {
      var mxRaw = response.Authority[0].data; 
    } else {
      var mxRaw = response.Answer[0].data;
    }
    
    var mx = mxRaw.toLowerCase();
    
    if (mx.indexOf("google.com") >= 0 || mx.indexOf("googlemail.com") >= 0)  { 
      var emailProvider = "Google Apps"; 
    }
    else if (mx.indexOf("outlook.com") >= 0) { 
      var emailProvider = "Office 365"; 
    }
    else emailProvider = "Other";
    
    return emailProvider;
  }
  catch (e) {
    return "ERROR";
  }
}

function MXLookupArray(input) {
  
  if (input.map) {
    return input.map(MXLookup);
  } else {
  
    try {
      var url = "https://dns.google.com/resolve?name=%FQDN%&type=MX".replace("%FQDN%",domain);
      
      //var url = "https://dns.google.com/resolve?name=e-mercy.com&type=MX"; // USED FOR TESTING ONLY

      var result = UrlFetchApp.fetch(url,{muteHttpExceptions:true});
      var rc = result.getResponseCode();
      var response = JSON.parse(result.getContentText());
      
      if (rc !== 200) {
        throw new Error( response.message );
      }
      
      if (response.Answer[0].data == null) {
        var mxRaw = response.Authority[0].data; 
      } else {
        var mxRaw = response.Answer[0].data;
      }
      
      var mx = mxRaw.toLowerCase();
      
      if (mx.indexOf("google.com") >= 0 || mx.indexOf("googlemail.com") >= 0)  { 
        var emailProvider = "Google Apps"; 
      }
      else if (mx.indexOf("outlook.com") >= 0) { 
        var emailProvider = "Office 365"; 
      }
      else emailProvider = "Other";
      
      return emailProvider;
    }
    catch (e) {
      return "ERROR";
    }
    
  }
}

Just copy the script into the Google Sheets script editor (from the Tools menu), save the script, then use functions mxlookup() or mxlookuparray() to run the lookups.

mxlookuparray accepts a range of cells - eg =mxlookuparray(A1:A500) - while mxlookup just accepts one cell at a time - eg, =mxlookup(A1). For larger lists use the array function - it seems to be able to handle around 1000 rows at a time.

It requires a naked domain (no www/http, etc). So you can use a regex formula to convert full domains to naked. I have a Gist for that here: https://gist.github.com/ciaranmahoney/046a6c1cfd9e12eb37a9adea34a5e7df

Added better error handling and a sleep function to the standard mxlookup to better handle larger data sets.

Thanks a lot!

Just wanted to say a hearty thank you for this. It saved me a bunch of time looking up what school districts in Idaho were using Google vs O365

I think it will not work for this, www.akasamconsulting.com !

And me thinking that it would give the result of nslookup, instead of "Other"

Please update this so that it is really useful.

@Jrosales2019 At Row 75: instead of return emailProvider; just use return mx;

Hi,

I have found this tool to be very helpful as well. However, today I am only receiving an "ERROR" response even when it is looking up domains that I know are either Google or Office etc. Can anyone help with this? Thanks.

Incredible.
works perfectly

How can I find new codes for google sheet for network functions, pint, tracert, whois, nslookup, among others

It works perfectly, thanks a lot.
I added some providers in the script (yahoo, comcast, proofpoint...) and it's perfect.

Perfect! Thank you so so much!

Thanks for this script.
It helped me a lot.
I made some changes to it to be able to get A record and NS record for a domain.

5 Starts 👍