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

Thanks for sharing your solution, Justin! 

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


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


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


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


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

0


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

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'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


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


Sign in to leave a comment.

Didn't find what you're looking for?

New post