Google sheet data download suddenly stopped working

[michael163]

I use data from google sheets in a number of our apps and as of this morning I got the same error message that they weren't automatically updated in Service. After further inspection (in PBI desktop) I found out that these connection don't download data they are supposed to but some gibberish in html (I attach the picture).

1) I tried the links and when I put them in browser they work correctly as they download what PBI is supposed to but doesn't. So clearly the problem is on the PBI side.

2) I cleared the cache and permissions but with no result

I need an urgent help as I am clueless and almost all of my business relies on these data downloads, thank you!

[michael163]

Thank you all for the reply. As the first solution I received was to change the download links, I spent whole day on rewriting them for every app that we have. This worked but it worked only beacuse I did the second step ->

I found out that all that I needed to do was what @NelxUK was saying, which is only to switch in Share button Get Link section to "Change to anyone with the link".

Until now I don't know the difference between publicizing the link to the web and writing the link in a manner suggested by @V-pazhen-msft. When I found out that I don't have to rewrite whole link I only enabled in google sheet sharing with anyone with the link and it worked as well. So I guess both options will work as long as you have it turned on in google sheet. Thank you everyone for your help!

[NealxUK · Accepted solution]

I have since found out that google have added something.

To resolve this, go to the google doc, select file, click on share.
On the Get Link section, click on "Change to anyone with the link".

All should be good from there.

[V-pazhen-msft]

@michael163 

The easiest way is to Get Data / From Web, then enter the URL to your google sheet, you must add "&output=xls" on the end, e.g.

http://spreadsheets.google.com/pub?key=r1hlZB_n1rpXTij11Kw7lTQ&output=xls

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

i have the exact same problem. (i am in SR too)

[michael163]

So I moved further with the solution, however I still dont have it. The PowerBI started treating my google sheet data as html (thus <doctype html>) instead of csv even though there is this start of script in advanced editor

Source = Csv.Document(Web.Contents("https://docs.go........."

I still cant figure out what has changed and causes this.