Creating a 'Page Review' reminder for SharePoint Site Pages

[Paul_Bird]

Hi community!

Is there a way of leveraging Power Automate to create a full process which manages SharePoint Site Page reviews? (As briefly discussed in Planning intranet governance - SharePoint in Microsoft 365 | Microsoft Learn under the 'Content review' heading.)

Process steps as we see them are:

1. Something which drives a constantly refreshing 'review date' value in the Site Page Library based on the creation date to begin with, and then subseqent review activities. Perhaps 6 months after either of those events.
2. Something which sends a reminder (email or 'Approval', or both) to a page owner defined in the Site Page Library, when the review date value is reached.
3. Something which then responds to the review activity to then move along the 'review date' value by another 6 months.
4. And all whilst ignoring News Posts in the Site Page Library, which I believe is done by looking for... 

   (PromotedState eq 0)​

It's one of those processes which must be required by most large organisations, or really anyone with an Intranet like solution. And I'm surprised there isn't an 'out of the box' solution offered by Microsoft, like for Site Page Approvals.

Thanks so much for your support!

[Expiscornovus]

Hi @Paul_Bird,

Yes, it should be possible to setup something like that.

I would suggest to start adding that review date column to the site pages libraries. This way you can use that field in your scheduled review flows. In your flows you can compare todays date with that review date. Let us know if you need any additional help with the flow configuration.

[Paul_Bird]

Hi @Expiscornovus, thanks for the speedy response!

Makes sense, and just done... 

Any thoughts on populating the 'ReviewDate' column? I could go with a calculated value, but that only works that one time, right? It doesn't work well for ongoing, continuous review activities.

[Expiscornovus]

Hi @Paul_Bird,

For ongoing review activities you could use that review date + number of days approach. After the review has been completed you can update the file properties and calculate/update the new review date within the flow. You could for example use an addDays function in an expression for this.

[Paul_Bird]

Thanks for the continued support. This makes sense I think!

If it helps with context and for anyone else doing this, I've put some more thought into the 'Review Date' column configuration within SharePoint and have carried out the following:

• Made it require a value.
• Set a default, calculated value to "=TODAY()+180".
• And set column validation to "=AND([Review Date]>TODAY(),[Review Date]<TODAY()+181)" in the library settings formula view. (Which shows as "=AND(ReviewDate&gt;TODAY(),ReviewDate&lt;TODAY()+181)" in the list column editing view.)

I believe this a good place to start as it ensures the Intranet content review process has a value to work with, ensures it doesn't add an extra step for users creating a new Site Page as it automatically populates it from the start, and ensures that future values cannot be greater than 180 days from now.

So, now that we have a property to use against each item/Site Page within the Site Page library, would you be able to assist in setting up the start of the Flow? 🙏

[Expiscornovus]

Hi @Paul_Bird,

Below is an example Recurrence flow.

1. A Filter Query in a Get files (properties only) which checks if the ReviewDate value is less than or equal to (le) today (utcNow)

ReviewDate le '@{utcNow()}'

2. An expression which uses the ReviewDate value of the current item and adds 180 days via the AddDays function. That expression is used in the Update file properties action.

addDays(item()['ReviewDate'], 180)

Btw, I used this setup for the ReviewDate column

[Paul_Bird]

Superb! Thank you very much for getting me started on the flow 🙌

I've been playing around and this does indeed do the trick! (To allow me to test it, I temporarily ditched the column validation on 'Review Date' so that I could create some test pages with today's date. I'll add that back in once I know the flow is running perfectly.)

I've subsequently tried to swap this to an 'Approval' action within the flow so that we can put all of this activity through the great Approvals app in Teams. I've clearly done this wrong as it seems to now be waiting on each approval step to complete before it (the flow) moves on to the next page in the Site Pages library.

Here's what I've created...

You can see how the 'Start and wait for an approval' step along with the 'Switch' steps, are all nested within the 'Apply to each' step. Presumably this is where I'm going wrong, but I'm not sure how I can do this in alternative fashion?

Thanks for your help again!

[Paul_Bird]

In case it's helpful - I ran the analysis capability and it provided this helpful process diagram of how it currently works...

[Expiscornovus]

Hi @Paul_Bird,

Yes, adding the start and wait for the approval makes the design totally different. My suggestion was for the notification/reminder stuff. Adding that waiting stepping will cause this behaviour.

In this scenario/with this requirement it might be better to have a parent flow which checks which files are due for review and trigger a child approval/reminder flow for the ones which are due.

[Paul_Bird · Accepted solution]

Thanks for your advice on this @Expiscornovus! 

After your very helpful foundations I spent quite a bit of time progressing the idea of an Approval focused approach, and now believe we have a pretty robust solution!

At the high-level, we are utilising the Site Pages library within the site, plus a new List which provides both a data store for the automation as well as a useful activity/audit log for the people interested in the process (the Internal Comms team in our instance). Then within Power Automate we have a 'Start' Flow, which is scheduled to run once a day, looking for pages within the Site Pages library which have a Review Date matching today's date. And 3 follow-up/completion Flows that look for the 3 possible responses to the Approval created by the 'Start' Flow, which update the page properties and audit List, and in one of those 3 Flows it also sends an email to the Internal Comms team to flag that a page is no longer required.

Here are some screenshots providing more detail for anyone interested...

--

The Site Pages library

--

The Review Date column settings

--

The List within the Site

--

The 'Start' content review process Flow (1 of 3)

The 'Start' content review process Flow (2 of 3)

The 'Start' content review process Flow (3 of 3)

--

An Approval response Flow "Changes made" (1 of 3)

An Approval response Flow "Change made" (2 of 3)

An Approval response Flow "Changes made" (3 of 3)

--

An Approval response Flow "No changes made" (1 of 3)

An Approval response Flow "No changes made" (2 of 3)

An Approval response Flow "No changes made" (3 of 3)

--

An Approval response Flow "Page no longer required" (1 of 4)

An Approval response Flow "Page no longer required" (2 of 4)

An Approval response Flow "Page no longer required" (3 of 4)

An Approval response Flow "Page no longer required" (4 of 4)

--

Some likely areas for improvement are:

• Make the trigger for the Approval responses more unique, to ensure they aren't unecessarily triggered by other Approvals which are given the same custom responses in the future. (It won't break the process if this happened though, it would just fail to find a matching ApprovalID within the List.)
• Tweak the wording in both the Approval and the email (for "page no longer required") and perhaps include more dynamic content to make them more useful.
• Introduce a reminder Flow which looks at items in the List which have been "Pending" for longer than 7 days (or something like that), to help reduce the risk of Owners forgetting they need to do something.

Otherwise I think this is what we were after and I suspect numerous other organisations might benefit from this as well!

[CraigVickery]

Hello @Paul_Bird, can you break out how you setup your columns for your audit page? (How did you define the Approval ID?)

[Paul_Bird]

Hi @CraigVickery - Of course.

The List I created within our new Intranet site (to provide an audit trail) consists of the following columns...

The ApprovalID value is pulled from the 'Approvers' output (after the Approval has been created within the flow), with it specifically using the 'Approval ID' to populate that column. It's a value which is automatically created behind the scenes and looks like it's unique to every Approval created, making it ideal for recording updates made against a single Approval workflow.

I hope that helps?

[Paul_Bird]

A little update on the solution I detailed above now that we're seeing it tested within our org.

In the 'Start' flow, within the 'Get files (properties only)' step, I've tweaked the Filter Query to avoid it triggering multiple times on the same Page if it's not reviewed before the next scheduled run.

Originally it was...

ReviewDate le '@{utcNow ()}' and PromotedState eq '0' 

And now it's:

ReviewDate eq '@{utcNow ('yyyy-MM-dd')}' and PromotedState eq '0'

If you only changed the le (less than or equal to) expression to eq (equal to), it won't work, because utcNow is looking for a time along with a date, and the ReviewDate value for each Page has a time of "00:00:00", so by including yyyy-MM-dd in the utcNow variable you're only looking for a date value.

[EspenLund]

Hello @Paul_Bird, How do you get all your site pages into the list? Is this a manual registration process, or is it possible to get it automated?

[Paul_Bird]

Hi @EspenLund ! The Flow uses the "Site Pages" Page library/List which already exists in every site (or at least I think it's there in every site by default). That way the Flow is always looking at the latest information 👍

[matthewcarter]

Thanks Paul for providing this tutorial, this is excellent information,

I have a question for anyone who may know the answer, when i run any of the approval response options, it fails with status code 404.

It also seems to be trying to apply to all pages on the site, not just the newly modified.

Raw Output

    "statusCode": 404,

    "headers": {

        "Cache-Control": "max-age=0, private",

        "Vary": "Origin",

        "X-NetworkStatistics": "0,525568,0,0,39587,0,36394",

        "X-SharePointHealthScore": "3",

        "X-MS-SPConnector": "1",

        "X-SP-SERVERSTATE": "ReadOnly=0",

        "DATASERVICEVERSION": "3.0",

        "SPClientServiceRequestDuration": "28",

        "SPRequestDuration": "29",

        "X-DataBoundary": "NONE",

        "X-1DSCollectorUrl": "https://mobile.events.data.microsoft.com/OneCollector/1.0/",

        "X-AriaCollectorURL": "https://browser.pipe.aria.microsoft.com/Collector/3.0/",

        "SPRequestGuid": "XXXX",

        "request-id": "XXXX",

        "MS-CV": "5kGYS+weSECB8UC9M3VyxA.0",

        "Strict-Transport-Security": "max-age=31536000",

        "X-Frame-Options": "SAMEORIGIN",

        "Content-Security-Policy": "frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com *.powerapps.com *.yammer.com *.officeapps.live.com *.office.com *.microsoft365.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com onedrive.live.com *.onedrive.live.com securebroker.sharepointonline.com;",

        "MicrosoftSharePointTeamServices": "16.0.0.24120",

        "X-Content-Type-Options": "nosniff",

        "X-MS-InvokeApp": "1; RequireReadOnly",

        "P3P": "CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"",

        "X-AspNet-Version": "4.0.30319",

        "X-Powered-By": "ASP.NET",

        "Timing-Allow-Origin": "*",

        "x-ms-apihub-cached-response": "true",

        "x-ms-apihub-obo": "false",

        "Date": "Tue, 10 Oct 2023 05:13:46 GMT",

        "Content-Length": "179",

        "Content-Type": "application/json",

        "Expires": "Mon, 25 Sep 2023 05:13:46 GMT",

        "Last-Modified": "Tue, 10 Oct 2023 05:13:46 GMT"

    },

    "body": {

        "status": 404,

        "message": "Could not find list item.\r\nclientRequestId: XXXX-40bd337572c4\r\nserviceRequestId: XXXX"

    }

}

[matthewcarter]

update, i got the error fixed by selecting approval model type, rather then ID, and the flow successfuly runs. however the fields now arent being updated in the audit list page.

[Paul_Bird]

Hi @matthewcarter ! Glad that this has been useful.

What's the error details you get now that you've resolved the initial issue? It could be a permissions issue rather than the design of your Flow. I encountered that at one point. Make sure the account/identity the Flow is using has the necessary permissions to update the Audit List page?

[matthewcarter]

Hi Paul, i ended up tweaking this a little differently however the foundation of how i have done is from this post, so thanks for the tutorial provided.

i have removed the 3 separate flows for different responses and combined into 1 flow with conditions.

I also created an add to time field, so rather then 'adddays' and entering a number in the formular, i can get the current date, and add to time which lets me select either days, months, or years.

[Paul_Bird]

Smart! When I get some time, I'll take a look at your approach and see if I can improve ours! 🤜