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 the PnP PowerShell cmdlet Set-PnPField, 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.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

3 thoughts on “SharePoint Online: Apply Column Format using PowerShell

Leave a Reply

Your email address will not be published. Required fields are marked *