Blog Post

Project Support Blog
8 MIN READ

Using Power Automate for Project Lifecycle Workflows in Project Online – Part 1

Brian-Smith's avatar
Brian-Smith
Former Employee
Mar 06, 2024

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.

 

 

Updated Mar 06, 2024
Version 1.0

5 Comments

  • Woody's avatar
    Woody
    Brass Contributor

    Hi Brian-Smith​,

    Following the announcement regarding the retirement of SharePoint 2013 Workflow, our team is actively planning our migration strategy for Project Online. We appreciate the advance notice and guidance provided so far.

    A key question has come up as we design our new processes in Power Automate, specifically concerning the $ProjectWorkflowStageDataSet$ OData table. We need to understand the intended state of this table after the April 2, 2026, deactivation date.

    Could you clarify if the $ProjectWorkflowStageDataSet$ table will:

    • Become Read-Only, to preserve a historical record of phases and stages from legacy workflows?
    • Remain Read-Write, allowing Power Automate and other tools to programmatically set and update project phases and stages?

     

    This information is crucial for us to architect a robust and future-proof solution for managing our project lifecycles.

    Thank you for your time and clarification on this matter.

    Best regards,

    Woody

  • Arintra's avatar
    Arintra
    Copper Contributor

    Hello Brian-Smith​,

    I am very newbie to Power Automate as normally I used SharePoint Designer to create workflow for Project Online. As SP Designer is being retired and I need to set up a workflow for a new MS tenant created early this year, so I follow your instruction on developing the workflow in Power Automate. 

    So far I create actions step by step following your blog and got error when running the flow.

    This is the flow I set:

    The error message: 401 UNAUTHORIZED
    clientRequestId: a8ffb78d-0e5c-4136-830d-f165e3b1ef75
    serviceRequestId: 2ae9a2a1-80d3-5000-4c0d-1ecf48623cdf

    How to fix

    You should be able to fix this by verifying your account with . You may need to re-enter your password.

    I've tried signing in the account, but still error.

    Is this error from missing initializing these variables?

    If yes, please advise the input data for each block. I have no idea how to fill in the information required.

     

    • Arintra's avatar
      Arintra
      Copper Contributor

      I have tried adding Initializing Variables while leaving value blank, the error is still there. Please advise.

       

  • BillDow55's avatar
    BillDow55
    Tin Contributor

    Thanks Brian, I can't wait to dive into this! 🙂

  • Ian Bruckner's avatar
    Ian Bruckner
    Brass Contributor

    I've been using a SharePoint list for ideation and project-level metadata for a long time now and have a flow setup to "create project in project online" once a given project reaches a "ready to be fit into the capacity planning" stage.... along with some other automated flows to keep some fields in sync between the SharePoint list and Project Online which we use extensively for capacity planning and timesheeting (which are not present adequately in P4W or maybe now called Planner [premium?]).

     

    I've found flow was a very good way to go until just last week when new capacity limits on flows hit and I started getting nastygrams from Microsoft https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Faka.ms%2Fpa-ppr&data=05%7C02%7Cimbruck%40ilstu.edu%7C168a47905f574634c57808dc4e6c9318%7C085f983a0b694270b71d10695076bafe%7C1%7C0%7C638471475438522739%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=uprWkm7PmNU19eaNvbR9ZCqcHp4AYQtNhrXxtGG4Vj4%3D&reserved=0

     

    Question, though - what does PJO stand for? I haven't come across that abbreviation yet. Is that ProJect Online?