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:

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

Future development depending on user need:

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

Getting the Zendesk Template spreadsheet

You find the spreadsheet here:
https://docs.google.com/spreadsheets/d/1rHv4I5l_7f_YSoyaM05zye7PTZBZoZ1XbuzLWcbB0BY
Click on File > Make a Copy… and save it somewhere on your Google Drive for easy access.
A Zendesk menu should appear in the top menu bar (after about 15-30 seconds on first load).
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

Click on Zendesk > Configure.
Select Continue if you receive the authorisation prompt, if you have already authorised the script, go to the next section.
Choose the account in which you saved the spreadsheet.
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.
The Settings menu should load.

Configuring your OAuth client

Click on Zendesk > Configure from the spreadsheet to load the Settings menu.
Type in your Zendesk subdomain (only the part before “.zendesk.com”).
Enter a Unique Identifier such as: google_script_app
Copy your Redirect URL and head over to your Zendesk agent interface.
Go to Admin > Channels > API and select OAuth Clients.
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.
Save your new client and copy the Secret that appears and head back to the spreadsheet.
Paste the Secret token, and save.
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.

From the Zendesk > Configure menu in the spreadsheet you should see a green Authenticate button, click it.
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.
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:

Data is not cleared from the sheet before populating with new data.
Renaming of sheets is not supported as this results in a new sheet being created.
References may break or be inaccurate when the data in the sheets is replaced.
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.

Open Tools > Script editor to see the ZenKit source code.
Look at the Code.gs file and take not of the functions getAgents() and getProblems().
Copy one of the functions and change some of the variables to suit your desire.
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