CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
MrPea
MrPea

Reputation: 655

How can I ensure a Google sheet updates an importRange() when using getValues() in Apps script

I have a Google sheet (A) that imports into another Google sheet (B). (B) then gets more data and the merged data imports to another Google Sheet (C)

This process of (A) to (B) happens in 40 sets of paired spreadsheets and so all 40 sets end up in (C) as a consolidated record of the status of them all.

I then run an Apps Script trigger to check the status and, depending on that status of each set, emails are sent out with reminders to do stuff.

The problem is that (C) is not always updating with the latest information from (A) and so incorrect status's are being sent. It's hit and miss - sometimes it works and other times it doesn't - as if (C) is working from some sort of cached version that updates periodically, but does not gather data real time when opened by a script

This morning it happened again. When I opened (C) I could see the incorrect status on screen then when it had finished "working" the statuses updated correctly

And so it seems too, that when using getValues() from getActiveSpreadsheet() in the script, it isn't waiting until the Sheet has finished "working" in order to get all the latest data before continuing.

So a simple question - Can you ensure a script waits until a Sheet is fully calculated back through the chain of 2 Sheets before continuing with the script?

or some other solution?

One thing I could think to try is in the script open (C) then open each (B) in turn, so that (B) gets the latest (A) and (C) might then get the latest (B) and (A), this seems arduous.

So wanted to check if there is something simple I'm missing here that could be added in the script to force a calculate.

To note: The script is a bound script to (C) and I already have SpreadsheetApp.flush() at the start of the code although I have just noticed that SpreadsheetApp.flush() is before getActiveSpreadsheet() so perhaps I should be flushing after I have 'got' it? - I'll try

Many thanks

Upvotes: 0

Views: 370

Answers (1)

Wicket
Wicket

Reputation: 38140

From the question

So a simple question - Can you ensure a script waits until a Sheet is fully calculated back through the chain of 2 Sheets before continuing with the script?

No. Google Apps Script doesn't have a built-in way to know the recalculation status of a spreadsheet.

If your spreadsheet has a cell that can serve as clear indication that the recalculation finished, then you might poll that cell displayed value by usinging getDisplayValue() and compare it with something like a previous displayed value stored in a Properties Service property or something similar.

If you really need to be sure that you are getting the most recently calculate values, instead of using formulas use Google Apps Script / JavaScript.

Upvotes: 1

Related Questions