Update Calculated Column Formula in SharePoint List using PowerShell
Requirement: Update a Calculated Field’s formula for a specific column of a list across the entire site collection.
PowerShell to update calculated field formula in SharePoint:
This updates the site column’s formula for all subsites in the site collection.
Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
#Variables
$SiteURL = "https://intranet.crescent.com"
$ColumnName="Productivity"
#Iterate through each web in the given site collection
$Webs = Get-SPSite $SiteURL | Get-SPWeb -Limit All | ForEach-Object {
#Check if the column exists
if($_.Fields.ContainsField($ColumnName))
{
$column = $web.Fields[$ColumnName]
#Update calculated field formula
$Column.Formula = "=(([PlannedEffort]/5)/ActualEffort)*8"
$Column.update($true)
write-host "Updated Calculated Field formula at $($_.URL)"
}
}
The same code can be used for List level formulas as well.
PowerShell to Update Calculated Column formula in SharePoint List:
We had to replace a field in the calculated column’s formula for all sites in another case.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Variables
$SiteURL = "https://intranet.crescent.com"
$ListName="Projects"
$ColumnName="Productivity"
$OldField="PlannedEffort"
$NewField="EstimatedHours"
#Iterate through each web in given site collection
$Webs = Get-SPSite $SiteURL | Get-SPWeb -Limit All | ForEach-Object {
#Get the List
$List = $_.Lists.TryGetList($ListName)
If($List)
{
#Check if the column exists
if($List.Fields.ContainsField($ColumnName))
{
$Column = $List.Fields[$ColumnName]
#Update calculated field formula
$Column.Formula = $Column.Formula.Replace($OldField,$NewField)
$Column.update($true)
write-host "Updated Calculated Field formula at $($_.URL)"
}
}
}
Creating a calculated field using PowerShell is explained in another article: How to Add Calculated Column to SharePoint List using PowerShell?
