Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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")
}
];