Power BI and Dropbox - Automatic refresh in Power BI service without expiring tokens

[yhogebrug]

I've been working on connecting Power BI with dropbox to extract the content of some files. Everything worked fine on Power BI Desktop and everything also seemed to work fine on Power BI Service, while i was able to refresh it. But the access_token that you use, is only valid for a few hours. After that, you have to generate a new token and use that token to connect. This is not a maintainable solution for connecting with Dropbox in Power BI. 

It turned out to be the case that everything worked fine before oktober 2022, but after that, the access_tokens are not permanent anymore. To get everything to work, i used this article as the main source: https://www.thebiccountant.com/2017/06/25/import-multiple-files-dropbox-folder-powerbi-excel-via-pow.... In here, it is also stated that this solution doesn't work anymore.

It seems that OAuth authentication is the new way, but I can't find any examples how to do that with Power BI. Dropbox has an OAuth guide (https://developers.dropbox.com/oauth-guide), but I couldn't make it work in Power BI. In Dropbox, you have a permanent 'App key' and 'App secret', when creating a Dropbox App. These are credentials you have to use, i think at this moment. I also read something about refresh tokens, but I don't know how to extract them and apply them in the code in Power BI.

Is there someone who can help me how I can achieve a permanent, workable connection with Dropbox in Power BI?:) 

[v-shex-msft]

Hi @yhogebrug,

Have you added the get access token steps into the query table before shaping table structures? They will auto refresh the tokens when your data source refresh to prevent the token expire issues.

For example:

let
    url = "api.xxxx.com",
    token = "xxxxx",
    response = Web.Contents(
        url,
        [
            RelativePath = "/oauth/token",
            Headers = [
                Authoraztion = "Basic " & token,
                #"Content-Type" = "application/json"
            ],
            Query = [
                grant_type = "client_credentials",
                client_id = "id string",
                client_secret = "secret string"
            ]
        ]
    ),
    AccessToken = Json.Document(response)[access_token],
    JsonResponse = Web.Contents(
        url,
        [
            RelativePath = "/xxx/xx",
            Headers = [
                Authoraztion = "Basic " & AccessToken,
                #"Content-Type" = "application/json"
            ]
        ]
    ),
    Result = Json.Document(JsonResponse)
in
    Result

Regards,

Xiaoxin Sheng

[yhogebrug]

Hi @v-shex-msft,
Thank you for your reply, unfortunately it isn't working and I get an 404 Not Found error. This is the code I am using now. 

    url = "https://api.dropboxapi.com/",
    response = Web.Contents(
        url,
        [
            RelativePath = "/oauth/token",
            Headers = [
                Authorization = "Basic " & token,
                #"Content-Type" = "application/json"
            ],
            Query = [
                grant_type = "client_credentials",
                client_id = "xxxxxxxxx1",
                client_secret = "xxxxxxxx2"
            ]
        ]
    ),
    AccessToken = Json.Document(response)[access_token]

While using this code, I get the following error in the 'response' step:

I did some additional research online and also found the following page: https://www.ibm.com/docs/en/app-connect/containers_cd?topic=type-dropbox-account-details#localconn_d.... 

In here, they use the client_id and client_secret to obtain a code and use that to make a connection. I am able to generate such a code via those steps, maybe I need to use that one in Power BI also? 

Finally, you have a variable, called 'token' in your script. That is the short-lived access token, right? Which you can generate in the settings of your Dropbox application.

Do you have a idea how I can solve the 404 Not Found error and do get a correct response?

[v-shex-msft]

Hi @yhogebrug,

404 error mean these requests does not exist in API lists(the above sample code is copy from other rest API and it may not be suitable for Dropbox API usage) , you can try to use the following codes which I modify based on the document:

let
    url = "www.dropbox.com",
    token = "xxxxx",
    response = Web.Contents(
        url,
        [
            RelativePath = "/oauth2/authorize",
            Headers = [
                #"Content-Type" = "application/json"
            ],
            Query = [
                client_id="MY_CLIENT_ID",
                redirect_uri="MY_REDIRECT_URI",
                response_type="code",
                token_access_type="offline"
            ]
        ]
    ),
    AccessToken = Json.Document(response)[access_token],
    JsonResponse = Web.Contents(
        url,
        [
            RelativePath = "/xxx/xx",
            Headers = [
                Authoraztion = "Basic " & AccessToken,
                #"Content-Type" = "application/json"
            ]
        ]
    ),
    Result = Json.Document(JsonResponse)
in
    Result

Regards,

Xiaoxin Sheng

[yhogebrug · Accepted solution]

Hi @v-shex-msft,

Thank you for pointing me in the right direction. Unfortunately, this also didn't work. The problem is that the first call with "/oauth2/authorize" returns nothing, so you can't retrieve an access token from there. This is also explained in the documentation from Dropbox by the way: https://www.dropbox.com/developers/documentation/http/documentation#oauth2-authorize. 

But, after some trial and error, I was able to get it work in another way🎉. I will explain it here below, in case someone is running into the same issue.

Using the documentation from Dropbox itself (link is above) for "/oauth2/authorize" and "/oauth2/token", a refresh token can be obtained which won't expire (it already works now for 4 days with the same refresh token). I used the command prompt to do the needed calls to eventually obtain a refresh token. In short, the steps are:

1. Generate a code via https://www.dropbox.com/oauth2/authorize with your app credentials. This code can be used only once in step 2

2. Use the code to generate a refresh token via https://api.dropboxapi.com/oauth2/token with your app credentials

3. Use your refresh token in your Power BI code. This refresh token will retrieve a new temporary token each time.

This topic (https://community.powerbi.com/t5/Power-Query/Refresh-token-api-call/td-p/937553) helped me writing a working Power BI code to make the call with the refresh token. My final code looks as follows: 

let
    tokenResponse = Json.Document(Web.Contents(token_uri,          
                                    [
                                        Content=
                                        Text.ToBinary(Uri.BuildQueryString(
                                        [
                                            client_id = client_id
                                            ,client_secret=client_secret
                                            ,grant_type = "refresh_token"
                                            ,refresh_token=refresh_token
                                        ]))
                                        ,Headers=
                                            [Accept="application/json"]
                                            , ManualStatusHandling={400}
                                    ])),
                                    access_token = tokenResponse[access_token],
    Token = "Bearer " & tokenResponse[access_token]
in
    Token

So finally, this token can be used everytime to obtain the content from Dropbox, while using the permanent refresh token😊.

Something worth mentioning, using this outcome in another query can trigger the error explained down here: https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n.... Here, it is also stated what you can do to solve the problem. Hope this helps!