URL link in export to CSV or XLS after "drill-in"
AnsweredI needed to generate reports for multiple customers containing a list of open tickets and export it to Excel to share with them. Such a list needs to have a ticket number (ID) field, but also a link to the ticket so that the customer can open it in our help center by clicking on the link.
In my Explore dashboard I have a query that can be drilled in
I created the text ticket field (Ticket URL) in Support Suite, which is getting populated by a trigger at the ticket creation. The following text is placed in the field by the trigger: https://userdomain.zendesk.com/agent/tickets/{{ticket.id}}.
When using my 'Ticket URL' field in a view in Support Suite and exporting such view to csv, this works well. Even though the view shows exact string (https://...../{{ticket.id}}, the placeholder "{{ticket.id}}" is getting replaced by the ticket number during export, and the links work perfectly well in the Excell cells.
However, when I use the "drill-in" feature in the Explore for the same ticket field, the placeholder "{{ticket.id}}" is not getting replaced by the ticket number, but rather just passed to the Excell cell as is. So, the exported xls or csv cells contain the same text for each ticket on the list, which is "https://userdomain.com/agent/tickets/{{ticket.id}}".
I understand that such result of a view export to csv may not be intended, but it is convenient and works well. I'd suggest to use the same routine for csv or xls export in Explore so that the community can benefit from it, the same way as if using export of a view.
I spent several hours chatting with a support agent to find out that the two Zendesk apps do not share the same functionality/ routines.
Shedding light or exchanging ideas on ways around is appreciated.
-
A Zendesk Help Center agent suggested to try a calculated attribute so I did.
I created the "Ticket Link" calculated attribute with the following line:
LINK("https://userdomain.zendesk.com/agent/tickets/"+[Ticket ID], [Ticket ID])
It works well within Explore resulting in a clickable hyperlink.
However, if I export such a report to xls or csv the "Ticket Link" cell is populated by "<a href="https://kautexinchelp.zendesk.com/agent/tickets/103037" target="_blank" style="color: inherit">103037</a>".
So it does not work for what we need it. I can only share exported xls files with customers, not the reports in Explore.
-
Roland,
Such concatenated cell doesn't seem to be clickable as a hyperlink in xls (even though it displays nicely as url text).
Is it in your case and if so how did you get it clickable?
Thanks for sharing your workaround ideas!
-
Hi Ronald,
You can copy entire concatenated column and "paste values (V)" right over it, or in a new column.
That results in clickable hyperlinks, but only after double-clicking on each cell once.
Unfortunately, it is too many manual steps as workaround for the feature that works well for exporting views in Support Suite. Zendesk could easily address this by adopting the same s/w routine for csv export.
Perhaps Dave can tell us when to expect this to be addressed.
Thank you!
-
So essentially you need a way to populate a custom ticket field with the ticket's Support URL without using the {{ticket.id}} placeholder.
One thought I had was you could use a webhook request to put the ticket's URL into your custom ticket URL field. However, as far as I can tell, there is no way to retrieve that value from the tickets API. But rather the only URL you'd be able to put into your custom URL field with a webhook and JSON payload would be the API URL like: https://userdomain.zendesk.com/api/v2/tickets/123456.json which would not allow a user to load the ticket in the Support UI when clicked through.
The other idea I can offer, which is something I've actually done quite a lot myself, is to use the CONCATENATE Excel/Google sheets function. You can drill in on Ticket ID and export a CSV with the list of ticket ID's. And then in the resulting CSV file you can add a column to the left of the column with the Ticket IDs and put the first part of the ticket's URL: https://userdomain.zendesk.com/agent/tickets/
And then in the column to the right of the ticket IDs use the CONCAT function to combine the strings in Columns A & B. The resulting string in Column C will be the entire ticket URL which is clickable in the sheet and will load the ticket in the Support UI.
I'm not sure if this completely addresses your scenario, and it is a bit of manual work, but this is a workaround I've used quite a bit myself in what sounds like a similar use case.
I wish Zendesk still had the ticket URL as a drill in value option. This was something I found quite handy in Insights. You could generate a report with ticket URLs which were clickable from directly within the reporting interface. I don't believe this functionality is present any longer in Explore as the closest I've seen is the Ticket ID value in the drill in/decompose options.
-
Thanks for the additional feedback and your proposed workaround, Rich!
-
Thank you Roland for your input.
-
Hi Serge Mucibabic!
I guess I hadn't ever tried this in Excel because I'm running into this same issue trying it out now.
It seems like even with the proper "autocorrect" settings Excel will only create a clickable link when typing the URL text directly in the text entry area at the top and submitting "Enter"/"Return" button. When I paste a full URL directly into an Excel sheet's cell the link isn't clickable. I have to paste it into the text entry thing at the top for it to render as a clickable link. So it seems this has more to do with the way Excel treats links than it does the constructed nature of the link.
I've been using Google Sheets at work more out of necessity than anything else but for this particular thing it seems Google Sheets is actually preferable. Maybe somebody with more experience in Excel can tell us if there's a way to get links in Excel to function more like they do in Google Sheets.
-
Insights used to have clickable links in download reports and I miss this feature all the time. I would love for any report with a ticket id, to download that report with the ticket ID as a functional hyperlink.
-
Serge Mucibabic Thanks for sharing the support response with the calculated attribute for clickable ticket links in the Explore interface. That will surely come in handy.
But I'm sorry to hear it hasn't helped you address your original issue of trying to generate an Excel sheet with clickable links. 🙁
Please sign in to leave a comment.
9 Comments