Forum Discussion
Power BI and Dropbox - Automatic refresh in Power BI service without expiring tokens
Hi Anonymous,
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 TokenSo 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-not/td-p/18619. Here, it is also stated what you can do to solve the problem. Hope this helps!
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
ResultRegards,
Xiaoxin Sheng
Hi Anonymous,
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
TokenSo 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-not/td-p/18619. Here, it is also stated what you can do to solve the problem. Hope this helps!