Using Google Sheets to Host Editable CSV Files

You can integrate Google Sheets with applications (like the Megalytic CSV Widget) that require CSV files by converting the Google Sheet URL to a slightly different form that forces a CSV download of the sheet’s data. This is very convenient because you can update the sheets via the Google UI and then the CSV download will be automatically updated. In the case of Megalytic, when you refresh the page, the CSV widget is automatically updated.


Google Sheets can be downloaded automatically as CSV files using a URL that you can construct from the sheet’s key. Take this sheet, for example:


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


The “key” is the long string appearing after “/d/”:
1qrtaGu5dwljVbHJeR4RIMWtdZKOauJfJ6nODlPwMRno


You can create a CSV download link for that sheet, but plugging the “key” into this URL structure:
https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv


So, in this case, using our example key, you get the following link (click to test it out):
https://docs.google.com/spreadsheets/d/1qrtaGu5dwljVbHJeR4RIMWtdZKOauJfJ6nODlPwMRno/gviz/tq?tqx=out:csv


If your Google Sheet has multiple tabs, you can add an additional “sheet” parameter on the end of the URL to specify which tab should be downloaded as the CSV. If you leave this off, the first tab will be downloaded. Here is an example using the “sheet” parameter:


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


Note that Google periodically updates how the exporting URL works. As of this writing (November 7, 2017) the approach outlined above works well. You can find more details in this stackoverflow post.