New Excel-like Format for Column Formatting

File this under “sometimes it’s useful to be subscribed to too many things”.

Yesterday I noticed in an alert from Github that Chris Kent (@theChrisKent) made a whole bunch of changes to the samples in the SharePoint/sp-dev-list-formatting Github repo. If you’re not familiar with that repo, it contains a whole pile of useful samples showing how you can use Column Formatting in your SharePoint list and library views.

Digging in a bit, I realized he added a whole new type of syntax to the samples. There are now two JSON files per sample. For example:

This sample formats a column using colors and icons based on various values.

It looks like Lincoln Demaris stealthily went into the main Column Formatting article – Use column formatting to customize SharePoint – a few weeks ago and added the Excel-like formatting to the examples. That guy is always doing good things for us.

Chris tells me that the old way of writing things is called Abstract Syntax Tree (AST). Apparently that’s the catchy name of the operator/operands syntax, which I find few people really understand. 

The new formatting is much more Excel-like. Let’s look at the two examples above. Here’s the AST approach:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": {
      "operator": "+",
      "operands": [
        {
          "operator": "?",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "@currentField",
                "Done"
              ]
            },
            "sp-field-severity--good",
            {
              "operator": "?",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    "@currentField",
                    "In progress"
                  ]
                },
                "sp-field-severity--low",
                {
                  "operator": "?",
                  "operands": [
                    {
                      "operator": "==",
                      "operands": [
                        "@currentField",
                        "In review"
                      ]
                    },
                    "sp-field-severity--warning",
                    {
                      "operator": "?",
                      "operands": [
                        {
                          "operator": "==",
                          "operands": [
                            "@currentField",
                            "Has issues"
                          ]
                        },
                        "sp-field-severity--severeWarning",
                        "sp-field-severity--blocked"
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        },
        " ms-fontColor-neutralSecondary"
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": {
          "operator": "?",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "@currentField",
                "Done"
              ]
            },
            "CheckMark",
            {
              "operator": "?",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    "@currentField",
                    "In progress"
                  ]
                },
                "Forward",
                {
                  "operator": "?",
                  "operands": [
                    {
                      "operator": "==",
                      "operands": [
                        "@currentField",
                        "In review"
                      ]
                    },
                    "Error",
                    {
                      "operator": "?",
                      "operands": [
                        {
                          "operator": "==",
                          "operands": [
                            "@currentField",
                            "Has issues"
                          ]
                        },
                        "Warning",
                        "ErrorBadge"
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

[scroll, scroll, scroll…] Boy, that’s long!

But take a look at the new Excel-like approach:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "=if(@currentField == 'Done', 'sp-field-severity--good', if(@currentField == 'In progress', 'sp-field-severity--low', if(@currentField == 'In review', 'sp-field-severity--warning', if(@currentField == 'Has issues', 'sp-field-severity--severeWarning', 'sp-field-severity--blocked')))) + ' ms-fontColor-neutralSecondary'"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField == 'Done', 'CheckMark', if(@currentField == 'In progress', 'Forward', if(@currentField == 'In review', 'Error', if(@currentField == 'Has issues', 'Warning', 'ErrorBadge'))))"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

Here we get to use some pretty intelligible if/then/else logic in our JSON instead. I don’t know about you, but it makes a lot more sense to me! Plus, it’s a much more compact approach.

I think one of the main reasons this sample repo has been so popular is it takes a Master degree in writing code in JSON to use the AST approach. The new Excel-like method ought to open things up to a much wider group of citizen developers and power users.

Chris also told me that unfortunately, although Column Formatting will be in SharePoint 2019, Excel-style expressions won’t be. That’s why there are two versions of each sample where it was useful to do the conversation now. (Some samples didn’t really benefit from the new approach).

Similar Posts

One Comment

  1. Hi Marc,

    Are you aware of any option to format the column on multiple choice field that every selected option would be displayed in new row (maybe bullet option) ?

Leave a 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.