topic Re: Dropbox API v2, as API1 has been turned off in Power Query

Dropbox API v2, as API1 has been turned off

masplin — Mon, 27 Nov 2017 08:01:32 GMT

I have been linking severla single files stored on dropbox using API! whiuch seems to have bene disabled last night. I think there are a lot of us in the same boat. Can someone say what the equivalent code is for API2? I tried change the path to "https://api.dropboxapi.com/2/files/auto/DataModel/SourceFiles/" but that just errors. My files are just sitting in a normal dropbox folder as can't us an App folder as can't be shared.

The code i was using was

let
    Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "Inputs.xlsx" & Token), null, true),
    Centre_Table = Source{[Item="Centre",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Centre_Table,{{"Opening Date", type date}})
in
    #"Changed Type"

Where Dropboxsourcepath

let
    Source = "https://api-content.dropbox.com/1/files/auto/DataModel/SourceFiles/"
in
    Source

and token

let
    Source = "?access_token=xxxxxxxxxxxxxxxxxxxxxxx"
in
    Source

Grateful any advice as whole company reporting has collapsed

Thanks

Mike

Re: Dropbox API v2, as API1 has been turned off

Anonymous — Tue, 28 Nov 2017 11:17:46 GMT

masplin,

Do mask sensitive data before uploading scripts or screenshots.

In your scenario, add a new blank query in Power BI Desktop, then paste the following code into the Advanced Editor of your query.

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer <your access token>", #"Dropbox-API-Arg"="{""path"": ""/folder/path/filename.xlsx""}"]]), null, true)
in
    Source

And I would commend you firstly test that if your API returns correct result in DropBox API Explorer.

Regards,
Lydia

Re: Dropbox API v2, as API1 has been turned off

masplin — Mon, 02 Oct 2017 12:47:15 GMT

Hi Lydia

I'm obviously doing somethnig stupid. the path to my file is U:\Dropbox\DataModel\SourceFiles\Input.xlsx

In API explorer I got this

Can you tell me where i've gone wrong please?

Thanks

Mike

Re: Dropbox API v2, as API1 has been turned off

granthworth — Mon, 02 Oct 2017 13:35:45 GMT

Hey masplin

I am still working on getting ours resolved also. I have not gotten around the issue completely yet but I was able to get at least a portion of Lydia's fix to work. See below for my info. Hope this helps.

Re: Dropbox API v2, as API1 has been turned off

masplin — Mon, 02 Oct 2017 13:42:00 GMT

let an "s" of the file name Duh!!!!

Brilliant

Re: Dropbox API v2, as API1 has been turned off

masplin — Mon, 02 Oct 2017 13:49:05 GMT

Ok so I got a repsonse from the API explorer uses this token and path, and can download the file however i then put this code in Power Query and get an error "unable to conenct to the remote server"? Any idea?

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer lkPfa4vOd-AAAAAAAAAAXpFuGV2UKJk0D3WGQtPjNeIeZNwdopvcaSs_y3gguRYY", #"Dropbox-API-Arg"="{""path"": ""/DataModel/SourceFiles/Inputs.xlsx""}"]]), null, true)
in
    Source

Re: Dropbox API v2, as API1 has been turned off

granthworth — Mon, 02 Oct 2017 14:07:35 GMT

That, I am not sure about.

I was able to finally get our entire system up and running again. I updated ONLY the first line of code in each query under the Advanced Editor (only the code highlighted in blue below) as shown below. I left all other code the same and all queries now return with no error.

Re: Dropbox API v2, as API1 has been turned off

masplin — Mon, 02 Oct 2017 15:43:15 GMT

Must have been an issue on my workcomputer as now works fine.

Last question as you can see in my old API1 code I used 2 subset queries one ot hold the Token and one to hold the path. Made it easier if either changed.

I tried this code by doesn't seem to work unless you write both out in full

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer Token", #"Dropbox-API-Arg"="{""path"": ""DropboxSourcePath & Inputs.xlsx""}"]]), null, true)
in
    Source

Where DropboxSourcePath is

let
    Source = "/DataModel/SourceFiles/"
in
    Source

and Token is

let
    Source = "lkPfa4vOd-AAAAAAAAAAKjmGh93HWPeYF-CY0LSTVss5JF53HOGK46xxk"
in
    Source

Assume i have an error in my syntax?

Mike

Re: Dropbox API v2, as API1 has been turned off

Dindo — Tue, 03 Oct 2017 11:21:41 GMT

If you're running with on premise gateway, how would you add this data source?

In V1 it's easy since it reads as a web url source. Anyone has luck trying this out?

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 11:41:33 GMT

The code above works fine except you have to put the exact tokena nd path in the source line. i managed to get it to read the table.

I'm just hoping to be able to use some sub queries ot make it look neater and easier to manage

Re: Dropbox API v2, as API1 has been turned off

Anonymous — Tue, 03 Oct 2017 19:03:20 GMT

your code (below) works only on PBI Desktop.

once it is published, it gets credential error on PBI Service.

is there any workaround to make it work and refresh on PBI Service?

let
Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer mytoken", #"Dropbox-API-Arg"="{""path"": ""/myfolder/myfile.xlsx""}"]]), null, true)

in
#"Source"

Re: Dropbox API v2, as API1 has been turned off

granthworth — Tue, 03 Oct 2017 20:27:40 GMT

You are exactly right. I just went to the service to change our refresh schedule and noticed the error. I am looking into it now and will report back if I find a solution. Sorry about that!

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 20:30:48 GMT

Hmm wierd as works fine for me on the srvice. I updated al lmy sources in desktop, republished and it just worked? did even touch the credentials.

Re: Dropbox API v2, as API1 has been turned off

Anonymous — Tue, 03 Oct 2017 20:36:02 GMT

yeah, I know. I suspect there is an issue in my Brazilian server from PBI Service, as i have another dropbox project that I know works on US east (Virginia) but does not work here.

where is your PBI Service data being stored? USA? other place?

which version are you using?

I am under:

Version 13.0.2597.188
Activity ID nah
TimeTue Oct 03 2017 17:34:25 GMT-0300 (Hora oficial do Brasil)
Your data is stored inBrazil South (Sao Paulo State)

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 20:37:36 GMT

where do you find that info? I'm in UK so guess some european host

Re: Dropbox API v2, as API1 has been turned off

Anonymous — Tue, 03 Oct 2017 20:47:52 GMT

at the upper right corner, click "?" button, and go to About Power BI.

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 20:49:48 GMT

north europe (ireland)

Re: Dropbox API v2, as API1 has been turned off

granthworth — Tue, 03 Oct 2017 21:00:36 GMT

My info:

Version13.0.2597.188

Activity ID......
TimeTue Oct 03 2017 15:54:20 GMT-0500 (Central Daylight Time)
Your data is stored inNorth Central US (Illinois)

Error screenshots from service:

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 22:03:31 GMT

Sorry I have no idea as not really a techy.

Re: Dropbox API v2, as API1 has been turned off

masplin — Tue, 03 Oct 2017 22:17:31 GMT

seems i have misled everyone. I rechecked and doesnt seem I had applied the source changes in my model. i just did republished and now have the same error as you guys. That's a little frustrating to say the least.

Mike