Connecting to data source hosted on Dropbox

I know what the catch is now. The devil is in the details.

At first I didn't at all know that there was a way to get any other access token string than to create an app and generate it from there. But when I saw your post and the bit with "In my case I have tried her folder app so I just got a token for the whole of dropbox.", I realized there had to be another way and was a bit irritated that the solution to this haven't been emphazised in any of the threads about this topic. Just as if it was obvious or something =). 

Once in dropbox.com/developers, go to API Explorer. For the action we are trying to do here is the list_folder command, so click on that from the margin. Then you can generate an access token for the whole of dropbox. You can even pick personal or organizational account when generating. In this case you don't need to have a function and so on. Just do the Web query, insert the url string as detailed in the previous responses and you'll then be able to pick the excel sheets as tables. 

It worked just fine when publishing as well, just like masplin said. (Y)

masplin, IT WORKED!!! thanks a lot!

No more credential errors, dropbox api v2 and PBI Service working as they should!

great!

Out of interest what were you trying to do?  If you just put the path to the dropbox folder like normal then it shoudl stil lwork as long as the path is the one on which your gatewayis running.  Theo nly advantage of the method i put is you can open desktop on various machines, where dropbox will have differnet paths, and still refresh them locally.

I need to connect dropbox cloud directly to PBI cloud. This way, me and my team will be able to use only the PBI Service to see all data that was edited by everyone.
example, user A may change all files 1, 2 and 3, also user B and C may change all 3 files.
once dropbox gets them updated, all 3 users will use only the PBI Service to see the updated changes.

now comes my new question: how to make it open full folders, intead of individual files? something like folder option on PBI Desktop.

ah when you work that out let me know!!!

Have you tried Imke solution where you create a folder under Apps?  That's what i was going to try next.

didn´t tried that yet... but I will try it soon.

thanks for your reply.

only today I got time to test it, and I couldn´t make it work.

I found the list_folder under API Explorer, got the HTML code, but I cannot insert it in the other web queries from the other posts.

could you please send an example of your working code?

@@JAkerblo, are you using api v1 or v2?

I cannot insert the list_folder code in the other web queries from the other posts.

Could you please send an example of your working code?

Sorry Anonymous! The example I used to implement this was a company production report. I'd have to make a test example, but I can do it with a little spare time today or tomorrow if you're still stuck. 

It is api v2. My instructions tell you to get the access token via the API Explorer link on the home page of dropbox.com/developers. Ctrl+F your way to "list_folder" and click on Get Token and follow the instructions to get a token for what you need. In my situation I have both a personal account and a company one, so I have to choose before I can generate a token. 

After that you have the query step in powerbi desktop. This line is all you need and then you can go on and navigate to the excel sheets you're interested in. Nothing else. 

= Excel.Workbook(Web.Contents("https://api-content.dropbox.com/1/files/auto/<PATH FROM DROPBOX ROOT TO EXCEL OF INTEREST>/<EXCEL FILENAME>?access_token=<ACCESS TOKEN GENERATED FOR THE DROPBOX ROOT YOU'RE IN>"), null, true)"

So in context, my M code looks like:

let
    Source = Excel.Workbook(Web.Contents("https://api-content.dropbox.com/1/files/auto/<PATH FROM DROPBOX ROOT TO EXCEL OF INTEREST>/<EXCEL FILENAME>?access_token=<ACCESS TOKEN GENERATED FOR THE DROPBOX ROOT YOU'RE IN>"), null, true), 
    Assignments_Sheet = Source{[Item="Assignments",Kind="Sheet"]}[Data], 
    #"Promoted Headers" = Table.PromoteHeaders(Assignments_Sheet, [PromoteAllScalars=true]),
    and so on...

JAkerblom, I really apreciate if you could test the list_folder command for me.

once I can connect to Dropbox using my token and view all files, I can handle them myself. but first I need to connect to the files and view them inside PBI query editor.

using web query or M language, I am having issue to make it work with my token and the supposed-to-be link below.

https://api.dropboxapi.com/2/files/list_folder

as I learned in Dropbox,

API v1 is: api-content.dropbox.com/1/etc

API v2 is: api.dropboxapi.com/2/etc

This is why I believe your code is using the old v1 API.

https://www.dropbox.com/developers/reference/migration-guide

Follow these images below.

And second, you're not supposed to use that link as you implied. Use the one I mentioned, and use it the way I described and all will be alright.

https://api-content.dropbox.com/1/files/auto/<Path to file>?access_token=<token>

yes, I followed these steps correctly, but this link only works when a file name is provided.

I created a new token for the new command list_folder.

I don´t want to load a single file, I need to load the contents of a folder, and view all files inside the folder.

I tried many times, many different codes. I got a lot of errors each test: 400, 404 and 405.

And so far nothing is loaded.

Hi I'm trying your suggestion.

Im on API Explorer page and clicked list_folder

I've done get token and in the path put  DataModel/3rd Party/WCFMC which is the folder where the files are

When I click submit call i get

Error in call to API function "files/list_folder": request body: path: 'DataModel/3rd Party Bookings/WCFMC' did not match pattern '(/(.|[\r\n])*)?|id:.*|(ns:[0-9]+(/.*)?)'

So I'm no further being ablew to access my folder on dropbox?  

What exactyl did you do to getthis to work please?

Mike

Hi there,

Trying to follow this code to get my PowerBi to refresh using web service but cannot seem to replicate.

In a blank query i am putting the following, is this right?

let

DropboxSourcePath = "https://content.dropboxapi.com/2/files/download/",

Token = "?access_token=[Token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

Any help appreciated!

Hi there,

Trying to follow this code to get my PowerBi to refresh using web service but cannot seem to replicate.

In a blank query i am putting the following, is this right?

let

DropboxSourcePath = "https://content.dropboxapi.com/2/files/download/",

Token = "?access_token=[Token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

Any help appreciated!

Reposting code as last version had wrong url! :)

let

DropboxSourcePath = "https://api-content.dropbox.com/1/files/auto/",

Token = "?access_token=[token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

I need to connect dropbox cloud directly to PBI cloud. This way, me and my team will be able to use only the PBI Service to see all data that was edited by everyone.

Can you say me if you could solve it?

Hi masplin,

I'm quite new on Power BI and doesn't have a developer background. I've noticed the solution you provided for this thread and i would like to understand how it work.

My problem is that i got tonnes of photos stored on Dropbox. I've copied the URLs into an Excel Spreadsheet and loaded in Power BI. The photos doesn't show up and i assumed it's because you need to connect Dropbox with Power BI first in order for Dropbox to give you the permission. Would you mind teaching me how it can be done, please? It would be great if we can chat on email as this is more appropriate. Here's my email - [email protected]

Regards,

Shen Tan

This is an excellent how-to - huge time saver!  Wish I had seen this right away.  Microsoft, are you listening?

Thank you for clarifying.  PowerBI's limitations and complexity are a huge time-sink. For anyone listening - NONE Of these issues exists with Tableau!!!! No DAX, no Garden, no fumbling through years of community frustration to find solutions.