How to Check DNS for Multiple Domains at Once Using 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 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 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 = '' + 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) {

  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:


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!

Courtesy Reminder: Watch Where You Enter Your Google Account Info

I’m sure there are lots and lots of these making the rounds at any given time. However, given that a client of ours was recently hijacked with a rather convincing-looking phishing scam, I felt it appropriate to publish this as a courtesy.

This one came in the form of an email. The message came from someone she knew, and it had a personal tone that made it seem a little more convincing than usual. Also, it contained the usual email signature of the sender (complete with their usual “sign-off” phrase, logo, contact info, etc.)

The subject line of the message making the rounds right now reads:

Please check out the document a very useful document that I believe we can all gain from.

If you see one like this, typically you’ll see that you were a bcc recipient. The message that got her had a body that read as follows:

I tried to get these document across to you before. Did you ever get it? VIEW HERE and sign on with your email to access it as attached on Google.doc, get back to me so we can discuss.

There were a couple of fonts in the message, so it was poorly formatted. The “VIEW HERE” phrase was linked to a website that looked like this:

Google Account Phishing Site

Clicking on any of the logos opens a small box that seems very official looking and you’re invited to enter your email address and password.

In the case of our friend, this all happened to her several days ago. She ultimately thought nothing had happened… until today. What tipped me off was that I got a message from her. I knew right away that it was a scam (the .ru domain name was a big clue), so I checked with other members of our team… and some had received the message and fallen for it.

Today, she found that after several hours of working, she suddenly couldn’t login to her Google Apps (corporate Gmail) account any more. This caused her to panic. So did the phone call from their corporate banker, who had been getting email requests for various bits of account information (including current balances, etc.). Thankfully, their banker refused to provide info via email and was kind enough to pick up the phone. Others may not be so lucky.

When In Doubt… Don’t

So like I said earlier, I’m pretty sure that there’s a new “flavor of the day” scam running at any point in time. So here are a couple of pointers that may help you:

  1. Don’t enter your email address & password into unknown websites. This particular one showed a lot of official-looking logos, so it seemed convincing. That’s why you always want to…
  2. Double-check the address of the website you’re visiting. This particular domain name was “” — and boy if ever anything screamed “I’m a Russian criminal,” this one does. But the little “Copyright 2013 Google” at the bottom might be enough of a distraction to keep you from noticing your browser’s address bar. So… always double check.
  3. Remember: You shouldn’t have to login to view a Google Doc if you’re already logged into your Gmail account. This goes for Google Apps users as well, of course. Google docs will automatically open for you because you’re already authenticated in your web browser. If you have to enter your Google account info again, it’s a red flag.

What If I Did It Before I Realized What I Was Doing?

If you enter your Google (or other) account info into an unknown website like this one before you realize it’s a scam, immediately go change your password. This is a pain, I know it. But you’ve just given away the keys to your personal kingdom, so you need to change the lock.

In our client’s case, she got kicked out of her Google account 3 days later when the thieves got around to trying to make use of her information. Thankfully, hers was a Google Apps account, and the domain administrator was able to reset her password for her, which effectively kicked the Russian crooks out of her account.

Which brings me to another important point: set up two-factor authentication for your Google account. It’s a little bit of an inconvenience, because it means that you have wait for Google to text a code to your mobile phone before you can log in to your account on a new browser or device, but it’s worth the short delay and the extra step because anyone trying to get into your account will also have to have your cell phone in order to get in. If you haven’t done this yet, now’s the time. Here’s more info about Google’s two-step verification process, including a guide to getting it set up for your account.

Something else you’ll need to do is check the apps and websites that are authenticated to access your Google account and revoke access for anything that looks unfamiliar or even remotely fishy.

Questions? Post ’em in the comments here and I’ll do my best to tackle them for you.

Safe browsing!

How to Get a Faster Sprint Hero

Update: On September 18th, a stable release of CyanogenMod 6.0 became available. Details are here. (The post below refers to my experience with the “release candidate,” which is the predecessor to the new stable release.) I updated my phone on October 23rd to the stable release and can attest it’s faster and better than ever! I was happy with the release candidate, but I’m even happier now!

HTC Hero for Sprint: Is There Any Hope?

I absolutely love my HTC Hero. I have since day 1, which for me was November, 2009.

But I’ve hesitated to recommend it to people… primarily because of the frustrations I’ve experienced with the device. It is plagued with significant lag (delays between when you expect something to happen and when it actually happens), some of the Android functions weren’t quite ready for prime time, and its battery life left something to be desired.

Nevertheless, I’ve been so thrilled with the Android operating system as a whole that I’ve personally just looked beyond those frustrations and made the best of it.

But a couple of months ago Sprint royally ticked me off. I’ll explain in a moment.

Cupcakes, Donuts & Eclairs

It may help here to provide a little background. My Hero originally shipped with Android “Donut,” which was version 1.6 of the Android Operating System.

For clarification, “Android” is the name of the open source operating system that is developed by Google (or has been since they acquired Android, Inc. about 5 years ago). There remains some confusion over terminology since Verizon licensed the term Droidâ„¢ from Lucasfilm, LTD. Verizon produces and sells several different devices under the name Droidâ„¢ as a way to brand their family of phones that run the Android operating system.

But any manufacturer is free to develop devices using the Android operating system. And many do. The devices began to take off when Android 1.5 (AKA “Cupcake”) released in early 2009. Google’s “Market” (their version of Apple’s “App Store”) began to explode with fantastic apps and the devices became more or less ready for daily use.

HTC Sense UI

So back to my Sprint HTC Hero. The Hero shipped with “Donut” (the successor to “Cupcake”), and as I said before, I loved it from day one. An important reason for it got so much love (from me and from others) was because HTC (the device’s manufacturer) developed an array of apps, widgets and modifications to the Android operating system that they labeled the “Sense UI” (UI is geek-speak for “User Interface”). Anyone who has used the Sense UI is spoiled.

I didn’t realize how spoiled I was until I picked up a friend’s Verizon Motorola Droidâ„¢ thinking I could use it. It was substantially clunkier and actually quite unfamiliar. I was surprised by the learning curve I had (considering I had owned and used my Android device regularly for months). But most surprising to me was how blazingly fast the Droidâ„¢ was in comparison to my Hero.

It was then that I began to realize just how unhappy I was with all the lag and the other frustrations I was experiencing.

This wasn’t just a case of device envy. I was syncing my Hero to an Exchange server and a Gmail account. I was regularly unable to answer calls because the lag was so long that they would go to voicemail before my phone was ready. Text messages were difficult at times. The browser was clearly powerful (especially when compared to my previous Blackberry and Windows Mobile browsers) but so painfully slow that it was rendered almost unusable.

So… imagine my delight when Sprint and HTC announced the availability of a significant upgrade from “Donut” (Android 1.6) to “Eclair” (Android 2.1) in May. Eclair boasted faster speeds — even on the same hardware (a rare occurrence in the world of hardware/software relations), and HTC had made substantial improvements to the Sense UI.

I backed up all my data (using an app that was readily available from the Android Market) and performed the upgrade. It was painful to watch the process run so slowly, but when it was over, my phone was noticeably more responsive.

But not responsive enough.

And even more painful was knowing that Eclair’s release date was October of 2009, fully 7 months before Sprint & HTC bothered to roll out the update. And also that “Froyo” (Android 2.2) was released by Google right about the time that I was downloading the Eclair update from HTC’s servers.

The Froyo Frustration

So… I said earlier that Sprint had ticked me off. Several things happened all about the same time in the world of Sprint. In June, they announced the HTC EVO… which they widely proclaimed the nation’s first 4G phone. It boasted a bigger screen, faster processor, and a big fat price tag. And even though I’m a Sprint “Premier” customer, I was still nearly 6 months away from qualifying for their “upgrade pricing.”

Another Sprint event: a leak. Word leaked out that although the EVO would be getting an upgrade to Froyo, the Hero (and a couple of other lesser phones) would not.

Whatever the reasons for their decision, here’s how it came across to the community of HTC Hero owners: a slap in the face. Some of them had just purchased the Hero, and in fact Sprint still sells it brand new today.

My wife is eligible for a Sprint upgrade and has been for probably 18 months or so since her last contract expired. No matter how easy to use, there was no way I was going to have her purchase the HTC Hero… because I knew that to a non-techie the problems I was experiencing would be absolute showstoppers.

But given Sprint’s attitude (“We’re not going to provide the software update, just buy our new $500 phone if you want something better…”), I seriously began contemplating a switch to another carrier.

I know, I know… they all screw their customers. And frankly, I’ve had almost no trouble at all with Sprint over the years… nor with Nextel prior to Sprint’s acquisition of it. Signal is good. Billing is accurate. Customer service (on the rare occasion when I’ve required it) has exceeded my (admittedly low) expectations.

So… why would I want to switch? It just felt like the decision was made purely to dangle a real expensive carrot in front of customers like me who pay significant fees every month for service.

It also happened that around July I began to face the fact that my dependence upon Microsoft was coming to an end. I’ve owned, managed or leased space on Exchange Servers for nearly 1o years. I’ve synced with a variety of mobile devices (as I mentioned before) and I am an enormous believer in “the cloud.” In fact, when I switched from my last smartphone (a Windows mobile device) to the Hero, my 1000+ contacts and an untold number of emails (even in the 3-day sync window) were synced before I left the Sprint store.

Realizing how good the sync is on the Android platform (including Facebook and Twitter integration), and that Google isn’t going anywhere, I decided to take the plunge and test out Google Apps For Your Domain (“GAFYD”). Holy cow. I wish I’d done it sooner. The Gmail platform (private-labeled for my team) is unbelievably powerful and easy to use. The extremely low cost ($50 per user per year) is an enormous cost savings over using (and supporting) the Exchange platform, and no software (Microsoft Outlook, you know who you are) is required.

So… a number of pieces were coming into place for me. I’m seeing a long term commitment to Google’s platform — including Android.

But man… the Hero was frustratingly slow.

So… last week, I bit the bullet and “rooted” my phone.

To Root or Not to Root

No… I’m not digging around in the soil. And no… I didn’t let it get acquainted with nature in an attempt to get an insurance upgrade (ever known anyone who’s tried that trick?)…

I did, however, void my warranty. At least temporarily.

The Android platform is closely related to Unix. On a Unix system, the “Administrator” (to use Microsoft’s terminology) is called the “Root” user. This user has “root” (the highest level of) access to the operating system.

For reasons that I’m sure are relatively obvious, Sprint (and every other carrier) does not provide “root” access to the operating systems on its devices. Instead, it locks down most configuration options and system areas so that the end user can’t screw things up too badly (and so that rogue apps don’t have the ability to behave too badly). Apple does the same thing with its devices.

Of course, there’s a vibrant community of hackers who will teach you how to gain root access to your Android device… and even provide software tools to avoid the most complicated, error-prone steps.

Why would you want root access? Well… for a long list of reasons, most of which involve gaining a higher level of control over the device. Want to overclock your processor? You need root access. Want to reconfigure your LED? You need root access. Want to do just about anything aside from installing the sanitized apps from the market? You need root access.

Want to install Froyo (Android 2.2)? You need root access.

Wait a minute… you can install Froyo? The same Froyo that boasts 3x-10x speed improvements (yes… on the same hardware) over Eclair? The same Froyo that allows for tethering (providing internet access via a USB cable from your phone to your laptop when not in range of wifi service… a feature blocked by Sprint in Eclair) and hotspot (turning your phone into a wifi hotspot so your laptop and other devices can utilize its internet connection… something Sprint charges an extra monthly fee for on the EVO even though it’s a built-in feature) and significantly-improved multiple Google account support?

Well… officially, no. You can’t have Froyo. You’re stuck with a slow Hero.

But unofficially… once you make the decision to take a few liberties with your device… you can do all of the above.

And let me tell you… the difference is nothing short of amazing.

On Saturday, I decided to take the plunge: root the phone and install Froyo. Of course, there’s no chance of just going to Google’s Android site and finding a download for Android 2.2 that’s going to actually work on your phone. But thanks to the community of developers/hackers I mentioned earlier, there are ready-made distributions available that are tailored to your carrier, device and desired configuration.

Let me be clear: this process is not for the faint of heart. There are portions that are highly technical in nature, and it’s best if you don’t expect someone to hold your hand. The community has produced a dizzying array of blogs, wikis and most importantly: forums, where answers can be found for all manner of technical questions.

I’m personally writing this post to inform some of the non-techies in the world that there are ways to get yourself a much better experience with your HTC Hero on Sprint (or just about any other Android device, for that matter). But I’m unable to provide technical expertise or guidance on this aside from sharing a few details that worked for me and pointing you toward the true masters of this game… the ones who have devoted untold hours to writing code, testing and supporting their work.

To these individuals — the ones who dared to say to Sprint, “Take that!” — I am truly grateful. I have today what amounts to a brand new phone. Yes, the hardware is no different. But how it performs… there’s absolutely no comparison.

So… let me provide a brief summary of the steps I took to get this amazing result.

The Process… Summarized

First and foremost, as with any operation that has the potential to affect valuable data, perform a backup. I highly recommend a phenomenal paid app from the Android Market called MyBackup Pro. Open the Market from your device, fork over a mere $4.99, and you can backup everything from your emails, contacts and calendar all the way to applications and even the layout of your homescreen. It will save to your device’s SD card and, if you choose, upload a backup to the developers’ servers where it can be retrieved later from the same device or from a replacement (if you’re switching hardware).

For me, my emails, contacts and calendar were all synced to Google accounts, so there was no need to actually store that data. But my call log, SMS (texts) and MMS (multimedia messages) and apps were valuable to me. I guess some people don’t see a need to hang on to those, but I like being able to refer back to things in the future. So I backed ’em up.

After you’re satisfied that you have a backup and can restore your phone to its current state if necessary (either because things go badly or because you need warranty service from Sprint because of hardware issues), then you can get under the hood and really start tinkering.

The short version is this:

  1. Gain root access to your device
  2. Download and install a recovery image (provides a boot platform as well as backup and other valuable tools)
  3. Perform another backup using Nandroid (part of the recovery image)
  4. Download and install a ROM that contains the distribution of Android and the configuration you’re looking for)
  5. Install the ROM
  6. Install the Google Apps (Market, Gmail, Maps, etc…) so that you can use the basic functions you’re expecting from Android
  7. Install/configure Launcher software (if you choose — as I did — to go with something different than what came with the ROM you installed)
  8. Selectively restore data from your backup (the one you performed prior to step 1). For me, this meant: call logs, SMS/MMS messages, and apps.
  9. Locate some new apps (as desired) to replace the stuff from HTC’s Sense UI that you might miss.
  10. Experience blazing speeds, better battery life, and overall… a fantastic phone!

I’ll provide a little more detail for you below. But here’s my caveat: this stuff changes… sometimes daily. Whatever I post here will be outdated by the time I hit publish, not to mention by the time you read it.

So… I’m going to point you in the direction of the valuable resources I have found. There are a few major players worth highlighting, but there are countless other players who may not be as visible or noticeable who have also played an enormous role in making this level of customization to your device possible. These are the real heroes, in my opinion. Obviously, Google and the original Android team deserve some major props as well.

The developers who have gone the “last mile” to us end users can be found in the forums at This is where you’ll find heroes like Darchstar — who created the final actual ROM I’m currently using and would highly recommend — and theimpaler747, who is one of many who deserve recognition for their tireless support answering questions from people like me who are trying to wrap our heads around what it takes to get the job done.

So, by topic, here are some important links you’ll need in order to undertake the process. (Note: these links apply — in most cases exclusively — to the HTC Hero on Sprint and may be out of date — see my red ink above. If you need stuff for a different device or a different carrier, then search the forums for your specific situation. Chances are, you’ll find great results.)

  1. Learn about (and download tools to gain) root access to your device here.
  2. Download the ROM Manager from the Android Market (using the Market app on your phone). It will only work after you have root access. Give it “Superuser” permissions and it will install the appropriate recovery image and the other tools (such as Nandroid for backups) to your device.
  3. Reboot to the recovery image and run a Nandroid backup to your SD card. This is a much more comprehensive, system-level backup of your entire device.
  4. Wipe your device. In hacker parlance, this means perform a “factory reset.” This is required in order to effectively install the ROM you’ll need. Alternatively, you can download the desired ROM and install it via the ROM Manager, which will prompt you for the wipe (which you should have it perform in this case).
  5. Here’s where to find Darchstar’s Froyo ROM RC1 for the Sprint Hero. (“Release Candidate 1” means it’s stable enough for you to use, but isn’t officially considered a full release yet as they’re still tinkering). Darchstar built upon the fantastic work of the CyanogenMod community in bringing us Froyo. This particular distribution bears the date of August 15, 2010. I’m sure I’ll be flashing (installing) a newer ROM when it becomes available — either RC2 or a formal release. There are also “nightlies” (nightly builds) available that may have newer features but may also be less stable. I’m not using the nightlies because my phone is something I absolutely depend upon on a daily basis and I can’t afford the luxury of testing at the bleeding edge for now.
  6. Darchstar also maintains a link to the latest version of the Google Apps distribution you’ll need. It’s posted on the same forum topic as his distribution. Grab it. You’ll want it. You “flash” this ZIP file right on top of the ROM (don’t perform a wipe this time) that you just installed. I used ROM Manager to do it, which Darchstar was kind enough to include in his Froyo distribution.
  7. Test, tinker and tweak.

I dug through the forums and decided to purchase the Launcher Pro App from the Android Market (after I synced my Google account, naturally). This brought some of the features of the Homescreen back that I would’ve missed from HTC’s Sense. I also gained some fantastic new features in the process (e.g. more rows for icons, a nifty all-new App Drawer, and some more fun stuff.)

I also decided to download the Dialer One app to regain some of the experience inside the actual phone functions that I liked from HTC Sense. It looks different, but performs very well. You can also turn it off and switch back to the standard Android dialer if it isn’t what you like.

For text messaging, I went with chompSMS. This was something I’d already switched to prior to rooting and upgrading to Froyo. It has a fantastic UI… including popups that appear when you receive an incoming text so you can answer (or not) without interrupting what you were doing. The threaded conversations are fantastic and visually appealing as well.

One of the most noticeable elements of HTC’s Sense UI is the big digital clock with the animated weather icons that typically adorned the Homescreen of most users. While Launcher Pro comes with some options, I ultimately decided to get the Beautiful Widgets app (and pay for the upgrade) from the Market. It has some obvious visual differences, but there are replacement widgets that look as good as (and are frankly more configurable than) the ones that come with Sense.

There are lots more tweaks available. And a few lingering issues are minor annoyances as well. The whole experience has opened my eyes to just how powerful the Android platform really is. At this point, I’m not sure I could ever be talked into buying an iPhone. Apple’s reputation for closing itself off to proprietary platforms is legendary… and ultimately not in the best interests of users. There are certainly those who think Google could be evil… and I’m mindful of the possibility that they could turn that direction somewhere along the line. But their commitment to open source development is clear. And there’s a clear path for getting your data off of their platforms at any point in time if you decide you want to switch.

As for the annoyances, there’s a lag that remains when you bring the phone back from sleep. Some users have overclocked their phone’s processors using “uncapped kernels” (another piece of software you can optionally flash on top of Darchstar’s Froyo ROM if you’re extra brave) and claim to have gotten rid of this. Frankly, I’m aware of it (it’s longer than the lag I had previously with Eclair/Sense), but it’s not a big deal. The blinding speed I get with every other function on the phone far outweighs any complaint I might raise about this lag. But the forums are filled with questions about it (typically the same question over and over), so some people are more annoyed by it than I am.  Occasionally, I uncover some other “missing feature” that I realize was part of Sense. But there are replacements for almost all of these. There’s a bug that occurs when you try to open the camera from inside the gallery (something I did regularly before) that causes the phone to hang. The fix is nifty: you get to pull the battery from your phone in order to reboot it. Not cool, but as with the other issue: it’s something I’m aware of and in this case, I can avoid it!

All in all, I’m so thrilled with my experience that I wish I’d done it a lot sooner. Of course, every day that goes by produces better and better code from the crew that’s working on it. So… perhaps the timing of my switch was good.

Either way, if you own a Sprint HTC Hero, I highly recommend that you root your phone and upgrade it to Froyo. You won’t regret it… and if for some reason you do, you can go back to the configuration you have today (if you really want to) by using the ROM that Sprint/HTC made available when they rolled out Eclair back in May.

This may be the longest post I’ve ever written here. But… what can I say… I’m thrilled with my Hero! And I’m running Froyo on it.

Incidentally, there’s a fantastic thread now running on that was started by the aforementioned theimpaler747 for users of any of the CyanogenMod ROMs for the Hero (this includes the one I’m using from Darchstar). In addition to the thread containing Darchstar’s ROM download, this one is highly useful.

I hope this post helps you make the decision to move forward with upgrading your Hero. It’s worth every minute of effort you spend learning your way around and going down the road, as complex as it may be!