Following on from part 1 where I introduced the idea of automating certain Microsoft 365 PPM Project Online customisations using PowerShell, Microsoft Flow / Azure Logic Apps and Azure Functions, in part 2 I will set up an example automation for creating a Project Online event driven snapshot application on project published without having to set up any server or write any complied code! If you missed part 1 where this concept was introduced, see the link below:
Firstly I created an Azure Function app in my Azure subscription then created a new function based on the HttpTrigger – PowerShell template:
Give the function a name and set the Authorisation level – set the authorisation level to the correct setting for your function. Click Create. For details on Azure Functions, start here: https://docs.microsoft.com/en-us/azure/azure-functions/
You will then be presented with the function and sample code:
We will now create the PowerShell script to create the snapshot. This is based on a script I published a while back: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/
The script has been updated to work in an Azure Function but also modified to use a parameter so that it only captures data for the published project, the PowerShell script can be seen further on in the post.
Firstly upload the SharePoint CSOM DLLs using the upload button:
I used the SharePoint CSOM DLLs from the SharePoint Online Management Shell:
Then enter the PowerShell code – screen shots below and code pasted below the images:
Code sample used in function:
# POST method: $req
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
$projID = $requestBody.projID
# GET method: each querystring parameter is its own variable
if ($req_query_name)
{
$projID = $req_query_name
}
#add SharePoint Online DLL - update the location if required
Import-Module "D:\home\site\wwwroot\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.dll"
Import-Module "D:\home\site\wwwroot\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.Runtime.dll"
#set the environment details
$PWAInstanceURL = "https://mod497254.sharepoint.com/sites/PWA2"
$username = "admin@MOD497254.onmicrosoft.com"
$password = "password"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force
#create the SharePoint list on the PWA site and add the correct columns based on the data required
$listname = "ProjectSnapShots"
$results1 = @()
#set the Odata URL with the correct project fields needed
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectId eq GUID'$projID'&`$Select=ProjectId,ProjectName,ProjectPercentCompleted"
#get all of the data from the OData URL
while ($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
$results1 += $results.d.results
if ($results.d.__next){
$url=$results.d.__next.ToString()
}
else {
$url=$null
}
}
#add data to snapshot list
#get PWA site client context
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($PWAInstanceURL)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass)
$ctx.Credentials = $credentials
$ctx.ExecuteQuery()
#get the target list
$List = $ctx.Web.Lists.GetByTitle($listname)
$ctx.Load($List)
$ctx.ExecuteQuery()
#for each project, create the list item - update the newitem with the correct list columns and project data
foreach ($projectrow in $results1)
{
$itemcreationInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newitem = $List.AddItem($itemcreationInfo)
$newitem["Title"] = $projectrow.ProjectName
$newitem["ProjectId"] = $projectrow.ProjectId
$newitem["PercentCompleted"] = $projectrow.ProjectPercentCompleted
$newitem.Update()
$ctx.ExecuteQuery()
}
The PowerShell code would need to be updated with your environment details: (PWAInstanceUrl, username, password and listname variables). Also the OData URL will need to be updated to include the project level fields that you want to snapshot.The target SharePoint list will also need to be set up in the PWA site collection for the project fields the script uses. This is the list I set up for this example:
SnapshotDate is set to Todays date so we don’t need to set that in the code.
The code is simple to follow but in summary the first part will get the projID from request body – we will pass in the ProjectID for the published project from the Flow / Logic App trigger. Then the SharePoint Online CSOM DLLs are imported in. Then the specific PWA environment details are set for the variables. The OData URL is then added to the url variable. Here notice we are filtering for the ProjectID and passing in the $projID variable we get from the request body. The Select part of the query will need to be updated for your project level fields. Next the code gets the data from the OData feed using the web request and adds the data into the results array. Once we have the data, we connect to the SharePoint list, in the example it is the ProjectSnapShots as set in the $listname variable. Lastly the new item is created in the list using the data from the results array.
Now the Azure Function is ready to be used. It can be tested using the Test option in the right hand panel, update the Request body:
Update it for a valid project ID. Then click Run above the function code:
The Logs window below will help you debug any errors etc.:
Then check the SharePoint list in the PWA site and the new item should have been created:
We now know the Azure Function is working as expected, now we need to call the Azure function when a project is published. All we need from the Azure Function is the URL to use, use the </>Get function URL button:
Select the correct Key, in this example I used the default function key. Copy the URL as it will be needed later.
To call the Azure Function when a project is published, the choice here for a no code option would be Microsoft Flow or Azure Logic App. For this I will use Microsoft Flow but the same steps (triggers , actions etc.) would be used in the Azure Logic App. Create a new Flow and search for Project Online:
Then select the Project Online – When a project is published trigger.
Enter the PWA URL:
Then click the ellipsis and set the connection for the PWA URL or create a new connection if needed:
Click + New step then Add an Action and search Http:
Select HTTP – HTTP:
Complete the HTTP action:
Method is POST, the Uri is the URL for the function that we copied earlier, Headers are not required. The Body is where we pass in the project ID from the published project trigger:
The Flow is now completed:
Now click Save flow.
In PWA, Publish a project or projects and see the snapshot data created on the configured snapshot list once the Flow has run:
Flow run:
Data added to the list for the project I published – in this example it was the Office 2016 rollout project:
This just shows a simple example and the some of the possibilities for extending the Project Online capability when making use of simple PowerShell scripts and other Microsoft 365 / Azure services for cloud / serverless solutions. Look out for more examples in the future.
Modern Work Management - Project and the Power Platform blog 