ZohoDesk & ZohoAnalytics: Display Ticket Attachments in Analytics Report

: Joel Lipman; Zoho; 05 December 2023; Hits: 946

What?
A quick article with the code to download image attachments from a Zoho Ticket and then to upload the attachment to Zoho Analytics.

Why?
First of all, we couldn't find how to sync attachments from ZohoDesk to ZohoAnalytics using the integration. Other tables will sync but we couldn't find attachments, so our solution here is to create a data table and then to push the attachment from ZohoDesk to ZohoAnalytics using the API.

The use-case here is that a client wants customers to submit ZohoDesk tickets and upload photos of the problematic product to the ticket. They then want an Analytics report to show these photos.

How?
The following is simply the steps to create a data table in ZohoAnalytics, then the code to list a ticket's attachments and push them to the ZohoAnalytics data table.

Check that ZohoDesk is sync'ing with your ZohoAnalytics:
1. Login to ZohoAnalytics as an Admin with access to ZohoDesk
2. Make a note of the workspace name that your ZohoDesk will sync with.
3. Select Data Sources in the left sidebar
4. Add Data Sources > Select Zoho Desk
5. Do "Next" and select the Zoho Desk Portal
6. Ensure that "Tickets" is selected (I'd tick most of the fields this contains)
Create a data table in ZohoAnalytics to hold the attachments
1. Create an Excel spreadsheet or CSV file with the headers: Ticket ID, Attachment ID, Image URL, Created Time, Modified Time
2. Add a dummy row with data that is expected to be in the rows (ZohoAnalytics may have issues loading a file with only headers)
3. Click on "Create" then "New Table/Import Data" > Select "Files" (the one with the Excel icon)
4. Give the table name "Attachments" > Select your Excel file > Next
Get the ZohoDesk Org ID:
1. Login to ZohoDesk > Setup > Developer Space > API
2. Scroll to the bottom of the page and you should see the 11-digit number "OrgId" there
3. Note this down for later use
Get the ZohoAnalytics Org ID:
1. Login to ZohoAnalytics and ensure you are at the "Home" level
2. Click on the cog icon in the top-right for "Settings"
3. You should be in the "Organization Settings"
4. Click on any page in the left sidebar (eg. Plan Details), your Org ID is in the URL as the last number after the slash (eg. "20012345678")
5. Note this down for later use
Create a connection to Zoho Analytics:
1. ZohoDesk > Setup > Developer Space > Connections > Create Connection
2. Select Zoho Analytics and give the required scopes (I've cheated and gone for ZohoAnalytics.fullaccess.all)
3. Note the connection link name (mine is called "zanalytics")
Create a Ticket workflow:
1. ZohoDesk > Setup > Automation > Workflows
2. Click on "Create Rule"
3. Select "Tickets" under "Module"
4. for the Rule Name, I called mine "Tickets - On Create or Edit"
5. Execute On: Create, Edit, Customer Reply
6. No Criteria
7. Under 4. Actions, I click on "All Actions" and select "Custom Functions"
8. Click on the Plus icon alongside this, scroll down to "Custom Functions" and click on "New"
9. I give it the function name fn_Tickets_OnCreateEdit
10. I click on "Edit Arguments" and give it the same function name as method name, then I click under "Name" and enter p_TicketID with the value "Ticket Id" and click on "Done"
11. Then I give it the following code:
  copyrawstyler
```
/* *******************************************************************************
Function:       void fn_Tickets_OnCreateEdit(int p_Ticket)
Label:          Fn - Tickets - On Create or Edit
Trigger:        Off a workflow when a ticket is created or modified
Purpose:		Pushes the tickets attachments to Zoho Analytics
Inputs:         -
Outputs:        -
Scope(s):		

Date Created:   2023-11-29 (Joel Lipman)
                - Initial release
Date Modified:	???
                - ???

******************************************************************************* */
l_Attachments = List();
//
// the zoho desk org ID noted earlier
v_DeskOrgID = 12345678901;
//
// the zoho analytics org ID noted earlier
v_AnalyticsOrgID = 23456789012;
//
// let's get the ticket details
r_TicketDetails = zoho.desk.getRecordById(v_DeskOrgID,"tickets",p_TicketID);
r_Attachments = invokeurl
[
	url :"https://desk.zoho.eu/api/v1/tickets/" + p_TicketID + "/attachments"
	type :GET
	connection:"zdesk"
];
l_AttachmentDetails = ifnull(r_Attachments.get("data"),{});
for each r_Attachment in l_AttachmentDetails
{
	m_Attachment = r_Attachment.toMap();
	v_Endpoint = m_Attachment.get("href") + "?orgId=" + v_DeskOrgID;
	info v_Endpoint;
	//
	// build data row to send
	m_Data = Map();
	m_Data.put("Ticket ID", p_TicketID);
	m_Data.put("Attachment ID", m_Attachment.get("id"));
	m_Data.put("Image URL", v_Endpoint);
	//
	// first parameter is the workspace name containing the "Attachments" data table
	r_Analytics = zoho.reports.createRow("My_Desk_Workspace", "Attachments", m_Data, "zanalytics");
	info r_Analytics;
	//
	// add to analytics
	m_Header = Map();
	m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
	m_Columns = Map();
	m_Columns.put("columns", m_Data);
	m_Params = Map();
	m_Params.put("CONFIG", m_Columns);
	//
	// in analytics, browse to the "Attachments" table and note the URL IDs after workspace and view
	v_WorkspaceID = "123456000000123456";
	v_TableID = "12345600000123457";
	v_Endpoint2 = "https://analyticsapi.zoho.eu/restapi/v2/workspaces/"+v_WorkspaceID+"/views/"+v_TableID+"/rows";
	r_AddRow = invokeurl
    [
    	url: v_Endpoint2
    	type: POST
    	parameters: m_Params.toString()
		headers: m_Header
    	connection: "zanalytics"
    ];
	info r_AddRow;
}
```
  1. /* *******************************************************************************
  2. Function: void fn_Tickets_OnCreateEdit(int p_Ticket)
  3. Label: Fn - Tickets - On Create or Edit
  4. Trigger: Off a workflow when a ticket is created or modified
  5. Purpose: Pushes the tickets attachments to Zoho Analytics
  6. Inputs: -
  7. Outputs: -
  8. Scope(s):
  10. Date Created: 2023-11-29 (Joel Lipman)
  11. - Initial release
  12. Date Modified: ???
  13. - ???
  15. ******************************************************************************* */
  16. l_Attachments = List();
  17. //
  18. // the zoho desk org ID noted earlier
  19. v_DeskOrgID = 12345678901;
  20. //
  21. // the zoho analytics org ID noted earlier
  22. v_AnalyticsOrgID = 23456789012;
  23. //
  24. // let's get the ticket details
  25. r_TicketDetails = zoho.desk.getRecordById(v_DeskOrgID,"tickets",p_TicketID);
  26. r_Attachments = invokeUrl
  27. [
  28. url :"https://desk.zoho.eu/api/v1/tickets/" + p_TicketID + "/attachments"
  29. type :GET
  30. connection:"zdesk"
  31. ];
  32. l_AttachmentDetails = ifnull(r_Attachments.get("data"),{});
  33. for each r_Attachment in l_AttachmentDetails
  34. {
  35. m_Attachment = r_Attachment.toMap();
  36. v_Endpoint = m_Attachment.get("href") + "?orgId=" + v_DeskOrgID;
  37. info v_Endpoint;
  38. //
  39. // build data row to send
  40. m_Data = Map();
  41. m_Data.put("Ticket ID", p_TicketID);
  42. m_Data.put("Attachment ID", m_Attachment.get("id"));
  43. m_Data.put("Image URL", v_Endpoint);
  44. //
  45. // first parameter is the workspace name containing the "Attachments" data table
  46. r_Analytics = zoho.reports.createRow("My_Desk_Workspace", "Attachments", m_Data, "zanalytics");
  47. info r_Analytics;
  48. //
  49. // add to analytics
  50. m_Header = Map();
  51. m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
  52. m_Columns = Map();
  53. m_Columns.put("columns", m_Data);
  54. m_Params = Map();
  55. m_Params.put("CONFIG", m_Columns);
  56. //
  57. // in analytics, browse to the "Attachments" table and note the URL IDs after workspace and view
  58. v_WorkspaceID = "123456000000123456";
  59. v_TableID = "12345600000123457";
  60. v_Endpoint2 = "https://analyticsapi.zoho.eu/restapi/v2/workspaces/"+v_WorkspaceID+"/views/"+v_TableID+"/rows";
  61. r_AddRow = invokeUrl
  62. [
  63. url: v_Endpoint2
  64. type: POST
  65. parameters: m_Params.toString()
  66. headers: m_Header
  67. connection: "zanalytics"
  68. ];
  69. info r_AddRow;
  70. }
12. Save Script and Save the workflow
Change the field type to image in the ZohoAnalytics report:
1. Go to the Attachments Data Table in Zoho Analytics
2. Right-click on the "Image URL" column and select "Change Data Type" > select under Data Type: "URL"
3. Right-click on the "Image URL" column again and select "Format Column" > Select under Display As: "Image"
4. You can now specify the size of the image as well (16x16 is a tad small).

Caveat(s):

A bit work needs to be done to the code above to prevent attachments from going in twice. You would need to record the attachment ID and only push it to Analytics if Analytics doesn't have that attachment ID in its table already... or updates it if it does.
You might want to add to the code to only push valid image file types as files.
The report will work for Zoho Users... didn't really test on permissions and sharing the report, I was logged in as the super admin on both ZohoDesk and ZohoAnalytics for the above demo.
Invalid OAuth Scope: Might be an invalid scope, or the TLD (datacenter) is incorrect. The above demo uses the EU datacenter (zoho.eu) rather than the US datacenter (zoho.com).

Update 2023:
This solution has been superceded by instead directing the ZohoTicket Attachment trigger (internal webhook in ZohoDesk rather than on Ticket Edit) and creates a row in a ZohoCreator report instead of ZohoAnalytics. The reason being is that the client needed to export the report and send it to suppliers; in ZohoAnalytics, the photos will not display (they appear as a URL) if the viewer is not a Zoho user belonging to the organization. Even in PDF format, the image is displayed as a URL. ZohoCreator was the only solution that produces a report that can be displayed to a non-Zoho user with photos displaying; also supported a PDF export with images instead of URLs.

Category: Zoho :: Article: 864