Jibble 2 API access with Bearer token

[Anonymous]

Hello,

I am trying to build an API request to Jibble 2, a time-tracking software our company uses. I am new to API requests and Power M Query, so I come up against a few errors that seem obvious to many people. After writing this I see it's quite long so here's the structure:

1. The problem and my understanding of it

2. My first attempt to solve it

3. My second attempt to solve it

4. Some general notes at the end 

The problem and my understanding of it

I want to pull this tracked time report (here in the docs) and store it as a table in my PowerBI Desktop Report. I don't quite understand how to use the Bearer token system that they have in place (here is the docs on it). 

I tried some of the methods outlined in this community post. 

I have the client ID and client secret from web access to my Jibble 2 service, but I'm stumbling with setting up the web connection, because I don't actually have the bearer key from that. In the community post linked above, some users report successfully connecting by giving HTTP request header parameter Authorization with value Bearer[bearer_key], but I don't have the key - I think I need to get it from the API.

I've tried contacting Jibble support about this, but they say that help with the API isn't part of the support subscription package. 

My current understanding of the problem is this two step process:

1. Write query to https://identity.prod.jibble.io/connect/token and retrieve Bearer token. Store bearer token as variable in the Power Query script I will write. Use my client ID and client secret.

2. Write query as part of same script to https://time-attendance.prod.jibble.io/v1/TrackedTimeReport (long link as given in docs omitted for brevity) using the API key and the Bearer key I now have stored as a variable. Save this as a table.

3. Manipulate data

What I've tried to solve it so far (attempt 1)

To achieve step 1, I've tried Get Data/Web then https://identity.prod.jibble.io/, and then using anonymous connection. However, I can't actually get to the stage of writing the M query script, because this request fails:

I assume this is because I need to include the client secret somewhere, so I've tried including the details given on the docs page (linked again here) as HTTP info. My client secret is called PowerBI (see below), I'm assuming that's what 'Client ID' is. 

So here's how I've tried to setup that request:

However I still get the Bad Request problem. Is my client ID wrong? Or should these pieces of information not be passed as HTTP headers? 

Attempt 2

Another attempt I made was using Anonymous' method from ‎02-13-2020 02:16 PM in this thread - linked with comment highlighted. This time, I tried to access the report I actually want to pull instead of the auth page.

I've used the Authorization header, followed by value of Bearer [my client secret].

This time clicking OK takes me to selection of Anonymous, Windows, Basic, Web API, Org account. Anonymous 'couldn't authenticate with the credentials provided', and pasting the client secret into the Key section of web api says 'web API key can only be specified when web API key name is provided'. 

Having seen this before, I understand that the best way to solve it is including the web API key name in the headers somehow. From the docs it looks like 'client_id' is the header I want, so I tried this:

Which then gives me this error here:

General

So that is roughly where I am up to. Sorry it's a bit long but I hope that the details helps anyone to understand what I'm trying to do and what I understand. I also can't see any Jibble posts on this forum yet so this thread may be the first one for that particular product and help future users, hence the detail. 

I am trying to solve this "independently" (😂) so I would appreciate answers that critique my thought process, as well as any hints on how to make some progress - it should help me become a better PowerBI user! 

[v-jingzhang]

Hi @Anonymous 

Thank you for the detailed information about the problem. I think your understanding is correct. 

According to the API doc, it requires a POST request to get access token, so adding parameters to the header doesn't work. You need to add body parameters to Content in Web.Contents() function to change a GET to a POST. This is not possible to be edited with Web connector's User Interface, you need to modify the M code in Advanced Editor. 

Create a blank query, open Advanced Editor from Home tab and paste below code into it to replace everything there. Use your client id and client secret for "xxxxxxx" in this code. 

let
    url = "https://identity.prod.jibble.io/connect/token",
    headers = [#"Accept"="application/json", #"Content-Type"="application/x-www-form-urlencoded"],
    postBody = [
        grant_type = "client_credentials", 
        client_id = "xxxxxxxxxxxxxxxx",
        client_secret = "xxxxxxxxxxx"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postBody))
        ]))
in
    response

If you are not sure what your client id and client secret is, ask your company's IT or this software's Admin for help. From the API doc, it says "Client ID and client secrets can be created by organization owner". 

Anyway, you need to get access token first before querying data from the report API. Let's go through the step one first. Here is a similar thread, hope it would be helpful: https://community.powerbi.com/t5/Power-Query/Channel-Advisor-API-Connection-with-auth-key-creation/td-p/2208091 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

I tried this and got the following error. 

My code is exactly the same as yours.

here's my Jibble API page. 

I wonder if my client ID is wrong? I assumed it was the 'Name' column shown above, but perhaps not.

[v-jingzhang]

Hi @Anonymous 

You didn't modify the response part. The url, headers and postBody should be passed to the Web.Contents function to make a POST request. 

Best Regards,
Community Support Team _ Jing

[Anonymous]

I am still getting the (400): Bad Request error, even with my code edited to be like yours. 

Is it wrong that both 'DataSourcePath' and 'Url' are the same?