Building a SharePoint File Retention Self-Service Report with Power Apps and Power Automate

 

Introduction

As organizations roll out SharePoint file retention policies, site owners and end users often want to know which files will be affected—especially before files are deleted. To empower users and reduce IT support requests, we built a Power App and Power Automate solution that lets anyone request a report of files in a SharePoint site that haven’t been modified in over 5 years. This post walks through the design, technical implementation, and optimizations of this solution.

Solution Overview

User Experience:

• Enter a SharePoint site URL in the Power App.
• Click a button to request a report.
• Receive an email listing all files (grouped by document library) that haven’t been modified in 5+ years.
• See a status message in the app confirming completion and showing the exact number of affected documents.

Purpose:

• Proactively inform users about files at risk of deletion due to retention policies.
• Enable site owners to review and update files before enforcement.
• Reduce surprises and support tickets when policies are enacted.

Power Automate Flow: Step-by-Step Breakdown

1. Trigger

• Manual trigger from Power Apps, accepting SharePoint site URL and requester’s email.

2. Calculate 5-Year Cutoff

• Uses Add to time to subtract 1825 days from the current date.

3. Initialize Variables

• varHTMLTables: Holds the HTML content for the email report.
• varDocCount: Tracks the total number of documents found.

4. Get All Lists and Libraries

• Retrieves all lists and libraries in the specified site.

5. Error Handling

• If the site is inaccessible, returns a clear error message to the Power App.
• If the site has zero libraries, returns a specific message.

6. Filter for Document Libraries

• Filters results to include only document libraries - item()?['Type'] = string('101')

7. Loop Through Each Library

• For each library:
• Retrieves files not modified in the last 5 years.
• Filter query is: Modified lt datetime'@{body('Add_to_time')}' and FSObjType eq 0
• If files are found:
• length(outputs('Get_files_(properties_only)_2')?['body/value'])
• Builds an HTML table (File Name, Modified Date, Last Editor, Link).
• FileName = item()?['{FilenameWithExtension}']
• Modified = item()?['Modified']
• LastEditor = item()?['Editor#Claims']
• Link = item()?['{Link}']
• Appends the table to the report variable.

<h2>Library:  @{items('Apply_to_each_2')?['DisplayName']}(@{length(outputs('Get_files_(properties_only)_2')?['body/value'])} files affected)</h2>

@{body('Create_HTML_table')}

<hr>

• Increments the document count.
• length(outputs('Get_files_(properties_only)_2')?['body/value'])
• If no files are found, skips to the next library.

8. Send Email

• Sends an email to the requester with the HTML report, BCC’ing the admin for tracking.

9. Status Message

• Returns a status message to the Power App:
• If files are found: “SharePoint site report request completed and emailed to [email]. There are [count] documents that have not been modified in over 5 years.”
• If no files are found: “There are zero documents older than 5 years in this SharePoint site.”

Power App: Step-by-Step Breakdown

1. Create a canvas app in Power Apps
2. On the left rail, click ... and click Power Automate to add your flow

1. In Data on the left rail, add Office 365 Users
1. 
2. Add in your text field headers, and then add in a text box, and a button. Add a text field below the button named LabelStatus.
3. For the text box, put in the HintText = "Put in a SharePoint site URL that you have access to."
4. Change the OnSelect for the button. You will need the name of your flow, and  Office365Users.MyProfile().Mail as parameters to run it. Here is an example:

Set(flowResponse, 'Button->Getfiles(propertiesonly),CreateHTMLtable,Addtotime...'.Run(TextInput1.Text,Office365Users.MyProfile().Mail));

LabelStatus.Text=flowResponse.status;

1. Change the Text of your LabelStatus to be: flowResponse.status

Conclusion

This solution empowers site owners and end users to proactively manage content ahead of retention policy enforcement. By combining Power Apps and Power Automate, we deliver a transparent, self-service experience that reduces IT workload and increases user confidence. The technical design is robust, extensible, and ready for future enhancements.