Download/Export Public Google Spreadsheet as TSV from Command Line?

Question

I have a public (published) Google spreadsheet that I’m trying to download programmatically in TSV form.

In my browser, with a Google login active, for some actual key $key, https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=$key&exportFormat=tsv works and produces a TSV file.

In my shell, however:

• curl -L "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=$key&exportFormat=tsv" produces a bunch of javascript.
• curl -L "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=$key&exportFormat=csv" also produces a bunch of javascript.
• curl -L "https://docs.google.com/spreadsheet/pub?key=$key&single=true&gid=0&output=csv" works and produces a CSV file.
• curl -L "https://docs.google.com/spreadsheet/pub?key=$key&single=true&gid=0&output=tsv" produces an error message.

(Attempts to use wget produced similar results.)

How do I make this work? All the Google documentation I’ve been able to find so far is geared towards much more complicated problems than a simple download and format change, and if the solution to my problem is in there somewhere, I haven’t been able to find it yet.

Answer 1

I found this to be frustratingly undocumented. I'm sure it's documented somewhere... but I never found it.

The premise is that your Google Sheet is published publicly. This is not intuitive for many folks. (Choose File -> Publish to Web...)

When you publish a sheet, you are given a url like this to copy: https://docs.google.com/spreadsheets/d/1XsfK2TN418FuEstNGG2eI9FmEV-4eY-FnndigHWIhk4/pubhtml

That url is nicely browsable... but it's not the downloadable CSV I wanted. Through a lengthy combination of search and trial-and-error I came up with this:

curl -L "https://docs.google.com/spreadsheets/d/1XsfK2TN418FuEstNGG2eI9FmEV-4eY-FnndigHWIhk4/export?gid=0&format=csv" > ./my_local.csv

Note that the gid=0 is often correct, since it seems to be the default ID for the initial tab. But it's not necessarily correct. See the answer below for more details about the gid.

I find this example to be tremendously helpful. I hope somebody comments with a link to the official docs explaining this in more detail.

Answer 2

I can download through the shell in this way:

1. File => Publish to Web
2. Choose a Sheet and the format do you want to download.
3. Click on Publish
4. Copy the link

5. and then use it:

   wget -O ./filename.csv "LINK" 

   or

   curl -L "LINK" > ./filename.csv 

in my case it worked as expected.

Plus I think that it publish all the formats so you can choose what to download changing the last part of the URL without un-publish and re-publish it:

output=tsv output=csv