I have an intermediate sheet with only one row that pulls the results of some calculations from my master data sheet in order create an archive entry.
This intermediate sheet has an automation set up to copy the row into my archive sheet at a certain day and time every week, i.e. it extends the archive with a snapshot every time.
But it seems like the intermediate sheet does not get refreshed with the latest values before the row is copied into the archive because it only updates when I open, not when I run the automation.
Is there anything I can do about this?
It defeats the purpose of a timed automation if I have to open the intermediate sheet anyway just before the automation is run so that I can ensure I actually have a snapshot of the latest data in my archive, not the status of when the intermediate sheet was last opened a day ago.
The cell history shows the value that was copied from the intermediate sheet to the archive at the very bottom, but the latest value in the master sheet had actually changed more than a dozen times since then.