“How to set up a notification 7 days prior to upcoming event using Power Automate?”
“Do you know of a way to set up reminders for each instance on the calendar?”
You might consider using Power Automate action ‘Delay until’ to send a notification for an upcoming event. But it’s not the right approach. Running a flow on each event you’ll end up with a ton of flows running for days, until day 30. Each flow will end 30 days after it started, no more waiting. Let’s try another approach.
Use a scheduled trigger
Instead of running 1 ‘waiting’ flow per item, you can build only one Scheduled flow, running daily and sending emails. It isn’t even a complex flow, as in the simplest form it needs just 3 actions, including the Scheduled trigger. So the 1st step: create a new Scheduled flow with daily recurrence.
Filter upcoming event(s)
The next step in the flow is to filter only the relevant events. Since the flow is running once a day, you need to filter events at any times during that day, from 00:00 to 23:59. That means you need two dates for the filter, today+X days and today+X+1 days. All relevant upcoming events in X days will be between these dates.
Get the dates
To get the dates, you’ll need to combine 3 expressions, utcNow() to get today’s date, addDays() for the +X days part and formatDateTime() to get the time part from 00:00 to 23:59. Let’s take an example of today+7 and today+8 days and store them into variables.
formatDateTime(addDays(utcNow(),7),'yyyy-MM-dd')
formatDateTime(addDays(utcNow(),8),'yyyy-MM-dd')
All events between these 2 dates will fit the ‘upcoming event in 7 days’ filter. If you’d like, you can learn more about the expression in this article.
Now, when we got the dates, it’s time to use them in a filter. First in a SharePoint calendar list, then in Outlook calendar.
Filter in SharePoint calendar
You use Power Automate action ‘Get items’ to get the selected events from the calendar list. Like in any other filter, you must use column internal name in the filter. If we continue with the example above for notification 7 days prior to event, the filter should be:
(Start_x0020_Date gt '@{variables('Today+7')}') and (Start_x0020_Date le '@{variables('Today+8')}')
That means get items where ‘(Start Date > today+7 days) and (Start Date <= today+8 days)’.
If the filter doesn’t work for you, you can check this debugging article.
Filter in Outlook calendar
If your events are in Outlook calendar, it’s a bit easier to filter them as Power Automate has an action just for that ‘Get calendar view of events’. You just use the calculated dates in variables for the Start Time and End Time inputs.
Send the email notification
Last step in the flow is the ‘Send an email’ action. Use the data from the ‘Get items’ or ‘Get calendar view of events’ actions and send the emails. Maybe even from other than your email address.
Summary
I’d say there are 2 important points from this article. First, if you have some time related process, it’s always better to have a single scheduled flow to process all items. It can be flow for reminder, notification, escalation, archiving or something else. One daily flow always beats many paused item flows.
Second, you should understand that when you’re working with dates, they often include also time. It’s not possible to use [equals] operator when hours, minutes and seconds are involved. You have to work with a range. Get the beginning and the end of a range and filter all in between.
Thank you for the article. I don’t see my calendar as an option for get item. Any ideas?
Hello Chau Le,
the ‘Get items’ options is for calendars in SharePoint. If you’re working with events in your calendar then use the Outlook action ‘Get calendar view of events’.