9

Google Sheets allows you to publish the data to the web as a .XLS file or .CSV etc. I have a system where I edit the spreadsheet and when I'm finished I run a script which downloads the .csv file and processes it for use on our website.

The problem is that the .csv file produced is sometimes a few minutes out of date which means every time I finish editing I need to hit the "publish to web"->"republish now" button before running my script. This is becoming a chore for me and not acceptable to ask my other users to remember.

I need to either make my command-line-script automatically tell Google to republish, or find another way to get the current data out of my spreadsheet.

Community
  • 1
  • 1
Christopher Gutteridge
  • 4,425
  • 2
  • 21
  • 20
  • Are you using the API or just downloading the file? – mcrumley Jan 26 '12 at 15:33
  • Here's a useful article on Medium that explains how to allow Google Sheets to update automatically: [How to share or embed a Google Sheets chart that updates instantly (no need to wait for 5 minutes)](https://medium.com/@steynviljoen/how-to-share-or-embed-a-google-sheets-chart-that-updates-instantly-no-need-to-wait-for-5-minutes-b6d325cd8598#:~:text=You%20probably%20know%20that%20you,updated%20once%20every%205%20minutes.) – kevinlacaille Aug 13 '20 at 14:15

2 Answers2

2

Google Sheets now has a setting to republish automatically on changes, but another thing that seems to be happening in my use is that an old version is getting cached by something on Google's side.

Adding a new parameter to the end of the URL (e.g. &foo=12312) works to avoid the cache and return the current version. If you're running from a script, just use the current time or a randomly generated number as the parameter value.

Edit: currently (January 2020) this doesn’t resolve the issue 100% of the time. I haven’t experimented to see if it still helps when using the API isn’t an option.

pidge
  • 1,037
  • 9
  • 26
0

If you are setting to "Anyone with this link can view". Then sharing the sheet will help rather than publishing, Put this url where you're accessing the sheet

https://docs.google.com/spreadsheets/d/{YOUR_KEY}/export?format=csv&id={YOUR_KEY}&gid={SHEET_ID}

YOUR_KEY - Id of your sheet which you can find in the url when you're on the spreadsheet

SHEET_ID - The last number in the same url

Credit This answer

Praful
  • 3
  • 3
  • 9