Google Docs CSV returns html instead of data

[jasv]

Hi there!

I have a report running for months. Starting yesterday, it stopped working. The Power Query that fails is:

= Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/[long code]/pub?gid=0&single=true&output=csv"))

This worked perfect until two days ago, but now it returns the html text instead of the csv output:

When I just hit a random key after 'csv' (&output=csvf), wait for a second or so and then change it back to the correct url, it works once but the refresh (the preview or the full data) still fails.

Does anyone have any idea how this went wrong?

[jasv · Accepted solution]

I checked the HTML page a little further, and it is a login page. Though the settings are so that it is publicly published, it appears to redirect to a login page only to check that a login is not required and then continues or something. In a browser, you don't see this but Power BI fails randomly. Remember: the Google link worked flawlessly for months and I didn't change the report at all.

@camargos88: Thanks for your reply. Your solution should work, as my solution should work as well. But it doesn't as of this week.

@thnemeth: Thanks for your backup. I'll keep you posted.

@edhans: That looks promising. I'll check right now.

--

Update: Worked!

The solution is simple: Go to file > share and change it to 'anyone with the link can read' (or something like that, I'm not using Docs in English). That is rediculously **bleep**ty because I used the 'publish on the internet' feature which is a completely different menu option and didn't need a change in 'share' previously. Apparently, Google changed this without notice.

[edhans]

See if this thread helps @jasv , @thnemeth , and @NealxUK  - another user had this issue and they changed the format of the download.

[thnemeth]

I keep trying to reply to this and it keeps disregarding my reply...

This is happening to me too.  For weeks I've had google sheets published online and I've connected to them as a CSV.  It's been working fine until yesterday.  Now, I get a bunch of errors (Referencing columns that no longer exist).  The columns no longer exist because if you go to "Source" you'll see HTML code instead of the raw CSV sheet.

ODDLY, if you start a new web connection to your CSV you'll see the preview load correctly.  And then you'll see it loaded correctly in the query editor.  But if you click refresh, and are on the last applied step (Probably "Changed Types") it'll fail.  Then if you go to "Source" You'll see HTML like in the OPs screenshot.

This is highly problematic.  I tried loading as XLSX instead but I can't seem to tell google sheets that my data is a table.

Edit: My only workaround for now is to edit my MSPBI queries a bit to look at the source as an actual webpage and then format a table based on that instead.  So like, publish as web page, use that link, and then different 'applied steps' to get it to look like what your table used to.  This works for me because my published tables are VERY SIMPLE reference sheets (Like 3-5 columns), but I can't imagine this working for larger published documents.

[camargos88]

@jasv ,

Check this link:

https://natechamberlain.com/2019/10/08/connect-a-google-sheet-spreadsheet-to-power-bi-as-a-data-sour...

I've tried with some different url from yours.

[NealxUK]

Hello. I also have the exact same issue as jasv.  

Mine were working perfectly and yesterday seemed to have stopped.

I have spent much time trying to sort this out, but no luck.

I went to the link posted, but there is nothing there that helps.

My link is currently (edited for privacy):

https://docs.google.com/spreadsheets/d/e/2PACX-1xxxxxxxxxxxxxxxxxpQDERxyhGfo/pub?gid=1086830843&sing...

It is one tab of a google sheets document.

I have two others, from the same document and they are both showing the same html.

The html is also very similar to jasv.

Do we know if this is this a Microsoft issue or a Google issue ?