Notify multiple selected people when a SharePoint document is expired or expiring using Power Automate

I spent a few hours researching this one to come up with sort of a hybrid solution based on articles I will reference below.

The customer wanted to be able to tag certain document with an expiration date column in their document library. They wanted a select group of individuals per document to receive an email 45 days before their document expired, and on the day their document expires.

I first tried a multi-choice people picker column in SharePoint to do this with and just could not figure the logic out. One site actually said that this was not possible, while others gave some pretty detailed explanations on how to accomplish this and I just could not adapt those solutions here.

So in Sharepoint I created a Date/Time Column (Date only) called “Expiration” and decided to create 3 people picker columns all in a row. Aptly named “Notify_1”, “Notify_2” and “Notify_3” each of which only allowed one person selection. The customer assured me they would never assign the notifications to more than three people per document. This looks a little messy, but it is functional:

Then I went to work in Power Automate:

I ended up creating two very similar flows. The first was titled “Check Expired Docs and Notify” and the second “Check Expired Docs In 45 Days and Notify”. I will explain how I wrote the first one, then copied the first one, made adjustments to make the second flow.

Go into Power Automate and create a new scheduled cloud flow.
Give it a name and choose your schedule. Mine is scheduled to run every day at 10:00 AM

3. Click on “Create.”
4. Add a Get Files (Properties Only) action and choose the site, as well as the Document Library you are using. Then next to Filter Query type in “Expiration ne null” or whatever the column name is for your expiration column. This makes sure that documents with empty Expiration columns do not get considered in your flow and is necessary to avoid getting errors later.

5. Add an Initialize Variable action and give it the same properties as below.

6. Now add a Set Variable action and give it the same properties as below.

This will automatically generate an “Apply to Each” action as so.

7. Now repeat steps 5 and 6 for the other two columns Notify_2 Email and Notify_3 Email, naming the variable varN2 and varN3 respectively.

8. When you finish adding the other two, your flow will look like this.

9. Now add a Filter Array action with the following properties.

The value above is from your Get Files (Properties Only). Use your Expiration column, then “is equal to”, and then the following expression: utcNow(‘yyyy-MM-dd’)

10. Net create a Select action with the properties below.

Enter the 4 keys in, then for the values, use the following expressions:

File Name | item()?[‘{FilenameWithExtension}’]
Created by | item()?[‘Author/DisplayName’]
Expiration Date | item()?[‘Expiration’]
Link to File | concat(‘<a href=”‘, item()?[‘{Link}’], ‘”>Go to File</a>’)

11. Add a Create HTML Table action using the Output of the Select and Automatic for Columns

12. Next we add a Condition action like below.

We added this condition so that we will only be sending out reports daily, if the output of the HTML has enough characters to constitute that files were actually found expired. If the string is not long enough that means it did not find any files meeting the criteria of the Select. So that if it does find files that are expired, it will send an email, if not, it will not send an email.

So for choose a value, enter this expression: length(body(‘Create_HTML_table’))
then “is greater than” gets selected from the dropdown, with the number 40 in the other field. So that if the HTML output is greater than 40 characters, the email gets sent, otherwise no.

13. Next create a Send Email (v2) action and fill it out like below

The expression used in the body of the email, gives the data the correct formatting for an email and it is the following: replace(replace(replace(body(‘Create_HTML_table’),’<’,'<‘),’>’,’>’),’"’,'”‘)

14. That is it for the first flow. In order to create the second flow I copied this flow (Saved as) and then renamed the copy to “Check Expired Docs In 45 Days and Notify”. Then I made two changes.

15. I edited the Filter Array action to say Expiration “is equal to” addDays(UTCNow(), 45, ‘yyyy-MM-dd’)

16. Then I changed the wording of the email to fit the flow.