SharePoint Online: Apply Column Format using PowerShell
Requirement: Format Column in SharePoint Online using PowerShell.
How to Apply Conditional Formatting in SharePoint Online Column using PowerShell?
Column formatting in SharePoint Online allows users to customize the appearance of columns in a list or library. Formatting columns in SharePoint Online is explained in my other post: Format Column in SharePoint Online. While applying column formatting can be done manually, PowerShell can automate the process. In this guide, you will learn how to apply column formatting in SharePoint Online using PowerShell.
Here is the PowerShell CSOM script to set column format 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"
#Set Variables
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName="Config"
$FieldName="Status" #Internal Name
$JsonFormat = @"
{
"`$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(@currentField == 'Completed', 'sp-field-severity--good', if(@currentField == 'In Progress', 'sp-field-severity--low', if(@currentField == 'On-Hold','sp-field-severity--warning', if(@currentField == 'Not Started','sp-field-severity--blocked', ''))))"
}
}
"@
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List
$List= $Ctx.Web.Lists.GetByTitle($ListName)
#Get the Field
$Field=$List.Fields.GetByInternalNameOrTitle($FieldName)
$Ctx.Load($Field)
$Ctx.ExecuteQuery()
#Apply Column Formatting to the field
$Field.CustomFormatter= $JsonFormat
$Field.Update()
$Ctx.ExecuteQuery()
PnP PowerShell to Set Column Format in SharePoint Online
We can also apply JSON column formatting using PnP PowerShell as:
#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName="Config"
$FieldName="Status" #Internal Name
$JsonFormat = @"
{
"`$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(@currentField == 'Completed', 'sp-field-severity--good', if(@currentField == 'In Progress', 'sp-field-severity--low', if(@currentField == 'On-Hold','sp-field-severity--warning', if(@currentField == 'Not Started','sp-field-severity--blocked', ''))))"
}
}
"@
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get the Field
$Field = Get-PnPField -Identity $FieldName -List $ListName
#Set Custom Format
$Field | Set-PnPField -Values @{CustomFormatter = $JsonFormat}
Here is the Result:
In summary, By using PowerShell to apply column formatting in SharePoint Online, you can save time and effort compared to manual methods. The PowerShell script makes it easy to customize the appearance of columns in SharePoint lists and libraries. Whether you want to add color coding, change the font size, or apply conditional formatting, PowerShell can help you achieve your goals.
Is possible apply to View format too?
Sure! Here you go: Set List View Format in SharePoint Online using PowerShell
The SPO version works a treat – thank you!