kwizcom banner advertisement

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.
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)"
        }
    }
} 
PowerShell to Update Calculated Column Formula in SharePoint

Creating calculated field using PowerShell is in my another article: How to Add Calculated Column to SharePoint List using PowerShell
Update Calculated Column Formula in SharePoint List using PowerShell Update Calculated Column Formula in SharePoint List using PowerShell Reviewed by Salaudeen Rajack on 7:38 PM Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.