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

here's a tiny command line tool that can do this (CSV, not TSV; maybe close enough): https://www.npmjs.com/package/gsheet-cmd

Answer 2

This is the parameters list I have founded along the years:

&format=pdf                   //export format
&size=a4                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
&portrait=false               //true= Potrait / false= Landscape
&scale=1                      //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
&top_margin=0.00              //All four margins must be set!
&bottom_margin=0.00           //All four margins must be set!
&left_margin=0.00             //All four margins must be set!
&right_margin=0.00            //All four margins must be set!
&gridlines=false              //true/false
&printnotes=false             //true/false
&pageorder=2                  //1= Down, then over / 2= Over, then down
&horizontal_alignment=CENTER  //LEFT/CENTER/RIGHT
&vertical_alignment=TOP       //TOP/MIDDLE/BOTTOM
&printtitle=false             //true/false
&sheetnames=false             //true/false
&fzr=false                    //true/false
&fzc=false                    //true/false
&attachment=false             //true/false

//FORMATS WITH NO ADDITIONAL OPTIONS
  //format=xlsx       //excel
  //format=ods        //Open Document Spreadsheet
  //format=zip        //html zipped          

  //CSV,TSV OPTIONS***********
  //format=csv        // comma seperated values
  //             tsv        // tab seperated values
  //gid=sheetId             // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID

  // PDF OPTIONS****************
  //format=pdf     
  //size=0,1,2..10             paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5  
  //fzr=true/false             repeat row headers
  //portrait=true/false        false =  landscape
  //fitw=true/false            fit window or actual size
  //gridlines=true/false
  //printtitle=true/false
  //pagenum=CENTER/UNDEFINED      CENTER = show page numbers / UNDEFINED = do not show
  //attachment = true/false      dunno? Leave this as true
  //gid=sheetId                 Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. 
                               // Leave this off for all sheets. 
  // EXPORT RANGE OPTIONS FOR PDF
  //need all the below to export a range
  //gid=sheetId                must be included. The first sheet will be 0. others will have a uniqe ID
  //ir=false                   seems to be always false
  //ic=false                   same as ir
  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number 

Answer 3

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 4

My answer is about how to find the answer.

In Chrome browser, navigate to your google document.

In the upper right corner of the browser, go to the three dots->more tools-> developer tools

This will bring up the html... debugger.

At the top of the debugger window, select network.

Now in your document, initiate the download as that you're trying to automate.

In the debugger, it'll show you any web requests that are made. The first new one is probably what you want.

You should be able to right click->copy-> copy link address

The url includes an ID. I don't know what it's for, but curl was able to download the doc without.

Hope it's helpful.

Answer 5

To add to the answer written by @mdahlman: there is a gid=<value> argument that lets you chose the sheet to view (as CSV and TSV support the viewing of just one sheet). This is a sheet ID and you can pick it up from the URL of each sheet.

So, to get a CSV/TSV publish link, do this:

1. Publish the document to get a URL like https://docs.google.com/spreadsheets/d/e/{key}/pub?output=tsv.

2. Then for each spreadsheet:

   1. Click on it.

   2. View its URL in your browser's address bar. It'll end with edit#gid={gid}. That's what you want.

   3. Make your URL from the one in step 1. and gid in 2.2.: https://docs.google.com/spreadsheets/d/e/{key}/pub?output=tsv&gid={gid}.

GIDs don't go in sequence (0, 1, 2,...). They are long numbers (9 digits for me), seemingly in no straight order or anything, so they're really more like sheet keys than what one would expect as an "id".

In my document, one of the GIDs was zero. I am assuming it's some sort of a default or a first created sheet. That explains why gid=0 worked for some people above, yet produced an error for others (those who don't have a sheet with such GID... they have possibly deleted it or something).

Answer 6

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