SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Another example use case for adaptive cards in Microsoft Teams to add additional Office 365 Project Online integration. In this example this solution will post a card in Teams for users who have timesheets in progress that are overdue. My previous example posted to Project Owners: https://pwmather.wordpress.com/2019/08/24/projectonline-and-adaptive-cards-in-microsoftteams-using-microsoftflow-for-project-owner-actions-ppm-projectmanagement-msproject-office365-powerplatform/

This example posts to the timesheet owners who have timesheets overdue. To avoid posting to all users in Project Online, this example filters the users based on an enterprise resource custom flag field called “Timesheet Required”. The resources that need to submit timesheets have this new field set to “Yes”.

Like previous posts, I used https://adaptivecards.io/designer/ to design the card.

The Microsoft Flow can be seen below:

The Flow is triggered on a schedule then uses the Current time action to get the current time. Then a SharePoint HTTP action is used to query the ProjectData API to get a list of resources in Project Online who are required to complete timesheets:

Next an Apply to each action is used as the previous action could return more than one resource, the output from the previous action is used for the input. Inside the Apply to each action, a SharePoint HTTP action is used to query the ProjectData API for each resource from the GetUsersForTimesheets action. In the Uri query, two variables are passed in, the resource name items(‘Apply_to_each’)[‘ResourceName’] and the current time:

Then a condition check is used to check if there is at least one timesheet for that resource using the expression length(body(‘GetAllUsersInProgressOverdueTimesheets’)[‘value’]) :

If there is at least one timesheet for that resource the Flow then posts a card to that resource in Microsoft Teams. This is posted to the timesheet owner using the expression items(‘Apply_to_each’)[‘ResourceEmailAddress’]. There are some dynamic values passed into the JSON too for the ResourceName items(‘Apply_to_each’)[‘ResourceName’] and count of timesheets length(body(‘GetAllUsersInProgressOverdueTimesheets’)[‘value’]):

The card is posted to the users Flow chat as seen below:

The user will then be able to click the button to access the timesheet summary page in Project Online and update the timesheets as needed.

Another simple low / no code option for integrating Office 365 Project Online and Microsoft Teams using Microsoft Flow.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

In my previous blog post I created an example of how a combination of Microsoft Teams, Adaptive Cards and Microsoft Flow can be used for project escalations by posting a card into a Microsoft Team for the PMO. In this post I’ve got a slightly different example that posts a card to a Project Owner to escalate to the project owner to action. For those that didn’t see my previous post, here is the link: https://pwmather.wordpress.com/2019/08/18/projectonline-and-adaptive-cards-in-microsoftteams-using-microsoftflow-for-project-escalations-ppm-projectmanagement-msproject-office365/

This example will post to the project owner when the project hasn’t been updated for the given period of time but the logic could easily be updated to another type of check. This has similar logic to the Flow I published last month: https://pwmather.wordpress.com/2019/07/31/projectonline-custom-email-notifications-using-msflow-microsoftflow-ppm-pmot-msproject-exchange-office365-powerplatform-planner-part-3/ 

Like in the previous adaptive card post, I used https://adaptivecards.io/designer/ to design this simple card, this time it’s slightly simpler for this example:

There are a few more actions in the Flow compared to the previous adaptive card example as this runs on a schedule and we need to perform an extra call to get the Project Owners email address. Here is the Flow overview:

The trigger is based on a schedule, in this example it is running weekly as this example checks which projects have not be published in that time. Then the Flow uses the Get past time action to get the date in the past, for this example it’s 5 days ago. These steps can be seen below:

The next action is a SharePoint HTTP action to query the ProjectData API in Project Online to get all projects that have not been published in 5 days using the get past time value and also where the projects are not 100 completed:

The logic can be changed by updating the OData query in the Uri property if you wanted to get projects based on other criteria. Next the Flow uses an apply to each action as the previous action could return more than one project, the output from the previous action is used for the input:

Within the apply to each action the Flow then uses another SharePoint HTTP action to query the ProjectServer API (note: not ProjectData so the account used in the connections will need access to all projects + the reporting OData API) to get the Project Owner details for the current project by passing in the ProjectId using the expression items(‘Apply_to_each’)[‘ProjectId’]:

Now the Flow has all the data to post the adaptive card to the project owner using the JSON payload as seen in the action below. This is posted to the project owner using the expression body(‘GetProjectOwnerDetails’)[‘Email’]. There are some dynamic values passed into the JSON too for the ProjectName items(‘Apply_to_each’)[‘ProjectName’] and the ProjectId items(‘Apply_to_each’)[‘ProjectId’]:

We also set the advanced options on this action to alert the user in Teams:

When the Flow runs this will post to the project owner in Microsoft Teams, on my test Project Online instance I’m the owner for all the test projects so this posted to my Teams client. The alerts are displayed as seen below:

Cards are posted to the project owners Flow chat too as seen below for the projects I own that need updating:

The owner can then easily click the access project button to go and update the project/s as required.

Another simple low / no code option for integrating Office 365 Project Online and Microsoft Teams using Microsoft Flow.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

As Microsoft Teams is the place to be currently I thought I would blog another option for integrating Office 365 Project Online data in Teams. My previous Teams post example was to create a Team and channel for a Project as seen here: https://pwmather.wordpress.com/2019/06/12/create-a-microsoftteam-for-a-projectonline-project-using-microsoftflow-office365-microsoftgraph-ppm-workmanagement-powerplatform-azuread-collaboration-automation-part1/ & here: https://pwmather.wordpress.com/2019/06/13/create-a-microsoftteam-for-a-projectonline-project-using-microsoftflow-office365-microsoftgraph-ppm-workmanagement-powerplatform-azuread-collaboration-automation-part2/ This time we will look at adaptive cards in Teams.

Adaptive cards are not new so I wont go into detail here but if you have not used adaptive cards before, start here: https://adaptivecards.io/. There are two handy Microsoft Flow Teams actions for adaptive cards:

Using Microsoft Flow, I’ve built a simple Flow that posts a card in the specified channel if the published project matches the criteria, in this example I’m posting a card for projects that have a red RAG status field. I designed the card using the adaptive card designer:

The Flow is very simple as seen below:

This Flow is triggered when a project is published – this is a full project publish from either Project Online Desktop or the schedule PDP. The Flow then uses the SharePoint HTTP action to query the ProjectData API:

Update the Uri as needed for your PWA configuration / fields you might want to use. Here we also pass in the project ID for the published project.

Next is a condition action to check for a value specific, in this example we are checking if the RAGPMStatus field has a value of “Slipped and cannot mitigate [Red]”:

If this is true, the Flow posts the card to the channel, if it’s false the Flow ends:

I’m posting to my example PMO Team in the Project Escalations channel. Once the Team and Channel are set, then the JSON is entered. The JSON defines the card:

As mentioned earlier on, I used the card designer to easily build my card with the correct JSON, I then copied this into the Flow action Message field then updated the content that needed to be dynamic such as the Project Name, Owner, RAG PM Status, Project description and action URLs. This is done using the expression option such as: body(‘QueryProjectStatusRAG’)[‘fieldName’]. Once a card is created, this is the output in Teams as you can see below for my two demo projects:

Your cards will look different based on how you design them, they will probably look a lot better too! On this card I have a background image, an image on the card, some project details then a button to access the project and another button to access the project site.

Another simple example of Microsoft Teams integration for Project Online!

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

It’s been a while since I published the last post in this mini series due to getting carried away with other Microsoft Flow goodness for Project Online. The previous post in this custom email notification series can be found below:

https://pwmather.wordpress.com/2019/04/30/projectonline-custom-email-notifications-using-msflow-microsoftflow-ppm-pmot-msproject-exchange-office365-powerplatform-part-2/

In the final part of this email notification series we send an email with a list of projects that have not been published in the defined period, in this example it’s set to 5 days. The Flow also creates a task in Microsoft Planner for the user to chase the project owners to update their project plans. The Flow summary can be seen below:

The trigger is based on a schedule, in this example it is running weekly. Then the Flow uses the Get past time action to get the date in the past, in this example 5 days ago. A compose action is then used to get todays date in the required format. These steps can be seen below:

The Flow then use the SharePoint HTTP action to query the ProjectData API to get the list of Projects where the last published date is on or before the past time date, in this case 5 days ago. The logic here would be changed for your requirements, for example you would also filter out projects that were completed / closed. The next action is a Create HTML table action, here we just pass in the results from the HTTP action as seen in the tooltip displayed in the screenshot below:

The final 2 actions in this Flow create the Planner task and then send the email. We pass in some variables to create the Planner task, in this example we use the output from the Today action to use todays date in the Title and used to set the due date. The task gets assigned to me in this example so I would put my PMO hat on, this would be assigned to someone in the organisation who would be checking project plan quality etc. Then the Flow sends an email, typically this could go to an individual, likely the same person the Planner task is assigned to, or it could go to a multiple people or a group / distribution list etc. In the body on the email the output from the create HTML table action is used and also the task Id for the new planner task:

When this Flow runs, this results in a new Planner task being created in the selected Planner plan:

Then the email is sent out:

As you can see, the email contains a table of test projects from my test Project Online instance that have not been published in the last 5 days and also a link to the task I have assigned in Planner.

Look out for more Microsoft Flow / PowerApps examples for Project Online in the future. For previous examples I have done, this links includes most: https://pwmather.wordpress.com/tag/power-platform/

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

More Microsoft Flow action here in this post, this time building a Risk to Issue escalator for Office 365 Project Online. In project management risks sometimes unfortunately turn into issues. When this event occurs it’s useful to be able to copy over some useful information from the risk item and automatically create that issue item with some of the risk data. In this blog post I will show you a simple solution to do this using a combination of Microsoft Flow, Project Online, SharePoint and a tiny bit of JavaScript.

Firstly let’s take a quick look an example risks list on my test Project Online PWA site:

This is pretty standard apart from two new columns Escalate? and EscalatedDate. The Escalate? column is used to tag the risks that need escalating and the EscalatedDate column is used to hold the date when the risk was escalated to an issue. The issues list in this example is just standard out of the box. On the Risks page I also have a button labelled “Escalate Risks” this is used to create an item on a central risk to issue escalation tracker list on the root PWA SharePoint site. There is a simple bit of JavaScript code behind the button as seen below:

This JavaScript file is added to a central library in PWA and added to the Project Sites on the Risks Allitems view page using a content editor web part. The code gets the project site title, Project GUID and Project Site URL from the associated Project Site then creates a list item on the central Project Risk to Issue Escalation Tracker list:

This simple example JavaScript code always creates items on the central list but in production you would only create the items if there were risks to escalate. EscalationDate defaults to today’s date, Completed defaults to No and the CountOfRisksEscalated is blank by default. The process of adding the escalation item here has two purposes, one to track the history of what risks are escalated so this can be reported on but also trigger the Microsoft Flow to carry out the escalation. The Microsoft Flow can be seen below:

The Flow trigger and actions are detailed below:

The Flow is trigger when an item is created in the Project Risk to Issue Escalation Tracker list then a SharePoint Get Items action is used. The ProjectSiteUrl property from the trigger action is used to dynamically set the correct site address. This action gets items from the risks list on the correct project site where the risks are tagged for escalation and have not yet been escalated.

Then an Apply to each action is used and the result from the Get Items action is passed in:

Inside the apply to action there is a SharePoint HTTP action to create the issue item using a REST call. Inside the body, JSON defines how to create the new item. For the issue columns in here, the values from the risks fields are used, this is where you can update this to map other risk columns to issue columns, the expression for the risk data is similar to this items(‘Apply_to_each’)[‘Category’][‘value’] for lookup columns or this for default text fields items(‘Apply_to_each’)[‘Description’]

Then another SharePoint HTTP action is used to update the risk item via REST:

The current risk ID is passed in to the Uri (items(‘Apply_to_each’)[‘Id’]). The only column updated is the EscalatedDate column on the risk list with the date the risk was escalated.

The final action in this Flow is a SharePoint Update Item action to update the SharePoint item on the central Project Risk to Issue Escalation Tracker list:

The item Id and ProjectName are passed in from the Trigger data but these values have not changed. The Completed column is set to Yes and the CountOfRisksEscalated is set using the length expression to count the number of items in the array from the first action: length(body(‘Get_items’)?[‘value’])

That’s it, a simple low / no code configurable solution starter for Risk to Issue escalation in Office 365 Project Online. A video of this solution starter in action can be seen here: https://youtu.be/IgKxDuu1sng

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Following on from a recent blog post where I demonstrated an example Microsoft Flow for syncing the Roadmap row item status with the associated Office 365 Project Online Task status, I have now made this solution starter Flow available as a package that can be downloaded and imported. For those of you that missed the previous blog post, a link can be found below here: https://pwmather.wordpress.com/2019/05/27/sync-msproject-roadmap-row-item-status-with-projectonline-task-status-using-microsoftflow-cds-powerplatform-msflow-rest-sharepoint-workmanagement-office365/

The Flow package can be downloaded from the Microsoft Gallery here: https://gallery.technet.microsoft.com/Flow-to-Sync-Roadmap-item-44174a4b

Once downloaded the Flow can be imported, here is a Microsoft Flow blog post on exporting and importing Flow packages: https://flow.microsoft.com/en-us/blog/import-export-bap-packages/

Once imported and the connections all set – this will require a Flow P1 or P2 license as it uses the CDS connector, ensure the account has the correct access to Project Online and the CDS, open the flow and update the trigger and actions as these will currently point to one of my demo tenants:

1. Update the “When a project is published” trigger with your PWA URL
2. Update the “GetTaskHealth” action the correct site address for your PWA URL
3. Update the “GetTaskHealth” action Uri to use the correct task level field, replace “RoadmapHealth” as needed
4. Update the Switch action to use the correct task custom field – the expression would be items(‘Apply_to_each’)[‘FieldName’] – replace the field name with the correct task field
5. Ensure the Case statements are updated to match the possible values in your custom field and map to the correct roadmap status value:
• On Track = 0
• Potential Problem = 1
• At Risk = 2
• Complete = 10
• Not Set = 100
6. Update the “List records” action to point to the correct environment
7. Update the “Update a record” action to point to the correct environment

Now save the Flow and test it.

Hopefully you find this useful as a solution starter.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Following on from my last blog post where I started to walkthrough a new Microsoft Flow I created for creating a Microsoft Team for a Project Online project, here is the final part of the Flow. For those that missed part 1, a link can be found below:

https://pwmather.wordpress.com/2019/06/12/create-a-microsoftteam-for-a-projectonline-project-using-microsoftflow-office365-microsoftgraph-ppm-workmanagement-powerplatform-azuread-collaboration-automation-part1/

In the last post we finished off where the Flow action had sent the request to the Graph API to create the new Team with the new channel and new web site tab and then discussed the 202 response and teamsAsyncOperation process. The next part of the Flow’s job is to get the new Teams webUrl and update the Team URL project level custom field in Project Online.

If the Status Code response is 202 to indicate its been accepted, the Flow them moves on to the next action which is a Parse JSON action to get the Location property from the headers output from the previous HTTP action response:

Then with the Location value another HTTP action is used to call the Graph API:

This performs an HTTP GET request to the Graph API to get the targetResourceLocation property from the newly created Microsoft Team, the Location property from the previous Parse JSON action is used in the URI. The advanced options are the same for all HTTP actions where the Graph API is used so I’ve not expanded this is this post – see part 1 for details.

The next action is another Parse JSON from the previous HTTPTeamResourceLocation HTTP action message body:

This time the targetResourceLocation property is needed. Then the final Graph API call is performed to get the webUrl for the newly create Microsoft Team with another GET request. The targetResourceLocation property from the previous Parse JSON action is used in the URI:

The Flow then moves on to the final Parse JSON action to parse the data returned in the HTTPTeamWebUrl message body:

The Flow now has the new Microsoft Team web URL to update the Project Online project level custom field. The next Flow action is a Checkout project action:

This action will checkout the project, the expression used here for the Project Id property is items(‘Apply_to_each’)[‘ProjectId’].

The next action is a SharePoint HTTP action to perform a REST call to POST to the Project Online CSOM REST API to update the custom field, this uses the same expression in the URI items(‘Apply_to_each’)[‘ProjectId’] :

In the REST call data is sent in the body of the request. This contains the correct internal custom field name for the “Team URL” project field and the custom field value to update the field with, which is the webUrl from the previous Parse JSON 3 action. The internal custom field name would need to be updated to the correct field from your PWA instance.

The final action in this example Flow is Checkin and publish project:

This action will publish the project after updating the custom field and check in the project, the expression used here for the Project Id property is items(‘Apply_to_each’)[‘ProjectId’].

Here are some projects that have been updated and have Microsoft Teams created:

Here is a Team for one of the test project – “1 Paul Mather Test Project 2”:

This Team has the new Project channel and the Project Page web site tab that loads the Project Details Page from PWA:

That’s it, a simple low / no code solution to create Microsoft Teams for Office 365 Project Online projects! To use this in production it needs some additional work to handle various different scenarios but hopefully this is a good starting point for someone looking to do something similar.

I will look to provide a download link for this solution starter Flow in the next few days but will post the link on my blog.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Following on from my Microsoft Flow theme of blog posts lately, I am a big fan of the Power Platform in general, but I love Microsoft Flow for building low / no code solutions for Office 365 Project Online. In part 1 of this blog post I will start to walkthrough a new Microsoft Flow I have created that will create a new Microsoft Team for a Project Online project with a new channel and web site tab in the channel that displays the Project PDP directly in Teams. This makes use of 2 Project level enterprise custom fields in PWA, in this example I have one flag field called “Team Required?” and one text field called “Team URL”. The flag field is used to control / request a Microsoft Team for the project and the Team URL is used to store a web URL to the newly created Microsoft Team. This Flow has a few actions, these can be seen below:

Inside the for each loop:

Inside the condition check:

The connections used in this Flow are:

The account used has full admin access to the Project Online PWA instance.

This is a scheduled Flow, I have set this to run daily, but configure the frequency as required:

It’s probably best to schedule it out of hours so that hopefully the projects it creates Microsoft Teams for are checked in at the time the Flow runs as it will edit the Team URL custom field for that project.

Next we set some variables, these variable are used when using the HTTP action to call the Microsoft Graph API. You will need to create an Azure AD app in the Azure Portal and grant it Group.ReadWrite.All Application access:

When creating the Azure AD App you will need to make note of the Application (client) ID and the Directory (tenant) ID:

You will also have to create a client secret for the app (keep this secure but make a note of the secret as you can’t view it after!):

These three strings / IDs are used in the three variables set in the Flow:

The next action is a REST call to the ProjectData API to get a the Project details for projects requesting a Microsoft Team but filtering out those that already have a Team created using this URL:

The full action details can be seen below:

The next  action is an Apply to each loop as the REST call could return more than one project the result array:

The input used is body(‘GetAllProjectsRequiringTeamCreation’)[‘value’], this is added as an expression.

The next action is another REST call but this time to the Project CSOM REST API – notice /ProjectServer rather than /ProjectData, this is the get the Project Owner’s user principal name as this is used later to set the Team / Office 365 group owner:

A variable is passed in to the URI to get the data for the current project, the expression used here is items(‘Apply_to_each’)[‘ProjectId’].

Then a Get user profile (V2) action is used, this is used to get the user ID:

The expression used here is body(‘GetProjectOwnerUPN’)[‘UserPrincipalName’]

The Flow now has all the data required to go and create the Microsoft Team, the next action is a standard Flow HTTP action:

In this action, an HTTP POST is used to post the JSON data defined in the body to the teams endpoint in the Microsoft Graph API to create the Team. Walking through the body of the request, firstly the the team template is set, in this example it is just the standard template, then the display name is set, here the items(‘Apply_to_each’)[‘ProjectName’] expression is used. The team description is then set using same text and the same expression used in the display name. Then the owner is set using the Id property in the Dynamic content from the Get user profile (v2) action. That is the basic properties set to create this team. This example creates a public team, you could look to also set the visibility property to private if you wanted a private team, the default visibility is public. In this example, a new channel is also defined, the channel display name and description is set. Within that new channel a new website tab is also defined setting the tab name and contentUrl / websiteUrl. For the URLs, this creates a web site tab with a link to the Project schedule PDP as an example, the items(‘Apply_to_each’)[‘ProjectId’] expression variable is used to dynamically pass in the correct project ID.

The next action is a condition action to check the response back from the Graph API:

This uses the Status Code output from the HTTP action, a 202 response indicates the API call was accepted, it doesn’t mean the process is completed as creating a team generates a teamsAsyncOperation to create the team. It is recommended to make a GET request to the Location found in the response header until that call is successful and returns the targetResourceLocation, retry every 30 seconds etc. This example Flow doesn’t perform the retry, it just attempts the call to the location and would fail if it is not completed. That would need to be handled in a production environment but in this test instance I’ve not had this fail yet (works on my machine ). I will offer this Flow solution starter as a download but before I do that, I will probably at least put a delay in before making the GET request to the location.

In the part 2 of the this blog post later this week, the rest of the Flow will be detailed.