I’ve been using the Google Sheets API to poll the values of cells computed from non-deterministic formulae, for example stock price quotes from Google Finance. Even though I poll every minute, and the data are constantly changing, the API usually doesn’t return the latest value. This is despite the fact that, if I have the spreadsheet open in a web browser tab, the cell shows the freshest data and updates every minute.

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.

The problem isn’t my code, I even see it using the Google APIs Explorer. Here’s a simple repro:

  1. Create a spreadsheet with the formula =NOW() in cell A1.
  2. In File > Spreadsheet settings… choose the Calculation tab and ensure Recalculation is set to On change and every minute.
  3. Use the Google APIs Explorer to perform sheets.spreadsheets.values.get to get the value of that cell (range parameter is Sheet1!A1).
  4. I’d expect every time I press the Authorize and execute button, I’d get a new value for the cell.
  5. What actually happens is that I typically get an old value.

This happens regardless of whether the sheet is also open in a browser tab. However, if I use the browser to edit the value of any cell in the sheet, the next call to the API returns fresh data. That hints that there may be a way to control when recalculation occurs.

Where are Formulae Calculated?

Are formulae calculated in the browser or on the server? Actually, they can be computed entirely on the server, entirely on the browser, or in combination. For example, if the sheet is open in a browser:

  • When the formula is =NOW(), there seems to be zero communication between the browser and the server when the formula is recalculated.
  • 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 calculated entirely in the browser.

If the sheet is not open in a browser, using the API to write the value of any cell in the sheet does force the formula to be recalculated. In this case, recalculation has clearly happened entirely on the server.

Workaround

According to a forum posting, there is no explicit provision in the API to force recalculation when getting the value of a cell. However, we observed that when the API is used to write to any cell, all the formulae are recalculated. This can be used to force a recalculation via the API.

It’s a little icky to write to some arbitrary cell to force recalculation: how should such a cell be chosen? How should a user of the spreadsheet be warned to avoid putting anything in that cell? Also, it is conceivable that Google could, in the future, implement an algorithm that triggers a recalculation only when a dependency of a formula changes. How can we future-proof our code against this possibility?

One way is to:

  1. Get the formula from the cell using valueRenderOption=FORMULA.
  2. Update the same formula back to that cell with valueInputOption=USER_ENTERED and responseValueRenderOption=FORMATTED_VALUE.
  3. Parse the newly-recalculated cell value from the response.

This does create a small race condition in which a user’s update to the formula might be clobbered, but for some applications this may be acceptable. Here’s some Java code, for use in Android, that implements this workaround:

public String getValue(GoogleAccountCredential credential, String sFileName, String sCellRef) throws IOException
{
    HttpTransport transport = AndroidHttp.newCompatibleTransport();
    JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();
    com.google.api.services.sheets.v4.Sheets service = new com.google.api.services.sheets.v4.Sheets.Builder(
        transport, jsonFactory, credential)
        .setApplicationName("Google Sheets API Android Quickstart")
        .build();

    ValueRange response;

    // GET the formula
    response = service.spreadsheets().values()
        .get(sFileName, sCellRef)
        .setValueRenderOption("FORMULA")
        .execute();

    List<List<Object>> values = response.getValues();

    if (values == null || values.isEmpty())
    {
        return "";
    }

    String sFormula = String.valueOf(values.get(0).get(0));

    if (!sFormula.startsWith("="))
    {
        s_logger.debug("Bailing early, formula seems to be a value");

        return sFormula;
    }


    // PUT the formula back, getting the value
    UpdateValuesResponse updateResponse;
    ValueRange content = new ValueRange();

    content.setRange(sCellRef);
    content.setValues(Collections.singletonList(Collections.singletonList((Object) sFormula)));

    updateResponse = service.spreadsheets().values()
        .update(sFileName, sCellRef, content)
        .setValueInputOption("USER_ENTERED")
        .setIncludeValuesInResponse(Boolean.TRUE)
        .setResponseValueRenderOption("FORMATTED_VALUE")
        .execute();

    response = updateResponse.getUpdatedData();
    values = response.getValues();

    if (values != null && !values.isEmpty())
    {
        return String.valueOf(values.get(0).get(0));
    }

    return null;
}