Round robin ticket assignment
By Michael Stone of BetterCloud and Bill French of iPad CTO
Overview
We worked with Bill French of iPad CTO to develop this Zendesk Round Robin Ticket Assignment application. This application was made using Google Apps Script and is built entirely in Google Docs. You can run this script as long as you have an active Gmail or Google Apps account.
This script is run off of a simple spreadsheet (linked below) which is used to manage your support team and the tickets that will be assigned to them. Assignments are made based on tags. We recommend you use Triggers to have a tag automatically applied to each ticket as it is submitted.
CLICK HERE TO CREATE A ZENDESK ROUND ROBIN APP
Steps
After you download the Round Robin app, follow these steps.
Create a trigger
Create a Trigger based on your criteria to add a Tag when the ticket is created.
This app will assign your tickets out based on this tag. It is very important that this Tag matches the tag placed in the Notes field of your column headers.
Set up the apps script
On the spreadsheet, navigate to: Tools > Script Editor.
- Set the [Spreadsheet ID] value in Line 10 to the ID of your spreadsheet.
This can be found in the URL of your spreadsheet after ‘http://…...spreadsheet/ccc?key=’ and before the ‘&’
CommunityTip - Google spreadsheets have a new URL format. There isn't any ampersand (&) symbol in the Google Docs / now Google Drive URL. Instead, just grab the info after https://docs.google.com/spreadsheet/ccc?key= and before #gid=0 (see attached screenshot - I obscured my spreadsheet ID).
- Set the [subdomain] value in Line 23 to your Zendesk subdomain.
For example if your Zendesk domain is: subdomain.zendesk.com, the value here would be: subdomain.
- Set the [username] value in Line 24 to your Zendesk username.
For example: mike@bettercloud.com
- Set the [Zendesk API KEY] value in Line 25 to your current Zendesk API Key.
This can be found by clicking the Manage icon then selecting Channels > API.
Community Tip - Save the API setting! If you don't already have Token Access enabled in Manage > Channels > API, you need to check that box AND CLICK THE SAVE BUTTON. I checked the box, copied the API token and went on my merry way only to find out that nothing was happening... until I went back and double-checked everything and figured out that I hadn't clicked the save button on that settings page. Also note that Zendesk issues a new API token if and when you enable it for the second time.
Set up automatic updates
To automate the process of assigning tickets in a timely fashion, you must use a trigger in Google Docs to run the makeAssignment() function every five minutes or perhaps at intervals suitable to the flow of the tickets being received.
- Select the Tools | Script Editor... from the sheet, and then select Resources | Current project’s triggers
- Run: makeAssignments Events: Time-driven: minutes timer > Every minute
Sample use case
We currently have four different Forms that End-Users can submit tickets under: Question, Problem, Account Inquiry and Other. We have set-up a Trigger that applies the tags ‘form_question’, ‘form_problem’, ‘form_account’, and ‘form_other’ when a ticket is submitted. Any users with an ‘x’ next to their name will be assigned the next ticket that comes in with that tag (as long as they are next in the queue).
You can use this script with other tags as well, it does not need to be used with Forms in particular. However, these tags must be applied to the ticket once it is created in order for the script to run properly.
Community Revisions
11/29/17
Rob Baker was kind enough to share his version of the Zendesk Round Robin Script titled 'Zendesk Roulette'. Here is a list of the new features and a link to the updated script:
| * Added Properties so that each function could be called independently | |
| * Added a main function so script can be manually run | |
| * Added search filter changes to limit results to only tags of interest and oldest tickets first | |
| to avoid hitting script processing time limits | |
| * Limited assignment to a maximum of 10 tickets per pass to avoid the entire queue from being assigned at once | |
| * Fixed an issue where the last agent assigned would continue to be pushed tickets if there were no active agents | |
| including the previously assigned agent | |
| Variables that need to be populated prior to use | |
| * [YourSheetID] | |
| * [YourSubDomain] | |
| * [YourUserName] | |
| * [YourToken] | |
|
* [YourTags] https://github.com/grnhse/zdr/blob/master/zendeskMakeAssignments.gs |
-
Hey Michael,
As I understand it (or atleast how I've been using it) is "Queue" marks the last agent to be assigned something. The next assignment will go to the user after the user marked with X in "Queue" and once assigned will move the X to that agent.
Active agents should be determined by the fields after "Queue"
Hope this helps
-
Hi
Our round robin has stopped working it's giving the below error, I am unsure how to resolve this so now our calls are not assigning correctly.
- File: zendeskMakeAssignments Line: 278
- File: zendeskMakeAssignments Line: 286
-
Hey Nadia,
Feel free to send me an email: mike@bettercloud.com I'm happy to take a quick look.
Based on those errors, Apps Script is saying the method we are trying to use is deprecated. However. if this was the case, I think everyone would likely be experiencing the same issue.
Mike
-
Running into this problem...
Execution failed: Cannot find method getRange(number,number,number,(class)). (line 286, file "zendeskMakeAssignments")
And here's what I have at line 286:
// get dyanamic range into array
var aFormTypes = agentSheet.getRange(1, 5, 1, dynamicRangeMax).getComments();I'm not sure what's incorrect, and help would be much appreciated! :)
-
Hey Elizabeth,
Sorry to hear you ran into this issue. Shoot me an email: mike@bettercloud.com I'm happy to take a quick look.
Right off the bat, is it possible 'agentSheet' was renamed or redefined in your script?
Mike
-
@michael, looks like the URL format for the spreadsheet may have changed again. Can you confirm what information I need to be putting in line 10? I've tried all kindsa combos, no dice.
Thanks!
-
Could please update this support article with the setup steps?
I updated the lines indicated in your instructions and I am getting messages for deprecated methods at lines 33, 278, 286.
Also the spreadsheet ID syntax looks different:
https://docs.google.com/spreadsheets/d/ +ID+ /edit#gid=0
Paul
-
I might be missing something, but I don't see where you are getting the values for your agents User IDs. Am I able to add their login ID (email address) here? Or, is there somewhere I should gather an Agent ID number?
-
Hey Issac!
You can find the user ID in the URL for each individual user profile:
If you're on the Professional or Enterprise plan you can do a user data export, which includes the user id as well.
If you have a lot of agents, and you're not on Professional or higher, your other option is to export the info using our API.
-
Hi Michael ,
I added more columns to the script to add more forms and here is the error i am getting.
your insight is highly appreciated .Thanks a lot -
@Michael, I have been using this script, and it is awesome! Thank you for providing it.
I am running into 2 issues with it though:
First, I have an 'rr' tag setup for new tickets that come in during business hours. For some reason, zen desk doesn't always respect my business hours rule added to the trigger. ZD tags it anyway and then the ticket gets assigned by the auto assigner.
To get around this, at the end of the day, I turn the trigger off so tickets are not tagged. But still sometimes the app assigns a ticket that hasn't been tagged. The only way I have found to stop it completely is to temporarily put the google document in the trash.
Has anyone else run into this? Or, is there a better way to only have it assign tickets during certain times of day?
-
Hey Isaac,
I had this same problem as well. I was able to solve this by setting the last line under a legit agent (so blank name, User ID etc) to "YES" and put the queue X on this line. Seems to work like a charm, so far. No need to trash the doc.
-
That's great, Eva. Thanks for sharing your solution!
Let us know if that does the trick Isaac.
-
Michael, this is quite possibly the coolest thing I have come across so far on the Community to date. This is perfect for our use case to round robin assign aging tickets in our Unassigned queue. I made a number of changes to the script to compensate for this use case as well as stuffing the global vars in Properties so each function could be called independently. Finally, I changed the scope of the search to only look for tickets with tags of interest and limit the number that can be assigned to 10 per pass to avoid a situation where a huge number of tickets could be auto-assigned all at once and when only a small number of agents are active. This saved a TON of effort writing a full blown we app and front-end to get us out of a tight spot operationally. Thanks so much for sharing with the Community!
-
Hey all,
Rob Baker was kind enough to share his version of the Zendesk Round Robin Script titled 'Zendesk Roulette (ZDR)'. Here is a list of the new features and a link to the updated script:
* Added Properties so that each function could be called independently * Added a main function so script can be manually run * Added search filter changes to limit results to only tags of interest and oldest tickets first to avoid hitting script processing time limits * Limited assignment to a maximum of 10 tickets per pass to avoid the entire queue from being assigned at once * Fixed an issue where the last agent assigned would continue to be pushed tickets if there were no active agents including the previously assigned agent Variables that need to be populated prior to use * [YourSheetID] * [YourSubDomain] * [YourUserName] * [YourToken] * [YourTags]
https://github.com/grnhse/zdr/blob/master/zendeskMakeAssignments.gs
Let me (or Rob) know if you have any questions.
-Mike
-
Wow, that's awesome. Thanks for the update Mike and Rob!
Mike, thanks for adding info about the update to the body of the post as well!
This tip is such an oldie but a goodie. Glad to see it's still so popular and useful to the community!
-
@Michael: If I got this right, this can only be used with Ticket Forms, the submitter must choose 1 between 4 types, right ? It could not be used with email, am I correct?
Thank you ?
-
Hey Kien!
If I understand this tip correctly, the app assigned tickets out based on ticket tags. Based on that, and this quote from the tip: "You can use this script with other tags as well, it does not need to be used with Forms in particular. However, these tags must be applied to the ticket once it is created in order for the script to run properly" it looks like you can use this app for any ticket, regardless of the channel, as long as a tag is applied upon ticket creation (you can do this with Triggers).
Let us know how it goes!
-
Ya, tags are just there for flexibility. You could do something similar to how chat works for example and have tags that represent skills based routing, or product based routing... Our primary use case is around unassigned ticket aging to improve our initial response time and consistency. We have an automation that applies a tag to every unassigned ticket that ages beyond a certain number of hours. We set the trigger in the Google script to run hourly, so in the next hourly sweep every "new" ticket with that applied tag will be assigned based on the agents, availability, and tag considerations populated in the associated Google sheet. There is no dependency on ticket source (aka channel, aka via).
-
Thanks for jumping in with some suggestions, Rob!
-
Thanks for the very nice job. I was able to get Rob Baker's version working.
A few tips:
- in getSortedAgents(): the OFFSET should be 1, not 2 (header row has index 0)
- in fetchOpenTickets(): adapt the query JSON to adapt (or remove) the tags-filtering (tags:...) when searching for tickets to assign
- the ticket matching works differently than in the original version: Baker's version matches hardcoded tags rather than tags per sheet column. Make sure to check ticketTagsMatchAgentTags().
- in logTicket() I added two lines to make sure that the loggin sheet doesn't continue to grow indefinitely
const logRowsToKeep = 100; //just after the isDebugMode test
...
logSheet.deleteRow(logRowsToKeep); //add the end of the function
- coding could be optimized, as I see for instance column indexes and column names being re-used in several methods (could be out-factored to configuration). When working with more than 3 tag levels (none/any agent, ace agents, veteran agents), like in the original version, the columns could use some re-ordering too. -
And two more tips:
- make sure that the 'working hours' column is filled with text values.
When entering eg 7-17, Google Sheets may interpret it as a date value (numeric).
Enter the value with single quote prefix to make it text (i.e. '7-17).
- bookmark the search-query, so you can execute it manually to see if it finds any tickets -
Thanks Tim for that helpful feedback! This is a work in progress for us so I want to add some color and context.
First, the original version of the script published publicly and referenced by Michael's post on Nov 29th can be found here. https://github.com/grnhse/zdr/blob/4ce1b37c0f66f8b17d8dea58692e1d8480f690dd/zendeskMakeAssignments.gs
The problem we ran in to and which Michael and I exchanged a few emails on, was the shift coverage. Essentially we wanted agents to be active/inactive based on their assigned shift with an override allowed if they were ooo, underwater, etc. We added shifts in this version which is also our current production version: https://github.com/grnhse/zdr/blob/d17bedbb505222f0179f0897b711c1db8186838a/zendeskMakeAssignments.gs
Tim is correct in that the addition of the shifts in the rightmost column breaks being able to add more tags. This was not a concern in the near term for us because we are only using one tag. But for others this could pose a problem for things like skills-based-routing implementations of the round robin ticket assignment.
-
Also, the latest version of the script can be found at https://github.com/grnhse/zdr/blob/master/zendeskMakeAssignments.gs and will always have the latest work in progress. This includes the version that Tim is commenting on above at https://github.com/grnhse/zdr/blob/36bd940007ddfd2d9fac2e15ee54c68dee37de31/zendeskMakeAssignments.gs with the following feature updates in progress:
1) checking the number of current open tickets for each agent and not allowing push-based assignment over a specified limit
2) a weighting methodology to a) favor ticket assignments for agents not on active chat shifts and b) assign based on agents that have the fewest open tickets first.
In its current implementation state this does not come without some drawbacks. For instance, it is a great deal less predictable who will get the next ticket (the queue marker has been disabled and the order does not go top-down), the max does not seem to be enforced on each iteration assigning outstanding tickets, and the additional column used to track the number of open tickets per agent needs to be refreshed with a different trigger if is it going to be used by a shift manager or anyone else eyeballing the sheet to keep it up to date in between passes of the assignment script itself.
Hopefully this provides some additional insight in to things, and I will be sure to pass Tim's feedback to the Dev team that I handed the script off to. Thanks for your continued interest, use, and feedback!
-
Thanks for the update and links, Rob and reminding us all on the context.
If you permit me, I'll try a Git pull-request to fetch the latest code and interact more directly with your developers.
In general, I think the code should distinguish more between skill/tag-based, load-based and shift/availability-based assigning.In v2AssignTickets(), I added
if(!isReadonly()) {
setAssignedTicketsPerAgent();
}just before the for-loop, to make sure the nb of tickets per agent is up-to-date (before the decision is made to which agent a new ticket is assigned).
The setAssignedTicketsPerAgent() inside the for-loop should update just the affected agent (agent to which a ticket was assigned), in order to save on the number of API calls (when iterating through many tickets).
-
@Michael, Thanks for the tip.
It worked a few times and now throws an error saying "Maximum execution time exceeded".
Can you please help with this?
Thanks.
-
One note on something that tripped me up - changing the column name in the spreadsheet doesn't work - you need to click the little purple triangle to change the actual header to a valid tag or form.
-
Thanks for pointing that out, Neil!
-
Hi Everyone!
This script is awesome, and I really need to use it, but I can't get it to work. :(
UPDATE - Got it to work, now I just need to check which version to use. :)
Thanks everyone for your awesome work.
-
Glad to hear you got it working, Bruno!
Please sign in to leave a comment.
111 Comments