Parsing JSON in Google Sheets with Zendesk and Google Script
About a year ago, I wrote a guide on how I was leveraging the Zendesk API alongside Google Sheets to build a user dashboard, with the goal of getting my arms around a significant amount of data to make informed decisions on managing our licenses.
For the most part, using ImportJSON works well—it’s a low effort way of pulling data from any API’s GET endpoint and storing that data in a sheet for further parsing and filtering. But, there was a flaw: Zendesk’s API calls are paginated and limited to 100 results per page. That meant that we needed to add a new ImportJSON() formula every 100 rows to get all of our data.
As I noted at the end of that guide, there was a solution:
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.
This follow-up guide goes the extra step—not to feed a big JSON file into ImportJSON, but to write a script that does everything, including pagination. And, as an added bonus, is easier on your API rate limit.
What You Need
Just like the last guide, there are a few pre-requisites before you dive into Google Script Editor:
- You need an API key, so create one and save it somewhere secure. If you created one already from following the last guide, feel free to just use that one.
- You will eventually need to authorize access between your script and your Google sheet.
- It helps to have a separate code editor, just for clarity. I like Sublime Text, but BBEdit is a good macOS freeware option. Just avoid anything like Word, which likes to pepper your word processing with fun, programming unfriendly special characters.
Also, just like the last guide, this is geared towards Zendesk users. But, as long as your help center has an API with which you can GET request a list of agents, you can still build something similar to this guide.
Authentication
Before you can do anything, you need to authenticate your requests with your username and the API key noted above:
// Prepare authentication to Zendesk
var apiUsername = 'YOURUSERNAME';
var apiKey = 'YOURAPIKEY';
var encodedAuth = Utilities.base64Encode(apiUsername + "/token:" + apiKey)
var zdAPI = 'https://YOURZDSUBDOMAIN.zendesk.com/api/v2';
var zdAuth = {'Authorization': 'Basic ' + encodedAuth};Four of these five lines create variables that are used through out the code: your username and API key, the base64 encoded version of that (using Zendesk’s authentication guidelines), and the final header that will get inserted into your API call.
The last line, what we’re calling zdAPI, is the base of your API endpoint, the part of the URL that won’t change.
Headers
Now that your authentication is set, you’ll now need to create a variable to hold the headers that your API request requires. That includes authentication (handily captured in the zdAuth variable above), and a content type.
// Set headers
var zdOptions =
{
'headers': zdAuth,
'contentType': 'application/json'
};Initial Pagination
Zendesk’s API does a super helpful thing at the end of every API call: it either tells you the next page to run or it returns null to indicate that there are no additional pages:
Google Scripts is essentially an offshoot of JavaScript, which means that we can take advantage of this next_page behavior to use in a do while loop. Before we get there, though, let’s set one more global variable to represent our pages:
// Initial pagination
var zdPage = 1;We’ll use that variable in a second.
The Loop
Now for the fun part! We’ve got most of the building blocks in place, now we just need to write the function that contains the loop that I mentioned in the last section.
First, define the function and create an array that will contain your user information:
function getZDUsers() {
var users = Array();Now, within that function, start writing your loop. As noted before, we’re opting for a do while loop, since we know exactly when the pagination ends: when next_page reports null.
Start your loop by defining a few more variables. In this, url uses the previously defined zdAPI and zdPage variables along with the specific endpoint that you’re pulling data from. In this case, we’re using users.json, with a couple of extra role filters to only pull agents and admins.
// Loop through API pagination
do {
var url = zdAPI + "/users.json?role[]=admin&role[]=agent&page=" + zdPage;
var response = UrlFetchApp.fetch(url, zdOptions);
var result = JSON.parse(response.getContentText());
Logger.log(result.user)From there, we’re defining the API response using GAS’s URLFetchApp.fetch command (which requests data from the URL we just defined, along with the headers from Step 2).
Then, we’re parsing that response using JSON.parse, feeding the response into that command and defining it as result. Finally, it all gets logged. Still with me?
Now, we’re going to do two major actions. First, we’re going to increase the page by one. We already defined zdPage as equaling 1 earlier on, so we can use zdPage++; to increase that number by one.
// Increase page by one
zdPage++;The second action we’re taking is, for each item under the users object in our API call, to grab the specific keys and values that we want to collect into the array that we defined earlier:
// For each user, we take the ID, name, email, creation date, login date, and suspension status
result.users.forEach(function(user) {
var id = (user.hasOwnProperty("id")) ? user.id : "unknown";
var name = (user.hasOwnProperty("name")) ? user.name : "unknown";
var email = (user.hasOwnProperty("email")) ? user.email : "unknown";
var createdAt = (user.hasOwnProperty("created_at")) ? user.created_at : "unknown";
var lastLoginAt = (user.hasOwnProperty("last_login_at")) ? user.last_login_at : "unknown";
var suspended = (user.hasOwnProperty("suspended")) ? user.suspended : "unknown";
var role = (user.hasOwnProperty("role")) ? user.role : "unknown";
var customRoleId = (user.hasOwnProperty("custom_role_id")) ? user.custom_role_id : "unknown";In our case, we’re interested in eight different keys: ID, Name, Email Address, Creation Date, Last Login Date, Suspension Status, Role, and Custom Role ID. These directly correspond to the keys shown in the JSON response.
Finally, we’re pushing that information into the array that we defined at the top of the function.
users.push([id,name,email,createdAt,lastLoginAt,suspended,role,customRoleId]);
});And then we’ll keep running those two actions until next_page comes up as null:
} while (result.next_page !== null)Return Data
The last step here is to return that array once the do while loop has completed:
// Return all items added into array.
return users;
}Jump back into your Google Sheet and add your function—=getZDUsers()—to a cell. You should see your user data flood into the sheet. Neat! If you see “Unknown” for all of your cells, double-check your code and make sure everything matches the object and keys that you would otherwise get from your JSON response.
Pulling It All Together
Here’s what your script should look like, top to bottom:
// Prepare authentication to Zendesk
var apiUsername = 'YOURUSERNAME';
var apiKey = 'YOURAPIKEY';
var encodedAuth = Utilities.base64Encode(apiUsername + "/token:" + apiKey)
var zdAPI = 'https://YOURZDSUBDOMAIN.zendesk.com/api/v2';
var zdAuth = {'Authorization': 'Basic ' + encodedAuth};// Set headers
var zdOptions =
{
'headers': zdAuth,
'contentType': 'application/json'
};// Initial pagination
var zdPage = 1;function getZDUsers() {
var users = Array();// Loop through API pagination
do {
var url = zdAPI + "/users.json?role[]=admin&role[]=agent&page=" + zdPage;
var response = UrlFetchApp.fetch(url, zdOptions);
var result = JSON.parse(response.getContentText());
Logger.log(result.user)
// Increase page by one
zdPage++;
// For each user, we take the ID, name, email, creation date, login date, and suspension status
result.users.forEach(function(user) {
var id = (user.hasOwnProperty("id")) ? user.id : "unknown";
var name = (user.hasOwnProperty("name")) ? user.name : "unknown";
var email = (user.hasOwnProperty("email")) ? user.email : "unknown";
var createdAt = (user.hasOwnProperty("created_at")) ? user.created_at : "unknown";
var lastLoginAt = (user.hasOwnProperty("last_login_at")) ? user.last_login_at : "unknown";
var suspended = (user.hasOwnProperty("suspended")) ? user.suspended : "unknown";
var role = (user.hasOwnProperty("role")) ? user.role : "unknown";
var customRoleId = (user.hasOwnProperty("custom_role_id")) ? user.custom_role_id : "unknown";
users.push([id,name,email,createdAt,lastLoginAt,suspended,role,customRoleId]);
});
} while (result.next_page !== null)
// Return all items added into array.
return users;
}
Routinize With Triggers
You can let that function run every time that you open your sheet where the function is being called, but that might unnecessarily eat up your API rate limit. You can use triggers to protect your rate limit and automate your data pulls.
- Save your project in Google Apps Script, if you haven’t already, and give it a name.
- Go back to the GAS home page and find your newly created script.
- Click the three dots next to that script’s name and choose Triggers.
- Click the + Add Trigger button, choose your function name, and walk through the settings to set the frequency of how often you want this function to run.
We update this data daily, so that we’re not overwhelming the system with too many calls for data that doesn’t change all that often.
Extending This Work
The nice thing about writing your own script is that you can take the same concepts and extend it to other endpoints and other tools. In total, we’re using this method to pull data from five different sources, including multiple Zendesk endpoints.
For example, we wanted to grab the custom role information so that we don’t have to memorize what ID numbers go with which roles. That function looks like this:
function getZDRoles() {
var roles = Array();do {
var url = zdAPI + "/custom_roles.json" + zdPage;
var response = UrlFetchApp.fetch(url, zdOptions);
var result = JSON.parse(response.getContentText());
Logger.log(result.role)
zdPage++;
result.custom_roles.forEach(function(role) {
var roleID = (role.hasOwnProperty("id")) ? role.id : "unknown";
var roleName = (role.hasOwnProperty("name")) ? role.name : "unknown";
roles.push([roleID,roleName]);
});
} while (result.next_page !== null)
return roles;
}
There are a few things to notice:
- We don’t need to re-define the global variables like Auth and zdPage.
- We’re creating a new function in the same script beneath those global variables.
- The array name is different, as is the url, the object, and the forEach function.
So far, this has worked really well for us. But, with that said, this code will change based on the JSON response that you’re parsing! Every JSON response is different, so your script will need to change to suit.
