I have a Google App Script which is supposed to run, collect information from a user, then update the spreadsheet that's currently open. After it updates the spreadsheet (edits some cells), I have more scripts that take the sheet and processes the cells according to the pre-existing code that processes them (no reason to copy the code over--it's already huge). However, what I'm finding is that the script is getting the old data when it pulls the cell's contents later. I can't figure out why.
From what I can tell (based on my work with Excel VBA), Google Sheets does NOT update the cells within the sheet (perhaps just with an active sheet that you're looking at) until a later point. So even though I run a method that sets the cell values, several lines later when the script (using a different function) requests the cell values for the same cells, it gets the old values, not the updated values. So it appears that Google just stores a list of changes to make and then runs them at a later time (perhaps when the script ends).
I cannot find a way around this, either. The script ends normally within 5 seconds and the updates are made visually at that point, but the error continues. I tried using Utilities.sleep(5000), and it doesn't change the behavior. The cell continues to show the previous data during the entire 5000 milliseconds, then finishes the script, then finally updates what appears in the cells, and the old data is still used. Is there a way to force Google Sheets to update what's on the cells and then continue?
Here's a snippet of my code (I doubt you'll be able to make it run).
//master is the name of the sheet.
master.getRange(rowIndex,1,1,3).setValues([[newFirst,newPreferred,newLast]]);
//New name designations
Utilities.sleep(5000);
// Run DirectoryUpdate
directoryUpdate(Logs);//reads the rows of the sheet and changes people's names in Google and Canvas. It is reading the old data.
Hope so you're doing well, When you modify cell values using setValues, the changes are not immediately reflected when you request the cell values again in subsequent lines. Instead, Google Sheets processes these changes asynchronously, which can result in the delay you're observing.
To work around this issue and ensure that you're working with the updated values, you can use the SpreadsheetApp.flush() method. This method forces Google Sheets to apply any pending changes and update the sheet before continuing with the script. By adding SpreadsheetApp.flush() right after setValues, you can ensure that the cell values are updated before proceeding with further processing.
Hope so this will work for you!
Regards, Bryce June
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments