#ProjectOnline #PowerShell to keep #PPM data in sync on #SharePoint list #PMOT #O365
   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 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.
SharePoint (and Project Server) Shenanigans 