Unable to fetch access_token from Extension.CurrentCredential()

[arindamroy19]

Hi experts

I am building a PowerQuery Custom Connector (for Zoho APIs) in Visual Studio to be deployed to Power BI.
While debugging/launching from Visual Studio, I can see the data being fetched from the API; however, if I deploy the connector to the Power BI Custom Connectors folder, the same code doesn't work.
I get an error in Power BI while trying to use the Connector :
Details: "The field 'access_token' of the record wasn't found."

The snippet where I am accessing the token after the OAuth flow -
`...
token = GetToken(),
apiResponse = zoho.CallApi(apiEndpoint, token),
...

GetToken = () =>
let
access_token = Extension.CurrentCredential()[access_token]
in
access_token;

zoho.CallApi = (apiEndpoint as text, accessToken as text) =>
let
// Make API request using the access token
apiResponse = Json.Document(
Web.Contents(
apiEndpoint,
[Headers = [#"Authorization" = "Zoho-oauthtoken " & accessToken]]
)
)
in
apiResponse;
...
zoho = [
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
]
];

//Implementations for StartLogin, FinishLogin, Refresh and Logout
`

Is there any restriction in invoking Extension.CurrentCredential() from Power BI (December 2023 version)?

[arindamroy19]

The reason I need to do this is because the APIs require the Authorization header to have the value : Zoho-oauthtoken <access_token>

[arindamroy19]

Any ideas?

[geologyrocks]

What is your entry point into this connector? I wonder if when you're calling the entry function that the authentication logic hasn't been run yet?

zoho.Contents(<mandatory parameters>) // this is the entry point

[arindamroy19]

hi @geologyrocks

This is the entry point -
[DataSource.Kind="zoho", Publish="zoho.Publish"]
shared zoho.Contents = (optional clientId as text, optional clientSecret as text, optional refreshToken as text, optional authCode as text) =>
...

Doesn't the auth happen before the entry point runs?
See full code below..

Thanks !

[mattmasson]

Make sure the shared function(s) (which will be the ones that become visible to Power Query) have the [DataSource.Kind] adornment over them. That is what flags them as Data Source functions and gives access to the zoho credential kind.

[DataSource.Kind="zoho", Publish="zoho.Publish"]
shared zoho.Contents = (server as text) => ...;

zoho = [
   Authentication = [
      OAuth = [
         StartLogin=StartLogin,
         FinishLogin=FinishLogin,
         Refresh=Refresh,
         Logout=Logout
      ]
   ]
];

[arindamroy19]

hi @mattmasson

Yes, the attribute is applied to the Contents function.
The full source code is per below -

section zoho;

tokenUrl = "https://accounts.zoho.com.au/oauth/v2/token";
clientId = "";
clientSecret = "";
authCode = "";

[DataSource.Kind="zoho", Publish="zoho.Publish"]
shared zoho.Contents = (optional clientId as text, optional clientSecret as text, optional refreshToken as text, optional authCode as text) =>
let

    // Use the access token to make API requests
    apiEndpoint = "https://www.zohoapis.com.au/crm/v2/Leads",
    token = GetToken(),
    apiResponse = zoho.CallApi(apiEndpoint, token),

    // Transform and return the API response
    result = apiResponse[data],
    totable = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedCol = Table.ExpandRecordColumn(totable, "Column1", {"id","ticketNumber","subject"})
in
    expandedCol;

zoho.CallApi = (apiEndpoint as text, accessToken as text) =>
let
// Make API request using the access token
apiResponse = Json.Document(
Web.Contents(
apiEndpoint,
[Headers = [#"Authorization" = "Zoho-oauthtoken " & accessToken]]
)
)
in
apiResponse;

GetToken = () =>
let
access_token = Extension.CurrentCredential()[access_token] //line that is likely throwing
in
access_token;

zoho = [
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
]
];

// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
// StartLogin doesn't do anything in our case; as the authCode is received out of band
StartLogin = (resourceUrl, state, display) =>
let
authCode = authCode,
logMessage3 = Diagnostics.Trace(TraceLevel.Warning, "TextValueFromNumber", "In StartLogin", false)
in
[
LoginUri = "https://www.google.com/", //as it cannot be null
CallbackUri = "https://www.google.com/",
WindowHeight = 0,
WindowWidth = 0,
Context = null
];

FinishLogin = (context, callbackUri, state) =>
let
result = TokenMethod("authorization_code", "code", authCode)
in
result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => null;

TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = grantType,
client_id = clientId,
client_secret = clientSecret
],
queryWithCode = Record.AddField(queryString, tokenField, code),

    tokenResponse = Web.Contents(tokenUrl, [
        Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
        Headers = [
            #"Content-type" = "application/x-www-form-urlencoded",
            #"Accept" = "application/json"
        ],
        ManualStatusHandling = {400} 
    ]),
    body = Json.Document(tokenResponse),
    result = if (Record.HasFields(body, {"error", "error_description"})) then 
                error Error.Record(body[error], body[error_description], body)
             else
                body
in
    result;

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

zoho.Icons = [
Icon16 = { Extension.Contents("zoho16.png"), Extension.Contents("zoho20.png"), Extension.Contents("zoho24.png"), Extension.Contents("zoho32.png") },
Icon32 = { Extension.Contents("zoho32.png"), Extension.Contents("zoho40.png"), Extension.Contents("zoho48.png"), Extension.Contents("zoho64.png") }
];

[geologyrocks]

Before I reply with a code pattern for you, just so I can understand better - is there a reason why you are trying to send the oauth credentials (client id, client secret) via your Contents parameters rather than putting them into a secrets.json file and then loading them in that way?

[arindamroy19]

@geologyrocks - No, this was just some starter code to get this working first; intend to cleanup those aspects later.

[arindamroy19]

@mattmasson , @geologyrocks -
I ran Fiddler while doing 'Get data' using the connector from Power BI, and I see that the token endpoint isn't being called. So, the OAuth flow isn't happening.
That explains why there is no access_token in the CurrentCredential() output object -
access_token = Extension.CurrentCredential()[access_token]

If I do a Visual Studio debug, Fiddler shows the call to the token endpoint and hence, it works fine.