Re: Zoho CRM API to Power BI Custom Connector

MarcVela · ‎10-05-2021

Hello everyone,

I'm trying to create a custom connector to import Zoho CRM API data into PowerBI and I am receiving an error after logging into Zoho through my custom connector.

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

API Reference: https://www.zoho.com/crm/developer/docs/api/v2/get-records.html

// 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 = "XXXXXX";

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

//


// Start Login thorugh OAUTH
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

];

// Finish Login through OAUTH

FinishLogin = (context, callbackUri, state) =>

let

Parts = Uri.Parts(callbackUri)[Query]

in

TokenMethod(Parts[code], "authorization_code");



TokenMethod = (code, grant_type) =>

let

Response = Web.Contents("https://accounts.zoho.eu/oauth/v2/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") }
];

I've been stuck a few days with this now. Can someone help me to get this right?

Thanks in advance

psdhillon1 · ‎06-25-2022

Spoiler

Has anyone solved this last piece?
I have also able to authenticate but i am struck at this last piece. May be it is about data format of Zoho CRM, which is not letting me through to get the data.

Here is what i am getting "Error message: [DataFormatError] We couldn't parse your query string as it was improperly formatted"

Please suggest if anyone able to resolve this error?
found same has been mentioned in few places on internet, but none have suggested the solution.

Has anyone solved this last piece?I have also able to authenticate but i am struck at this last piece. May be it is about data format of Zoho CRM, which is not letting me through to get the data.Here is what i am getting "Error message: [DataFormatError] We couldn't parse your query string as it was improperly formatted"Please suggest if anyone able to resolve this error? found same has been mentioned in few places on internet, but none have suggested the solution.

jhcannon · ‎03-14-2022

Did you ever get this working? I am just starting to work on a connector and am hitting the same error message.

Anonymous · ‎11-10-2021

Hello,

I'm trying to create the custom connector but I don't find a solution. did you have any solution for this ?

I should connect zoho crm to powerBI or to SQL.

Thanks,

*K

v-eqin-msft · ‎10-13-2021

Hi @MarcVela ,

Could you tell me if my post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Best Regards,
Eyelyn Qin