{"id":22,"date":"2023-01-20T06:13:00","date_gmt":"2023-01-20T06:13:00","guid":{"rendered":"https:\/\/editor.mediahack.co.za\/databites\/?p=22"},"modified":"2025-11-17T17:42:19","modified_gmt":"2025-11-17T17:42:19","slug":"automating-data-downloads-from-a-google-spreadsheet","status":"publish","type":"post","link":"https:\/\/outliereditor.co.za\/index.php\/2023\/01\/20\/automating-data-downloads-from-a-google-spreadsheet\/","title":{"rendered":"Automating data downloads from a Google spreadsheet"},"content":{"rendered":"\n

Downloading data from a Google Spreadsheet is usually as easy as clicking File->Download->CSV<\/p>\n\n\n\n

But sometimes you need to automate the download.<\/p>\n\n\n\n

If you have editing access to the file, you can do this by publishing a CSV version of the file (File\u2192Share\u2192Publish to the web)<\/p>\n\n\n\n

If you don\u2019t have editing access, you can still access the data on public documents by using a URL like this:<\/p>\n\n\n\n

https://docs.google.com\/spreadsheets\/d\/KEY\/export?format=csv<\/code><\/pre>\n\n\n\n
The KEY <\/strong>in the URL above would be a long alphanumeric string that looks something like this:<\/p>\n\n\n\n
1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08<\/code><\/pre>\n\n\n\n
Replace the word ‘KEY’ with the matching alphanumeric key string that you find in the URL for a spreadsheet.<\/p>\n\n\n\n
For example, this is the URL for a public spreadsheet with loadshedding data for South Africa. The KEY is the portion in bold:<\/p>\n\n\n\n
https:\/\/docs.google.com\/spreadsheets\/d\/1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08<\/strong>\/edit#gid=863218371<\/code><\/pre>\n\n\n\n
To construct the CSV URL, remove everything after the forward-slash that follows the KEY and replace it with export?format=csv<\/strong><\/p>\n\n\n\n
The new URL will look like:<\/p>\n\n\n\n
https://docs.google.com\/spreadsheets\/d\/1ZpX_twP8sFBOAU6t--Vvh1pWMYSvs60UXINuD5n-K08\/export?format=csv<\/code><\/pre>\n\n\n\n
If you open this URL you will have a CSV-format file downloaded to your computer.<\/p>\n\n\n\n
Note: The spreadsheet URL used in the example above is for the Eskom Se Push loadshedding dataset<\/a>. (Loadshedding is the South African term for rolling blackouts, which have been in effect since 2014.) 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. <\/p>\n\n\n\n