Update Calculated Column Formula in SharePoint List using PowerShell
Requirement: Update a Calculated Field's formula for a specific column of a list across 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.
PowerShell to Update Calculated Column formula in SharePoint List:
In an another case, we had to replace a field in the calculated column's formula for all sites.
Creating calculated field using PowerShell is in my another article: How to Add Calculated Column to SharePoint List using PowerShell
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 = "http://intranet.crescent.com" $ColumnName="Productivity" #Iterate through each web in 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)" } }Same code can be used for List level formulas as well.
PowerShell to Update Calculated Column formula in SharePoint List:
In an another case, we had to replace a field in the calculated column's formula for all sites.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Variables $SiteURL = "http://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 calculated field using PowerShell is in my another article: How to Add Calculated Column to SharePoint List using PowerShell
No comments:
Please Login and comment to get your questions answered!