Auto-Refreshing Sheets/Grids - to auto update formula cells.

Gary M. Gordon · 08/02/19

I saw a post in the community at https://community.smartsheet.com/discussion/auto-refreshing-sheetsgrids which had a similar question. But no answer was provided and the last update (from another member) was in Dec. 2018. Again, no response or answer was provided.

I have a sheet, with formulas, to calculate today's date, and determine if today is within 7 days (or less) than the "due date" column. So each row has a due date. And I'm wanting to create alerts that would be emailed out, when the due dates are within 7 days or simply past due. But since the formula only runs when the sheet is accessed by someone, I'm concerned that the dates won't actually be updated and therefore the alerts won't go out as expected.

So I'm trying to figure out how the sheet can "auto-refresh" (as if someone accessed the sheet manually) so the formulas would run and update the TODAY date, etc.

My question is, how can I get the formulas (in a sheet) to run, even if no one actually accesses the sheet? I'm looking for a way to auto-refresh the sheet (as if someone accessed the sheet manually) so the TODAY formula would run (every day on its own).

Any advice or help would be appreciated.

Gary

Andrée Starå · 08/03/19

Hi Gary,

The third-party service/integration Zapier is an excellent option for this scenario. Is that an option for you?

Another workaround would be to use an update request to update the sheet daily, and then it would also update. I have a few clients that do this because they don't want to or can't use Zapier or another similar service.

Would that work/help?

Also: Please submit an Enhancement Request when you have a moment.

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dave-inden · 08/06/19

There are two possible solutions to this. First, I think with the new automated workflows you could have this behavior in Smartsheet. Workflows can be set to act on dates in Date type columns. For example, you could set it so that the workflow runs when a row is added or changed with a Condition where the date in a specific Date type column is in the next 7 days. You would chose the In the next (days) option and give a value of 7. Then you could have it send an alert to a contact in a Contact column to alert someone.

If you still want to use your formulas you could do something via the API. When a change is made via the API all of the formulas on the sheet will be run and evaluated even if the sheet isn't being opened in a browser. So, you could build something that adds or updates a small piece of data on the sheet. Since a change is being made your formulas will run and will get the new date for the current day.

But, with the new automated workflows I don't think you will have to do that. I would suggest looking into building one and seeing if it will meet your needs. More info on those is in the Help Center.

Auto-Refreshing Sheets/Grids - to auto update formula cells.

Comments

Help Article Resources

Categories