Adding HTML to SharePoint Columns – Color, Images and More – Round 2!

Back in June Microsoft announced they were blocking HTML created by Calculated Columns with the June 2017 Public Update (PU) for SharePoint 2013, 2016 and SharePoint Online.

See here: http://techtrainingnotes.blogspot.com/2017/12/no-more-html-in-sharepoint-calculated.html

Before the June update:

After the June update:

You can turn this new “feature” off using PowerShell… but only for on-prem.

https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

$wa = Get-SPWebApplication http://yourWebAppUrl
$wa.CustomMarkupInCalculatedFieldDisabled = $false
$wa.Update()

Repeat for each web application as needed.

There’s a workaround!

There's a fairly simple solution that works in all versions, if you don't mind using a workflow.

1. Edit the Calculated Column with the HTML and change it's "The data type returned from this formula is" back to "Single Line of Text". (Just change the result type... leave the column as a Calculated Column.)
2. Create a new Multiple Lines of Text column and set it to "Enhanced rich text (Rich text with pictures, tables, and hyperlinks)".
3. Create a workflow that simply copies the Calculated Column to the new Multiple Lines of Text column. Set the workflow to run on Created and Changed.
4. Edit your views to hide the Calculated Column and add the Multiple Lines of Text column.

The workflow is just a single Set Field in Current Item action.

Set the “field” to the new Multiple Lines of Text column and set “value” to the Calculated column. Publish and test!

This solution will let you keep the Calculated Column for easy revising of the formula logic. You could also let the workflow do all of the work to create the logic and HTML using a String Builder, and eliminate the need for the Calculated Column.

Update the Existing Items

You now need to get the workflow to run on all of the existing items. You can run a PowerShell script to start the workflows, you can run a PowerShell script just to copy the data from the Calculated column the new column, you can manually run the workflows on each item, or if you don't mind the Modified date and Modified By being changed switch to the Quick Edit view and copy all the items in one column and then paste them right back.

After the workaround:

What does not work?

Script tags and Style tags. (and I’m sure there are a few more) Style blocks are emptied and script blocks are completely removed. But, basic HTML for hyperlinks, image tag, etc. still work.

Before: <style>#test { color:red }</style><script>alert(1)</script> more HTML…

After: <style></style> more HTML…

.