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,
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
ResultRegards,
Xiaoxin Sheng
Hi Anonymous,
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_dropbox__locatevalues.
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?
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!