SharePoint Online: Add Calculated Column to List using PowerShell

Requirement: Add Calculated Column to SharePoint Online List

How to Add Calculated Column to SharePoint Online List?

Calculated columns in SharePoint Online are used to manipulate other columns in the list item. Instead of being entered by a user, these field values are automatically calculated based on the formula. Calculated fields will not appear on the New Form or Edit forms but are visible on View Forms and SharePoint views. To add a calculated column to the SharePoint Online list, do the following:

  1. Browse to your SharePoint Online site and navigate to the target list in which you want to create a calculated column.
  2. Under the List tab, click on the “Create Column” button in the ribbon.
  3. Provide the Name to your new column, specify the type as “Calculated (calculation based on other columns)”
    Add calculated Column to SharePoint Online List using PowerShell
  4. Enter the Formula for your calculated column in the “Formula” field and specify the return type of the data. 
  5. Scroll down and set other parameters such as decimal places and format of the data and click on “OK” to create a calculated field in the SharePoint Online list.

PowerShell to Create Calculated Column to List in SharePoint Online:

You can use SharePoint Online PowerShell to add a calculated column to a list:

#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"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

#Custom function to add column to list
Function Add-CalculatedColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description=[string]::Empty,
        [Parameter(Mandatory=$true)] [string] $FieldsReferenced,
        [Parameter(Mandatory=$true)] [string] $Formula,
        [Parameter(Mandatory=$true)] [string] $ResultType
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

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

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Frame FieldRef Field
            $FieldRefXML=[string]::Empty
            $FieldRefs = $FieldsReferenced.Split(",")
            foreach ($Ref in $FieldRefs)
            {
                $FieldRefXML = $FieldRefXML + "<FieldRef Name='$Ref' />"
            }

            #Create Column in the list
            $FieldSchema = "<Field Type='Calculated' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' ResultType='$ResultType' ReadOnly='TRUE'><Formula>$Formula</Formula><FieldRefs>$FieldRefXML</FieldRefs></Field>"
            $FieldSchema
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    
            
            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="TotalInvestmentBuffer"
$DisplayName="Total Investment Buffer"
$Description="5 Percentage in Total Investment"
$Formula = "=([Total Investment]*5)/100"
$ResultType="Currency" 
$FieldsReferenced="[Total Investment]" #Fields Names participating in Formula. Should be Comma (,) Separated

#Call the function to add column to list
Add-CalculatedColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Formula $Formula -ResultType $ResultType -FieldsReferenced $FieldsReferenced

PnP PowerShell to Add a Calculated Column to List in SharePoint Online

Here is how to add a calculated column to a list using PnP PowerShell:

#Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/pmo"
$ListName= "Projects"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Add a calculated column to list
Add-PnPField -List $ListName -Type Calculated -InternalName "Buffer" -DisplayName "Buffer" -Formula "=[Budget]*0.10"

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

One thought on “SharePoint Online: Add Calculated Column to List using PowerShell

  • i have a date column in the sharepoint list. i wanted to generate 30 business days from this date to another column. I am not sure how to use a loop to exclude weekends. Can someone help?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *