Download unpublished Google spreadsheet as CSV

Question

I have a Google spreadsheet that has not been made public, but just available to anyone that has the access link. Though I can access the data in CSV format from my browser even when I am not logged into my Google account (that is, anonymous access is allowed), there is no way I can download the data from the command line using wget, for instante. I have found several web pages with some instructions to create the download URL, but so far I've had no success. Is there an easy, straightforward way of doing this or will I have to use some Google Data library to access that data?

Answer 1

Maybe I'm not correctly understanding what you're trying to do, but I found a solution in this article that works just fine for me.

In the article, the author creates a download link for the spreadsheet as an XLS file (using "&output=xls”), but I tried "&output=csv" and successfully downloaded a correct CSV file.

Here's the download link to my "Download Test Spreadsheet", constructed just the way the author of the article suggested, but with "csv" substituted for "xls":

https://docs.google.com/spreadsheet/ccc?key=0At2sqNEgxTf3dEt5SXBTemZZM1gzQy1vLVFNRnludHc&output=csv

The link is anonymous (i.e., "Anyone who has the link can view"), and I downloaded it without logging into my Google account. I'll admit that I didn't use wget to do it (I just used a browser -- I didn't have wget installed), but I can't think of a reason that wget wouldn't work just as well.

Actually, I just grabbed a copy of wget and tried it, and it downloads the file correctly too:

% wget --no-check-certificate --output-document=test.csv 'https://docs.google.com/spreadsheet/ccc?key=0At2sqNEgxTf3dEt5SXBTemZZM1gzQy1vLVFNRnludHc&output=csv'

< bla bla bla, reams of output from wget >

% cat test.csv
Foo,Bar,Baz
1,2,3
4,5,6

So there ya go...

---

UPDATING FOR 2018

As commented by @AndyMortimer, the new download URL is
  https://docs.google.com/spreadsheets/d/<KEY>/export?gid=<GID>&format=csv
where <KEY> and <GID> can be obtained from your navigation's URL,
  https://docs.google.com/spreadsheets/d/<KEY>/edit#gid=<GID>

PS: spreadsheets may have multiple workbooks, GID is the desired workbook ID. One-workbook-spreadsheet usually has gid=0, but if you add more they'll have random numbers (the GID is preseved even changing tab-order).

So, using wget and the same spreadsheet,

   wget --no-check-certificate -O test.csv \
    'https://docs.google.com/spreadsheets/d/0At2sqNEgxTf3dEt5SXBTemZZM1gzQy1vLVFNRnludHc/export?gid=0&format=csv'

Answer 2

I'm able to get a wget-able URL from public spreadsheet URLs that look like this:

https://docs.google.com/spreadsheets/d/LONG_ID_STRING/edit?usp=sharing

by changing them to look like this:

https://docs.google.com/spreadsheets/d/LONG_ID_STRING/export?format=csv

so that my wget command would look like:

$ wget "https://docs.google.com/spreadsheets/d/LONG_ID_STRING/export?format=csv"

Make sure you share the spreadsheet first with the blue "Share" button in the top right, then "Get Sharable Link".

Answer 3

To access other tabs you need to click and get the gid of that tab, so:

https://docs.google.com/spreadsheets/d/1GxmL3bbejjGx-Ji7kzsnZLzkSaMrfX5WwgRAvvqfqpw/pubhtml?widget=true&amp%3Bheaders=false#gid=2120833204

Becomes:

https://docs.google.com/spreadsheets/d/1GxmL3bbejjGx-Ji7kzsnZLzkSaMrfX5WwgRAvvqfqpw/export?format=csv&id=1GxmL3bbejjGx-Ji7kzsnZLzkSaMrfX5WwgRAvvqfqpw&gid=2120833204

Answer 4

Now I can answer questions, so I will leave it answered for good (pasting from my previous comment).

My problem was that I was not storing the cookie correctly. At the time, I was using a Python program to try to download this. This fixed the problem I was reporting:

# Cookie management 
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(CookieJar())) 
csv_data =  csv.reader(opener.open(url))

Answer 5

Ran into this problem and tried a few of the solutions. None were working perfectly so I wrote this curl which seems to do the job. You need to share your google spreadsheet with anyone who has the link for it to work

curl -o /path/to/file/you/want/csv/to/go.csv 'https://docs.google.com/spreadsheet/ccc?key=[the-id-of-your-spreadsheet]&output=csv' -L

the -L is the trick here has google often moves the csv around but will send you a redirect link. -L tells curl to follow the redirects until it lands on the file.

you can find the id of your spreadsheet by opening it in google drive and looking at the url

https://docs.google.com/spreadsheets/d/[idwillbehere]/edit#gid=0