How to Fix SharePoint Calculated Columns

One of the most oft-used features in the SharePoint Designer’s toolbox is the calculated column. Whether it’s adding two columns together or displaying custom code that renders HTML code, there are many uses. Recently, however, Microsoft released an update to SharePoint online that broke the HTML markup hack.

From their perspective, the use of SharePoint calculated columns is an “undocumented” feature, which can introduce some security risks and unstable pages. Starting on June 13, 2017, they are effectively blocking the execution of custom markup in SharePoint online and SharePoint Server 2016.

But fear not, there’s a workaround called Column Formatting.

Column Formatting – Coming Soon

Column formatting is an upcoming feature that customizes how fields in SharePoint lists and libraries are rendered. It will be available in the Column Settings menu, under the heading “Format this column.” There, you can add JSON (Javascript Object Notation) code to set the formatting specifics.

In a nutshell, JSON is built on a collection of name and value pairs, which are called objects. A very simple JSON code would be to set the name (element type or elmType) to the value of “div”, and the content to a column name, thus placing the value of the column inside a <div> tag (and thus making it ready for more custom formatting). Additional names like attributes and class have their own values and syntax as well.

Conditional Formatting

One of the most common uses of the calculated column HTML rendering is to change the color of a column based on status. So, if you’ve got a task marked overdue or a project status that’s red, SharePoint would graphically indicate the status using icons or colored bars. This feature is now available under Column Formatting. SharePoint offers a set of pre-defined classes, like field severity, progress bars, and trending icons.

Rendering HTML Links and Adding Actions

Another common calculated column trick is to render custom HTML links, which can be useful in place of a Hyperlink column. Now, you can format the column to render the text. Another new feature that might be useful to SharePoint administrators is the addition of an action button, such as emailing a contact. This is a great feature for external contact lists, like contacts and suppliers.

The Sky’s the Limit

Moving into a JSON formatting versus SharePoint calculated columns may take some getting used to, but it provides much more flexibility for administrators to view the data. Once you understand the syntax, you can build your own JSON schemas and formatting, reusing the formatting across your sites. This goes for everything from icons, colors, spacing, fonts, you name it.

If JSON seems a little out of your wheelhouse, contact eSoftware Associates to find out how you can take advantage of this new feature—and more.