SharePoint Online New Column Formatting Capabilities
It is now possible to apply custom format to your SharePoint Online lists (as now only for first release tenants and users), which you can think of something similar to Excel conditional formatting. But it’s more than that.
Beside applying styles to your list columns (think CSS), it is also possible to modify the content of the fields. This is done by adding a custom “code” to the column settings. This code must be in JSON format and follow a defined schema.
Please first note that this new feature is only for lists viewed within the “modern” experience.
To add the column-formatting code, go to your list and click on the column header, then Column settings > Format this column.
Then you can just add your custom JSON and save (preview seems to work in Chrome but not Firefox).
As said, the column-formatting JSON should follow a particular schema. Below is a simplified version of what your custom JSON can contain based on the full schema definition.
Get started
First you should probably start by opening the official documentation, and especially at the detailed syntax reference.
The documentation page contains example, but there is also a community repo on GitHub with samples.
To write your custom JSON, you can use Visual Studio Code and take advantage of auto-completion and validation (ctrl+space).
Edit: A SharePoint Framework Webpart has been developed by Chris Kent which allows to directly write your JSON column formatting within your SharePoint site.
Basic formatting
As a first example we can see quickly how to change the background and text color of a column.
In this example we just set the field element to be a div and the content is just from the field itself. Then we define the style for this element, it’s just like CSS but with key-value pairs. It’s possible to use most of the common CSS properties but you can refer to the documentation for the complete list.
Formatting with logic
This first example was not containing any logic and all fields on the column are formatted in the same way. That can be useful, but we can do better.
Let’s say that we want to highlight tasks based on the completed percentage. To do this, we have to use expression objects which are evaluated in the context at run time. The expression objects normally contain an operator and operands.
In this example we set the background color in function of the value of the Completed column.
To make a condition, we have to use the operator “?” and for the operands we needs 3 arguments:
- the test
- the value if true
- the value if false
In that case, we want to have 3 different background colors, so we have to nest another conditional operator “?”.
As you can see, we apply the formatting to the column Task but we base the test on the value of the column Completed. To do a lookup to another column, you have to use [$ColumnName].
Two tips about this:
- First it’s case sensitive
- Second to make sure of the column name, you can go to List Settings and in the Columns section hover on the desired column, you will see in the URL which name SharePoint is really using (it’s possible that you renamed the column but in the back-end it’s still the old name).
This example is fairly easy, but we can see that we already nested multiple operators and operands. It’s not super complex, but it’s important to get comfortable with the JSON syntax.
So here is the result, and we can notice that the default named colors are quite awful.
Using Office UI Fabric
It’s possible to use CSS classes from Office UI Fabric to apply style.
This example is similar to the previous one, but instead of changing the style, we apply directly specific classes from the UI Fabric.
It’s also possible to use some SharePoint CSS classes, like the ones used in the official documentation.
sp-field-severity--low, sp-field-severity--good, sp-field-severity--warning, sp-field-severity--blockedI noticed one advantage using the SharePoint class, is that on hover the background color will change, which is not the case with the UI Fabric.
More advanced formatting
Continuing on the same list we can try a bit more advanced formatting, like creating data bar similar to the example from the official documentation.
This is achieved with the following JSON code.
This example reuse a bit of everything that we have seen so far, and it uses the progress percentage to change the field width.
Make a pseudo app from a list
The visual improvements and the logic permitted by the column formatting can allow us to create something close to an actual app, or at least more useful and engaging for the users.
We can keep building from the previous example and add:
- highlight the task from the current user
- clickable icon to email the task owner
- warning on overdue task
First to highlight the tasks owned by the current user, we can use “@me” that returns the email address of the current user and compare it to the Owner field value.
Then for the Owner field we add a clickable icon to send out an email. This is transposed for an example available in the documentation, only the icon has been changed and the mailto content.
And finally for the due date, we just compare the current date using “@now” to the field value. Also there is a check on the task completed percentage, so we don’t flag as warning the tasks that are already done.
And here is the result after adding the above custom formatting to the Task, Owner and Due Date columns.
So now, instead of having just a list of tasks, we have different visual indicators that provide a better user experience and a quick action if they want to email the task owner.
This task list is just a simple example, and there are maybe more appropriate way to manage tasks… however it demonstrates few of the capabilities of this new SharePoint Online list feature.
Furtermore, we can imagine adding more logic and workflow using Flow. And also the upcoming PowerApps custom form in list view, could provide a richer experience for data input.
Conclusion
I think this new column formatting capability in SharePoint Online is a nice addition to the list. We can now add styles and actions to the list, using logic construct. However, I’m not sure it’s really user friendly, nor power-user friendly. Still it’s easier to implement than the SharePoint Framework (SPFx) Field Customizer.
I noticed few display bugs, especially in IE, so I recommend to use Chrome or Firefox (latest version). And I would recommend to save all your custom JSON codes somewhere, as I experienced few times that the customization had simply disappeared. Also when I tried to get the value from the people field for department and jobTitle, I got an error, the picture property didn’t return any value too. Let’s hope these problems will be resolved at GA.
If you want to get started with the column formatting, remember to check the samples repo on GitHub. In this example we went through, I didn’t really try to push the limits of it. But it’s probably possible to do fancier things using the HTML and CSS attributes available.
Update:
Chris Kent is demonstrating the SharePoint Column Formation in a new PnP webcast, he is going through the capabilities, limitation and the difference between Column Formatting and Field Customizer. He is also demonstrating his Column Formatter SharePoint Framework client-side webpart which should be useful to help creating nice column formatting rules with a GUI and push it to your list columns. It also includes the Monaco Editor so you are getting the same power as Visual Studio Code if you want to edit and customize the JSON within your browser.
This is the version 1.2 of his Column Formatter webpart, so I guess he will keep improving and the dev community as well.
His work should help and give inspiration to Microsoft team to ship their own UI tool for Column Formatting (hopefully directly within the SharePoint list view to avoid mock-up data).
Official GitHub repo for the Column Formatter SPFx webpart:
