Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Coffee321
Frequent Visitor

Oauth 2.0 Custom Connector working but need to extract another value in addition to Auth Code

Hi all,

 

Accountant here trying to create a custom connector so any help will be greatly appreciated!

 

Connector is successfully extracting auth code and exchanging it for access code. No issues there. The issue is I need to extract an additional code in addition to the auth code and I need to pass it to the URL in API calls.

The URL I'm passing in PowerBI to make API calls looks something like the one on the Web.Contents below, I added the #dynamicrealmidneededhere# to highlight where I need the value that I will be extracting. Right now I’m inserting that code manually to make the connector work:

 

[DataSource.Kind = "CustomConnector", Publish = "CustomConnector.Publish"]

shared CustomConnector.Contents = (optional message as text) =>

let

source = Json.Document(

Web.Contents(

"https://accounting.api.testing.com/v3/company/#dynamicrealmidneededhere#/reports/ProfitAndLoss?start_date=2023-10-01&end_date=2023-10-31&qzurl=true&minorversion=69"

)

)

in

source;

 

The code realmId is a value that represents a company. When a user logs in to the accounting system, the user selects a company from a list of companies that it wants to query. The realmId represents the selected company. That realmId is included in the auth URL the API sends after a user successfully logs in. URL includes both auth code and realmId as per the structure below:

 

https://testingaccounting.com/app/developer/playground?code=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&state=state&realmId=xxxxxxxxx

 

As per mentioned above, the connector successfully grabs the auth code from said URL and exchanges that for the access token, no issues there. I just need to somehow also grab the realmId and pass it to the Web.Contents API call included above (section in the URL that says #dynamicrealmidneededhere#). I've tried a couple of things but so far I haven't been able to make it work. Here is the structure of my connector in case it is needed. As I mentioned above, any help will be greatly appreciated!! Thanks!

 

// This file contains your Data Connector logic

[Version = "1.0.0"]

section CustomConnector;



// QBO OAuth2 values

issuer = "https://oauth.test.testing.com/op/v1";

client_id = Text.FromBinary(Extension.Contents("client_id.txt"));

client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));

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

token_uri = "https://oauth.test.testing.com/oauth2/v1/tokens/bearer";

authorize_uri = "https://test.testing.com/connect/oauth2";

logout_uri = "https://login.microsoftonline.com/logout.srf";



// Login modal window dimensions

windowWidth = 720;

windowHeight = 1024;



// OAuth2 scope

scope_prefix = "";

scopes = {"testing"};



[DataSource.Kind = "CustomConnector", Publish = "CustomConnector.Publish"]

shared CustomConnector.Contents = (optional message as text) =>

let

source = Json.Document(

Web.Contents(

"https://accounting.api.testing.com/v3/company/#dynamicrealmidneededhere#/reports/ProfitAndLoss?start_date=2023-10-01&end_date=2023-10-31&qzurl=true&minorversion=69"

)

)

in

source;



// Data Source Kind description

CustomConnector = [

TestConnection = (dataSourcePath) => {"CustomConnector.Contents", dataSourcePath},

Authentication = [

OAuth = [

StartLogin = StartLogin,

FinishLogin = FinishLogin,

Refresh = Refresh,

Logout = Logout

]

],

Label = Extension.LoadString("DataSourceLabel")

];



// Data Source UI publishing description

CustomConnector.Publish = [

Beta = true,

Category = "Other",

ButtonText = {Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp")},

LearnMoreUrl = "https://powerbi.microsoft.com/",

SourceImage = CustomConnector.Icons,

SourceTypeImage = CustomConnector.Icons

];



// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout

StartLogin = (resourceUrl, state, display) =>

let

authorizeUrl = authorize_uri

& "?"

& Uri.BuildQueryString(

[

response_type = "code",

state = "state",

client_id = client_id,

scope = GetScopeString(scopes, scope_prefix),

redirect_uri = redirect_uri

]

)

in

[

LoginUri = authorizeUrl,

CallbackUri = redirect_uri,

WindowHeight = 720,

WindowWidth = 1024,

Context = null

];



FinishLogin = (context, callbackUri, state) =>

let

// parse the full callbackUri, and extract the Query string

parts = Uri.Parts(callbackUri)[Query],

// if the query string contains an "error" field, raise an error

// otherwise call TokenMethod to exchange our code for an access_token

result =

if (Record.HasFields(parts, {"error", "error_description"})) then

error Error.Record(parts[error], parts[error_description], parts)

else

TokenMethod("authorization_code", "code", parts[code])

in

result;



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



Logout = (token) => logout_uri;



// see "Exchange code for access token: POST /oauth/token" at website for details

TokenMethod = (grantType, tokenField, code) =>

let

queryString = [

grant_type = "authorization_code",

redirect_uri = redirect_uri,

client_id = client_id,

client_secret = client_secret

],

queryWithCode = Record.AddField(queryString, tokenField, code),

tokenResponse = Web.Contents(

token_uri,

[

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;



Value.IfNull = (a, b) => if a <> null then a else b;



GetScopeString = (scopes as list, optional scopePrefix as text) as text =>

let

prefix = Value.IfNull(scopePrefix, ""),

addPrefix = List.Transform(scopes, each prefix & _),

asText = Text.Combine(addPrefix, " ")

in

asText;



CustomConnector.Icons = [

Icon16 = {

Extension.Contents("CustomConnector16.png"),

Extension.Contents("CustomConnector20.png"),

Extension.Contents("CustomConnector24.png"),

Extension.Contents("CustomConnector32.png")

},

Icon32 = {

Extension.Contents("CustomConnector32.png"),

Extension.Contents("CustomConnector40.png"),

Extension.Contents("CustomConnector48.png"),

Extension.Contents("CustomConnector64.png")

}

];

 

 

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

Please check the "RelativePath"  portion of the documentation.  Web.Contents - PowerQuery M | Microsoft Learn

Thanks for the reply! I think the issue is that I'm not able to extract the value I need to include in Web.Contents. Even if I create a function that extracts said value in my FinishLogIn section, I'm not able to call it and pass it to my Web.Contents. Nothing that has already been created in said section that is currently working (the section is extracting the auth code and exchanging it for the access code needed to make API calls) is callable and can't be included in my Web.Contents URL. I'm fixating on the FinishLogIn section because as per the API's docs, the realimID (value I need) is included on the same URL where the auth code is (so if the FinishLogin section already is calling the auth code, I'm supposed to be able to create a function there that also calls the realmId, but apparently it can't be done). I'm following MS docs custom connector structure here, and this tutorial here. If I'm able to extract that value, I will definitely use the info on the Web.Contents link.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors