Using Excel style expressions in SharePoint Custom Column Formatters

Those that have read articles on my blog in the past know that I’ve been a massive evangelist for the use of JSLink for list and library customisations in the past. Judging by the traffic we still see to the SPCSR Display Templates GitHub Repo, JSLink still remains a perfectly valid way to create custom list views, but it’s biggest problem is it’s inability to be used in the Modern page experience. Microsoft accepted very early on that something had to be brought in that replaced JSLink and provided the ability for local customisations to be applied to SharePoint list and library views. They did this by introducing JSON Custom Column formatters.

For me the biggest problem with these has been the bulkiness of the ‘code’ that you have to write to do a simple comparison. Let’s take a simple example of a priority field with just two values, “High” and “Low”. In JSLink if we wanted to change the colour of the background of the field to Red or Green depending on the value, we could use a reasonably simple JavaScript if statement during the tag construction for the class parameter:

if (ctx.CurrentItem.Priority == 'High')
{itemHTML += "backgroundRed'>"} else {itemHTML += "backgroundGreen'>"}

With the first release of Custom Formatting for Modern, this statement became more challenging and certainly much more difficult to follow:

{
    "class": {
        "operator":"?",
        "operands": [
            {
                "operator":"==",
                "operands":[
                    "@currentField",
                    "High"
                ]
            },
            "backgroundRed",
            "backgroundGreen"
        ]
    }
}

And this was for a simple 2 parameter comparison. If we added medium into the mix, we then had to nest the comparison objects another level:

{
    "class": {
        "operator":"?",
        "operands": [
            {
                "operator":"==",
                "operands":[
                    "@currentField",
                    "High"
                ]
            },
            "backgroundRed",
            {
                "operator":"?",
                "operands":[
                    {
                        "operator":"==",
                        "operands":[
                            "@currentField",
                            "Medium"
                        ]
                    },
                    "backgroundAmber",
                    "backgroundGreen"
                ]
            }
        ]
    }
}

As you can see, complex scenarios rapidly meant that the JSON formatting started running to many lines in length and became rather unwieldy to create. This led to people like fellow MVP Chris Kent to release the Column Formatter solution that works as a web part on a modern page and assists in creating the JSON formats. He’s very kindly made this available as part of the Patterns and Practices and I certainly recommend that you take a look as it’s a great way to start understanding some of the column formatting options. In parallel to this, Microsoft also worked on improving the custom formatting engine with a focus on improving the readability of code such as that shown above. In July 2018, they released an update to JSON Column Formatting that allows the use of Excel style Expressions to simplify formatting code.

If we take our example above for Low, Medium and High, our more simplified code becomes:

"class": "=if(@currentField = 'High', 'backgroundRed',if(@currentField = 'Medium','backgroundAmber','backgroundGreen'))"

 

That’s a much simpler statement, especially if you’re already familiar with how Excel style expressions work in SharePoint calculated columns, particularly around the nesting of IF statements. As a reminder, the IF statement looks like this:

IF (valueToTest <operator> valueToCompare, outputIfTrue, outputIfFalse)

If we want to nest a second if statement, we replace the outputIfFalse with the second IF statement and so on…

IF(valueToTest <operator> valueToCompare, outputIfTrue, IF (valueToTest <operator> valueToCompare, outputIfTrue, outputIfFalse))

You do need to watch the number of brackets in your statements and remember that for every opening ( you’ll need to make sure there’s a closing ). When it comes to troubleshooting, my rule of thumb is, if the view fails to render, then the problem likely lies in your JSON format code, if it’s just the column that fails to render, the problem is more likely in your expression statement.

The release that introduced the use of expressions also introduced the ability to change the rendering of the entire View, something that JSLink was brilliant for and something that I’ve really missed in the modern experience. I’ve not had much call to work with custom formatting until a recent project, but I’ll be revisiting some of the earlier JSLink samples now to see if they would be possible under the new model.

There’s still a few elements that I really miss, the key one being the ability to trigger JavaScript from click events and the like. We won’t be seeing these come to custom formatting, but we do now have the ability to trigger a flow from a column format. I can’t say that answers every scenario where I used JavaScript in the past, but the ability to trigger Flows does have some amazing power which I look forward to digging into more.

Paul.

<footnote> I’ve been a little lax in the blogging department since late last year due to a massive building project to extend my home. We started in August and just managed to get the downstairs habitable the week before Christmas. That’s taken a lot of energy and living out of two rooms for 5 months has been challenging, but the builders are doing the final touches now and we have a whole house once more, so apart from a lot of painting and decorating to do, I can spend a bit more time doing this kind of thing!

Leave a Reply

Your email address will not be published.

*

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