Automated Monthly Alerts

Dean Lewis, PMP, PG

Alert Trigger

I would like to send an "alert" each month on the 14th for rows in a sheet.  I would prefer not to update/maintain this each month or year but just have it automatically go each month.

I created a data column called Today using formula =Today() to return the current date and a text/number column called Trigger Day with formula =DAY(Today1) to return the day of the month as a number from the Today column.

I thought I could use the "send on date" Alert based on a date column Today with condition of when Trigger Day = 14.  See screenshot. 

This does not seem to be working.  Suggestions?

Do you need to access/save the sheet to activate the "Today()" calculation? or is that occurring automatically in background?

Including Attachments

Also would like to send attachments along with this Alert but that looks like it only happens for any attachments that were updated or changed.  If no changes then the attachments don't send.

 

Paul Newcome

What is it you are alerting the recipients of? To have a TODAY() function calculate, you have to access the sheet itself. It does not happen in the background when the sheet is closed.

Kirstine

Dean, this is such a good point! I've only just started using this technique of sending automated emails but it absolutely makes sense to have the ability to automatically send it once a week/month/year etc. I'm going to Submit Product Enhancement Request and I hope you will too!

Paul, I'd say that having to go into the sheet to make it work almost makes the automation redundant as you're still having to manually force it to work really. Is there any other workaround you can suggest until this function is built in? 

Paul Newcome

I've actually been doing some testing, and have found the following...

 

The sheet does not have to be OPENED for the TODAY() function to calculate. It just needs to be ACTIVE. Forms and update requests work for this as does cell linking. Having it accessed as Published on a dashboard through a URL Widget does not work, and following a published link for View Only doesn't make the cut either. 

 

I didn't think to test this before, so I am going to try to get it done today. I am wondering if the sheet sending out some other automatic alert that is NOT dependent on the TODAY() function would work. I'll let you know what I find out as soon as I get a chance to test it.

Kirstine

That sounds like it has potential! Mine has cells linked to a sheet I use every day, so that should work shouldn't it? Would the email send as soon as I've opened the main sheet in the morning? (I'm in the UK so the emails usually are sent overnight.)

Paul Newcome

It only works one way. The cell has to be linked FROM your daily sheet TO the sheet with the TODAY() on it. If the linking is going the other way (originating from the TODAY() sheet) it won't work.

Paul Newcome

@Dean:

 

If you are just trying to send the sheet, you could have it sent as an attachment in which case you can include sheet attachments and schedule it for a specific day of the month.

 

The Alerts and Actions are definitely trickier, but if sending the sheet as an attachment works for you, let me know and I'd be happy to post some screenshots on how to set that up.

Kirstine

Hmm... mine is a report so that won't work for me will it? 

Kirstine

Paul, is there any chance you can look at another issue I have...? (You're a victim of your own success!)

Dashboard width

 

Paul Newcome

Exactly how is your's set up and exactly what are you trying to do?

Paul Newcome

Replied.

Dean Lewis, PMP, PG

Paul and Kirstine,

Thanks for all the info and ideas.  I will submit an enhancement request.  Similar functionality exists for setting up repeat Update Requests so hopefully not a big lift to get it done for Alerts.

For now the optimal solution for my case was manually updating the date trigger each month.  I added a few sheet error checks to the process so the monthly update/check is a value add.