Copy row automation returning outdated value for =today()

Rob W.

Application: I have a set of KPI values we need to trend in a graph over time.

Strategy: I've built a KPI tracker sheet that captures live metadata from various sources. Through a recurring weekly copy row automation, this takes a snapshot of those values and adds a row to a KPI history sheet with dates to support a report / dashboard graph.

• The date value cell in the metadata sheet is =today(), and the history sheet is expecting an entry every 7 days.
• The automation is set to recur 1x weekly on Thursday at 4:00 AM.

Problem: The copy row automation is returning Wednesday's date despite the automation running on Thursday. When I check the metadata sheet, it's showing the correct (Thursday) date.

I have tried changing the timing of the copy row event, but still returning the old value. I found that the cell history always shows that the cell value in the metadata sheet updates not at 12:01 AM, but 1 minute after the automation runs. This suggests to me that the sheet data is static until 'called' for either via an automation or user checking the sheet which prompts any calculations / formulas to refresh.

Curious how to either fix (preferred) or trick the sheet into refreshing the data to be current before the copy row event happens.

Paul Newcome

The TODAY function does not update until the sheet is activated. Try inserting a date type column and setting up a Record A Date automation to record the date daily at 3:00am. This will "activate" the sheet which will update the date stored by the TODAY function.

Paul Newcome

The TODAY function does not update until the sheet is activated. Try inserting a date type column and setting up a Record A Date automation to record the date daily at 3:00am. This will "activate" the sheet which will update the date stored by the TODAY function.

Rob W.

Thanks Paul,

I've got that set up now, ran a test and it's looking promising. Will check again Thursday morning and advise if I'm still having any issues.

Rob W.

Circling back to confirm that this workaround did the trick and has been working for a couple weeks now.

Paul Newcome

Excellent. Happy to help. 👍️