Reply
New Member
Posts: 1
Registered: ‎09-13-2016

Connecting to data source hosted on Dropbox

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!

 

Super Contributor
Posts: 5,376
Registered: ‎07-11-2015

Re: Connecting to data source hosted on Dropbox

When you said that it didn't work, what kind of error/issue were you seeing?

Highlighted
Super Contributor
Posts: 1,662
Registered: ‎12-15-2015

Re: Connecting to data source hosted on Dropbox

@JohnSwid You need to register for Dropbox developer account and generate access token. Then use URL provided as below to connect. FILE PATH is folder/filename.extension. When you register app for access token choose full permission and it should work.

 

https://api-content.dropbox.com/1/files/[FILE PATH]?access_token=[ACCESS TOKEN]

 

https://api-content.dropbox.com/1/files/abc/myfile.txt?access_token=12345 where abc is folder, myfile is file and 12345 is access token.

Moderator
Posts: 4,661
Registered: ‎03-10-2016

Re: Connecting to data source hosted on Dropbox

[ Edited ]

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.
1.PNG

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

Established Member
Posts: 178
Registered: ‎06-28-2015

Re: Connecting to data source hosted on Dropbox

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?

Member
Posts: 131
Registered: ‎09-27-2015

Re: Connecting to data source hosted on Dropbox

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

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

 

Member
Posts: 108
Registered: ‎02-03-2017

Re: Connecting to data source hosted on Dropbox

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.

Frequent Visitor
Posts: 2
Registered: ‎03-01-2017

Re: Connecting to data source hosted on Dropbox

[ Edited ]

Thanks to you I am finally able to call data from Dropbox. I built upon your list_folder API call , added a download call after it:

 

- Actual data table would be:

 

let
    header = [  #"Authorization"="Bearer "&token,
                #"Content-Type"="application/json"],
    data = [    path="",
                recursive=true,
                include_media_info=false,
                include_deleted=false,
                include_has_explicit_shared_members=false],
    response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]),
    out = Json.Document(response,1252),
    entries = out[entries],
    #"Converted to Table" = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}, {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([#".tag"] = "file")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "file", each fGetTable([path_display])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}),
    #"Expanded data" = Table.ExpandTableColumn(#"Removed Columns", "file", {"COLUMN1 HERE", "COLUMN2 HERE"}, {"COLUMN1 HERE", "COLUMN2 HERE"})
in
#"Expanded data"

 

- keep in mind you have to type your expanded columns manually, user interface expanding does not seem to work with this method...

- "token" (line 2) is a defined parameter with the actual generated access token for the Dropbox application, you could type it between quotes as text right there instead.

 

- fGetTable function, which is the actual download query (referenced in line 15) would be:

 

 

let GetFile=(file) =>    

let
    header = [  #"Authorization"="Bearer "&token,
                #"Dropbox-API-Arg"="{"&""""&"path"&""""&":"&""""&file&""""&"}"],
    out = Web.Contents("https://content.dropboxapi.com/2/files/download",[Headers=header]),
    excel = Excel.Workbook(out, null, true),
    table = excel{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(table, [PromoteAllScalars=true])
in
    #"Promoted Headers"

in GetFile

  

The "Dropbox-API-Arg" was escaping-quotes hell...

Edit: double quotes is one escaped quote inside a string... didn't need so much escape and concatenate, just changed that line to:

 

#"Dropbox-API-Arg"="{""path"":"""&file&"""}"],
Member
Posts: 108
Registered: ‎02-03-2017

Re: Connecting to data source hosted on Dropbox

Hi @gparrello I'm glad that it helped you.

Not sure what you mean that you have to type the column names yourself. I'm sure that you can work out some automated solution. If you need help, just start a new thread and I'll look into it.

Established Member
Posts: 178
Registered: ‎06-28-2015

Re: Connecting to data source hosted on Dropbox

[ Edited ]

Thanks so much @gparrello and @hugoberry for sharing your findings.  Am I right in understanding that this will only work on folders owned by the Developer who generates the Dropbox app & access key?

 

I want to point Power BI at a Dropbox folder that's been shared with me.  So I dont know the full folder path.