Using PnP PowerShell to apply modern column formatting

Clients have asked me about visually improving documents to highlight content that contain sensitive information (for example) and then applying this change to columns in lists in all their groups (roughly 100 of them).

There is detailed documentation at docs.microsoft.com about how to create and apply the JSON formatting to a single column; but not so much about how to update existing columns with the API, so what and how should we get this done?

With PnP PowerShell of course…

If you haven’t used PnP PowerShell before you have to check it out; it’s my go to place for provisioning and applying bulk changes to SharePoint. It takes much of the pain out of writing large amounts of CSOM code or Rest API calls, so you can get, what you have built, into SharePoint without having to write lots of deployment code.

Okay, show me now I hear you say…

For this approach, you will need a few things:

• Column formatting JSON, if you haven’t started writing any or need an example check out the PnP GitHub project for column formatting examples.
• PnP PowerShell modules installed - use this to get started.
• SharePoint Online Client Browser, this is my go to tool if I want to learn how modern functionality applies under the hood.

In this example, I have setup a Communications Site and added a document library, a site column called Demo Sensitivity (an example site column based on UK security classifications) and associated it with the library.

I have tweaked the example JSON from the PnP sample “text-conditional-formatting” - see my alterations on GitHub, I recommend that if you experimenting with column formatting there are some built-in UI tools in SharePoint to help you preview your work.

Next, what you need to do is get a reference to the field and update the CustomFormatter attribute with your column formatting JSON.

# Connect to SharePoint
Connect-PnPOnline -Url 'https://<tenant>.sharepoint.com' -ErrorAction Stop

$field = Get-PnPField -Identity "Demo Sensitivity"

# Update field using internal name
$field | Set-PnPField -UpdateExistingLists `
    -Values @{CustomFormatter= @'
{
  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
  "elmType": "div",
  "attributes": {
    "class": {
        "operator": "?",
        "operands": [
            {
              "operator": "==",
              "operands": [
                {
                  "operator": "toString()",
                  "operands": [
                      "@currentField"
                  ]
                },
                "Official"
              ]
            },
            "sp-field-severity--good",
            {
                "operator": "?",
                "operands": [
                    {
                      "operator": "==",
                      "operands": [
                          {
                            "operator": "toString()",
                            "operands": [
                                "@currentField"
                            ]
                          },
                          "Official-Sensitive"
                      ]
                    },
                    "sp-field-severity--low",
                    {
                        "operator": "?",
                        "operands": [
                            {
                              "operator": "==",
                              "operands": [
                                  {
                                    "operator": "toString()",
                                    "operands": [
                                        "@currentField"
                                    ]
                                  },
                                  "Secret"
                              ]
                            },
                            "sp-field-severity--warning",
                            {
                              "operator": "?",
                              "operands": [
                                {
                                    "operator": "==",
                                    "operands": [
                                        {
                                            "operator": "toString()",
                                            "operands": [
                                                "@currentField"
                                            ]
                                        },
                                        "Top Secret"
                                    ]
                                },
                                "sp-field-severity--severeWarning",
                                "sp-field-severity--blocked"
                              ]
                            }
                        ]
                    }
                ]
            }
        ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      }
        
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}
'@
}

Code sample for setting the CustomFormatter column - also on GitHub

I discovered by using the SharePoint Online Client Browser (which is a great tool for looking at what goes on under the hood) to see which field was altered by the UI.

There you go…

Now you have the building blocks for applying you column formatting across the wide range of lists and groups.

Remember, if you have used site columns or the content type hub there is a field in the UI called “Column Formatting” that you can paste this JSON into which will also propagate to all your lists.

Further reading

• Column formatting documentation | Microsoft Docs
• PnP PowerShell cmdlet reference | Microsoft Docs
• Repository for column formatting examples | GitHub

Enjoy!