0

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

MrPea
  • 635
  • 1
  • 6
  • 22
  • Related: https://stackoverflow.com/a/49274461/11551468 – Rafa Guillermo Oct 13 '20 at 11:06
  • Importrange is shaky at best, you can't force it to recalculate programmatically. I would suggest maybe caching sheet C into another sheet every 30 minutes with `.getDisplayValues()` and use that, but it's never going to be guaranteed to be 100% up to date. – Rafa Guillermo Oct 13 '20 at 11:09
  • Hi. Thanks. I'm not sure getDisplayValues() would help as the displayed values are not the updated values until after "working" has finished. Or are you saying that somehow it wouldn't get the displayed values until it knows it has the newly calculated data to display? To note, the script only runs once a day at 3am so having it bang up to date is not necessary - a few hours old would be fine. – MrPea Oct 13 '20 at 12:06

1 Answers1

1

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Yeah, not sure I can get that cell. I've decided, as the script only runs once a day at 3am with no users active, that I will add a Utilities.sleep(60000) and see if that allows enough time to calculate then continue - bit of a bodge – MrPea Oct 13 '20 at 16:20