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…

.

SharePoint 2013 Workflow Error: Invalid Text Value

I got the not so obvious error below from a simple SharePoint 2013 style workflow.

Invalid text value.
A text field contains invalid data. Please check the value and try again.

The problem was actually pretty simple. I was trying to write more the 255 characters to the Workflow Log. You will get the same error writing more than 255 characters to a Single Line of Text column. You can use an Extract Substring from Start of String action to retrieve only the first 255 characters.

PowerShell to Bulk Add Lists and Libraries to SharePoint’s Quick Launch

Someone created a bunch of lists and libraries in a new site, and then a few days later wondered why they were no longer in Quick Launch. They had seen them there the day before in the “Recent” section.

I told them the steps to edit the properties of each list to add them to Quick Launch, and then they told me that there are over 30 lists. So… PowerShell to the rescue!  Here’s the on-prem version:

$site = Get-SPSite http://sp2016/sites/calcdemo

$web = $site.RootWeb     #or which ever web is needed

$lists = $web.Lists

$lists | where {-not $_.Hidden -and $_.Created -gt (Get-Date 12/21/2017)} |
           foreach { $_.OnQuickLaunch = $true; $_.Update() }

 

All done!

I filtered by date so that I would not change any lists that existed before their new work, and filtered by Hidden to exclude the SharePoint auto-generated lists.

No More HTML in SharePoint Calculated Columns!

Update: Here's a workaround using a workflow: http://techtrainingnotes.blogspot.com/2018/01/adding-html-to-sharepoint-columns-color.html


Just in case you missed it:

“Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013.”

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

So, no more of this:
  
Or this:
    =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])