SharePoint Online: Update Calculated Field Formula using PowerShell

How to Update the Calculated Column Formula in SharePoint Online?

Calculated columns in SharePoint allow you to perform calculations on other columns within a list or library. They can be used to automatically calculate values based on data entered in other columns or perform more complex calculations involving multiple columns. SharePoint supports a wide range of mathematical and logical functions that can be used in calculated field formulas, including basic arithmetic functions like addition and subtraction and more advanced functions like IF statements and conditional formatting. There may be times when users need to update the formula used in a calculated column to correct errors or accommodate changing business requirements.

Updating the formula used in a calculated column in SharePoint Online can be done using the SharePoint web user interface. Here are the steps to update the calculated column formula in SharePoint Online:

1. Navigate to the list that contains the calculated column you want to update. Click on the gear icon in the top-right corner of the page, and select “List settings” from the drop-down menu.
2. Scroll down to the “Columns” section of the list settings page, and click on the name of the calculated column you want to update. This will take you to the column settings page for that column.
3. In the “Column settings” section, find the “Formula” field and update the formula as needed. You can use the formula editor to enter your new formula or manually enter the formula in the field.

   

4. Save the changes. Once you have updated the formula, click on the “OK” button at the bottom of the column settings page to save the changes.
5. Finally, you should verify that the updated formula has been applied correctly. You can do this by navigating to the list and checking that the calculated column shows the correct values based on the updated formula.

Update Calculated Column Formula using PowerShell

Manually updating the formula for each calculated column in a list can be a time-consuming and error-prone process, especially for lists with a large number of columns. So, let’s see how to use PowerShell to update the formula used in a calculated column in SharePoint Online.

#Load SharePoint CSOM Assemblies
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.SharePoint.Client.Runtime.dll"

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName ="Projects"
$ColumnName="Productivity"
$ColumnFormula = "=(([Planned Efforts]/5)/[Actual Efforts])*8"

Try {
    #Get Credentials to connect
    $Cred= Get-Credential    

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
        
    #Get the List
    $List = $Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Get the column to Update
    $Field = $List.Fields.GetByInternalNameOrTitle($ColumnName)
    #Cast the field
    $CalculatedField = New-Object Microsoft.SharePoint.Client.FieldCalculated($Ctx,$Field.Path);
    $calculatedField.Formula=$ColumnFormula
    $calculatedField.Update()
    $Ctx.ExecuteQuery()
            
    Write-Host "Calculated Column Formula has been updated successfully!" -ForegroundColor Green

}
Catch {
    write-host -f Red "Error Updating Column!" $_.Exception.Message
}

PnP PowerShell to Set the Formula for Calculated Fields

Here is the PnP PowerShell way of updating the calculated field formula using the Set-PnPField cmdlet:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName ="Projects"
$ColumnName="Productivity"
$ColumnFormula = "=(([Planned Efforts]/5)/[Actual Efforts])*8"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Set the calculated column Formula
Set-PnPField -List $ListName -Identity $ColumnName -Values @{Formula=$ColumnFormula}

In conclusion, updating the formula used in a calculated column in SharePoint Online can be done using PowerShell, which can save you time and effort and ensure consistency across all columns in a list. By following these steps, you can update the formula for a calculated column and verify that the change has been applied correctly.