4

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!

2 Answers 2

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.

3
  • 2
    Hmm, I see what your idea is here. I've tried it within my project however, it still doesn't seem to be working. I've logged both of the values for each iteration of the do while loop and can see that they're the same each time. However, it never seems to be the case that it grabs an updated value for the b1Value and so it loops endlessly. Could it be something to do with the opened instance of the spreadsheet? Maybe opening or getting the spreadsheet creates a sort of static copy of the spreadsheet that we refer to rather than actually looking at the spreadsheet dynamically?
    – happymeal
    Jul 31, 2019 at 16:19
  • 1
    @RyanAnderson It looks that you are right. It looks that the solution is to use a time-driven trigger or HTML Service to do the poll thing.
    – Rubén
    Jul 31, 2019 at 17:58
  • 1
    I figured out a solution for my instance. It is posted here as an answer if you'd like to see. It ended up being rather simple actually, but thank you for your insight!
    – happymeal
    Aug 6, 2019 at 20:28
0

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.

2
  • 1
    Thank you for sharing the idea behind your solution. Could you also please share your code with the rest of the community? It may help other users trying to solve similar issues to yours. Aug 6, 2019 at 20:46
  • 1
    The "specific time interval" settings means that functions like now() will be recalculated even there is no user made changes. It this kind of functions aren't used then this setting hasn't any effect on the recalculation process. By the other hand, if this is what you are looking for I think that the question should be reworded as this solution doesn't waits for a recalculation to finish.
    – Rubén
    Aug 6, 2019 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.