Connecting to data source hosted on Dropbox

[JohnSwid]

Hello PowerBI-Ites,

Has anyone had any luck connecting to Dropbox as a data source.  Couldn't find many articles on this topic.  Here's one  article that had a solution we couldn't get to work: http://community.powerbi.com/t5/Desktop/Data-source-hosted-on-Dropbox/td-p/47882.

Just wondering if anyone could shed some light on this topic.  Thank you!

[v-yuezhe-msft]

Hi @JohnSwid,

Which file or data source do you store in Dropbox? I am trying to connect to a Excel file stored in DropBox following ankitpatira’s suggestions and everything works well.


Moreover, some ideas about that connecting to data source stored in Dropbox have been submitted in the following links, please vote them. Some changes may be made for the connection to Dropbox and it could be easier for us to connect to Dropbox from Desktop in the future.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6663925-dropbox  
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9045649-please-add-more-data-serv...

In addition, you can also consider to store files in OneDrive instead of  Dropbox and connect to them from Power BI Desktop.


Thanks,
Lydia Zhang

[mike_honey]

While researching this topic, I noticed that all the examples given appear to be for the Dropbox v1 API, which has been deprecated and will be turned off in a few months (June 2017):

https://blogs.dropbox.com/developers/2016/06/api-v1-deprecated/

The v2 API looks much more complex - there doesnt appear to be any direct equivalent for the method suggested above (single HTTPS call with an API Key). The authentication requirements now seem more complex:

https://www.dropbox.com/developers/reference/auth-types#user

It's stumped me for now - has anyone got this working?

[elliotdixon]

I agree with @mike_honey - cant seem to get it working.

Any tips on connecting into the dropbox files would be great.

[hugoberry]

The documentation on their developer site is quite comperhansive https://www.dropbox.com/developers/documentation/http/documentation

As for some boilerplate code to get you going have a look at the snippet below for an example of geting folder listing.

let
    data = [    path= "/code",
                recursive=false,
                include_media_info=false,
                include_deleted=false,
                include_has_explicit_shared_members=false],
    header = [  #"Authorization"="Bearer ZZZZZZZZZZZ",
                #"Content-Type"= "application/json"],
    response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]),
    out = Json.Document(response,1252)
in
    out

The only setup that I had to do on the Dropbox site was to create an App in the developer space and then generate an access key, which by the way is ZZZZZZZZZZ in my snippet.

[masplin]

I tried your code and got this error. I just created an app and added the access code where you had zzzzzzz.

DataSource.Error: Web.Contents failed to get contents from 'https://api.dropboxapi.com/2/files/list_folder' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.dropboxapi.com/2/files/list_folder
Url=https://api.dropboxapi.com/2/files/list_folder

Any ideas what this means?

Thnaks

Mike

[hugoberry]

I have created a DropBox data connector using the Power Query Extensions SDK. You are welcome to try it or check the code for a possible answer
https://github.com/Hugoberry/PowerQueryConnectors/blob/master/src/DropBox/DropBox.pq

[masplin]

Actually I managed to set up the new personal gateway so that i coud use the files on dropbox as a data source for a PBIX file

I think thisis doing somethnig different? Coudl you just explain in what instances I woudl be looking to use this for futre refernece?

Thnaks

Mike 

[ImkeF]

Hi guys,

thanks for these helpful different solutions! I've picked up some code bits from this thread and created a function that makes it pretty easy to grab files from a dropbox-folder: https://www.youtube.com/watch?v=eEVRO-Zc7pg

This should be helpful for folks who are not so tech-savy and might stumble across this post.

[masplin]

Hi Imke

I set out to follow you instruction to use the App folder instead of a normal dropbox folder which seems to be almost impossible. Anyway i created my app and put some files in the folder.  However I got imeddiately stuck because you can't share an app folder.  We have multiple users sharing our BI dropbox and any of them might be adding new files tot the fodlers each month. So if the App folder can't be shared does that mean the only way to get new data in an App folder is to login to Dropbox as the app owner?  Just seems a real pain if thisis the case as otherwise your solution looks elegant.

I realsie thisis a dropbox questionnot a PowerBI question, but thoguht you might know if any way round this.

Thanks

Mike

[ImkeF]

Hi Mike,

having difficulties to follow you here: What do you mean with "App folder"?

To my understanding you need to create an App in Dropbox to be able to generate an API-key. For the use in PBI you don't need the Dropbox-app explicitely, just the key/token.

The folder you can choose is just a "normal" folder in the dropbox.

But I might be totalally mistaken here, actually I don't use this myself.

[masplin]

In your post you put the files inside the App folder so assumed you had to do that. I'll go and try with a normal dropbox folder. and report back

[masplin]

Ok so I used your code and token I had got for the whole of my dropbox. If I put no folder path I get somethnig

However i need a folder that is several layers down  Datamodel/3rd party Bookings/WCFMC

If i put in anything in the folder bit it errors

An error occurred in the ‘Query1’ query. DataSource.Error: Web.Contents failed to get contents from 'https://api.dropboxapi.com/2/files/list_folder' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.dropboxapi.com/2/files/list_folder
Url=https://api.dropboxapi.com/2/files/list_folder

Any ideas how to get it to read the contents of subfolders?

Thnaks

Mike

[ImkeF]

Folder need to start with a slash:

see here: http://www.thebiccountant.com/2017/06/25/import-multiple-files-dropbox-folder-powerbi-excel-via-powe...

[masplin]

Ah perfect. looks like its working. Fantastic.

your the best

Mike

[masplin]

Sadly the previous poster is correct. your method works within excel and PowerQuery, but you cannot use that same query in PDI Desktop as fails on the gateway

That is really annoying.

Mike

[ImkeF]

Really? That is a pain!

Actually, I don't have a gateway in place on my local machine, so that could explain why it still works for me.

@JAkerblom : How about you: Do you also don't have a gateway installed?

[JAkerblom]

@ImkeF, No I don't have a gateway installed. Didn't understand why I'd need one to make a specific API call. 

I see now that people need to specifically list folder contents and not just go for a specific file, which is what I did. First I thought it was the api method that couldn't provide that list results of folder contents, but when I tried to just do a command line curl call it worked just fine, i.e. the list_folder method can return the list of folder contents (but just not in PowerBI Desktop for some reason..). I tried all types of header parameters in the web.contents method to make it work, but I didn't succeed. Either I'm doing it wrong or it's just not possible. But it would be really strange if certain API calls just don't work.  

[masplin]

I think this is the best one for us to vote on

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6663925-dropbox

[masplin]

Ok so plan B. The out of the box method in power Query for querying a folder is create this big mess with sample files that is tremendusly confusing.  Is there a way to just have a single qury that does the job?

For example I want to query these 2 files in a dropbox folder. The first step I see this

However if i click the arrows on binary it sets off greating the sample file.

Can I get round this with some code similar to what Imke wrote for dropbox?  I assume it is a similar exercise but my M is too pooor to work it out 😞

Mike

[masplin]

Found it 🙂

https://www.myexcelonline.com/blog/consolidate-multiple-excel-workbooks-using-power-query/