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?


UPDATE: 2017-09-17 

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.

Closing Thoughts

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.

 

 

Comments

William Pineda

Could the same principle (using zapper to update a sheet) be used to automatically sort a sheet on a daily basis. For example, lets say I have a form and the form data dumped into a sheet requires to be sorted manually for nesting purposes. Could a zap automatically sort itself daily that way?

    Ronin Global

    Great question.
    Unfortunately, the quick answer is ‘no’ for at least several reasons.

    Zapier (and other 3rd party API integrations) are being triggered by changes on the row level. There are other triggers types, such as ‘new things’ (like Sheets) but are not triggering on global changes.
    So when a row is added (from your Form) or modified (by a user), the Zap does not have the data it would need to know if the row above or below it was higher in the sort order.
    This is likely why Zapier does not have a ‘Sort Sheet’ action. It just doesn’t have the data it needs to perform this action.
    Additionally, the hierarchy structure of Smartsheet makes a simple sort difficult to do because the Sort function (from the GUI) does lot allow a ‘Sort sheet, respecting hierarchy’ feature.


    However, all is not so bleak. For some use cases, I have built Zaps (plural) that will:
    1. Take a new Form entry row
    2. Determine where it goes in the Sheet (for example, by Category or Manager) and if there is room there*
    And then
    3. Move the new row into one of the available slots.

    It takes a while to set up the Sheet to be ready for this and to set up the Zaps. Small sheets work best.

    I hope this helps and thanks for reading my post.

    -Craig

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.