SharePoint Online: Add Number Column to List using PowerShell
Requirement: Create Number Column in SharePoint Online List.
How to Add Number Column to SharePoint Online List?
Adding columns is a quick and easy way to extend the functionality of your SharePoint Online lists. The number column in SharePoint Online is used for numerical entry. Further, this field can be configured to present the value as decimal numbers and set minimum-maximum allowed values.
- Browse your SharePoint Online site and navigate to the target list in which you want to add the number column.
- Under the List tab, click on the “Create Column” button in the ribbon.
- Provide the Name to your new column, and specify the type as “Number”
- Scroll down and fill in other optional values such as Required Column, Unique, Minimum and Maximum, Number of decimal places, default value, etc. and click on “OK” to create a number column to the SharePoint Online list.
PowerShell to Create Number Column to List in SharePoint Online
Here is how to create list columns using PowerShell 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"
#Custom function to add column to list
Function Add-NumberColumnToList()
{
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=$false)] [string] $IsRequired = "FALSE",
[Parameter(Mandatory=$false)] [string] $EnforceUniqueValues = "FALSE"
)
#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
{
$FieldSchema = "<Field Type='Number' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' />"
$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="HeadCount"
$DisplayName="Head Count"
$Description="Enter the Number of Team Members in the Project"
#Call the function to add column to list
Add-NumberColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description
To add additional parameters such as Min, Max, Percentage, and Decimal, append below to the Schema XML:
#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"
#Custom function to add column to list
Function Add-NumberColumnToList()
{
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=$false)] [string] $IsRequired = "FALSE",
[Parameter(Mandatory=$false)] [string] $EnforceUniqueValues = "FALSE",
[Parameter(Mandatory=$true)] [string] $Min,
[Parameter(Mandatory=$true)] [string] $Max,
[Parameter(Mandatory=$true)] [string] $Percentage,
[Parameter(Mandatory=$true)] [string] $Decimals
)
#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
{
$FieldSchema = "<Field Type='Number' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' Min='$Min' Max='$Max' Percentage='$Percentage' Decimals='$Decimals' />"
$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="Probability"
$DisplayName="Probability"
$Description="Enter the Probability Percentage"
$Min="0"
$Max="1" #100%
$Percentage="TRUE"
$Decimals="2"
#Call the function to add column to list
Add-NumberColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Min $Min -Max $Max -Percentage $Percentage -Decimals $Decimals
PnP PowerShell to Add Number Column to SharePoint Online List
Adding a column to a SharePoint Online list can be done using PnP PowerShell too.
#Config Variables
$SiteURL = "https://Crescent.sharepoint.com/sites/marketing"
$ListName= "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
#Add Number Field to list
Add-PnPField -Type Number -List $ListName -DisplayName "Head Count" -InternalName "HeadCount"
This will add a new column called “Head Count” to your list. You can also specify other options such as the description, required, default value, and so on. Similarly, We can create a number column from XML schema as well.
#Define XML Schema for Number Field
$FieldXML= "<Field Type='Number' Name='HeadCount' ID='$([GUID]::NewGuid())' DisplayName='Head Count' Required ='FALSE' Min='1' Max='50' ></Field>"
#Add number Field to list from XML
Add-PnPFieldFromXml -FieldXml $FieldXML -List $ListName
Set Percentage=’TRUE’ attribute to mark the field as a percentage!
Nice article!
I would like to know if it’s possible to read a file with the site url and library name, to create a choice column in libraries of multiple sites and display the column in default view. Im struggling with that problem, actually I tried using flow without luck…so I’m looking for a powershell solution. Thank you beforehand!
Nevermind, I followed another of your articles and managed to create the ps1 file. Thanks