Custom Data Connector Oauth2

[VinceG0214]

I am trying to build a custom data connector to get our IT ticket information from our ticketing system. Our ticketing system does offer an API that uses Oauth2 and a new refresh token every 60 minutes. Everything looks good but when I use the custom data connector in PowerBI Desktop I am still getting an error saying "invalid_token." Can anyone help me with this?

section GTAConnector;

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://api.getgo.com/oauth/v2/token";
authorize_uri = "https://api.getgo.com/oauth/v2/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";

windowWidth = 720;
windowHeight = 1024;


[DataSource.Kind="GTAConnector", Publish="GTAConnector.Publish"]
shared GTAConnector.Contents = (url as text) =>
    let
        source = Web.Contents(url)
    in
        source; 

GTAConnector = [
    TestConnection = (dataSourcePath) => { "GTAConnector.Contents", dataSourcePath },
    Authentication = [
        OAuth = [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

GTAConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = GTAConnector.Icons,
    SourceTypeImage = GTAConnector.Icons
];

StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            client_id = client_id,
            response_type = "code",
            redirect_uri = redirect_uri
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        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("authorization_code", "code", refresh_token);

Logout = (token) => logout_uri;

TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "authorization_code",
            redirect_uri = redirect_uri

        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Authorization" = "Basic XXXXXXXXXXX encoded client key and secrect",
                #"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;
        

GTAConnector.Icons = [
    Icon16 = { Extension.Contents("GTAConnector16.png"), Extension.Contents("GTAConnector20.png"), Extension.Contents("GTAConnector24.png"), Extension.Contents("GTAConnector32.png") },
    Icon32 = { Extension.Contents("GTAConnector32.png"), Extension.Contents("GTAConnector40.png"), Extension.Contents("GTAConnector48.png"), Extension.Contents("GTAConnector64.png") }
];

[VinceG0214]

This connector is now working when I refresh it in Power BI Desktop but will not refresh in the Power BI service online. I am getting an error that says the data source credentials are invalid. When I select "Edit Credentials" and sign in the login windown just refreshes and doesnt do anything. Does anyone have an idea what the issue here is? 

[Nolock]

Hi @VinceG0214,

as @v-juanli-msft said, the code is long, we can't test it and therefore it is complicated to help. But I think I see a problem because I've had a similar one.

The function GTAConnector.Contents expects an URL but you get a JSON file where the first element is url - you can see it on the screenshot in PBI Service.

It is described somewhere here: https://docs.microsoft.com/en-us/power-query/samples/trippin/readme but unfortunately I can't find the exact spot right now 😞

Try followings:

[DataSource.Kind="GTAConnector", Publish="GTAConnector.Publish"]
shared GTAConnector.Contents = (json as text) =>
    let
        source = Web.Contents(json[url])
    in
        source; 

[VinceG0214]

Here is the documentation for what I am trying to accomplish and the endpoint is https://deskapi.gotoassist.com/v2/incidents. Am I going about this wrong?

[v-juanli-msft]

Hi @VinceG0214 

You M queries are too complex and seems not valid.

To create an M query in the Query Editor, you follow this basic process:

Create a series of query formula steps that start with the let statement.,

Output a query formula step using the in statement. Generally, the last query step is used as the in final data set result.

https://docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language

Please take a look at the following examples about how to connect to API in Power BI Desktop.
Get Data from Twitter API with Power Query
Power BI – Connect to your secure API

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.