How to wait for Google Spreadsheet calculations to update before retrieving cell data in Apps Script web app?

Question

I am creating a Google Apps Script web app that uses Google Sheets as its backend. This web app basically allows users to track their work time (clock in for the day, clock out for lunch, clock in from lunch, & clock out for the day). Currently, I have the app reading data from the spreadsheet and displaying to an HTML table using a client side javascript call to the server side javascript, which is what actually opens the SpreadsheetApp (by URL) and retrieves the data to send back to the client side javascript for display. However, the spreadsheet has quite a few calculations that need to be performed and takes a few seconds before updating the values. My script, however, reads the initial values and doesn't wait for them to update; often leading to stale data being displayed. I've tried using SpreadsheetApp.flush() to force the spreadsheet to finish calculating before retrieving the data in my script, however it still seems to pull the data before calculations are finished. Should I be using SpreadsheetApp.flush() before or after opening the spreadsheet with SpreadsheetApp.openByUrl(spreadsheetURL)? Or maybe after my call to userSpreadsheet.getSheetByName("Timecard")? I've tried each, but to no avail.

Any help is appreciated!

Answer 1

SpreadsheetApp.flush() doesn't force a spreadsheet to finish calculating, actually calling it could trigger a recalculation as its purpose is to tell to the Google Apps Script engine that it should send to the spreadsheet now the pending to be sent changes made by it .

Google Apps Script doesn't include a built-in method to tell that a recalculation is finished. Build a custom method is a bit tricky because certain conditions could trigger a "recalculation chain" complex spreadsheets that includes volatile functions like NOW(), RAND() among others.

On a simple spreadsheet that you know that a cell will change its value every time it's recalculated you could use a "poll" function to know if the recalculation is done. Let say that cell A1 has a "static copy" of the result of formula on B1 at certain time. When you need to know if the recalculation is done, call a function like the following:

function poll(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var a1Range = spreadsheet.getRange('A1');
  var b1Range = spreadsheet.getRange('B1');
  var a1Value =  a1Range.getValue();
  do{

     Utilities.sleep(1000);

     var b1Value =  b1Range.getValue();

  } while (a1Value === b1Value);
  
  // Reset the value of A1;
  a1Range.setValue(b1Value);

  // Do whatever the code should do once B1 value changes
      
}

NOTE: I didn't test the above code yet.

Answer 2

After some trial and error, I've figured out a solution to this problem. It seems as though the Google Sheet recalculation settings are set to occur only when a change to the sheet occurs, or you can change these settings to occur on a change to the sheet and on specific time intervals. For my purposes, I left the settings to on change only and then in my script, I simply retrieved the value from a cell that I know will never change (it was a simple text field in my case) and then simply set that same cell with this same value. This "change" caused the sheet to recalculate every cell and then I retrieved my relevant values from the sheet. I additionally used SpreadsheetApp.flush() after re-setting the cell to ensure all calculations were performed then, though this may not be necessary.