Tag: Google Sheets

Yesterday, I needed to get current results for the “Nameservers” for a batch of about 400 domains. To look each domain up manually would take forever—even using dig from the Linux terminal like I do it. Plus, I wanted to record the results in a spreadsheet for reference and for some quick analysis.

I had the domains in a Google Sheet already, so it was just a matter of spinning up a quick Google Apps Script to perform the DNS lookups and return the results to the Google Sheet.

Naturally, I turned to Google to help with this situation and stumbled across this piece by Alex Miller on Medium that suggested a method for doing exactly what I wanted to do. Unfortunately, I had some trouble with Alex’s code, which relied upon queries to a Google DNS service.

Thankfully, he linked out to a useful Stackoverflow thread which provides quite a few code options for performing DNS lookups in Google Sheets. It’s clear from the history of the responses that various public DNS resolvers have come and gone in the ~6 years since the question was originally asked. Naturally, changes in Google Apps script have also affected the utility of some of the scripts in the replies.

The Solution: Using Cloudflare’s 1.1.1.1 DNS Resolver with Google Sheets

I tested some code that appears in this reply to the Stackoverflow thread linked above, but had some difficulty at first. No matter what I added to my formula for “record type,” the query returned an IP address corresponding to the “A record” for the domain.

That led me to find the code (below) from Cloudflare’s 1.1.1.1 Docs:

function NSLookup(type, domain) {

  if (typeof type == 'undefined') {
    throw new Error('Missing parameter 1 dns type');
  }

  if (typeof domain == 'undefined') {
    throw new Error('Missing parameter 2 domain name');
  }

  type = type.toUpperCase();

  var url = 'https://cloudflare-dns.com/dns-query?name=' + encodeURIComponent(domain) + '&type=' + encodeURIComponent(type);

  var options = {
    muteHttpExceptions: true,
    headers: {
      accept: "application/dns-json"
    }
  };

  var result = UrlFetchApp.fetch(url, options);
  var rc = result.getResponseCode();
  var resultText = result.getContentText();

  if (rc !== 200) {
    throw new Error(rc);
  }

  var errors = [
    { name: "NoError", description: "No Error"}, // 0
    { name: "FormErr", description: "Format Error"}, // 1
    { name: "ServFail", description: "Server Failure"}, // 2
    { name: "NXDomain", description: "Non-Existent Domain"}, // 3
    { name: "NotImp", description: "Not Implemented"}, // 4
    { name: "Refused", description: "Query Refused"}, // 5
    { name: "YXDomain", description: "Name Exists when it should not"}, // 6
    { name: "YXRRSet", description: "RR Set Exists when it should not"}, // 7
    { name: "NXRRSet", description: "RR Set that should exist does not"}, // 8
    { name: "NotAuth", description: "Not Authorized"} // 9
  ];

  var response = JSON.parse(resultText);

  if (response.Status !== 0) {
    return errors[response.Status].name;
  }

  var outputData = [];

  for (var i in response.Answer) {
    outputData.push(response.Answer[i].data);
  }

  var outputString = outputData.join(',');

  return outputString;
}

I’m not 100% sure how close the two code snippets are to one another (they may be identical, I just didn’t check). Once I implemented the code from Cloudflare, though, the issue with only receiving the “A” record result persisted.

Here’s what I learned about getting it to work.

The mistake I was making was that I was manually adding the record type to the formula I was typing in Google Sheets. For example:

=NSLookup(NS,A2)

where “NS” is the record type I was hoping to retrieve and “A2” is the cell containing the domain name.

Once I added a column for the record type, everything worked. My updated formula reads:

=NSLookup(D2,A2)

where “D2” is the cell containing the record type.

In hindsight, I probably could have just wrapped the NS in quotes and my first attempt at this formula probably would have worked.

In any case, this worked beautifully. I was able to retrieve the Nameserver records for more than 400 domains in a matter of just a few seconds, and then do some quick “conditional formatting” to easily note the specific situations I was trying to track down.

One thing that occurred, though, was that Cloudflare’s DNS resolver returned refused for maybe 20 or 30 of the domains. I suspect this was a result of throttling. I was able to force the query to run again by modifying the “record type” value, which triggered Google Sheets to notice the formula reference was no longer current, and the script ran again. Changing the record type back to “NS” in the corresponding cell caused the query to return the proper result for the “Nameserver” record as expected.

I hope this helps someone! Feel free to ping me in the comments if you have questions or thoughts. Cheers!