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?
Number column in SharePoint Online is used for numerical entry. Further, this field can be configured to present the value as decimal numbers, set minimum-maximum allowed values.
  • Browse to your SharePoint Online site and Navigate to the target list in which you want to add Number column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the type as "Number " 
    add number column to sharepoint online list using powershell
  • Scroll down and Fill other optional values such as Required Column, Unique, Minimum and Maximum, Number of decimal places, default value, etc. and Click on "OK" to create number column to SharePoint Online list.

PowerShell to Create Number Column to List 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="",
        [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, 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="",
        [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:
#Config Variables
$SiteURL = "https://crescenttech.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"
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 percentage!
SharePoint Online: Add Number Column to List using PowerShell SharePoint Online: Add Number Column to List using PowerShell Reviewed by Salaudeen Rajack on August 26, 2016 Rating: 5

2 comments:

  1. 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!

    ReplyDelete
    Replies
    1. Nevermind, I followed another of your articles and managed to create the ps1 file. Thanks

      Delete

Please Login and comment to get your questions answered!

Powered by Blogger.