Send a reminder Email for a SharePoint Document Library

[AdamInLondon]

Hello friends

I have a SharePoint Document Library called Standard

It has these columns:

Name, "Date Shared" (this is column type Date) , "Shared by" (this is column type Person)

The user creates a folder, gives it a Name and uploads files into it and shares it with someone

Then they add the "Date Shared" date. This will not always be the same day that the folder was created which is why I am not using the "Created" value...

The access to the folder is only for two weeks and must then be deleted.

So, I want a Flow that will run daily,

1. look at the Standard Document Library, find any Date Shared dates that are 14 days or more in the past.

2. Send an email to the person in "Shared by" saying they need to go to the library and delete a folder.

Im thinking this should be simple but I cant figure it out.. Can anyone help please?

Adam.

[creativeopinion · Accepted solution]

@AdamInLondon 

Compose a Dynamic Date

Please note: The Scope action is optional. Not required, but I use them in my flows to keep things organized.

Not sure when you plan to run this flow but it's always best to ensure that the time zone matches which is why I use the Convert time zone action.

For the Base Time field you need to insert an expression.

utcNow()

The utcNow() function will return the current date and time.

For the source time zone, select (UTC) Coordinated Universal Time. Select the appropriate destination time zone to match your time zone. In the Format String field, select Round-trip.

Add a Compose action. Use this compose action to store the expression that will return the date 14 days prior to today. Use the addDays() function. There are three parts to this function. 

addDays([orginal date],[days to add/remove],[dateformat])

Switch to the Dynamic content tab. Make sure your cursor is in between the parentheses. Locate the Converted time dynamic content and select it to insert it into the expression.

Add a comma after the dynamic content and insert the number of days to add/remove. In your case you'll need to enter a -14 to remove 14 days from today's date.

Add another comma and single quotes. In between the single quotes enter a date format: yyyy-MM-dd.

Press OK to add the expression to the Compose action. 

Run a test. Confirm the date returned is 14 days prior to today.

Get files (properties only)

Fun Fact: This action gets files and folders. To return folders only, you can use the following Filter Query:

FSObjType eq 1

Combine the filter query above with the query to target folders with a Date Shared date older than 14 days.

You will need to use the internal column name of your Date Shared column. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials.

Your Filter Query may look something like this (again, ensure that you confirm the Internal column name of your Date Shared Column). Important: Ensure you insert the dynamic content between the single quotes.

FSObjType eq 0 and DateShared le '[insert outputs from the dynamic date compose action]'

This Filter Query will check the specified doc library for Folders only with a Date Shared date that is older than or equal to the date that was 14 days prior to today. You can use the 'lt' operator if you'd like it to check for files that are only older than 14 days prior to today (not equals to). Customize the filter query expression to suit your needs.

Limit Top Count

Depending on how many folders you have in your doc library, I would recommend limiting the Top Count to a smaller number than the total number of items in your library while you are building and testing out the flow. This will increase the speed of your flow runs. Instead of returning all items in your list—it'll limit the Get Items action to the number entered here. Once you've confirmed your flow works—you can remove the top count.

Get Item Count

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get files (properties only) action into the length() function. 

Run a test. Confirm that the number of items returned matches what you're expecting.

Condition

Add a Condition action. In the first value field, insert the outputs from the Compose action above. Change the operator to is not equal to and insert a zero into the second value field.

Apply to Each

Add an Apply to Each action to loop through each folder returned from the Get files (properties only) action. I like to use Compose actions in my flow for dynamic content and to precompose links. This is optional (not necessary).

You can also compose a hyperlink to the folder (if you'd like by using HTML)

<strong><a href="[insert dynamic content for link to item here]">View the Folder</a></strong>

I also like to nest those actions inside a Scope action to make it easy to collapse those actions with a single click.

Run a test. Run a test to verify that the dynamic content is outputting exactly what you'd like to output

Send an Email

Whenever you are using a Send an Email (V2) action, always insert your email address in the To field while you are testing and building your flows.

I've inserted the dynamic content from the previous Compose actions in the Send an Email action.

Tip: Insert a line at the top of the email that includes the dynamic content that you'll use in the To field once the flow is ready to go. This way you can confirm who the email is going to while you are running your tests. If the emails are being sent from your account (instead of a service account) I would recommend marking the automated emails so users know that the email is automated and not coming directly from you.

Run a test. Review the email and make any formatting changes as you see fit. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

[AdamInLondon]

This is brilliant! Thank you so very much! Working very well 🙂

[AdamInLondon]

Thank you again. Do you think it possible to automatically delete the contents of the folders when they are past 14 days using some of the above Flow?

[creativeopinion]

@AdamInLondon You should be able to delete the folder contents using the same concept as the flow above. Will you be using a Filter Query to check the dates of the files or the folder? Which column will you be using in your Filter Query? Created Date?

[AdamInLondon]

Hello and thank you

the Flow would use the "Date_Shared" value to delete the contents of any folder shared more than 14 days ago.