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:
- Browse to your SharePoint Online site and navigate to the target list in which you want to create a calculated column.
- Under the List tab, click on the “Create Column” button in the ribbon.
- Provide the Name to your new column, specify the type as “Calculated (calculation based on other columns)”.
- Enter the Formula for your calculated column in the “Formula” field and specify the return type of the data.
- Scroll down, set parameters such as decimal places and data format, and click “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"
Can we filter Calculated column in SharePoint online List
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?