Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Zoho Desk connector to Power BI desktop

  • 1.  Zoho Desk connector to Power BI desktop

    Gold Contributor
    Posted Feb 04, 2019 02:17 AM
    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
    ------------------------------


  • 2.  RE: Zoho Desk connector to Power BI desktop

    Silver Contributor
    Posted Feb 05, 2019 09:00 AM
    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
    ------------------------------



  • 3.  RE: Zoho Desk connector to Power BI desktop

    Gold Contributor
    Posted Feb 07, 2019 01:18 AM
    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


  • 4.  RE: Zoho Desk connector to Power BI desktop

    Silver Contributor
    Posted Feb 08, 2019 10:26 AM
    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.
    ------------------------------



  • 5.  RE: Zoho Desk connector to Power BI desktop

    Posted Sep 06, 2019 04:44 PM
    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
    ------------------------------



  • 6.  RE: Zoho Desk connector to Power BI desktop

    Posted Jun 01, 2020 09:37 AM
    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
    ------------------------------



  • 7.  RE: Zoho Desk connector to Power BI desktop

    Posted 18 days ago

    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
    ------------------------------



  • 8.  RE: Zoho Desk connector to Power BI desktop

    Posted Feb 05, 2019 01:10 PM
    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
    ------------------------------



  • 9.  RE: Zoho Desk connector to Power BI desktop

    Posted Oct 11, 2019 03:32 PM
    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
    ------------------------------



  • 10.  RE: Zoho Desk connector to Power BI desktop

    Gold Contributor
    Posted Jul 01, 2019 02:05 AM
    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
    ------------------------------



  • 11.  RE: Zoho Desk connector to Power BI desktop

    Gold Contributor
    Posted Jul 01, 2019 07:05 PM
    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
    ------------------------------



  • 12.  RE: Zoho Desk connector to Power BI desktop

    Top Contributor
    Posted Jul 03, 2019 07:57 PM
    Hi Eliz,

    CData connector is the paid one right ?

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



  • 13.  RE: Zoho Desk connector to Power BI desktop

    Posted Dec 20, 2021 04:56 AM

    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
    ------------------------------