Forcing TODAY() refresh in Smartsheet
Because Smartsheet and other spreadsheets lend themselves to being used as task lists, a common request on the Smartsheet Community is how to “create recurring tasks in Smartsheet”.
While on the surface, this seems to be a simple question, it isn’t. The answer isn’t simple either. We aren’t going to cover that bigger question here. One method, however, uses formulas with date functions and the TODAY() function. This article will cover that piece of the puzzle.
Suppose, for example, that I have task that must be done every Tuesday.
This week’s Tuesday can be found using this formula:
=TODAY() – WEEKDAY(TODAY()) + 3
Trust me on that.
The problem lies in that a sheet in Smartsheet won’t refresh if no one has viewed and saved it. Or rather, it hasn’t been “touched” (not quite, but the analogy holds). If the person assigning the recurring tasks is updating a sheet (but not very often, because these are, you know, recurring for the person doing them but shouldn’t be for the person assigning them) and the person doing the recurring task is using a Report to see only their tasks out of all the ones in the sheet, the sheet is not getting opened and saved or touched very often. This means that the formula with TODAY() won’t really be today, but some time in the past when the sheet was touched.
Still with me?
The previous version of this work-around used Gmail as the trigger for starting the whole process. Now I use the App Scheduler by Zapier. I think I tried that before and for some reason, did not use it, but I don’t recall why and my notes are unclear on that point. It (the App) seems to function OK (now and maybe it always did) and the end result is much quicker to implement. Also, one less e-mail accounts for something, doesn’t it?
Using Zapier, and a bit of know-how, an automated touch of the sheet can be performed daily (or even on specific days of the week) to keep the formulas using TODAY() up to date.
One minor point about your Smartsheet sheet
Before we jump over to Zapier, we are going to want to set up or identify a specific cell (or more than one, but one will do) to update from our Zap.
For my example, I’ve chosen the cell [Start Date]19 – which is the [Start Date] column and row 19. It can be any column type.
You don’t need to make any modifications, just make sure it will be OK to update the cell from Zapier.
Things to watch out for if hard-coding the row is any row above where you are will be deleted or added (because the hard-coded number will be wrong). I often have a “header” section of rows that are unchanging and most sheets add new rows at the bottom.
Setting up the Zap
The steps to creating a new Zap are fairly straight forward. If you don’t know the basics, read about them here.
The trigger will be the Schedule By Zapier App and the Every Day periodicity.
I have my time set to 3am
Test the trigger.
The Zap’s action is also simple.
Choose the sheet, choose the row (recall the example is using row 19):
and update the value of any of the columns. This example uses the [Start Date], but any value will suffice.
Test the Zap and turn it on. Every day, Zapier will trigger at the time specified and update the sheet, which will refresh the formulas in the sheet, including the ones with TODAY() in them.
Originally used for recurring tasks, this method of touching a sheet can be used for a variety of purposes, in particular sheets rich with the TODAY() function.
If there is a better (easier) way to accomplish this, please let me know in the comments below. If you need help setting up this or any other Zap, I might be able to help there too.
If this helped you, a like is always appreciated.