· 3 min read

Using PnP PowerShell to apply modern column formatting

Learn to use the latest list formatting capabilities and apply them to existing sites with PnP PowerShell

Learn to use the latest list formatting capabilities and apply them to existing sites with PnP PowerShell

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:

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.

Before applying formatting

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.

Formatting UI preview tools - built into SharePoint

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

screenshot of the JSON to apply to a column

# 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.

SharePoint Client Browser

There you go…

After running the script

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

Enjoy!

Back to Blog