This blog post is inspired by the following Microsoft Tech Community (SharePoint > PowerApps & Flow) conversation:
The stated requirements are:
- “send a link to a SharePoint document with a message for them to review, make comments, and save when done”
- “create a Status flag for the documents so when a link has been sent out for review, the Flow would automatically set the flag for that document to Under Review”
- “capture the time:date sent in SharePoint in a Sent for Review field”
- “an auto-calculated Due Back field (i.e. five working days after the Sent date), with an email reminder sent midway through day 3”
- No “Content Approval and Checkout”
We can meet these requirements by:
- Adding a “button” to the document library that triggers the document review via Flow / Power Automate
- Using Office’s (Word, Excel etc.) out-of-the-box “Review” features to “review, make comments, and save when done”
- Adding site columns to the document library that store “Status”, “Sent for Review”, “Due Back” and “Reviewer” values
- Creating 2 Flows – 1 to initiate and 2 to remind
Using an out-of-the-box document library, add the following columns:
- “Status” choice column with the values of “Under Review”, “W-I-P” and “Complete”
- “SentForReview” date and time column
- “DueBack” date column
- “Reviewer” person column
- “Flow” string column
When complete, your document library should look like this:
From your document library, create a new blank Flow:
- Click “Flow” > click “See your flows”
- Click “Create” and select “Instant flow”
- Provide a Flow name I.e. “Start document review”; select the “For a selected file” trigger and click “Create”
- Point it to your site and document library. “Add an input” for Email.
- Initialize a string variable and set it to “FileId”
- Add “Get files (properties only)” action. Point it to your site and document library. Specify a “Filter Query” of ID eq variable name from previous step
- Add “Update file properties” action. Point it to your site and document library:
- A) Set “Status Value”. Set “SentForReview” to expression utcNow(‘yyyy-MM-dd HH:mm’)
- B) Set “DueBack” to expression addDays(utcnow(‘yyyy-MM-dd’), 5)
- C) Set “Reviewer Claims” to “Email”
- Add “Send an email” action. Set “To” to “Email” from “For a selected file”
- Click “Save”
Your completed Flow should look like this:
From your document library, format the “Flow” column:
- Click the down arrow; select “Column settings” and click “Format this column”
- Use the Create a button to launch a Flow JSON code to format the column. Make sure you get the Flow ID correct!!!
Upload a document to test:
- Select the document and click the “It’s Flow Time!” button
- Click “Continue” to execute the Flow
- Enter the document reviewer email address and click “Run flow”
Once complete the “Status”, “SentForReview”, “DueBack” and “Reviewer” columns will be populated and the reviewer will have received an email.
Create a new blank Flow to send an email reminder:
- Select “Scheduled flow”
- Provide a Flow name I.e. “Document review reminder”; set repeat to “Daily” and click “Create”
- Add “Get files (properties only)” action. Point it to your site and document library. Specify a “Filter Query” of DueBack ne null
- Initialize a string variable and set it to expression substring(addDays(utcnow(‘yyyy-MM-dd’), 4), 0, 10)
- Add a “Condition” and “DueBack” is equal to the variable from the previous step
- In the “If yes” section add “Send an email” action. Set “To” to “Reviewer Email”
- Click “Save”
Your completed Flow should look like this:
Reminder emails will look like this:
What’s not covered here is the changing of the document review status’. These manual steps would be performed by the reviewer by modifying the “Status” site column.
There are many other approaches to document review and approval including out-of-the-box options but this solution showcases SharePoint and Power Automate in a way that business value, through automation and configuration, can be achieved.
Thanks for stopping by.
Norm
Norm Young 