#ProjectOnline custom #email notifications using #MSFlow #MicrosoftFlow #PPM #PMOT #MSProject #Exchange #Office365 #PowerPlatform #Planner Part 3

July 31, 2019 at 12:01 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Reporting, Workflow | 1 Comment
Tags: , , , , , , , , ,

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:


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/

#ProjectOnline Risk to Issue Escalator built using #MicrosoftFlow #Office365 #SharePoint #JavaScript #PPM #ProjectManagement #MSProject #PMOT #PMO

July 11, 2019 at 11:35 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Workflow | Comments Off on #ProjectOnline Risk to Issue Escalator built using #MicrosoftFlow #Office365 #SharePoint #JavaScript #PPM #ProjectManagement #MSProject #PMOT #PMO
Tags: , , , , , ,

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:

Risks List

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:

Risk to Issue Tracker

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:

Flow Trigger

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:

Apply to each

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:

Tracker Update

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

Create a #MicrosoftTeam for a #ProjectOnline Project using #MicrosoftFlow #Office365 #MicrosoftGraph #PPM #WorkManagement #PowerPlatform #AzureAD #Collaboration #Automation Part2

June 13, 2019 at 8:25 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Workarounds | 1 Comment
Tags: , , , , , , , , ,

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:


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:

Parse JSON Action

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:

Parse JSON 2

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:

Parse JSON 3

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:

Checkout Project

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:

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.

Create a #MicrosoftTeam for a #ProjectOnline Project using #MicrosoftFlow #Office365 #MicrosoftGraph #PPM #WorkManagement #PowerPlatform #AzureAD #Collaboration #Automation Part1

June 12, 2019 at 9:52 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Workarounds | 2 Comments
Tags: , , , , , , , , ,

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 Smile). 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.

#ProjectOnline custom #email notifications using #MSFlow #MicrosoftFlow #PPM #PMOT #MSProject #Exchange #Office365 #PowerPlatform Part 2

April 30, 2019 at 8:22 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Workflow | Comments Off on #ProjectOnline custom #email notifications using #MSFlow #MicrosoftFlow #PPM #PMOT #MSProject #Exchange #Office365 #PowerPlatform Part 2
Tags: , , , , , , , ,

Following on from my last post on email notifications using Microsoft Flow, this post looks at further examples. Part 1 can be found here: https://pwmather.wordpress.com/2019/03/18/projectonline-custom-email-notifications-using-msflow-microsoftflow-ppm-pmot-msproject-exchange-office365-powerplatform-part-1/

In case you missed it, I also published a video last week with a simple example Flow to send the project owner an email on project creation: https://youtu.be/CCdxUqBrhEA

In part 2 we will look another example email notification to email each resource the projects they are assigned to for the coming week. The Flow can be seen below:


This is triggered on schedule as seen below, update as needed:


The Flow then gets some date time values using the Date Time actions for the current date time and a future date time:


The Flow then fires off an HTTP request to SharePoint to get a list of resources with email addresses from the Project Online Odata Reporting API:


Then using an Apply to each action we send an email to the assigned resources. Firstly we pass in the output from the previous step, which is:



Then inside the loop we perform another HTTP call to SharePoint, this time to get the resource’s assignments for the week by querying the Project Online Odata Reporting API as seen below:


Here we are passing in 3 variables to the Odata query:

  • ResourceId which is the following expression added in: items(‘Apply_to_each’)[‘ResourceId’]
  • Current time and Future time to filter the data returned from the time phased resource demand endpoint to this week, these are the outputs from the previous date time actions:


The Flow then creates an HTML table from the data returned from the previous action:



Then the final action in the Flow is to send an email:


The To value is an expression: items(‘Apply_to_each’)[‘ResourceEmailAddress’]

Update the email body as needed and include the output from Create HTML table action.

This will result in an email being sent to all resources in Project Online with email addresses containing their weekly assignments detailing the projects that they are working on, here is an example email:


Another example that demonstrates how easily custom email notifications can be created for Project Online using Microsoft Flow.

#ProjectOnline custom #email notifications using #MSFlow #MicrosoftFlow #PPM #PMOT #MSProject #Exchange #Office365 #PowerPlatform Part 1

March 18, 2019 at 9:26 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Workflow | 1 Comment
Tags: , , , , , , , ,

This blogs post has been delayed due to all of my blog posts on Microsoft’s new Roadmap service – summary post here with most of the posts: https://pwmather.wordpress.com/2019/03/01/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom-dynamics365/

This post continues the series of posts I started to do in December 2018 following on from a Microsoft Tech Sync session where I presented a session on Project Online and Flow better together. As it’s been a while, here are links to the previous posts:

Post 1: https://pwmather.wordpress.com/2018/12/06/projectonline-publish-all-projects-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform-part-1/

Post 2: https://pwmather.wordpress.com/2018/12/12/projectonline-publish-all-projects-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform-part-2/

Post 3: https://pwmather.wordpress.com/2018/12/14/projectonline-snapshot-data-to-sharepoint-list-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform/

In this post we take a look at an option for building custom email notifications with a no code / low code solutions using Microsoft Flow. This example sends an email for projects that are running late. There are two simple versions for this, one with a details table in the email and one with just the project name but includes hyperlinks in the email to the project detail page. These are both very similar, the first one can be seen below:


This is triggered on the Recurrence trigger, set based on your requirement. This then uses the Sent an HTTP request to SharePoint action to query the Project Online OData Reporting API:


This will control the data that is included in the email, so this OData query can be updated based on your requirements. Next the Flow uses the Create an HTML table action:


For this action we pass in the project data array from the previous action using a custom expression:


The final action is to send the email:


In the body of the email here we are just using the output from the previous Create HTML table action:


This results in an email being sent with the data from the OData query used (these are just my test projects and not real projects!):


Very simple! Sticking with the same theme for late projects but this time the email contains hyperlinks into the projects, this Flow is slightly different:


The difference here is we do not use the Create HTML table action but instead use Select and Join from the Data Operations actions. Firstly the select actions looks like this:


The Select action is used to transform the data in the results array from the previous step. Just the same as the Create HTML table in the first example, we pass in the project data array value from the previous action into the From property. Then the Select action was changed to use the text mode using the toggle option outlined in red below:


In Map properties, transform the data as needed in the email such as:


Here we are building up a URL passing in the ProjectId for the PDP URL (update to the correct PDP) and the ProjectName for the URL title. Then we use the Join Data Operations action to put each project on a new row in the email:


The From property is just using the Output from the previous Select action:


Then the final action is the email:


Note the Is HTML property is set to Yes. In the Body we type the email body as required plus the Output from the previous Join action:


Then the email is sent on the defined schedule with clickable links to the Project Detail Pages (again, these are just my test projects and not real live projects!):


These are two simple examples but as you can see, it’s very easy to build Project Online related emails using Microsoft Flow. I have some more examples in my next posts coming soon.

#ProjectOnline Snapshot / data to #SharePoint list using #MSFLow #MicrosoftFlow #PPM #PMOT #Office365 #PowerPlatform

December 14, 2018 at 10:00 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, Workflow | 2 Comments
Tags: , , , , , , , ,

Next in my series of posts on using Microsoft Flow with Project Online is capturing Project Online data into a SharePoint list, this is a useful scenario for simple snapshot requirements. For example, if you want to snapshot some key project level data, the easiest place to store this data is in a SharePoint list. I have blogged simple code examples before that do this: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/ & https://pwmather.wordpress.com/2018/01/27/projectonline-project-level-html-fields-to-a-sharepoint-list-powershell-ppm-office365/ Whilst these approaches work, the PowerShell does need to be run from somewhere, a server / Azure Function etc. This post provides the same end result with Project Online data in a SharePoint list but all from a Microsoft Flow. The Flow can be seen below:


This simple example makes use of the recurrence trigger to schedule the process, the “Send an HTTP Request to SharePoint” action to get the project data from Project Online and a SharePoint create item action inside an Apply to each loop. We will walkthrough the actions later in the post.

Firstly, the SharePoint list was created:


This was created in my Project Online Project Web App site collection. I created SharePoint columns on this list for each of the fields I wanted to capture from my Project Online dataset. As this is just an example, the number of fields and data is quite limited. Now back to the Flow. We will skip over the recurrence trigger to the first action that gets the Project Online data, this just uses the “Send an HTTP Request to SharePoint” action to call the Project Online OData REST API so that we can easily get all of the Project Online data. In this example we are accessing the Projects endpoint in this API and selecting a few example project level fields including an example custom field:


This action will get all of the data based on the Odata query used in the Uri input. We wont cover all of the settings here in this post as I covered this in the last post found here: https://pwmather.wordpress.com/2018/12/12/projectonline-publish-all-projects-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform-part-2/

Next we need to loop through all of the projects in the results array to create a SharePoint list item for each project. To do this we need to use an “Apply to each” action:


In the output from the previous step we use body(‘ReadallProjects’)[‘value’] to use the data from the previous step which is all of our Project Online projects with some data minus the timesheet project in this example. Then for each project in the array we create a list item on our target SharePoint list using the create item action. In the create item action we just map the data from the array to the correct list column. The Project Online fields are accessed using an expression, for example for ProjectCost in this example Flow the expression is items(‘Apply_to_each’)[‘ProjectCost’] where apply to each is the name of the action and ProjectCost is the field / property in the results from the Odata query.

Once this Flow runs a few times you can then easily create snapshot / trend reports or even extend the SharePoint view to show what you need:


As you can see in this example, I’ve updated the SharePoint view to show the RAG icon in the Overall RAG column rather than the text value. This is very simple with the column formatting options available with the SharePoint modern UI using JSON.

Another example of extending Project Online with low / no code solutions in Office 365.

There will be further example solutions built for Project Online using Microsoft Flow in later posts.

Change required for #SharePoint Online / #ProjectOnline REST API calls when using WebRequest #PowerShell #dotnet #office365dev

May 9, 2018 at 7:00 am | Posted in .Net, Administration, Customisation, Functionality, Information, Issue, PowerShell | 2 Comments
Tags: , , , ,

Just a quick blog post to highlight a change the is required when querying Project Online / SharePoint Online REST APIs in code when using the WebRequest class. Previously the PowerShell code sample below would work and authenticate with no issues:

#add SharePoint Online DLL - update the location if required
$programFiles = [environment]::getfolderpath("programfiles")
add-type -Path $programFiles'\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll'

#set the environment details
$PWAInstanceURL = "https://PWAURL"
$username = "username" 
$password = "password"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force

#set the Odata URL with the correct project fields needed,
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectType ne 7&`$Select=ProjectId,ProjectName,ProjectPercentCompleted,ProjectOwnerName"

#get all of the data from the OData URL
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass)  
$webrequest = [System.Net.WebRequest]::Create($url)
$webrequest.Credentials = $spocreds
$webrequest.Accept = "application/json;odata=verbose"
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()
$results = ConvertFrom-Json -InputObject $data

There has been a change in Office365 and this would now generate a 401 unauthorized error as seen below:


It is now required to use the authentication cookie, not sure if this is a permanent change or a temporary issue. Adding the line below resolves the issue:

$webrequest.Headers["Cookie"] = $spocreds.GetAuthenticationCookie($url)

#get all of the data from the OData URL
[Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass)  
$webrequest = [System.Net.WebRequest]::Create($url)
$webrequest.Credentials = $spocreds
$webrequest.Accept = "application/json;odata=verbose"
$webrequest.Headers["Cookie"] = $spocreds.GetAuthenticationCookie($url)
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()

This change would be applicable to all of my PowerShell code samples that query the Project Online OData API found here: https://gallery.technet.microsoft.com/site/search?f%5B0%5D.Type=User&f%5B0%5D.Value=PWMather&sortBy=Date

Hope that helps

#ProjectOnline #PowerShell to keep #PPM data in sync on #SharePoint list #PMOT #O365

March 1, 2018 at 1:38 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information | 1 Comment
Tags: , , , , ,

Following on from my two PowerApps posts on creating an example Project Online PowerApps app, I thought I would publish the example PowerShell script that I used to populate and update my SharePoint list in the Project Web App site collection. For those that missed my Project Online PowerApps posts, please find the links below:



The script sample can be downloaded from the Microsoft Script Gallery here: https://gallery.technet.microsoft.com/Keep-Online-data-in-sync-06a1bf8d

This PowerShell script will use the Project Reporting OData API to get all of the published projects in the Project Online PWA Site Collection, then for each project it will then create or update a list item on the specified SharePoint list. If the project has already been created on the SharePoint list on a previous run, the items will be updated rather than creating a new item.The user setting up the script will need to make some changes to the script, this is covered in the blog post.

The account used will need access to the OData API in PWA and contribute access to the target SharePoint list. The SharePoint list will also need to be created beforehand with the required columns.

To get the script to work you will need to reference the DLL as seen in the image below:


This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Firstly decide what project level fields you want to include in your PowerApp / SharePoint list, this will determine the list column requirements. Then create the SharePoint list in the PWA site collection with the required columns, for this example I created a list called ProjectData with the columns below:


Title is used for my Project Names in this example. You will then need to update the list item update / creation part of the sample script to map to the correct SharePoint column names you created and the project fields:


Also ensure the variables have been updated correctly, placeholder values seen below:


Save and run the PowerShell script (fully test on a non-production PWA site collection before Production) to ensure the data is captured correctly in the target SharePoint list. This script could be run manually on demand or on schedule using a scheduled task if running on a server or a scheduled Azure Function or other methods. This script could also be updated to work based on the project publish event using a combination of Flow / Logic App and an Azure Function based on previous examples I have blogged: https://pwmather.wordpress.com/2017/08/01/running-projectonline-powershell-in-azure-using-azurefunctions-ppm-cloud-flow-logicapp-part2/

Whilst the purpose of this script was to enable us to get the data into a SharePoint list and keep the data in sync for our PowerApp, it can be used for other purposes. For example, you could use this example script to modified the last script I published for HTLM fields to update existing items rather than creating new items each run. Do keep in mind that this SharePoint list would not be security trimmed like a Project Center view though, so you might want to restrict access to the SharePoint list depending on your data / security policies for your PPM data.

The script is provided "As is" with no warranties etc.

#ProjectOnline #PowerApps example #Office365 #PPM #PMOT #Apps #MSProject #SharePoint Part2

February 24, 2018 at 10:17 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Mobile | 2 Comments
Tags: , , , , ,

In part 2 of this series we look at continuing with the example PowerApp for Project Online for project details and project navigation. For those of you that missed part 1, a link to the post can be found below:


In part 2 we will update the detail screen to display the data we want and include links to PWA for the associated project. If you are following, open the app you started in part 1:


Using the tree view on the left, click DetailScreen1 to load the detail screen or click the chevron next to a project from the browse screen:


Firstly I updated the screen label to “Project Details” and deleted the delete and edit icons:


Next two buttons were added to the top bar, one for the Site and one for the Tasks. The Site button will launch DataCardValue8 which in my case is the Project Site URL as seen below:


The Tasks button will launch the Project Schedule PDP but concatenating the PWA URL for the schedule PDP (or whatever PDP you want to link to) then using the ProjectID, in my case DataCardValue2, from the detail screen for the projuid URL parameter:


The buttons were added to the screen using the insert menu:


As you can see, there are lots of options on the insert ribbon! Other quick links could easily be added such as Issues, Risks, New Risk etc. That is it for the detail view, we have some project details and two buttons that link out to PWA for the associated project:


Just for fun I also added a new screen in for a chart. I inserted a bar chart and created a project count by progress chart:


For the Items, I entered the following formula to count the number of projects Completed, Not Starter or In Progress:


The formula is below for my data, ProjectData is the name of my SharePoint list and Progress is the column on my SharePoint list that contains the % complete value for each project:

    {Column: "Completed", Value: CountIf(ProjectData, Progress = 100)
    {Column: "Not Started", Value: CountIf(ProjectData, Progress = 0)
    {Column: "In Progress", Value: CountIf(ProjectData, Progress <> 0 && Progress <> 100)

Then set the item colours:


I then added the back arrow to navigate back to the main browse screen:


The final change was to add an icon to the main browse screen that enabled navigation to the chart screen:


To preview your app in PowerApps, press F5:

Main screen: list of projects with search and sort using project name, link for each project to project detail screen, access to chart screen, refresh the data and link to create a new project in Project Online


Details screen: project details with button links to the project site or schedule PDP in Project Online PWA and a back button to the main screen


Chart screen: bar chart display project count by progress for Completed, Not Started and In Progress with a back button to the main screen


Once you are happy with your app you can share it to your organisation. Before you do, access the App Settings view and give it a name, set the icon and description:


Then click Save and save it to your organisations PowerApps environment then share the app:


For details on Saving, publishing and sharing your apps see the links here: https://docs.microsoft.com/en-us/powerapps/save-publish-app & https://docs.microsoft.com/en-us/powerapps/share-app Users can then access your app using the browser, PowerApps Studio or the PowerApps mobile app!

Hopefully that has given you enough to get started or given you some ideas for Project Online PowerApps. There are so many features to PowerApps, they are very powerful! For getting started guides on PowerApps start here: https://docs.microsoft.com/en-us/powerapps/getting-started

Have fun!

Next Page »

Blog at WordPress.com.
Entries and comments feeds.