'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