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")
}
];
