Bulk update resource level custom field in Project Server

There are plenty of blogs available about the same, I am writing it again because I found most of the code is not working else throwing the error, here I have tested these code with Project Server online and Project Server on-prem version, and it works like charm.

This is a very common scenario where every project server developer needed once a time in his career.

So this is the solution, just need to replace site URL, username, and password and custom field which you wanted to update.

<# Below is CSV sample which I am using for this code. First Column is headers. #>;
<#
ResourceName,ResourceEmailAddress,EmpRate
Kuldeep, kuldeep.verma@xyz.com, 10000
Rajdeep, rajdeep.sardesai@xyz.com, 20000
Jaydeep, Jaydeep.prajapat@xyz.com, 40000
#>

#Download and install Microsoft Client Component from https://www.microsoft.com/en-in/download/details.aspx?id=42038
#Microsoft SharePoint Client Component is mandatory to move further.
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.ProjectServer.Client.dll"

#replace CSV path
$csvpath="C:\FakePath\FileName.csv"
$siteURL = "{Site URL}"
$loginname = "{UserName}"
$pwd = "{Password}"
$securePassword = ConvertTo-SecureString $pwd -AsPlainText -Force
$creds = New-Object System.Management.Automation.PsCredential $loginname,$securePassword
$projContext = New-Object Microsoft.ProjectServer.Client.ProjectContext($siteURL)
$projContext.Credentials = $creds
$customfields = $projContext.CustomFields
$projContext.Load($customfields)
$projContext.ExecuteQuery()

And if you don’t know the Internal Name of the field then simply use the below script after running the above code.

#get custom field by anem
$customfield = $customfields | select InternalName, Name | WHERE {$_.Name -eq "&lt;Field display name&gt;"}

If you wanted to list down all custom fields then you can simply use the below piece of code.

#get all custom field
$customfields | select InternalName, Name, IsRequired

Below I am comparing the resource email and updating the custom field, you can use the Resource Name also for comparison.

Import-Csv $csvpath | ForEach-Object {
Try {
Write-Host "Updateding Resource $($_.ResourceName)..." -ForegroundColor Green
$resourceEmail = $_.ResourceEmailAddress
$resource = $projContext.EnterpriseResources | select Id, Name, Email, UserPrincipalName | where { $_.Email -eq $resourceEmail }
if ($resource -ne $null) {
$res = $projContext.EnterpriseResources.GetByGuid($resource.Id)
#update $_.Value with your field name of CSV and Change the Internal field name
$res["{Internal Field Name}"] = $_.EmpRate
$projContext.EnterpriseResources.Update()
$projContext.ExecuteQuery()
Write-Host "Updated Resource $($_.ResourceName) successfully!" -ForegroundColor Green
}
else {
Write-Host "Resource $($_.ResourceName) not found!" -ForegroundColor DarkCyan
}
}
catch {
$_ | select -expandproperty invocationinfo
Write-Host "Error occurred while updating $($_.ResourceName)..." -ForegroundColor Red
write-host "$($_.Exception.Message)" -foregroundcolor DarkRed
}
}
Write-Host "Script executed successfully" -ForegroundColor Yellow

Download complete source code from GitHub

2 thoughts on “Bulk update resource level custom field in Project Server

  1. I’m trying to adapt this code to update the Standard Rate for all resources from a csv file I have with their usernames and rates, but need some help.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s