I was playing with the new Automation feature today and found a useful way to create a master list of date notifications (instead of using a calendar). All you need is a single Automation, and you can create a list that will send notification emails from spreadsheet.com with a specific SUBJECT and MESSAGE to a specific USER.
Here is a screenshot showing how to set up the worksheet.
NOTES: You need to make sure the DATE column is the Date data type (doesn’t currently work with a Column Formula data type). Also, make sure the USER column is the User data type.
Here is a screenshot of the worksheet Automation after it is created.
Here is a more detailed screenshot showing the notification email. It is really cool how you can include fields within the subject and body of the email.
Daily quote from a list
=INDEX(quote_range,RANDBETWEEN(first_row_num,last_row_num))
And yes, you could create all of these types of notifications (except the random quote) individually using the “Recurring” automation trigger, with greater flexibility in defining the recurrence, but the beauty of this master list is how simple it is to add new notifications by just adding a row to the table (if you are comfortable using or copy/pasting the formula for the Date column).
This simple new feature in spreadsheet.com is going to replace my need for using 3rd party email reminder services.
After some more testing of this worksheet, with some dates working (notifications sent) and other dates not working (no notifications sent), I think I’ve narrowed things down to the “On Date” notification not working if the cell contains a formula. Even though the column I’m using is a Date data type, the dates created with formulas are not sending notifications. Perhaps there isn’t a formula evaluation step included in the Automation?
This use case isn’t nearly as cool when you have to manually enter all of the dates. I suppose you could just convert all of the values to just dates after using formulas to create them, but ultimately I’d forget to do that which would lead to not getting the reminders.
On-Date not triggering for formulas is a defect. We are tracking this as 003853.
In general, as of now other trigger types also do not fire for changes on formula cells ( Changes that are due to re-calc because some dep cells changed). We’ll be enhancing this behaviour shortly.
Okay - So Volatile functions - I’m pretty certain that Volatile functions do not re-evaluate at server-side today. On clientside, they do re-eval upon Sheet load ( We dont have a F9 function yet ) but its not propagaged to the server-side.
So for now, I’d not depend on Volatile cells to trigger Automations.
For formulas that use TODAY(), sounds like after the formulas work for On-Date triggers, I just need to figure out how to automatically open my worksheet once per day.
Or, here’s an idea - could there be a new type of Automation that would tell spreadsheet.com to re-evaluate the workbook on a daily/weekly/monthly/yearly basis (at a specific time of day)? By making it a user-defined automation, I doubt it would result in much of a load on the server.
Thanks for this feedback. We may not support volatile functions updating headlessly automatically, but eventually we may provide a way to trigger a re-evaluation, possibly with an automation or other mechanism.
One related example is in the newly published Stock Portfolio Template – it would make sense to be able to trigger automations to notify me when a stock reaches a certain buy/sell threshold, or when the realized gain/loss reaches a certain amount. We’ll discuss this internally. For now it’s not something that fits into our short term roadmap, though there are many use cases for it.
