Google App Script, updates to sheet are not being found by rest of script. How do I make it include updates previously done in the script?

Jason Bagley

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.
Bricied

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.

edited at
0

Comments

0 comments
Login to comment

Related

How do I execute inserts and updates in an Alembic upgrade script?

DB is being updates sometimes in script execution

How do I make this Google Script faster?

How do I get the true range when editing multiple rows on Google Sheet via Google App Script

How do I get arrays mapped in a row in google sheet using JavaScript/Google App script

How do I deploy a simple Google App Script Library to a Google Sheet

How do I automatically archive a google sheet with a google script?

How can I write the data in Firestore to Google Sheet with app script?

How do I make my script target a specific column in Google Sheet?

How to make a loop script for Google sheet?

How to refer to other sheet in google app script?

So how do I read the contents of a Google Drive file in App Script without the lines being reordered?

How can I write a script which updates a text file?

Google Sheet Include user by email in protected rang using app script

How do I ensure consumers of my web applications pages have the latest script files after updates are pushed

How do I manually generate an alembic migration script that updates an Enum column

How do I store 1 INT into another or have it stored somewhere so it updates every time the script finishes?

How do I include a file in HTML Google Apps Script?

How do I make a "stop" script in bash that closes gnome-terminal tabs that I had previously opened with a different bash script?

How can I make a 'google sheet script' or formula to change values in different cells based on a checkbox in the sheet

How to assign Sheet with Sheet CodeName in Google Sheets App Script

Why does the looping in Google Sheet App Script stop in middle of the looping process and the rest of the codes do not run?

Google app script only updates document's changes after finishing running. Can I force it to refresh before?

How do I make formula auto populate in gsheets with google app script

How can I assign a Google Sheet script to only one sheet?

How do I include jars in a groovy script?

How do I make the variable bold in Google Apps Script?

Is it possible to make a POST as being on A client side using google app script?

Using Google Apps Script, how can I replace text in a Google Sheets template to make a new Sheet?