SharePoint Online: Add Currency Column to List using PowerShell
Requirement: Add Currency Column to SharePoint Online List.
How to Create Currency Field in SharePoint Online List?
Similar to the number field, The Currency column in SharePoint Online captures currency values as metadata and adds currency formatting options.
- Browse to your SharePoint Online site and navigate to the target list where you want to add the Currency column.
- Under the List tab, click on the “Create Column” button in the ribbon.
- Provide the Name of your new column, and specify the type as “Currency”.
- Scroll down, fill in other optional values such as Minimum and Maximum values, decimal places, Currency format, etc., and click “OK” to create a Currency field in the SharePoint Online list.
PowerShell to Add Currency 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-CurrencyColumnToList()
{
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='Currency' 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="TotalInvestment"
$DisplayName="Total Investment"
$Description="Enter the Total Investment"
#Call the function to add column to list
Add-CurrencyColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description
To support additional parameters, such as Minimum & Maximum values, Decimal places, and Currency format, You can add them to the parameters and the field schema. Here is the script:
#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-CurrencyColumnToList()
{
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] $Decimals,
[Parameter(Mandatory=$true)] [string] $LCID
)
#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='Currency' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' Min='$Min' Max='$Max' Decimals='$Decimals' LCID='$LCID' />"
$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="TotalInvestment"
$DisplayName="Total Investment"
$Description="Enter the Total Investment"
$Min="1"
$Max="100000"
$Decimals="2"
$LCID="1081" #India
#Call the function to add column to list
Add-CurrencyColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Min $Min -Max $Max -Decimals $Decimals -LCID $LCID
PnP PowerShell to Add Currency Column to SharePoint Online List
Here is the PnP PowerShell to add a currency field to the SharePoint Online list. Replace the variables with the appropriate values for your SharePoint Online site, list name, and currency field properties.
#Config Variables
$SiteURL = "https://Crescent.sharepoint.com/sites/marketing"
$ListName= "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Add currency Field to list
Add-PnPField -Type Currency -List $ListName -DisplayName "Total Budget" -InternalName "TotalBudget" -AddToDefaultView
Similarly, you can add a currency field from the XML schema:
#Define XML Schema for currency Field
$FieldXML= "<Field Type='Currency' Name='TotalBudget' ID='$([GUID]::NewGuid())' DisplayName='Total Budget' Required ='FALSE' Decimals='2' LCID='1033'></Field>"
#Add Currency Field to list from XML
Add-PnPFieldFromXml -FieldXml $FieldXML -List $ListName
Adding a currency field to a SharePoint Online list is a simple and effective way to store and manage financial data. With this step-by-step guide, you can easily add a currency column to your list and configure its settings to meet your specific requirements.
