Dashboard refresh

Linda Sears

My sheet gets data from a form and update requests, it rarely needs to be opened directly.

I have reports for open items and completed items. I have summary fields in the sheet that count and calculate late items.

The Dashboard has metric widgets that look at the summary fields and two report widgets.

The dashboard doesn't show current info unless I've open the sheet that day. Is that normal?

~ Linda

Paul Newcome

Ok. Set up two separate automations. The first one will lock the rows at 1am and the second will unlock the rows at 2am. The lock/unlock should update the TODAY function without you having to manually activate the sheet.

Paul Newcome

I am going to assume that some of your calculations also include the TODAY function. The TODAY function only updates when the sheet is "activated". There are a number of ways to activate a sheet to include opening it directly, form submission, update requests, cell link updates, and certain automations.

There are a number of solutions for this, but the best one will depend on some details?

How many different sheets do you use the TODAY function on?

Linda Sears

There's only one sheet driving the reports and dashboard. All of the summary fields (and conditional formatting) that calculate late use Today().

Linda Sears

Also, the reminder automation emails for late items are firing successfully.

Paul Newcome

Is there a particular hour each day that the sheet containing the TODAY function can be "locked"? Such as from 1am - 2am or something like that?

Linda Sears

Yes. Anytime after hours.

Paul Newcome

Ok. Set up two separate automations. The first one will lock the rows at 1am and the second will unlock the rows at 2am. The lock/unlock should update the TODAY function without you having to manually activate the sheet.

Linda Sears

Awesome, thanks! I'll try it tonight.

Paul Newcome

Definitely let me know how it goes!

Linda Sears

Thank you! It worked. I'm so glad my mostly hands off sheet can stay that way.

Paul Newcome

Happy to help. 👍️