{"id":1419,"date":"2019-01-12T18:51:14","date_gmt":"2019-01-12T18:51:14","guid":{"rendered":"http:\/\/www.myfatblog.co.uk\/?p=1419"},"modified":"2019-01-12T18:53:46","modified_gmt":"2019-01-12T18:53:46","slug":"using-excel-style-expressions-in-sharepoint-custom-column-formatters","status":"publish","type":"post","link":"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/","title":{"rendered":"Using Excel style expressions in SharePoint Custom Column Formatters"},"content":{"rendered":"<p>Those that have read articles on my blog in the past know that I\u2019ve 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 <a href=\"https:\/\/github.com\/SPCSR\" target=\"_blank\" rel=\"noopener noreferrer\">SPCSR Display Templates GitHub Repo<\/a>, JSLink still remains a perfectly valid way to create custom list views, but it\u2019s biggest problem is it\u2019s 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.<\/p>\n<p>For me the biggest problem with these has been the bulkiness of the \u2018code\u2019 that you have to write to do a simple comparison. Let\u2019s take a simple example of a priority field with just two values, \u201cHigh\u201d and \u201cLow\u201d. 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:<\/p>\n<pre class=\"brush: js;\">if (ctx.CurrentItem.Priority == 'High')\r\n{itemHTML += \"backgroundRed'&gt;\"} else {itemHTML += \"backgroundGreen'&gt;\"}<\/pre>\n<p>With the first release of Custom Formatting for Modern, this statement became more challenging and certainly much more difficult to follow:<\/p>\n<pre class=\"brush: js;\">{\r\n    \"class\": {\r\n        \"operator\":\"?\",\r\n        \"operands\": [\r\n            {\r\n                \"operator\":\"==\",\r\n                \"operands\":[\r\n                    \"@currentField\",\r\n                    \"High\"\r\n                ]\r\n            },\r\n            \"backgroundRed\",\r\n            \"backgroundGreen\"\r\n        ]\r\n    }\r\n}<\/pre>\n<p>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:<\/p>\n<pre class=\"brush: js;\">{\r\n    \"class\": {\r\n        \"operator\":\"?\",\r\n        \"operands\": [\r\n            {\r\n                \"operator\":\"==\",\r\n                \"operands\":[\r\n                    \"@currentField\",\r\n                    \"High\"\r\n                ]\r\n            },\r\n            \"backgroundRed\",\r\n            {\r\n                \"operator\":\"?\",\r\n                \"operands\":[\r\n                    {\r\n                        \"operator\":\"==\",\r\n                        \"operands\":[\r\n                            \"@currentField\",\r\n                            \"Medium\"\r\n                        ]\r\n                    },\r\n                    \"backgroundAmber\",\r\n                    \"backgroundGreen\"\r\n                ]\r\n            }\r\n        ]\r\n    }\r\n}<\/pre>\n<p>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 <a href=\"https:\/\/twitter.com\/theChrisKent\" target=\"_blank\" rel=\"noopener noreferrer\">MVP Chris Kent<\/a> to release the <a href=\"https:\/\/github.com\/SharePoint\/sp-dev-solutions\/tree\/master\/solutions\/ColumnFormatter\" target=\"_blank\" rel=\"noopener noreferrer\">Column Formatter solution<\/a> that works as a web part on a modern page and assists in creating the JSON formats. He\u2019s very kindly made this available as part of the <a href=\"https:\/\/github.com\/SharePoint\/sp-dev-solutions\/tree\/master\/solutions\/ColumnFormatter\" target=\"_blank\" rel=\"noopener noreferrer\">Patterns and Practices<\/a> and I certainly recommend that you take a look as it\u2019s 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.<\/p>\n<p>If we take our example above for Low, Medium and High, our more simplified code becomes:<\/p>\n<pre class=\"brush: js;\">\"class\": \"=if(@currentField = 'High', 'backgroundRed',if(@currentField = 'Medium','backgroundAmber','backgroundGreen'))\"<\/pre>\n<p>&nbsp;<\/p>\n<p>That\u2019s a much simpler statement, especially if you\u2019re 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:<\/p>\n<p><strong><em>IF (valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, outputIfFalse)<\/em><\/strong><\/p>\n<p>If we want to nest a second if statement, we replace the <strong><em>outputIfFalse <\/em><\/strong>with the second IF statement and so on\u2026<\/p>\n<p><em><strong>IF(valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, <\/strong>IF (valueToTest &lt;operator&gt; valueToCompare, outputIfTrue, outputIfFalse)<strong>)<\/strong><\/em><\/p>\n<p>You do need to watch the number of brackets in your statements and remember that for every opening ( you\u2019ll need to make sure there\u2019s 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\u2019s just the column that fails to render, the problem is more likely in your expression statement.<\/p>\n<p>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\u2019ve really missed in the modern experience. I\u2019ve not had much call to work with custom formatting until a recent project, but I\u2019ll be revisiting some of the earlier JSLink samples now to see if they would be possible under the new model.<\/p>\n<p>There\u2019s 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\u2019t be seeing these come to custom formatting, but we do now have the ability to trigger a flow from a column format. I can\u2019t 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.<\/p>\n<p>Paul.<\/p>\n<p><em>&lt;footnote&gt; I&#8217;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&#8217;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!<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Those that have read articles on my blog in the past know that I\u2019ve 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 &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"http:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\">Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[16],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict<\/title>\n<meta name=\"description\" content=\"I&#039;ve been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict\" \/>\n<meta property=\"og:description\" content=\"I&#039;ve been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog of an overweight SharePoint addict\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-12T18:51:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-01-12T18:53:46+00:00\" \/>\n<meta name=\"author\" content=\"Cimares\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cimares\" \/>\n<meta name=\"twitter:site\" content=\"@cimares\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Cimares\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\",\"url\":\"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\",\"name\":\"Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict\",\"isPartOf\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\"},\"datePublished\":\"2019-01-12T18:51:14+00:00\",\"dateModified\":\"2019-01-12T18:53:46+00:00\",\"author\":{\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\"},\"description\":\"I've been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/\"]}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#website\",\"url\":\"http:\/\/www.myfatblog.co.uk\/\",\"name\":\"Blog of an overweight SharePoint addict\",\"description\":\"The rantings of a (not so) food obsessed IT consultant!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6\",\"name\":\"Cimares\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"contentUrl\":\"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg\",\"caption\":\"Cimares\"},\"sameAs\":[\"http:\/\/www.myfatblog.co.uk\"],\"url\":\"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict","description":"I've been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/","og_locale":"en_US","og_type":"article","og_title":"Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict","og_description":"I've been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.","og_url":"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/","og_site_name":"Blog of an overweight SharePoint addict","article_published_time":"2019-01-12T18:51:14+00:00","article_modified_time":"2019-01-12T18:53:46+00:00","author":"Cimares","twitter_card":"summary_large_image","twitter_creator":"@cimares","twitter_site":"@cimares","twitter_misc":{"Written by":"Cimares","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/","url":"https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/","name":"Using Excel style expressions in SharePoint Custom Column Formatters - Blog of an overweight SharePoint addict","isPartOf":{"@id":"http:\/\/www.myfatblog.co.uk\/#website"},"datePublished":"2019-01-12T18:51:14+00:00","dateModified":"2019-01-12T18:53:46+00:00","author":{"@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6"},"description":"I've been a massive evangelist for JSLink in the past and the release of Excel style expressions for custom column formats may just have converted me.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.myfatblog.co.uk\/index.php\/2019\/01\/using-excel-style-expressions-in-sharepoint-custom-column-formatters\/"]}]},{"@type":"WebSite","@id":"http:\/\/www.myfatblog.co.uk\/#website","url":"http:\/\/www.myfatblog.co.uk\/","name":"Blog of an overweight SharePoint addict","description":"The rantings of a (not so) food obsessed IT consultant!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.myfatblog.co.uk\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/55ae8f6885bb5b8390dad001f3da83c6","name":"Cimares","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.myfatblog.co.uk\/#\/schema\/person\/image\/","url":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","contentUrl":"http:\/\/www.myfatblog.co.uk\/images\/BlogImages\/About_D057\/TopOfTheWorld.jpg","caption":"Cimares"},"sameAs":["http:\/\/www.myfatblog.co.uk"],"url":"http:\/\/www.myfatblog.co.uk\/index.php\/author\/reginald\/"}]}},"_links":{"self":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1419"}],"collection":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/comments?post=1419"}],"version-history":[{"count":4,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1419\/revisions"}],"predecessor-version":[{"id":1424,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/posts\/1419\/revisions\/1424"}],"wp:attachment":[{"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/media?parent=1419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/categories?post=1419"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.myfatblog.co.uk\/index.php\/wp-json\/wp\/v2\/tags?post=1419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}