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

One thought 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.

    Liked by 1 person

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 )

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