Hi,
I have a sheet with a value is computed from external data, so it is
constantly changing. This works well when running Sheets in a browser
window. But I’m observing that the value returned by
*sheets.spreadsheets.values.get* is often stale. I’m hoping that someone
reading this forum can provide some insight as to what’s going on and how I
can get fresh data via the API.
Here's a simple example:
- Create a spreadsheet with the formula *=NOW()* in cell *A1*
- In *File* > *Spreadsheet settings…* choose the *Calculation* tab and
ensure *Recalculation* is set to *On change and every minute*.
- Use the Google APIs Explorer to perform
*sheets.spreadsheets.values.get* to get the value of that cell (*range*
parameter is typically *Sheet1!A1*)
- I’d expect every time I press the *Authorize and execute* button, I’d
get a new value for the cell.
- What actually happens is that I typically get an old value.
I wrote a Python script to poll the API every minute. The median time
elapsed before a new value appears is about 70 minutes, but I have seen as
high as 1006 minutes, and as low as 1 minute.
When the sheet is loaded in a browser window, the cell gets updated every
minute as expected. Using the browser to edit another cell causes the API
to return the most recent version of the computed cell.
It seems that the computation of formulae is a complex process that can
execute entirely on the server, entirely on the browser, or in combination:
- When the formula is *=NOW()*, there seems to be zero communication
between the browser and the server when the formula is recomputed.
- When the formula is *=GOOGLEFINANCE("GOOG")*, it seems that the server
sends a push notification to the browser about new external data being
available, the browser retrieves the external data from the server, and
then any dependent cells are computed entirely in the browser.
What I’d like to do is be able to force the back-end to recompute the
formula when the get API is called. Does anybody know how to do this?
The best hack I have is to *get* the formula from the cell using
*valueRenderOption=FORMULA*, and then *update* the same formula back to
that cell with *valueInputOption=USER_ENTERED* and
*responseValueRenderOption=FORMATTED_VALUE*. This forces the server to
recompute the formula and returns the result in the response to the *update*
call. It also doesn’t seem to pollute the history of the document too
much. Unfortunately, this approach creates a race condition that I'd rather
avoid.
Thanks,
James
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
