Dashboard Metric Widget Not Updating with Current Backend Sheet Value

Cassie L

Hi all,

This is from a sheet that uses a formula to count down / display the number of days til end of Quarter.

• What is happening: Displays 37 when the backend sheet actually says 34.

The dashboard does not update until I click to open the backend sheet then the connection seems to go through to update the widget/dashboard.

Does anyone know what I am doing wrong?

Thank you!!

Best,

Cassie

Kerry St. Thomas

Is it possible the underlying sheet not updating the calculation? Sheet formulas only update when activated: opening the sheeet directly, if the sheet receives an update request, the sheet receives a form submission, or an automation changes or adds something to the sheet. So if you're opening the sheet to check the field that says "34"… that causes the formula to recalculate. This is especially noticeable with the TODAY function, when you reference a sheet on a dashboard — just as you've apparently done. Even if your refresh is often on the dashboard, it will still show "stale" data if the underlying sheet formulas aren't activated.

If this is the case, one workaround is to include a daily automation on the sheet - "Today's Date" as a Record A Date calculation, for example. I have a dedicated "Time Intelligence" sheet that uses the Record A Date calculation, and several formulas like "First of this month", "Beginning of Next Quarter", and the like (because it's faster to just reference that sheet than to redo the automation for every project I work on).

Good luck!

ChristianFinke

Hey @Cassie L

I would try setting your dashboard’s publish settings to refresh more frequently or use a report widget instead of a metric widget, as reports often update in real-time. You can also manually refresh the dashboard to prompt an update. Let me know if this doesn’t resolve it!

Cassie L

Hey Christian,

Thank you for the response!

My Dashboard is set to refresh every 2 minutes, and the Metric Widget only allows the mapping from a Sheet (not report).

I would like to just display this one metric from a specific sheet — it seems like this is the primary purpose for the widget but it seems like the backend sheet is not updating as it should until I look at it.

Best,

Cassie

Cassie L

(sorry forgot to tag you!)

Hey @ChristianFinke

Thank you for the response!

My Dashboard is set to refresh every 2 minutes, and the Metric Widget only allows the mapping from a Sheet (not report).

I would like to just display this one metric from a specific sheet — it seems like this is the primary purpose for the widget but it seems like the backend sheet is not updating as it should until I look at it.

Best,

Cassie

Kerry St. Thomas

Is it possible the underlying sheet not updating the calculation? Sheet formulas only update when activated: opening the sheeet directly, if the sheet receives an update request, the sheet receives a form submission, or an automation changes or adds something to the sheet. So if you're opening the sheet to check the field that says "34"… that causes the formula to recalculate. This is especially noticeable with the TODAY function, when you reference a sheet on a dashboard — just as you've apparently done. Even if your refresh is often on the dashboard, it will still show "stale" data if the underlying sheet formulas aren't activated.

If this is the case, one workaround is to include a daily automation on the sheet - "Today's Date" as a Record A Date calculation, for example. I have a dedicated "Time Intelligence" sheet that uses the Record A Date calculation, and several formulas like "First of this month", "Beginning of Next Quarter", and the like (because it's faster to just reference that sheet than to redo the automation for every project I work on).

Good luck!

Cassie L

Thanks so much, Kerry! I suspected something like this was happening. Love the workaround recommendation, I will give that a try.