Custom Connector How to extract specific part of Authorization Code and pass it to Web.Contents URL

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. Issue is I need to extract an additional code in addition to the auth code and I need to pass it to my 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. 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")

}

];