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

google sheetsgoogle-apps-script

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!

Best Answer

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.getActiveSpreasheet();
  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 should do once B1 value changes;

}

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

Related Topic