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&"""}"],
