[Elizabeth Tachjian CPA]

Hi,

Any ideas on how to connect Zoho Desk to Power bi Desktop?  Not the same as Zoho CRM.

Cheers
Elizabeth

——————————
Elizabeth Tachjian CPA
Business Intelligence Dashboards Developer
Analytics Realtime
Melbourne [VIC]
0417457054
——————————

[Tony McGovern]

Elizabeth — Zoho products allow applications to authenticate to their APIs using OAuth 2.0. This means you’ll have to build a custom connector in Power BI.

My company uses Zoho products and I’ve built custom connectors in both Flow and Power BI to our data in Zoho Books, Zoho CRM, and Zoho Campaigns. I quickly looked at the API documentation for Zoho Desk and that API authenticates the same way as other Zoho products. And these APIs implement the “authorization code” grant type, which is a way for your application to request tokens from the authorization server.

Here’s a sample custom connector for the DataWorld API, which also uses OAuth 2.0 authentication with an authorization code grant type. This other custom connector connects to Dropbox, which again, also uses OAuth 2.0 with the same grant type. (Both examples were created by developers on the Power BI product team.)

You can use these examples as templates for your Power BI custom connector to Zoho Desk.

Let me know if you run into any problems; I’m happy to help.??

——————————
Tony McGovern
co-Founder & Data Scientist
em•data Inc.
tony@emdata.ai
(202) 594-6194
——————————
——————————————-

[Elizabeth Tachjian CPA]

Hi Tony,

How long did it take you to develop the custom connector?  Will I need an ODBC driver to connect the custom connector with Power BI desktop.

Thank you for time.

Elizabeth Tachjian
Analytics Realtime——————————————-

[Tony McGovern]

Elizabeth — You don’t need an ODBC driver to connect the custom connector. With Zoho products, you just need to call the API with Web.Contents.

If you’re not familiar with custom connectors, particularly those that authenticate with OAuth 2.0, prepare to spend a few days or even weeks figuring it out. Luckily, this GitHub custom connector tutorial walks you through the basics. And once you get the hang of that, it’s not that challenging to swap out the GitHub authentication parameters with Zoho parameters. In other words, the flow to authenticate to both the GitHub and Zoho APIs are similar.

Note: if you’ve not created a custom connector before, you’ll need to read and understand the prerequisites first.

If you start down this path, I’m more than happy to help you through it. Just give a shout.

——————————
Tony McGovern
co-Founder & Data Scientist
Emdata Inc.
——————————
——————————————-

[Diego Carballo]

Hello Tony, I went down this path using the DropBox example as an example, and I paste below what I have. I am stuck getting Login Failed , can’t parse Query String. Any ideas what I could be doing wrong ? Regards,

=====================
// This file contains your Data Connector logic
section ZohoDesk;

// Data Source UI publishing description
ZohoDesk.Publish = [
Beta = true,
Category = “Other”,
ButtonText = { Extension.LoadString(“ButtonTitle”), Extension.LoadString(“ButtonHelp”) },
LearnMoreUrl = “https://powerbi.microsoft.com/”,
SourceImage = ZohoDesk.Icons,
SourceTypeImage = ZohoDesk.Icons
];

ZohoDesk.Icons = [
Icon16 = { Extension.Contents(“ZohoDesk16.png”), Extension.Contents(“ZohoDesk20.png”), Extension.Contents(“ZohoDesk24.png”), Extension.Contents(“ZohoDesk32.png”) },
Icon32 = { Extension.Contents(“ZohoDesk32.png”), Extension.Contents(“ZohoDesk40.png”), Extension.Contents(“ZohoDesk48.png”), Extension.Contents(“ZohoDesk64.png”) }
];

client_application = Expression.Evaluate(Text.FromBinary(Extension.Contents(“client_application”)));
windowWidth = 1200;
windowHeight = 1000;

TokenMethod = (code, grant_type) =>
let
Response = Web.Contents(“https://accounts.zoho.com/oauth/v2/token”, [
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_application[ClientId],
client_secret = client_application[ClientSecret],
grant_type = grant_type,
code = code,
redirect_uri = client_application[CallbackUrl]
]))
]),
Parts = Json.Document(Response)
in
Parts;

StartLogin = (resourceUrl, state, display) =>
let
AuthorizeUrl = “https://accounts.zoho.com/oauth/v2/auth?” & Uri.BuildQueryString([
client_id = client_application[ClientId],
response_type = “code”,
state = state,
scope = “Desk.tickets.READ”,
redirect_uri = client_application[CallbackUrl]])
in
[
LoginUri = AuthorizeUrl,
CallbackUri = client_application[CallbackUrl],
WindowHeight = windowHeight,
WindowWidth = windowWidth,
Context = null
];

FinishLogin = (Context, CallbackUri, state) =>
let
Parts = Uri.Parts(CallbackUri)[Query]
in
TokenMethod(Parts[code], “authorization_code”);

GetDirectory = () =>
let
source = Web.Contents(“https://desk.zoho.com/api/v1/tickets?include=contacts,assignee,departments,team,isRead”, [
Headers=[#”Content-Type”=”application/json”]
]),
json = Json.Document(source),
cursor = json[cursor]?,
table = Table.FromList(json[entries], Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
table;

Refresh = (resourceUrl, refresh_token) => TokenMethod(refresh_token, “refresh_token”);

[DataSource.Kind=”ZohoDesk”, Publish=”ZohoDesk.Publish”]
shared ZohoDesk.Contents = () => GetDirectory();

ZohoDesk = [
Authentication=[OAuth=[StartLogin=StartLogin, FinishLogin=FinishLogin, Refresh=Refresh]]
];

——————————
Diego Carballo
+54 1147819300
——————————
——————————————-

[Alexander Ferguson]

Hi Tony – thanks for your contribution in this thread, this has helped me greatly in trying to connect to Zoho CRM. I’m trying to connect to Zoho CRM API and I am receiving an error after logging into Zoho through my custom connector, I hope you can help as my connector seems 90% complete, just missing the final step! (code below).

Error message: [DataFormatError] We couldn’t parse your query string as it was improperly formatted

// This file contains your Data Connector logic
section Zoho_Connector___V1.1;

// TODO: add your client id and secret to the embedded files

client_id = “XXXXX”;

client_secret = “XXXXXX”;

redirect_uri = “https://oauth.powerbi.com/views/oauthredirect.html”;

windowWidth = 800;

windowHeight = 800;

//Oauth base url for

OAuthBaseUrl = “https://accounts.zoho.eu/oauth/v2/auth?”;

[DataSource.Kind=”Zoho_Connector___V1.1″, Publish=”Zoho_Connector___V1.1.Publish”]
shared Zoho_Connector___V1.1.Contents = () =>
let

navTable = Web.Contents(“https://www.zohoapis.eu/crm/v2/Leads”)

in

navTable;

// Data Source Kind description
Zoho_Connector___V1.1 = [
Authentication = [
// enable both OAuth and Key based auth

OAuth = [

StartLogin = StartLogin,

FinishLogin = FinishLogin,

Refresh=Refresh

]

],
Label = Extension.LoadString(“DataSourceLabel”)
];

// Data Source UI publishing description
Zoho_Connector___V1.1.Publish = [
Beta = true,
Category = “Other”,
ButtonText = { Extension.LoadString(“ButtonTitle”), Extension.LoadString(“ButtonHelp”) },
LearnMoreUrl = “https://powerbi.microsoft.com/”,
SourceImage = Zoho_Connector___V1.1.Icons,
SourceTypeImage = Zoho_Connector___V1.1.Icons
];

// OAuth2 flow definition

//

StartLogin = (resourceUrl, state, display) =>

let

AuthorizeUrl = OAuthBaseUrl & Uri.BuildQueryString([

scope = “ZohoCRM.modules.all”,

client_id = client_id,

redirect_uri = redirect_uri,

response_type = “code”,

state = state,

access_type = “online”])

in

[

LoginUri = AuthorizeUrl,

CallbackUri = redirect_uri,

WindowHeight = windowHeight,

WindowWidth = windowWidth,

Context = null

];

FinishLogin = (context, callbackUri, state) =>

let

Parts = Uri.Parts(callbackUri)[Query]

in

TokenMethod(Parts[code], “authorization_code”);

TokenMethod = (code, grant_type) =>

let

Response = Web.Contents(OAuthBaseUrl & “/token”, [

Content = Text.ToBinary(Uri.BuildQueryString([

grant_type = “authorization_code”,

client_id = client_id,

client_secret = client_secret,

redirect_uri = redirect_uri,

code = code
]
)),

Headers=[#”Content-type” = “application/x-www-form-urlencoded”,#”Accept” = “application/json”]]),

Parts = Json.Document(Response)

in

Parts;

Refresh = (resourceUrl, refresh_token) => TokenMethod(refresh_token, “refresh_token”);

Zoho_Connector___V1.1.Icons = [
Icon16 = { Extension.Contents(“Zoho_Connector___V1.116.png”), Extension.Contents(“Zoho_Connector___V1.120.png”), Extension.Contents(“Zoho_Connector___V1.124.png”), Extension.Contents(“Zoho_Connector___V1.132.png”) },
Icon32 = { Extension.Contents(“Zoho_Connector___V1.132.png”), Extension.Contents(“Zoho_Connector___V1.140.png”), Extension.Contents(“Zoho_Connector___V1.148.png”), Extension.Contents(“Zoho_Connector___V1.164.png”) }
];

Thank you!

——————————
Alexander Ferguson
——————————
——————————————-

[PS D]

Hey Alexander Ferguson, Have you figured out the Zoho CRM connection.
I would be really thankful to you if please share some light on the rectification you must have done to get through the connection.

in case you can share some sample code or at least how the problem have been resolved. 

Cheers!!!

——————————
PS D
Cofounder
——————————
——————————————-

[Rafael Guerrero]

Hi Elizabeth: You have a few options. For example, we had a similar request for one of our clients. They needed reports from Zoho Desk and the reporting engine was PowerBI.  What we did is pull the data from Zoho Desk via the REST API framework and load the data into an internal reporting database, that refreshed everyday.  Once the data was in the database it was fairly straightforward to create the reports.

Good luck; feel free to message me if you need any assistance.

——————————
Rafael Guerrero
Gnnovation, LLC.
Denton TX
2145297050
——————————
——————————————-

[David de la Nougerede]

Hi Rafael

Is this a service you sell. I am very interested.

Can you get back to me asap

Thanks

——————————
David de la Nougerede
ME19 4YU
——————————
——————————————-

[Daniel Demers]

Hi Elizabeth,

Did you have any luck connecting Power BI to Zoho? I’ve just begun working with a company that uses Zoho after being on Dynamics CRM for 10 years and am wondering what the easiest/best way to connect Power to Zoho would be. 

Thanks

——————————
Daniel Demers
Chief Digital Information Officer
Dan
61423566444
——————————
——————————————-

[Elizabeth Tachjian CPA]

Hi Daniel,

I connected Power BI desktop and Zoho CRM using Cdata ODBC Driver.

Warm Regards
Elizabeth Tachjian
Analytics Realtime
elizabeth@analyticsrealtime.com.au

——————————
Elizabeth Tachjian CPA
Business Intelligence Dashboards Developer
Analytics Realtime
Melbourne [VIC]
0417457054
——————————
——————————————-

[Hasham Niaz]

Hi Eliz,

CData connector is the paid one right ?

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Tamara Tsyhan]

Hi Daniel,

ODBC drivers could be a good solution to the problem, but many of them are quite costly. If you are looking for an effective solution for good money try Devart ODBC Driver for Zoho CRM

Hope you will find it useful

Regards,

Tamara

——————————
Tamara Tsyhan
Product Marketing Manager
——————————
——————————————-