Analyze Your Domains in Google Sheets

Programming can be difficult to master, and usually requires years of practice. But you don't need to be a master of computers to unleash the real powers of the DNSimple API.

Imagine using common tools to answer complex questions about your domains. In this post, I'll share a simple way to access all the information in your DNSimple account via Google Sheets. Exposing this information to sheets makes this data accessible to everyone in your company.

We'll accomplish this by using functions. In Google Sheets, functions are used to create formulas to manipulate data and make calculations.

Let's walk through Google Sheets to answer the question:

When do my domains expire, and are they set for automatic renewal?

Prerequisites

Access token

First, you need a DNSimple API access token. You can generate an account access token by logging into your account and navigating to your account page. We have a full step-by-step guide in our support site. This token grants you access to all your assets on the DNSimple platform.

A fresh sheet

Navigate to your Google Drive and open a new Google Sheet. Click : "Tools" 👉 "Script Editor" to open the Google Script Editor (GS-Editor). This opens a new file (Code.gs) in the Google Sheets editor. You can remove the demo content. We'll add our own.

We'll need to store some data across function calls.

Add this line at the top of the script:

var scriptProperties = PropertiesService.getScriptProperties()

The PropertiesService allows us to save and fetch data by any key we specify.

Fetch your domains

Let's start by adding our first sheet function: FETCHDOMAINS. This function will do some initial setup and fetch all the domains from your DNSimple account.

FETCHDOMAINS will take your DNSimple access token as a parameter, so you would call it =FETCHDOMAINS("SECRET_TOKEN") in your sheet on the next line.

Copy and paste the following into the sheet (the code comments provide more details):

function FETCHDOMAINS(token) {
  // store token in the PropertiesService
  scriptProperties.setProperty('TOKEN', token)
  // fetch and store account id in the PropertiesService
  scriptProperties.setProperty('ACCOUNT_ID', accountId(token))

  // fetch domains from DNSimple API
  var response = fetchData("/domains")
  // initialize empty `names` list
  var names = []

  // collect domain names from response data
  for(i = 0; i < response.length; i++) {
    names.push(response[i].unicode_name)
  }

  // return list of domain names
  return names
}

fetchData = function(url) {
  var account_id = scriptProperties.getProperty('ACCOUNT_ID')
  var token = scriptProperties.getProperty('TOKEN')

  return JSON.parse(UrlFetchApp.fetch("https://api.dnsimple.com/v2/" + account_id + url, {
      headers: {"Authorization": "Bearer " + token}
  }).getContentText())["data"]
}

accountId = function(token) {
  var response = JSON.parse(UrlFetchApp.fetch("https://api.dnsimple.com/v2/whoami", {
    headers: {"Authorization": "Bearer " + token}
  }).getContentText())["data"]
  return "" + response.account.id // cast to string
}

Click "Save" in the GS-Editor and name the project. Now all functions defined like this pattern: function <NAME>() { ... } are accessible from the sheet.

To call this function from your sheet, go into any cell and type =FETCHDOMAINS("SECRET_TOKEN") and hit enter. Please replace SECRET_TOKEN with your own DNSimple API token you noted at the beginning of this tutorial.

The field will show loading first, then fill the cell and the following cells in the column with a domain name per cell. It should look like this:

More domain data

Next, let's look at how we can fetch more data from the API about our domains. The question we want to answer is: When does a domain expire and does it renew automatically?

We need a function that gives us the expiration date for a given domain. Let's define DOMAINEXPIRES. With the fetchData helper, this will be simple.

Copy and paste the following into your Google Sheet script editor (below the other functions we just created):

function DOMAINEXPIRES(domain) {
  return fetchData("/domains/" + domain).expires_on
}
function DOMAINAUTORENEW(domain) {
  return fetchData("/domains/" + domain).auto_renew
}

Next, hit "Save" again and switch over to the sheet. Now, with the power of Google Sheets, we can reference the values we just fetched from the API. In the second column you can now call these new functions like so =DOMAINEXPIRES(A1). This will use the domain name we just fetched from the API. By dragging the lower right corner to all rows that also contain a domain name, sheets will extrapolate the formula needed, and we have all domains and their expiration dates.

This queries the domain info endpoint and fetches the data in the expires_on field. We can apply the same to fetch, whether the domain is set to auto-renew or not.

Note: For domains that are already expired, or not registered through us, the cell will remain empty.

Repeat the same for the DOMAINAUTORENEW function. With conditional formatting you can also highlight the domains that won't auto renew.

Make sure to save the project in the Editor and look for any errors reported. You can find the full script over here, in case you want to try our version, or use it if yours doesn't work.

Wait, there's more!

Congratulations on making it this far. You leveraged the power of the DNSimple API. This example only uses the domains info API, but our API does way more. You can fetch certificates, create e-mail forwards, or even register or renew domains.

Visit https://developer.dnsimple.com/ to learn more about our powerful API, and let us know what you build with it!

Caveat

This script is a proof of concept, and is meant to illustrate the power of the DNSimple API. If you are planning to make extensive use of this, make sure to consult your friendly programmer, as this may result in an extensive amount of queries to our API, and we rate limit API requests.

Share on Twitter and Facebook

Ole Michaelis

Conference junkie, user groupie and boardgame geek also knows how to juggle. Oh, and software.