Background to the requirement to move to Power Automate
SharePoint 2013 Workflows, the workflow technology used in SharePoint and Project Online is being d (https://support.microsoft.com/en-us/office/sharepoint-2013-workflow-retirement-4613d9cf-69aa-40f7-b6bf-6e7831c9691e. ) At around the same time SharePoint Designer 2013, the only tool to easily manage Project Server workflows (they are called that, even in Project Online), is being deprecated(actually reaching end of life). Although this is a little way off – there is another date to be aware of and that is April 2024, when new tenants in M365 will not have SharePoint 2013 workflows available. This does not impact existing tenants who add a new PWA, or even add Project Online to an existing tenancy – just net new M365 customers. The expectation is that most of these would not be familiar with, and so not miss, the ‘current’ workflow technology.
This article describes ways customers can obtain similar functionality to the current workflows by using Power Automate and concentrates on the flow through the stages and a potential way to record workflow status. Although it may not give a 1 to 1 match in functionality – Power Automate can go way beyond what is possible in Project Online today.
Starting a workflow
The role of workflow in Project Online has always been to gather information about the project, usually populating some of this information into custom fields, then control the project lifecycle through its phases and stages, again gathering or displaying information along the way.
In Power Automate there are project related triggers for Project Creation and Project Publishing. (There are also Task and Resource related triggers – which could be used for functionality beyond what has previously been possible). In this article I will concentrate on the When a new project is created (V2) although it may be that the When a project is published (V2) trigger may also be useful at certain points in a larger workflow. An example I’m thinking of (untried) is using the publish trigger to enable an existing workflow that has become disabled due to exceeding a 30 day run period. It could be that a trigger totally outside project could also be used.
Companies could well have many EPTs, and many different workflows used across them – so an initial challenge would be to identify which workflow should run when a project is created. The most straightforward way, but inefficient, is to have all the workflows triggered on create then each look to see if the specific project needs that workflow to run – checking ‘is this for my EPT?’.
I will start with this approach, will enable the create trigger to check for a property value (EPT) before starting – which would be much more efficient.
of a skeleton workflow (that doesn’t really do anything) is shown first, then I’ll walk through the detail of each step, then look at how the stages might be driven. Most of the work is in the Condition action – I’ll open that up later.
Screenshot of firs few stages in the workflow, showing the project create trigger, several variables getting initialized then the Enterprise Project Type being identified
Initializing Variables
In this example the create trigger then initializes a variable IsWorkflowCompleted to false. This variable can then be set to true when the workflow is complete (checked in the Do Until section).
Screenshot showing how the IsWorkflowCompleted variable is set to false
Several other variables are also initialized that get used later. Power Automate needs all the initialize options at the top level.
Get the Enterprise Project Type (EPT)
After the initializations a SharePoint REST call is used to get the EPT details. This action is Send an HTTP request to SharePoint and isn’t a Premium action – so is used in favor of the HTTP Request action. The action uses the site address of the PWA site, a GET method and the Uri field adds _api/ProjectServer/Projects('@{triggerOutputs()?['body/ProjectId']}')/EnterpriseProjectType to the site – hence making a call to Project Online’s REST endpoint, with the GUID of the project inserted that is available from the trigger outputs.
Screenshot of how a REST call to Project Online is configured using a Sent HTTP request to SharePoint flow action
This is then parsed to make the elements of the response available. To use the Generate from sample option to get the json schema – I simply ran a flow that performed the above action – then pulled the body from the output as my sample.
Details of the result of the above step where the body can be used in the subsequent step to generate the sample schema
In the Parse JSON action click the Generate from sample and paste in the body json and click Done.
This then gives you a parsing action like this:
The parse JSON step showing the body dynamic content and part of the schema generated using the sample from above
Now you have the parsed json, the name will be available to check if the project that triggered the flow was created using the EPT we are interested in.
Is this the EPT we were looking for?
A Condition action can then be used to compare the name of the EPT from the parsed json to our target EPT, in this case called FlowEPT. If it isn’t the same, then we are ending the workflow down the no path. Setting the IsWorkflowCompleted doesn’t really do anything in this case and could be left out.
The condition step, checking if this is the FlowEPT and taking a different path for yes or no
If the name is FlowEPT then we take the ‘yes’ path, pull together some details from the workflow runtime by getting the runtime details and parsing it (not used for anything special in this case but could be useful for re-enabling later) then set some variables with key details from the runtime.
Set up our Project Status list
In this example a SharePoint list is used to keep status information of the workflow and progress. It might make more sense to use Dataverse in the real world, as the list could be manipulated more easily outside of the workflow. The list is already created, called WorkflowStatus, and has columns for Stage, Status, Additional and Project Name. The list items are initialized by the following flow actions, which are SharePoint Create item actions.
Screenshot of the initialization steps for the stages displayed in the SharePoint WorkflowStatus list
The idea would be to add this list also to the Project Detail Page, filtered for the specific project (TBD), so that workflow progress could be tracked as it is for the current Project Online workflows.
Stepping into the State Engine
Once these steps are completed, we are ready to roll, and enter a Do Until loop that completes when IsWorkflowCompleted is set to true, but will also become disabled after 30 days, which is a Power Automate limit. More on this later (might be much later, in a future part). The Switch handles what happens within each stage.
Screenshot of the switch control and the different stages that can be invokded
Looking at the first stage in the Switch, Propose, we first pull out the requester e-mail address, by making a SharePoint REST call to get the project’s owner’s properties.
A REST call being configured to get the requestors email address
Parsing the resulting body so we can pull out the e-mail address:
Another parse JSON step to help get the objects returned from the REST call
Updating the list item to say that the proposal is Waiting for Submission. This uses the SharePoint Update Item action, using the ID output from the Initialize_stage_Propose action made earlier)
A step shown that uses the SharePoint update item action to set the workflow stage status
Get Approval to submit the Proposal
The submission form itself is handled using a Start and wait for approval action, into which we put the e-mail it is going to and a link to the project itself:
Screenshot showing the details configured for the Start and wait for approval step, including the email and link to the project
This will put an Approval into the Power Automate Approvals page, like this example:
Shwoing how the above approval shows in the approval page
As well as sending an e-mail to the approver:
The email generated from the above approval
It can be approved in either place. Once approved, the list item is updated again, using the SharePoint Update Item action, to show the Propose stage has been completed – and the time of submission is also put into the list.
SHarePoint edit item action showing how the workflow stage is updated to completed
Switch to the next Stage
The final part of the Propose stage sets the WorkflowStage to Review using an Set variable action – so that the switch takes us to the next column of actions
Set variable action showing WorkflowStage being set to Review
The Review stage in total looks like this – with some updates to the status table and another approval check, which if the response is to revise the request is sent back to the Propose stage – otherwise the workflow moves to Finished and concludes.
Outline of the review stage of the workflow showing another approval stage then updates on the status
Stage Status Updates
Let’s look deeper at the individual steps. The first it to update the stage status, using the now familiar SharePoint Update Item action – and we set the status to Waiting for Review.
Stage status set to waiting for review
Sending for Review
The review of the proposed project is sent out to a named user in this case, Megan, with options to Approve, Revise & Resubmit or Reject. An item link is again constructed to be a link to the project. As before, this uses a Start and wait for approval action, this time waiting for all responses (even if in this case there is just Megan):
The approval configuration, sent to Mega Bowen and requesting Approve, Revise and resubmit or reject
Megan also sees the approvals both in her e-mail and the Approvals page in Power Automate, and can return the workflow to propose for revision, approve it or reject it:
How the above email generated for approval looks to Megan - buttons for actions and links to the plan
The approvals list showing the same request for Megan
Receive and format Review Response
Once the review is actioned, the next step formats the response from the approval step and adds to the Workflow Status list. In this case we take the Responses and format an HTML table, which the following step will put into our status list:
Megan's approval results set as an HTML table for use in the status list
The Output from this step goes into the column called Additional in the Edit item step next:
Posting the results of the approval, including the HTML table, to the workflow status list
An example of the Workflow status list, for a project that has been sent back for revision before finally being approved is below:
Screenshot of the WorkflowStatus list and the rows updated by the workflow
Are we there yet?
Finally in this stage, a condition action is used, to set the next appropriate stage – Propose, if the approval suggested revision was needed, or Finished if it was either Approve or Reject.
The final switch, either going back to propose or onward to finished
If it goes back to Propose it just goes around the loop again, if it goes to Finished then the IsWorkflowCompleted is set to true (which will break out of the Do Until at the end of the cycle) and the SharePoint Edit item is used again to update the status to Completed.
The finished stage screenshot, updating the IsWorkflowCompleted variable and the status list
The Workflow Status list can be added to the Project Details PDP, so that it is available much the same as the existing workflow has its lists. Currently this list would contain all statuses for all projects so would need to be filtered. Feels like a new web part that can filter down to the project (by GUID) to make this easier.
Closing Statement
Wow, that’s a lot to take in, and more coming in a ‘Part 2’ soon, to talk over some ways of using Power Automate, Teams and Adaptive Cards to gather data for custom fields in Project Online, during the workflow. I'm sure there will be further parts after that too. What do you want to hear about?
We would also love to hear from you on how you feel this would work for you – or if you are a partner, your customers. Are you already doing something much better than this? We’d love to hear that too. As mentioned earlier, we do have some plans for investment on the engineering side to make some of these steps easier and more efficient – feedback from you will help focus this investment. Either comment here, or reach out to us at PJOQueries@microsoft.com and let us know
For customers who believe the above approach using Power Automate is not addressing their requirements, and are seeking extension of workflows for genuine scenarios, also please reach out to us on PJOQueries@microsoft.com
Thanks to Divya Tiwari for the original concepts for these workflows, and also to Ramesh Maruthupandiyan (both of whom will be eager to get your feedback at the above addresses) and Jason Rhoades for feeding in his real-world experiences.