How can I make my Grids auto-update in the background to keep my Dashboards up to date?

Graham Rimell

I've encountered an issue with a system i've put together of grid data that is shown on a Dashboard via Metrics.

In one instance I've got a 'Last tested' date that has conditional formatting in the cell that make it change background colour from Green to Red once it exceeds a certain number of days, indicating that a test is overdue. Then this cell is shown as metric on the Dashboard to give a nice visual indicator of whether we are on track or not.

All wonderful and slick in theory, however I've realised that the Grids don't seem to update without manually going in and opening them, and then saving. So in the case of the 'Last Tested' cell it won't update by itself without going in and refreshing the grid file. Which makes it useless as a method to automatically track the status of each test.

Having to manually open the source files to just keep the flow of up-to-date data going to the dashboard seems like a massive oversight.

I see there were threads complaining about the same problem in 2018, and it still seems to be a problem.

Is there a way to do this now?

Genevieve P.

Hi @GrahamR

I haven't tested this specifically for Conditional Formatting, however one way to "refresh" your sheet each day without actually needing to open it up is to set up two time-based Automations on that sheet:

• One automation to "lock rows" at 12am (or some other middle-of-the-night time).
• A second automation to "unlock rows" at 1am (or an hour later than the Lock automation).

This does mean that for one hour in the middle of your night your rows will be locked, but as long as you set it for a time when no one is working on the sheet it shouldn't block you from doing any work, and it doesn't adjust any of the content in your sheet.

Let me know if this works for you!

Cheers,

Genevieve

Graham Rimell

Thanks for the idea!

I tried this on a few of our sheets, even made a test page with the TODAY function and had the date on a metric on a dashboard. However after a couple days it's still not worked.

On a side note, we are thinking of upgrading to the Enterprise edition, is there anything on there that would help with this?

Genevieve P.

Hi @GrahamR

When you say it's not worked, do you mean specifically just the colour of the cell with conditional formatting, or with the TODAY() function as well? You will want to ensure that these are two separate workflows, one to lock and a separate one to unlock, in order for the sheet to update.

Conditional formatting will function the same way across all sheets & plans. You can schedule a daily sheet save if you're using Bridge, but this is separate from an Enterprise plan.

There are other ways to update a sheet without opening it as well... these are listed in the TODAY help center article, here:

Smartsheet will update all instances of the TODAY function in a sheet to the current date when any of the following actions are taken:

• You open the sheet and save it
• You add a new row to the sheet from a form submission
• You make changes to the sheet from an update or approval request
• You update the sheet by way of a report
• A linked cell updates the sheet
• You open the sheet being referenced in a cross-sheet formula

Graham Rimell

Hi,

Both actually, I have some conditional formatting and a date using TODAY() and neither have updated overnight. Both of these are set using two different automations at 0100 and 0200 to lock, and then unlock.

Was there anything specific I need to do in the set up of the automations?

I'll have a look at bridge too, as that's a potential option!

Genevieve P.

Hi @GrahamR

It sounds like you have them set up correctly - the one at 0100 should be the lock, then the one at 0200 would be the unlock.

If that's not working for you, there's a discussion in this thread, here, that has alternatives that could work!

Graham Rimell

Hi,

So this is a bit of a weird one! It appears it has actually updated, but a day late. So today's the 4th, but the TODAY function says it's the 3rd. It seems to lag behind a day.

I had it set to lock at 00:00 and unlock at 01:00, so i've nudged it forward an hour to 01:00 & 02:00 to see if that makes a difference. It could be that the 00:00 lock tetters on the edge of one day and next, so isn't reliable.

Thanks for the link to that thread, I'll give it another go tonight and check tomorrow morning to see if it worked. Failing that I'll try some of those solutions.

Genevieve P.

Oh interesting! You may want to double-check the timezone that's showing in your alert, then. It will default to your personal setting's timezone. For example, mine is set to Pacific time in this alert:

Graham Rimell

It's on the correct time zone, GMT. Everything looks to be okay, so hopefully it'll work tonight!

Thanks for the help.