1

I've been downloading the first sheet of old Google Spreadsheets as a TSV file using URLs like:

 https://docs.google.com/feeds/download/spreadsheets/Export?key=ID&exportFormat=tsv&gid=0

After creating some new Google Sheets, I notice that the URL format in exportLinks has changed to this sort of thing (could only see PDF and XSLX formats though):

 https://docs.google.com/spreadsheets/export?id=ID&exportFormat=tsv&gid=0

But while either URL format works fine for the old Spreadsheets, neither of them works for the new Google Sheets... instead I get an error about this not being a P3P policy.

So can new Sheets only be downloaded using some new protocol surrounding the URL?

That would be sad.

I'm using the Python 2 Google API (because there hasn't been a Python 3 version).

Does it need to be upgraded?

Is there a Python 3 version?

Why does a P3P policy error show up?

What question should I be asking?

The Google Sheets I'm trying to download was created by me, although it is shared with others. I can export it from the menu in the browser, but can't grab it from my program. Would surely like to download data from the new Sheets, as well as the old Spreadsheets.

Here are my imports:

from apiclient import errors
from apiclient.discovery import build
from apiclient.http import MediaFileUpload
from oauth2client.tools import run
from oauth2client.file import Storage
from oauth2client.client import OAuth2WebServerFlow

Edit: While I am using the Google Python library, additional testing using only the browser inspired by the first answer received from pnuts indicates that the same URLs have the same problems direct from an appropriately logged in browser, so the age of the Python library doesn't seem to be the issue.

Improve this question

2 Answers 2

Reset to default
1

OK, some more searching and I found enough hints‡ to find something that works, whether it is officially documented this way, I have no idea. I would love to know where the documentation might be.

https://docs.google.com/spreadsheets/d/ID/export?format=tsv&gid=GID

The ID is what you would expect, the ID of the sheet's key from the URL. But the other surprising thing with Google Sheets, is that the GID no longer predictably starts at 0 for the first sheet, and increments for subsequent sheets. Whether this is supposed to add security or is an accident of the implementation, or an especially useful design for some internal reason, I do not know... but the GID numbers on a 7 sheet Sheet I just uploaded from XLS format are all large, and appear random.

While a particular sheet from a given spreadsheet may easily be determined from the URL displayed while viewing that Sheet in the browser, it looks like the knowledge that "all Sheets in this folder contain only one sheet" is insufficient to be able to download them, and the ability to "download the first sheet from each of these Sheets" is eliminated, unless the GIDs can be determined.

In fact, it seems my whole problem was the assumption of the first gid being zero, which it always has been for old Sheets... the URL form in this answer seems to be closest to what is used for editing new Sheets in the browser, but both forms of URL in the original question also work, if the proper GID is used.

Bonus points for a technique to determine the GID... based on what I thought I knew about old Sheets, I was planning for a system of 8000+ Sheets, with 4-6 sheets each, and thinking I could download them, one sheet of each Sheet at a time, by iterating through low-numbered GID values. KaBLAM goes that design, with the non-predictable GID values!

‡ The hints start here (but this solution seems excessively lengthy) and proceed to the here and here.

Improve this answer
1
  • Now I've found this which talks about using the sheet parameter instead of the gid parameter. That might re-enable my system of 8000+ Sheets, because I could control the sheet names. But it doesn't enable download the first or only sheet (with unknown names) of a bunch of Sheets.
    – Victoria
    Jul 2, 2014 at 6:13
0

I'm not sure why this Q is being asked on this site (seems may be rather more about Python coding than use of Google Spreadsheets) but this format (ie as above):

https://docs.google.com/spreadsheets/export?id=ID&exportFormat=tsv&gid=0

works for me where ID is replaced by the sheet's key and, if required the 0 on the end with the relevant sheet's gid number.

Improve this answer
3
  • The Python script worked fine for old Google Sheets, but fails with new Google Sheets... so it seemed the Google Sheets changes were more at fault than the Python script? But the error is very strange. Other than the specific URL used, it would seem that the script would do the same thing, so again it seems that it is Google Sheets that changed, rather than the Python script. But it is hard to say. Using that form URL produces an error in my logged-in browser too, "file does not exist", but I pasted the ID and checked both ends, so I'm sure it is correct.
    – Victoria
    Jul 2, 2014 at 4:11
  • And doing the same thing for an old Google Spreadsheet worked fine. So that absolves the Python script. Are you sure you are successful with this for new Google Sheets?
    – Victoria
    Jul 2, 2014 at 4:23
  • Although, from the browser, in spite of the "exportFormat=tsv&gid=0" it downloaded XLSX for the old Google Sheet, with either of the forms of URL in my original question.
    – Victoria
    Jul 2, 2014 at 4:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.