CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-drive-api
FCastell
FCastell

Reputation: 474

"Publish to web" on Google Sheets not working properly

Yesterday there was a disruption on Google Drive according to Google Appstatus. At the same time I start getting erros from several apps that READS PUBLISHED TO WEB Google sheet files in XLS and CSV format. 24 hours later my team still got erros.

When I tried to read a public link in Python, QlikView ou PowerBI I got an error; when a use it in browser it works fine (shows the file if html or download XLS ou CSV files). when I download the files I can load then in the application with no problems.

as an example, the link is this: https://docs.google.com/spreadsheets/d/e/2PACX-1vR-n29UtC60LUE4q0YB0mDW8LNlhychOlCqOv_svzL4tuUfYmTD15o2nmleW0k4GKLaJWBzBKtVRo4c/pub?output=xlsx (you can change XLSX for HTML or CSV to change the format).

Strange thing is that in Python when I try to load the XLSX file it does not work, however it does if I change to HTML, as below:

#I GET AN ERROR: import pandas as pd

df = pd.read_excel('https://docs.google.com/spreadsheets/d/e/2PACX-1vR-n29UtC60LUE4q0YB0mDW8LNlhychOlCqOv_svzL4tuUfYmTD15o2nmleW0k4GKLaJWBzBKtVRo4c/pub?output=xlsx')

#I DO NOT GET AN ERROR:

df = pd.read_html('https://docs.google.com/spreadsheets/d/e/2PACX-1vR-n29UtC60LUE4q0YB0mDW8LNlhychOlCqOv_svzL4tuUfYmTD15o2nmleW0k4GKLaJWBzBKtVRo4c/pub?output=xlsx')

The downloaded file is attached.

Any help is appreciated. Thanks.

Upvotes: 1

Views: 4749

Answers (2)

Dennis
Dennis

Reputation: 76

Solution found : Make sure to set the published spreadsheet as public. you also need to make it public in the sharing options at the top right (green button)

I got the same issue, but I learned a few things around it. An already published file will not work, but if you publish a new file, it will. Easy way to test the bug is also an issue for google is to open a new sheet and write =ImportFile("PathToAPublishedCsv") you will see it works fine for newly published, but not for already published files.

I have tried to find a google support to chat with, but no luck so far

The reason for the html working is that you are getting an inbetween page before you get to the data on the already published files, and that is an html page

Upvotes: 4

Kalman Holes
Kalman Holes

Reputation: 1

I have the same issue when I try to open the published csv links. All published csv ask for permission in the last 2 days. Something changed within the Google Sheets, but I didn't found any information about this.

Upvotes: 0

Related Questions