Forum Discussion

JohnSwid's avatar
JohnSwid
New Member
9 years ago

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: h...
elliotdixon's avatar
elliotdixon
Responsive Resident
9 years ago

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

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

 

hugoberry's avatar
hugoberry
Responsive Resident
9 years ago

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.

  • gparrello's avatar
    gparrello
    Regular Visitor
    9 years ago

    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&"""}"],
    • hugoberry's avatar
      hugoberry
      Responsive Resident
      9 years ago

      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.

      • mike_honey's avatar
        mike_honey
        Memorable Member
        9 years ago

        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.

      • masplin's avatar
        masplin
        Impactful Individual
        9 years ago

        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