Home > Paul Mather, Work > Update #ProjectOnline Project custom field values using #PowerShell with data from a CSV file #PPM #ProjectServer #CSOM

Update #ProjectOnline Project custom field values using #PowerShell with data from a CSV file #PPM #ProjectServer #CSOM

Paul Mather
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)

This blog post will demonstrate a simple example of how Microsoft’s PPM tool, Project Online, can include data from an external system. This is a very simple example using a demo CSV mock up of data that could be from an external system. The CSV part could easily be replaced by a SQL query or a REST API query to the source system etc.

Example CSV data used can be seen below:

image

As you can see, my imagination for example project names has not improved! The code uses the Project Name as the key between the two data sets – the CSV file and PWA so the names will need to match.

This script example can be downloaded here: http://bit.ly/2edq0Ii

The user setting up the script will need to update the correct location to the DLLs required, the PWA instance URL, username, password, the custom field internal name and the location of the CSV file. The account specified will need edit access to all of the projects in PWA. Ensure the variables have been updated correctly, placeholder values seen below:

image

image

The DLL’s used are from the NuGet package here: http://bit.ly/2eMprBu. There are later version available, check here: http://bit.ly/1SO8x4X. Download those as required.

The get the correct custom field internal ID I use the REST API, <PWA Site URL> + /_api/ProjectServer/CustomFields. Find the project level custom field and copy the InternalName as seen below:

image

Once the PowerShell script has been updated, save it and it can then be tested on a test / non-production environment. Once executed in PowerShell, each project found in the CSV file will be outputted as seen in the example below. In this example two projects exist in the CSV data but not in my example Project Online PWA instance (remember for this example, the names need to match in the CSV file and PWA):

image

Checking in the Project Center after the script has run you can see for the projects that exist in the CSV file, the project budget includes the values found in the CSV file:

image

This PowerShell script could be run on a schedule from a server on-prem or even in Azure. The script would just need access to the data to import (CSV file etc.) and the DLLs. Fully test this script on a test / non-production PWA instance before running on a Production PWA instance.

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

Advertisements
Categories: Paul Mather, Work Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: