Using Google Spreadsheet as Data Source, Strange Caching(?) Issue

[Stephen Snow]

I'm trying to plot data on a website via the Google Visualization API and a Google Spreadsheet as the datasource. Using google.visualization.Query() and the instructions provided at https://developers.google.com/chart/interactive/docs/spreadsheets initially everything works perfectly. After I close the Google Spreadsheet however, about 10-30 minutes later (haven't been able to determine the exact amount of time) the chart on the website is drawn with data from a previous version of the Google Spreadsheet and not the latest save. If I reopen the Spreadsheet, refresh the webpage, the chart is redrawn with the latest data. Close the Spreadsheet, wait 10-30 minutes, refresh the webpage, and it again reverts to an old version of the Google Spreadsheet.

The only conclusion I can draw at this point is it may be some issue with the way Google caches spreadsheet data when it's not actively being used? I've tried different "fixes" such as a script that updates a lone cell every 10 minutes or inserting =NOW() into a cell and also changing the Recalculation settings to "on change and every minute" but the older data persistently reappears on the chart.

Any help is appreciated!

[Romain Vialard]

Hi Stephen,

There's a new behaviour in Sheets and cells using formulas aren't working well with Google Chart Tools.

Most people have reported that data aren't fetched at all (cells with formulas are returned as empty). In your case, can you confirm that you see old values but at least you see some values ?

[Stephen Snow]

Hi Romain,

When I initially open the sheet it presents itself as an earlier version. This only seems to be happening when the sheet contains formulas that pull data from another sheet or file. For example, say I have a sheet that is pulling values from another sheet and it initially contains 10 rows and is then updated to contain 100 rows, if I close the sheet and reopen it later (seems to only happen after about 15-20 min) none of the new values are there and I'll see only the first 10 rows. Hitting refresh in the browser causes the new rows to reappear. This also affects the chart being drawn using Google Chart Tools. After about 15-20 min, the new rows disappear from the chart but if I hit refresh on the sheet they will reappear.

Hope this is helpful.

Regards

--

Stephen Snow

Science and Technology Coordinator

Mid Continent Well Logging Service, Inc.

www.mcwlinc.com

Office: 405-360-7333

Cell: 405-627-3155

Fax: 405-928-8939

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/04LtAj0IGj8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.