Column Formatting in SharePoint Online Rolling Out!

Every once in a while, something comes to Office 365 where no one has anything negative to say. (Is that a back-handed complement?)

The fact that this tweet got 22 retweets and 34 likes (as of this writing) and lots of eyeballs means this is one of those things.

Over the years we’ve had several ways to change the look of a column in a list view. Early on – say in the SharePoint 2007 and 2010 days – we often used jQuery in the page to do what we needed. This was WAY before it was fashionable to write client side code for SharePoint. It worked just fine, but had pretty decent barriers to entry. You had to know how to write JavaScript, get it into the page, use CSS3-like selectors in jQuery, etc.

In SharePoint 2013, we got something called jsLink, which was part of what most people refer to as Client Side Rendering, or CSR. This was a pretty good mechanism, and also used JavaScript, but with some fairly arcane functions and structure. However, it allowed us to customize both individual fields and entire rows.

Announced at Ignite, we now are getting a new capability to format columns in “modern” list views. It doesn’t seem to have a snazzy name other than “Column Formatting“, but that doesn’t matter.

There’s a great page with documentation and examples which we can tweak to get what we need: Use column formatting to customize SharePoint. This new mechanisms use what’s called declarative customization, meaning we don’t actually write code – though it may feel like code to many of you. Rather than code, we write JSON, which stands for JavaScript Object Notation. JSON is basically a way to store data.

Let’s look at one of the examples from that page.

Say we want to color a column’s value if that value is less than 70. (The text on the page says color it red, but the little image is yellow, so I’m not sure they have it right at the moment – especially since the number is exactly 70.)

In any case, the JSON data below says the following:

If @currentField < 70 Then add the CSS class = sp-field-severity–warning.

which gives us something like this (with the above caveat):

{
   "elmType": "div",
   "txtContent": "@currentField",
   "attributes": {
      "class": {
         "operator": "?",
         "operands": [
            {
               "operator": "<",
               "operands": [
                  "@currentField",
                  70
               ]
            },
            "sp-field-severity--warning",
            ""
         ]
      }
   }
}

While the techies will tell you this isn’t code, it really sort of is. The condition we want to apply is stored as JSON – which is data – but tells SharePoint how to render the field. The fact that we use operators, operands, etc. to make this happen means that we are effectively writing code. That said, I expect we will see a great number of “recipes” arise out in the community, just like we did with the great SPCSR repo in Github. In fact, I challenge all of you to start sharing your recipes as soon as you start using this feature!

As soon as we can all get our hands on this new capability (watch for it in First Release by the end of October), I expect to start adding examples to a new Github repo I just created: SPColumnFormatting. There is a new repo on Github managed by the SharePoint team called /sp-dev-column-formatting. It already contains the samples from the Column Formatting documentation, and expect it to grow.

If you don’t understand Github, don’t fret. You’ll be able to just copy the recipes and alter them to meet your needs. If you do understand Github and would like to contribute, please do with a pull request. The faster we build up a set of recipes people can use, the more productive the citizen developer army out there will be!

Similar Posts

12 Comments

  1. Thanks for trying to get a recipe book started, I’m sure that it will be helpful.

    However, given that MS has already created a tool in Power Apps to create Rules in the UI, https://powerapps.microsoft.com/en-us/tutorials/working-with-rules/ I’m disappointed that they did not announce something similar for SPO. Hopefully, a point-click UI tool for end users will be released soon. Sharing and tweaking the same JSON file with multiple users could quickly become painful in a large organization.

    1. @Dean:

      Absolutely agree. I look at this release as the first step in a larger set of capabilities. One would hope they reuse good UIs from across their products, like PowerApps or Excel.

      Be sure to add ideas for improvement into the SharePoint UserVoice. At a minimum, I expect we’ll see a front end UI to edit the JSON. I’d also like to see a capability to build a central store of formats we could reuse across our organization, sort of like using the Content Link in CEWPs.

      M.

  2. I’m going to disagree, this paradigm of nested orperator/operands attributes to the JSON object seems absurd. Saying that it is not code is false; as the only difference is that the variable definition at the start was excluded (ex: var format = {JSON} ). A simple IF/ELSE or SWITCH would reduce the complexity significantly. Having to bracket 5 levels deep just to add a class is excessive, and I would expect the likelyhood for a syntax error is much higher.

  3. Is there a list of fields that are in the data object? I am trying to add an email link and I want to put the items name in the text, but [$Name] is not there, neither are any of the other core fields (Created, Modified etc)

  4. Hi Marc,
    I use JSON to add a link and I’ve realised that the format of a calculated column (the data type returned is date and time – date only) changes when I use JSON.
    Before was “dd/MM/yyyy” and now is “yyyy-MM-ddT00:00:00Z”.
    If I delete the JSON formatting the date is set correctlly again.

  5. I’m trying to do some conditional formatting and getting no results. I have two date Columns, Planned Delivery Date and Actual Delivery Date. When the Actual Delivery Date is after the Planned Delivery Date, I want the font of Actual Delivery Date to turn red. Can anyone help?

  6. Hi Mark,

    I’ve used the below code to format a calculated date time column:
    {
    “elmType”: “span”,
    “txtContent”: {
    “operator”: “toLocaleDateString()”,
    “operands”: [
    “@currentField”
    ]
    }
    }
    It worked just fine few months ago, but know the column display Invalid Date.
    Any idea?

    Thank you,
    Paula Crai

Leave a Reply to Marc D Anderson Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.