How to Send Timed Reminders Based on a Trigger?

StevieSango

Hi, everyone; first post, Intermediate user. I'm hoping you can help me.

I've set up a SS to send an approval request when a new line is added to the sheet from a form entry. I've got the automation figured out for approval or declination. I'd like to send reminders if no one answers that approval, though.

What I'd like to do is, once an approval request is sent, count down a day from that request and send a reminder if it hasn't been answered, then increment a field to count the number of reminders sent. Once three reminders have been sent, a reminder will go to my manager to follow up.

I've tried with automation, and I can't get the workflow to occur daily if I use a cell change to trigger the initial automation. Change Cell Value doesn't seem to work for incrementing a cell, so I suppose I'll have to use a formula for that... but I can't seem to put all these disparate details together.

Last... I'm fine with using hidden columns, but I'd really like to keep this all on one sheet if I can.

Any ideas, or am I asking for the moon, here? Apologies if someone has tried the before, but I can't seem to find it with my Google-Fu.

Thanks!

Paul Newcome

Have you tried setting up an automation where the conditions are that the submission is not in the past 3 days and the status is still "Submitted"?

Paul Newcome

Have you tried setting up an automation where the conditions are that the submission is not in the past 3 days and the status is still "Submitted"?

StevieSango

Thank you, Paul!

I actually figured this out, but hadn't had the time to document it. What I did was a little more complex; I like your idea better.

FYI, I created a field with formula

=IF((TODAY() > [Submission Date]@row), (TODAY() - [Submission Date]@row), 0)

This created the count I needed, and then I used automations once daily to send updates based on the submission status. I don't like that calculation occurring ad infinitum in every row, though, as the sheet will continue to grow. I'll try it your way and see what happens--so far it just looks cleaner.

Thanks again! I'll let you know how it works.

StevieSango

Just to follow up...

It took a few days for people to ignore their requests long enough for the alerts to go out. It's all working perfectly. Thanks for the better idea, Paul!

I'll try not to miss the really obvious stuff next time... 🙄

Paul Newcome

No worries and happy to help. 👍️