Alerts not Sending to Assigned To Column

Lauren B

 

I want to send a daily reminder listing open tasks to contacts listed in my Assigned To column. All contacts have been shared to the sheet as Editor - cannot share. At least a couple of these contacts are users who have used SmartSheet before.

 

I set a conditional formula in a reminder date field to populate with today's date if tasks have not been marked Complete: =IF(Complete1 = 0, TODAY())

 

I then set my reminder like so: Send Assigned To a reminder on Reminder2 - Daily Open Tasks Updates for this sheet. Assigned To is a contact field. 

 

I set this reminder yesterday, and no one received the reminder this morning, even though the Reminder2 field has populated as expected with today's date.

 

Any thoughts on why this would be happening?

Shaine Greenwood

Hi Lauren,

 

Formulas are only updated when a sheet is opened and saved, and Reminders are sent out between 12am - 4am on their designated reminder date. This means that using this type of formula won't trigger reminders to be sent out unless someone's able to open and save the sheet at 12am every day to let the formulas update with the current date.

 

You may need to adapt the formula to remind people the day after:

=IF(Complete1 = 0, TODAY(1))

 

Otherwise, someone will need to open the sheet every day at 12am and save it, so formulas will calculate with the correct date and reminders will be triggered.

Lauren B

Shaine, thank you so much for the feedback. Can you think of a formula workaround to avoid the TODAY function?

 

Although the sheet is used often, it is not feasible to expect someone will be in it every day. For example, if the sheet is not opened on Sunday, I would still want the alert to go out Monday morning.

 

I appreciate your thoughts -- I would like to avoid using the report builder in this case, but it may be the only option until conditional alerts are implemented.

Shaine Greenwood

There isn't a formulaic workaround for this—all formulas operate this way and there currently isn't a way to change the behavior. I've got your vote down for a way to have formulas update on a sheet without having to open the sheet on our enhancement request list for further review by our Proudct team.

 

You might be able to automate the action of opening the sheet and saving it via our API (https://smartsheet-platform.github.io/api-docs/) or a third party integration such as Zapier (https://zapier.com/).

 

The key is that the sheet needs to be opened and saved, so you might have to create an API automation or a "zap" in Zapier that adds random data to your sheet and saves it. (Something like "check this box" and save, then uncheck this box and save again.)

Tim Meeks

Add my +1. I'll also mention this if I'm able to take part in the Formula Feasibility study.

Zapier is just not very practical for our government agency to purchase. I might be able to get a developer to do an APi, but having a sheet autmotically get TODAY() would be great. 

 

At this time, I can't think of a reason I would want this NOT to work this way.

 

tim

 

 

Shaine Greenwood

Hi Tim,

 

The reason that the formula engine currently works this way comes down to the backend performance. It wouldn't be feasible to open every sheet and run the formula engine to figure out if the sheet needs to update a result, this would degrade performance in the app. Since we always put security first, I'm not sure if we even have visibility on what functions you're using in a sheet. (So we may not know which sheets to open for recalculation and which ones we should leave alone.)

 

The key would be to figure out when it's most beneficial to open and run a sheet on the backend without making the app slower.

Shaine Greenwood

Hi again Tim—forgot to mention that I've added your vote for this as well.

eric1234

Hmmm....couldn't one change their time zone setting to make the open & save time be something more convenient?