Downloading data from a Google Spreadsheet is usually as easy as clicking file->Download->CSV

But sometimes you need to automate the download.

You can either do this by publishing a CSV version of the file (file→share→Publish to the web) if you have editing access to the file.

If you don’t have editing access you can still access the data on public documents by using a URL like this:

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

The KEY in the URL above is a long alphanumeric string that looks like this:

1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08

Replace the KEY with the matching KEY you find in the URL for a spreadsheet.

For example, this is the URL for a public spreadsheet with loadshedding data for South Africa:

https://docs.google.com/spreadsheets/d/1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08/edit#gid=863218371

The KEY is the portion in bold.

To construct the CSV URL remove everything after the forward-slash immediately after the KEY and replace with export?format=csv

The new URL will look like:

https://docs.google.com/spreadsheets/d/1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08/export?format=csv

If you open this URL you will have a CSV-format file downloaded to your computer.

Note: The spreadsheet URL used in the example above is for the Eskom Se Push loadshedding dataset (Loadshedding is the South African term for rolling blackouts, which have been in effect for the past few years). We use this technique to download a daily copy of the data into DataDesk which we then publish as in both CSV and JSON format. The end result is the loadshedding calendar we publish on The Outlier here.