Connecting to Box

[Anonymous]

My company uses Box to store all its files. My PowerBI file references excel documents which are stored on Box. 
Currently the work around we are using is to download (or sync) the files to our personal computers and change the source of the data between employees. However, this often means that the latest version of the excel file may not be used to create the PowerBI dashboard. 

I have read through the documentation for Box. 

First thing I wanted to try was to see if I could access the file and after reading through a lot of the details on https://developer.box.com/, I was able to connect to the excel file using a developer token following directions here: https://developer.box.com/docs/setting-up-a-jwt-app

The M code used is : 

let

  options =[ 
        Headers =[
            #"Authorization"="Bearer ******"
                ]
        ],
    Source = Web.Contents("https://api.box.com/2.0/files/*****/content", options),
    #"Imported Excel" = Excel.Workbook(Source)
in
    #"Imported Excel"

I've blocked out the fileID and the developer token for obvious reasons. 

I've since tried to move forward from using the developer token as it only lasts for 1 hour into using the JWT authentication: https://developer.box.com/docs/construct-jwt-claim-manually 
which should in theory allow me to connect and access the file. 

This is where I am completely lost. I can't figure out how to get the token using this method and I can't even get jwt.io to produce the required token. 

I have two ideas at this point. 
1. Try and figure out how to proceed with this and hopefully getting the JWT working is not too dificult and someone here can help me out. 
2. Try and figure out how to proceed with the BoxSDK (https://developer.box.com/docs/authenticate-with-jwt). However, I do not know how to access any version of the BoxSDK from Power Query as it is not really available for power query. 

Has anyone made similar progress or gone further to successfully connect Box to PowerBI? 

[bryce-haboian85]

Hi Anonymous,

As for OAuth 2.0 with JSON Web Tokens (Server Authentication), I haven't had much luck due to encryption issues. However, if you are able to generate a Box App with the Client Credentials Grant auth type, here is a snippet I've had success with:

let
    base_url = "https://api.box.com",
    // Define the parameters (you can modify these as inputs)
    client_id = "#####",
    client_secret = "#####",
    grant_type = "#####",
    box_subject_type = "#####",
    box_subject_id = "#####",

    // Construct the request body in JSON format by concatenating the variables
    body = 
        "{" & 
        """client_id"": """ & client_id & """, " &
        """client_secret"": """ & client_secret & """, " &
        """grant_type"": """ & grant_type & """, " &
        """box_subject_type"": """ & box_subject_type & """, " &
        """box_subject_id"": """ & box_subject_id & """ " &
        "}",

    // Send the POST request with the constructed JSON body
    jsonResponse_auth = Json.Document(Web.Contents(base_url, [
        RelativePath = "/oauth2/token",
        Headers = [#"Content-Type"="application/json"],
        Content = Text.ToBinary(body)
    ])),

    // Extract only the "access_token" from the JSON response
    access_token = jsonResponse_auth[access_token],

    // Get File Information
    file_id = "#####",
    relative_path = "/2.0/files/" & file_id,
    jsonResponse = Json.Document(Web.Contents(base_url, [
        RelativePath = relative_path, Headers = [#"Content-Type"="application/json", #"authorization"="Bearer " & access_token]
    ])),

    // Extract modified at
    modified_at = jsonResponse[modified_at],
    modified_at_record = Table.FromList({modified_at}, Splitter.SplitByNothing(), {"Modified At"}),

    // Convert text to datetime (remove timezone)
    #"Change Type" = Table.TransformColumns(modified_at_record, {{"Modified At", each 
        let
            Position = Text.PositionOfAny(_, {"-", "+"}, Occurrence.Last),
            CleanedText = Text.Middle(_, 0, Position) // Fixed: use Position directly
        in
            DateTime.FromText(CleanedText)
    }}),
    #"Changed Type" = Table.TransformColumnTypes(#"Change Type",{{"Modified At", type datetime}})
in
    #"Changed Type"

In my case, I needed to call the Get file information endpoint to retrieve when the file was last modified at. Also, for publishing this to the PBI portal, I needed to add this connection to my gateway with the Authentication method set to "Anonymous", "Skip test connection" box checked, and "Privacy level" set to "Organizational". 

Unsure if you are still having this issue but I thought I'd share my own experience.

[wardt]

Perhaps you could use the "Direct Link" (direct download link) feature. It is not available for free personal box.com accounts. You can read more here: https://support.box.com/hc/en-us/articles/360043697554-Configuring-Individual-Shared-Link-Settings

[v-juanli-msft]

Hi @Anonymous

As i don't have a BOX account, i can't test it with power bi. Maybe the following link can give you some ideas .

Power BI - Get JWT Token from Auth API

Regarding to get token from JWT Application, it isn't the matter from power bi side, you may read this article to know more about JWT.

GET the TOKEN: The token should be present in the header with name jwt-token (you can choose your custom name or send it in authorization header after all it’s custom contract). Also, the best practice is to send it via Authorization Bearer scheme.

Best Regards

Maggie

[Anonymous]

Hey Maggie, 

Thanks, that's helpful, but I still don't know what to do. 

How do I get the token? Namely, without the packages used here available for PowerBI: https://developer.box.com/docs/construct-jwt-claim-manually, Can I still get the Bearer token? 

Copying the steps from that page, 

1. Read JSON config - Read the private key, password, enterprise ID, and more from the configuration file.
2. Decrypt private key - The private key is encrypted and needs to be decoded before we can use it
3. Create JWT assertion - We need to create a signed JWT assertion that we can use to authenticate to the API
4. Request Access Token - With this JWT assertion we can then request an Access Token from the authorization endpoint
5. Make any API call - With the Access Token we can then make any API call needed

I have all the information from the json file (Step 1). 
I have no idea how I can use Power Query to decrypt the private key (Step 2) 
For the JWT assertion, I have no idea how I can create the jti (unique hex code) I believe I should be able to get the unix time from powerBI doing something like this. Once I have that, I still need a way to convert it into the JWT Assertion which for instance in python is done using the "jwt" package. (Step 3) 

I'm quite confident steps 4 and 5 will be similar to my first post to achieve.