Recent searches


No recent searches

Problems and Incident counts with Google Apps Script - integration template & guide



Posted Feb 05, 2019

Hello everyone,

I have made a Google Sheet & Apps Script template to help pull and report on data that you may not be able to access using the normal Zendesk interface.

Currently it is designed to perform the following:

  1. Allow easy configuration of OAuth.
  2. Authenticate with Zendesk using OAuth.
  3. Export problem ticket information.
  4. Export current list of agents.
  5. Easily expandable with custom API endpoints with minimal code.

Future development depending on user need:

  1. More built functions to extract Zendesk data.
  2. Editing data within the sheet and submit changes back to Zendesk.


Getting the Zendesk Template spreadsheet

  1. You find the spreadsheet here:
    https://docs.google.com/spreadsheets/d/1rHv4I5l_7f_YSoyaM05zye7PTZBZoZ1XbuzLWcbB0BY
  2. Click on File > Make a Copy… and save it somewhere on your Google Drive for easy access.
  3. A Zendesk menu should appear in the top menu bar (after about 15-30 seconds on first load).
  4. If you’re feeling adventurous, click on Tools > Script editor to make sure the script is available.

Note: Make sure you are not logged in to multiple drives and are the primary/default user. Google scripts doesn’t play nice when you have multiple users logged in.


Configure and Authenticate with your Zendesk

If you have yet to authorise the script to run on your Google Drive you will need to before you connect it Zendesk.

Authorise the script

  1. Click on Zendesk > Configure.
  2. Select Continue if you receive the authorisation prompt, if you have already authorised the script, go to the next section.
  3. Choose the account in which you saved the spreadsheet.
  4. Click Allow to allow ZenKit to access your Google account.
    ZenKit is just the name of the script attached to the spreadsheet, you can view and edit all code by going to Tools > Script editor.
  5. The Settings menu should load.

Configuring your OAuth client

  1. Click on Zendesk > Configure from the spreadsheet to load the Settings menu.
  2. Type in your Zendesk subdomain (only the part before “.zendesk.com”).
  3. Enter a Unique Identifier such as: google_script_app
  4. Copy your Redirect URL and head over to your Zendesk agent interface.
  5. Go to Admin > Channels > API and select OAuth Clients.
  6. Click the little + icon to create a new client and fill out the form, entering the same Unique Identifier and Redirect URL from the spreadsheet earlier.
  7. Save your new client and copy the Secret that appears and head back to the spreadsheet.
  8. Paste the Secret token, and save.
  9. Be sure that both your Zendesk client settings and spreadsheet settings match.

Authenticating with Zendesk

Now that we have configured our app, we can now authenticate with Zendesk.

Hint: The spreadsheet will automatically prompt you for Authentication if you try and run any of the functions from within the Zendesk menu.

  1. From the Zendesk > Configure menu in the spreadsheet you should see a green Authenticate button, click it.
  2. Click Sign in on the Authenticate modal that appears. You will be redirected to your Zendesk instance to allow access using the client we previously configured.
  3. Click Allow and you will be granted access and taken back to the spreadsheet.

Hint: You can sign out by going to Zendesk > Configure > Authenticate > Sign Out


Using the spreadsheet

Now that we are all set, give it a go by selecting Zendesk > Get Problems; this will populate the Problems sheet with all of your current unsolved problem tickets and their information, including incident counts.

Hint: Be sure to empty the sheet before running the function again.


Limitations

This template is very basic and is intended to be used as a foundation for future development.

Here is a list of things that you may experience during the use of this app:

  1. Data is not cleared from the sheet before populating with new data.
  2. Renaming of sheets is not supported as this results in a new sheet being created.
  3. References may break or be inaccurate when the data in the sheets is replaced.
  4. Formatting may be removed or broken when new data is added to the sheet.


Further development

I would encourage you to take this as step further and see if you can extract your own data from the Zendesk API, with this script it is now super easy.

  1. Open Tools > Script editor to see the ZenKit source code.
  2. Look at the Code.gs file and take not of the functions getAgents() and getProblems().
  3. Copy one of the functions and change some of the variables to suit your desire.
  4. Reference the Support API documentation here: https://developer.zendesk.com/rest_api/docs/support/introduction

I would love to see what you come up with


0

21

21 comments

image avatar

Brett Bowser

Zendesk Community Manager

This is fantastic! Thanks for taking the time to create this and share with everyone :)

0


image avatar

Heather Rommel

Zendesk LuminaryThe Product Manager Whisperer - 2021Community Moderator

I agree, this is fantastic! 

0


Thanks! Happy to help.

0


This is amazing, thanks!

0


Hey Justin,

 

Great tool! I've taken a copy of this and I've been having a play around with it. I managed to create one to pull down organisations and support addresses. They work pretty good!

function onOpen(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menu = [
{name: "Configure", functionName: "zendeskConfigure"},
{name: "Get Agents", functionName: "getAgents"},
{name: "Get Problems", functionName: "getProblems"},
{name: "Get Organisations", functionName: "getOrganisations"},
{name: "Get Support Addresses", functionName: "getSupportAddresses"},
{name: "Get Triggers", functionName: "getTriggers"}
];
ss.addMenu("Zendesk", menu);
}

// Get all organisations
function getOrganisations() {
var resultId = 'organizations';
var sheet = 'Organizations';
var params = 'organizations.json';
var fields = [ "id", "name", "domain_names","organization_fields" ];
var result = getClient().request(params);

spreadsheetDBSave(result, resultId, sheet, fields);
}

// Get all Support addresses
function getSupportAddresses() {
var resultId = 'recipient_addresses';
var sheet = 'Support Addresses';
var params = 'recipient_addresses.json';
var fields = [ "id", "name", "email", "default", "forwarding_status" ];
var result = getClient().request(params);

spreadsheetDBSave(result, resultId, sheet, fields);
}

 

Could you think of a way to handle pagination on Organisations? I could only pull down the first 100 :(

 

Thanks!

0


Hi Gareth,

Apologies for the delayed reply. Pagination is something I really should have worked in, but I no longer have access to a zendesk account, and I'm waiting for my Zendesk developer account to be activated. If someone from Zendesk is reading this, hint hint.

In the interim something like the following should work if you add it to the Libs/Client.gs file.
This has not been tested and I am going off memory of how the api data was structured. 
I have also tried to keep this compatible with the spreadsheetDBSave helper function by passing in the resultId and structuring the results data accordingly. 

Client_.prototype.requestAll = function (uri, resultId) {
var userProperties = this.authPropertyStore_;

if (this.authService_) {
if (this.authService_.hasAccess()) {
var url = 'https://'
.concat(userProperties.getProperty('subdomain'), '.zendesk.com/api/v2/', uri);
var results = {};
results[resultId] = [];

while(url) {
var response = UrlFetchApp.fetch(url, {
headers: {
Authorization: 'Bearer ' + this.authService_.getAccessToken()
}
});
var result = JSON.parse(response.getContentText());
results[resultId].push(result[resultId]);
url = result.next_page;
}

return results;
} else {
this.zendeskAuth();
}
}
}

You should be able to call this new function like so:

var result = getClient().requestAll(params, resultId);

Hope this helps, let me know how you go.

Justin

 

0


image avatar

Brett Bowser

Zendesk Community Manager

Thanks for sharing this Justin!

I did want to follow-up with you as well regarding your developer account.

I checked internally and as far as I can tell your account should be up and running.

Can you confirm on your end?

0


Hi Brett - always happy to help out when I have the time!

I just logged in and you are correct; there is no longer a trial message and the account appears to be working correctly. Thank you for taking the time to look into this for me.

Justin

0


Hello everyone.

First of all, thank you so much for sharing this, this can be a great workaround.

I have three questions though:

- When trying to get the problem data, I end up with 100 rows only.
Can you guys let me know how to squeeze this limitation?

- When trying to Authenticate, nothing happens after clicking the Auth button.
A colleague from the IT tried himself and it worked straight away, but I can't figure why. Do I need to be the main Zendesk administrator to do that, or am I just doing something wrong?

- Additionally, I want to add a custom ticket field to be able to sort the results by products. Do I just need to add the field ID number in the var fields line?

var fields = [ "id", "subject", "status", "incident_count", "created_at", "updated_at", "FIELD ID" ];

Thanks in advance for your help !!!

Edouard

0


Justin,


Sorry, I gave that a try but couldn't get it to work for me. Did you ever get a chance to have another look at this after your developer account was approved?

0


@Edouard - You should be able to get more than 100 rows if you use the requestAll code that I provided in an earlier reply.

Regarding Authentication, make sure that the browser isn't blocking anything. I had some trouble using brave browser with the shields on. 

Custom fields can be added as you describe. Just make sure you use the correct syntax as per the Zendesk developer documentation. 

@Gareth - I completely forgot, I'm sorry. All caught up in my new job - I will try to find some time this week to get it sorted. 

0


Hello guys.

  • Regarding Authentication, make sure that the browser isn't blocking anything. I had some trouble using brave browser with the shields on.

Indeed the authenticate window couldn't pop out, I had to copy the button link and open it in a new tab.

  • @Edouard - You should be able to get more than 100 rows if you use the requestAll code that I provided in an earlier reply.

I think there is a problem when parsing the results. I end up with the following (see pic below)

I asked one of my colleague to look into it, but he couldn't figure what's wrong.

  • Custom fields can be added as you describe. Just make sure you use the correct syntax as per the Zendesk developer documentation. 

For each of my problem tickets, I need to add a custom field, here the product.
I tried to add this custom fields in the var fields, but without success.

I first tried with the ticket filed name:

And with the ticket filed ID as well:

But I always end with "undefined" values for each of my problem tickets...
Anyone know how shall I proceed?

Thanks again for your help.

Edouard

0


@Edouard - I'm not sure why you're getting undefined, does this happen when you use the default functions?

For custom fields I believe the format is something like "custom_field_123456789"

 

@All I have updated the Zendesk Template so that requesting problems now grabs all of them rather than just the first 100.

0


I have updated the Zendesk Template so that requesting problems now grabs all of them rather than just the first 100.

Yay this works Justin ! Thank you so much !

I'm not sure why you're getting undefined, does this happen when you use the default functions?
For custom fields I believe the format is something like "custom_field_123456789"

Well, I'm afraid it's not working either, I've got the same "undefined" values for each row, even though I tried with "custom_field_name" or with "custom_field_id"
I checked the API developer documentation and I could find any related info...

Another question @Justin Does your app allows to extract data to build reportings?
Or is it just about monitoring in Zendesk interface?

Thanks again for your precious help 

Edouard

0


@Edouard - I have updated the sheet so that it will handle the custom fields. The format "custom_field_123456789" should now work.

0


Justin, is there any address where I can send love right now?
You are amazing, thank you so much.

0


An email address?

I have released an application on the Zendesk market place called Problem Tracker. If you would like to show your support you could sign up for that temporarily. I would definitely love some feedback on the app.

0


Hello,

Is there any plan to add a column for this in zendesk views, or a simpler workaround than a google apps script integration?

Don't get me wrong, many many thanks to Justin, who has spent a lot of time and was nice enough to share, but I simply want to make sure I am not missing something here. All we want to do is look in our view and see which problems actually have incidents attached, and if possible how many incidents. We have a small volume of tickets, and and we separate our views into Tier 1, 2, & 3 so that by the time tickets get to T3 there are so few that implementing this solution/workaround is less likely than just clicking on each ticket individually to see if and how many incidents are attached

 

0


image avatar

Devan La Spisa

Zendesk Community Manager

Hello Kevin,

At this time, there is anything we can announce that pertains to your request and our current development roadmap. However, I would recommend sharing your feedback on our Support Feedback forum so our developers can consider your suggestion for potential future updates.

Support Product Feedback

Best regards. 

0


Hey Kevin - have developed a similar app that works from the sidebar here: https://www.zendesk.com/apps/support/problem-tracker/

Would love some feedback.

Justin

0


image avatar

Nicole Saunders

Zendesk Community Manager

Thanks for sharing your solution, Justin! 

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post