Automating data downloads from a Google spreadsheet
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 […]
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=csvThe KEY in the URL above is a long alphanumeric string that looks like this:
1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08Replace 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=863218371The 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=csvIf 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.
