How to use an API to Download a Google Docs Sheets Spreadsheet as CSV

This blog talks about how you can use a little known Google Docs Sheets API to download spreadsheets as a CSV.

Obtaining the documents Sheet ID

Before the spreadsheet can be downloaded you will first need to find the Sheet ID. This is a string made up of letters and numbers that uniquely identifies the spreadsheet.

For a Google Docs Spreadsheet the URL will be in the form:

https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit

You will need to take the ID from this to use it with the download API.

For example, the Pokemon Go Nesting data spreadsheet has the following URL

https://docs.google.com/spreadsheets/d/1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU/edit

From this we can obtain the sheet ID which is

1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU

The second piece of information that is needed is the name of the sheet you want to download. Since CSV can only hold a single page at a time you need to get the name of the Sheet. This is the name at the bottom of the page.

Now we are able to construct the download link

Constructing the CSV download link

Taking the Sheet ID above, we can enter it into the download link below

https://docs.google.com/spreadsheets/d/{SHEET_ID}/gviz/tq?tqx=out:csv&sheet={SHEET_NAME}

This means that if I wish to export the above spreadsheet as CSV, downloading the “Nest” sheet I can create the following URL.

https://docs.google.com/spreadsheets/d/1wlfCjE0gu1lCsNMEfv_81gUpoVdK80367slCBW_JoDU/gviz/tq?tqx=out:csv&sheet=Nest

Some of the formatting will be lost when the CSV is exported but this allows programmatic conversion of the Google Docs Sheets spreadsheet to CSV.