Create a User Dashboard with Google Sheets and Zendesk

If you’re managing more than a couple of users, keeping tabs on your agent list can be something of a hassle. As your agents move in and out of roles, change departments, or otherwise stop using Zendesk, it’s on your administrators to ensure that you’re making efficient use of your seats.

We have more than 800 users in our Zendesk account, which is an awful lot to sift through, ten at a time in the People tab. Rather than flipping through these pages and manually reviewing each individual user, we opted for an external solution: I built a dashboard using Google Sheets and Zendesk’s API.

In this guide, I’ll offer a step-by-step process for building a dashboard of your own, allowing you to keep better tabs on your agents. This presumes that you already either:

1. Have an API key, so be sure to create one and save it somewhere secure. You’ll also need to encode your username and API key in Base64, input [YOUR USERNAME]/token:[YOUR API KEY] to get that string.
2. Have a Base64 encode of your username and password, formatted as username:password.

Oh, and by the way, this is geared towards Zendesk users, but as long as your help center has an API with which you can retrieve a list of agents, you can still build something similar to what I’ve gone through in this guide.

Install ImportJSON in Google Script Center

Before we do anything, we need a way for Google Sheets to talk to the Zendesk API. If you’re a whiz at writing scripts that call data from APIs in JavaScript or Python, you probably don’t need this guide. For the rest of us, the ImportJSON script will let you parse JSON responses right into Google Sheets. It’s a great place to start, but needs a few modifications.

Here’s what you need to do (with many thanks to Paul Gambill for his original guide):

1. Create a new Google Spreadsheet
2. Click on Tools -> Script Editor
3. Click Create Script for Spreadsheet
4. Delete the placeholder content and paste in the code for this script: https://gist.github.com/paulgambill/cacd19da95a1421d3164
5. Find var jsondata = UrlFetchApp.fetch(url); in the code, it’ll be around line 127.
6. Change var jsondata = UrlFetchApp.fetch(url); to the following

var jsondata = UrlFetchApp.fetch(url, {
headers: {
‘Authorization’: ‘Basic BASE64-ENCODED USERNAME/TOKEN:APIKEY or BASE64-ENCODED USERNAME:PASSWORD’
},
//muteHTTPExceptions:true
});

7. Rename the script to ImportJSON.gs and click the save button.

This sets up the function in Google Sheets so that you can parse API calls into individual columns and rows. The headers that we added in Step 6 authenticates your Zendesk access, so that Sheets can talk to your Zendesk account.

If you’re having trouble with this step, be sure that you’ve authorized the script to have access to your Google account. This script uses the UrlFetchApp class, which mandates specific scopes.

Import Your Data

With the ImportJSON function, you’re now able to bring data into your Google Sheet from Zendesk. In this case, we’re bringing in admin and agent data from their Users method, which looks something like this:

https://subdomain.zendesk.com/api/v2/users.json?role[]=agent&role[]=admin

Pasting that into your browser window will spit out a nigh-unreadable JSON text wall. In Google Sheets, you can parse that data with your new function. Enter the following into the first column of your sheet:

=ImportJSON(“https://subdomain.zendesk.com/api/v2/users.json?role[]=agent&role[]=admin",,"noInherit,noTruncate")

After a little processing, you’ll see a list of all of your agents and admins, along with all of their associated meta fields. To make this a little more useful, you can limit the fields that you’re pulling into your sheet with a slight modification. Try something like this:

=ImportJSON(“https://subdomain.zendesk.com/api/v2/users.json?role[]=agent&role[]=admin","/users/id,/users/name,/users/email,/users/role,/users/created_at,/users/last_login,/users/suspended,/users/role_type","noInherit,noTruncate")

These are the fields we used for the main user listing in our dashboard, minus a couple of custom fields that I’m omitting here. Keep in mind though: if you have custom fields, they can be brought in as well.

The last part of this equation are the optional parameters at the end of the formula. There are five in total:

• noInherit: This will prevent rows from inheriting their parent values. You probably won’t be using this at all, but it won’t hurt to keep in your formula.
• noTruncate: This will prevent truncated responses — responses that cut off for being too long.
• rawHeaders: By default, the script will attempt to prettify your column headers by removing common prefixes and putting everything in Title Case. Use rawHeaders to prevent that if they come out looking wrong.
• noHeaders: Or, if you don’t want headers at all, use the noHeaders option. This comes in handy if you need paginate your requests (we’ll talk about that in a minute).
• debugLocation: You’ll only use this if you want to see which rows and columns the script is sorting your values into.

Define Your Requirements

Now that you have a single list containing all of your agents and admins, you’ll need to develop a set of guidelines for what’s most important to surface. For us, we wanted to know a couple of things right off the bat:

• Who is suspended but hasn’t been downgraded to end-user?
• Who hasn’t logged into their account in two months or longer?
• Who hasn’t logged in since their account was created?
• Who hasn’t logged in at all?

After developing these four buckets, we were able to find 40 users whose access we could review and eventually revoke — either by removing their accounts entirely or by downgrading them to Light Agents.

Filter Your Data

To build those buckets, we created new sub-sheets to slice your first single source of truth into groups using the FILTER function. Here’s a couple of our formulas:

• FILTER(Agents!A:I,Agents!H:H = TRUE)

Displays anything from the Agents sheet, where the “suspended” column (H) is TRUE, indicating a suspended light agent, agent, or admin.

• FILTER(Agents!A:I,DATEVALUE(MID(Agents!F:F,1,10))<DATEVALUE(TODAY()-60),Agents!G:G=””)

Displays anything where the Last Login At date (F) has exceeded 60 days, excluding any Light Agents (G). We have to be a little tricky and use the MID function to discard the timezone and time information, so that we can convert the date into a value.

• FILTER(Agents!A:I,MID(Agents!F:F,1,10)=MID(Agents!D:D,1,10),Agents!G:G=””)

Displays anything where the Last Login At date (F) equals the Created At date (D), excluding any Light Agents (G).

• FILTER(Agents!A:I,Agents!F:F="",Agents!G:G="")

Displays anything where the Last Login At date (F) is blank, excluding any Light Agents (G).

Your sheet name and columns will depend upon your set-up, so be sure to customize those formulas to your own specifications. (And come up with your own!)

Extending This Work

After getting all of the data in place, we added some extra bells and whistles to make this data stand out more:

• Consider using Conditional Formatting to identify vendors and internal users that might never log in. We color those green to indicate good accounts that are serving their purpose (even if they aren’t logging in).
• A column showing Days Since Last Login can help identify your aging accounts. We use something like this: DATEVALUE(TODAY())-DATEVALUE(MID(F2,1,10)).
• A good measure of whether or not your agents are active is to pull a list of users and the number of tickets they’ve solved over a given period of months. The trouble is, GoodData will show you all of your agents, past and present. With your new Dashboard, pull that list from GoodData into a new sheet and use the VLOOKUP function to match against your API-generated list of agents.

Known Issues

If you’re managing a lot of users, there are a few quirks that you should be aware about.

Paginating API Calls

Zendesk’s Users API is limited to 100 responses per call. That means that if you have 101 users, you’ll need two API calls to return all of your users. Our rudimentary solution was to simply add more ImportJSON functions at the end of each list, specifying a new page each time, like this:

ImportJSON(“https://subdomain.zendesk.com/api/v2/users.json?role[]=agent&role[]=admin&page=2",,"noInherit,noTruncate,noHeaders")

Notice that we add the noHeaders parameter to make this into a streamlined list.

Unstructured JSON

This solution creates another issue: Zendesk may return user fields out of order if the first user in an API call doesn’t have a value for a particular field. For example, if the first person in your API call has never logged in, their Last Login At field will be blank and Zendesk will return your columns in a different order from the previous call.

For that reason, be sure to only use the fields that you absolutely need for your dashboard.

Future Work

The best way to get around the pagination issue would be to actually write a script that requests information from the Zendesk API and returns that data in one long JSON string, that ImportJSON would then read. Expect another post in the future once I’ve cracked that case.