'Create a custom connector to import data from Square into Power BI
I've found tutorials online for connecting OAuth2 protected APIs to Power BI, and I've tried translating them to work with Square, but to no avail. I can easily access Square's API data via C# and PHP, but neither of these are relevant for my purpose. The major stumbling block is an error message that reads:
Credentials are required to connect to the SquareConnector source. (Source at {"url":"https//connect.squareup.com/v2/orders"}.)
This is the connector code I'm using:
// SquareConnector.pq
//
// This file contains your Data Connector logic
section SquareConnector;
//Oauth2 values
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";
authorize_uri = "https://connect.squareup.com/oauth/authorize";
token_uri = "https://connect.squareup.com/oauth2/token";
logout_uri = "https://squareup.com/logout";
WindowWidth = 1024;
WindowHeight = 720;
//Oauth2 scope
scope_prefix = "";
scopes = {
"MERCHANT_PROFILE_READ",
"PAYMENTS_READ",
"SETTLEMENTS_READ",
"BANK_ACCOUNTS_READ"
};
[DataSource.Kind="SquareConnector", Publish="SquareConnector.Publish"]
shared SquareConnector.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;
// Data Source Kind description
SquareConnector = [
TestConnection = (dataSourcePath) => {"SquareConnector.Contents", dataSourcePath},
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
SquareConnector.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = SquareConnector.Icons,
SourceTypeImage = SquareConnector.Icons
];
// Helper functions of OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
response_type = "code",
client_id = client_id,
redirect_uri = redirect_uri,
state = state,
scope = GetScopeString(scopes)
])
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 containts 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 acccess token: POST /oauth/token on the Square documentation page for details
TokenMethod = (grantType, tokenField, environment, code) =>
let
queryString = [
grantType = "authorization_code",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret,
environment = "production"
],
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;
SquareConnector.Icons = [
Icon16 = { Extension.Contents("SquareConnector16.png"), Extension.Contents("SquareConnector20.png"), Extension.Contents("SquareConnector24.png"), Extension.Contents("SquareConnector32.png") },
Icon32 = { Extension.Contents("SquareConnector32.png"), Extension.Contents("SquareConnector40.png"), Extension.Contents("SquareConnector48.png"), Extension.Contents("SquareConnector64.png") }
];
and this is the query file:
let
result = SquareConnector.Contents("https://connect.squareup.com/v2/orders")
in
result
Has anyone got this connected before?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
