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 18.104.22.168 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 this code from Cloudflare’s Github respository.
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:
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:
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!