Download/Export Public Google Spreadsheet as TSV from Command Line?
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
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 "https://docs.google.com/spreadsheets/d/1XsfK2TN418FuEstNGG2eI9FmEV-4eY-FnndigHWIhk4/export?gid=0&format=csv"
I find it to be tremendously helpful. I hope somebody comments with a link to the official docs explaining this in more detail.