44

On June 13th 2017 all SharePoint calculated columns with HTML markup stopped working.

Basically the fields now render text (regardless of setting the data type to number). The gist of the notice from Microsoft (Handling HTML markup in SharePoint calculated fields) is:

In Microsoft SharePoint lists and libraries, calculated fields that are running in the classic UI mode can be used to display results based on Excel-like formulas. This is a long standing capability, and is documented in Calculate data in lists or libraries.

Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will begin blocking execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. We are also providing this as a configurable option for on-premises in SharePoint Server 2016 and SharePoint Server 2013 via the June 2017 and subsequent Public Updates.

As we don't have on premise SharePoint (online), we have to find an immediate work around, given that this impacts our processes and thus, business.

Is anyone aware of third party applications that will allow for HTML, script markup (that works with SP online)?

8
  • 1
    I am also looking for workaround Commented Jun 13, 2017 at 20:20
  • 2
    Thanks for the alert. We just used this trick for the first time today. How ironic.
    – Rothrock
    Commented Jun 13, 2017 at 20:38
  • Did anybody try a jquery replacing all &lt; with "<" and all &gt; with ">"?
    – Marpio
    Commented Jun 14, 2017 at 10:05
  • I've just checked this in our tenant and the form where we were using calculated fields with HTML just shows this HTML as a plain text Commented Jun 14, 2017 at 15:38
  • I am incredibly frustrated with Microsoft's tendency to modify their products for themselves and not their end-users. Especially the products that are installed on end-users' systems, but SharePoint Online has been paid for by the user and so should not be nerfed without a renegotiation of price.
    – wizzwizz4
    Commented Jun 14, 2017 at 16:27

17 Answers 17

31

Sorry,

with http://www.ViewMaster365.com/#How I have been an avid promotor of the undocumented HTML trick (worked since SP2010, maybe even SP2007)

Like you I am amazed Microsoft actually disabled it, then again it already was disabled for Modern Experiences, so it was clear the HTML trick had no future

Must be kismet or faith or karma or just plain common sense,
I switched careers, and totally left the Microsoft Front-end world I worked in for 12 years,
the day before Microsoft really killed all Front-end Fun.
(They already killed UserCustomActions and CSR/JSLink Client Side Rendering in Modern Experiences, and SPFx sucks)

I don't even use Outlook anymore for email .... LOL


At the moment (june 13th, 2017) my tenant still does the HTML trick just fine.

And as you can read in the announcement, you can request Microsoft to delay implementation till September 10, 2017

The Solution

Is to switch back to the original 2007 workaround:

  • Let the Column display the bare HTML
  • Use a document ready script to process the DOM elements
  • and rewrite to .innerHTML

Christophe
(also left the Microsoft business..LOL.. does something with games now)
blogged about this some 9 years ago

https://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/

Note; that script could do with an update to modern JavaScript...
I'll see if I can drag myself away from the modern WebComponents world I am in now for some minutes .. tomorrow.. or the day after.. or never

A day later:

No need for jQuery, replace HTML with Vanilla JavaScript

jQuery is f* slow with the contains filter

Since IE9 everyone can use the TreeWalker API (every other Browser supported it long ago..)

but ofcourse no jQuery addict wants to learn Modern and Faster techniques ;-)
If I had gotten a dime from every "Front-End developer" who actually knows about this API I still couldn't pay for a one months O365 subscription

Whack this code in a JS file, then reference it from a CEWP remember! put it below all other ListView WebParts! because those DOM nodes need to exist

console.clear();
document.querySelectorAll('.ms-listviewtable').forEach(function (table) {
    function getTextNodes(element) {
        function acceptNode() {// stupid IE needs this function where other browsers don't.
            return NodeFilter.FILTER_ACCEPT;
        }

        var safeFilter = acceptNode;
        safeFilter.acceptNode = acceptNode;
        return document.createTreeWalker(element, NodeFilter.SHOW_TEXT, safeFilter, false);
    }

    var TRrows = table.querySelectorAll("tr[role='row']");
    var htmlNodes = [];
    TRrows.forEach(function (TRrow) {// processing by row so you could do fancy Row actions
        var tree = getTextNodes(TRrow);//standard since IE9, get TEXT nodes
        while (textNode = tree.nextNode()) {//loop all found textnodes
            console.log('textNode id:', TRrow.id, 'value:', textNode.nodeValue);
            var isHTML = textNode.nodeValue.match(/<[a-zA-Z]>/);
            if (isHTML) {
                htmlNodes.push({
                        //store all data now so we don't have to do any DOM calls later
                        node: textNode,
                        parent: textNode.parentNode,
                        value: textNode.nodeValue
                    }
                );
            }
        }
    });
    console.log('processing', htmlNodes.length, 'htmlNodes:', htmlNodes);
    htmlNodes.forEach(function (htmlNode) {
        console.log('processing HTML:', htmlNode);
        var fragment = document.createElement('SPAN');
        fragment.classList.add("processedHTML");
        fragment.innerHTML = htmlNode.value;
        htmlNode.parent.replaceChild(fragment, htmlNode.node);
        //htmlNode.parent.style.background='lightgreen';  // for debugging purposes
    });
});

Yes.. you can do this with 7 lines of jQuery...
But you need to load a 90 KB Library... slow!
And the jQuery code itself is about 30% slower!
jQuery slowness means: your users could see a screen flash from old content to new HTML content on larger pages

CSR / JSLink

OR use the above code in the OnPostRender function of a JSLinked CSR file

All Pages

But executing on one page is no fun... execute it on every page wih a UserCustomAction ...
this very powerfull official technique has only been around since 2010 (but was killed by Microsoft in Modern Experiences)..
(If I would get a penny.....)

Remember, you then need to make sure it executes after the DOM is created (where jQuery lovers use $.ready())

So wrap it in something like:

document.addEventListener("DOMContentLoaded", function (event) {
 [the above code goes here]
}

Before:

After:

Green marked are the only TextNodes converted to HTML

... Yes.. why not do the Column name as well.. think about the functionality you can add :-)

7
  • Danny. Thank you for the reply. I have been trying all sorts of 'tricks' to no avail. Of course, people are a bit in an uproar given that our processes depended on this nifty HTML trick in SP (which is another reason that I preferred the classic experience.. but I digress). I'll take a look at the information you posted and see if I can come up with a fix. It's that or third party applications (which I can't seem to find any at the moment.. at least for SP Online). Also, do you still have room on that "left Microsoft world" trip? I'll buy a ticket!!
    – user67867
    Commented Jun 13, 2017 at 20:45
  • You're amazed at Microsoft using an iron-shod boot to stamp out anything non-standard that doesn't exactly comply with their image of how Sharepoint should be used? I'm not. Commented Jun 14, 2017 at 15:37
  • 2
    I am amazed because this undocumented behavior has worked for 8 years... since they are pushing Modern Experiences.. where all branding is aan iron chastity belt.. something serious must have happened with this undocumented behavior that caused them to fix this...But.. I don't really care.. SharePoint (the backend stuff) is about to die soon ... API calls taking 150 milliseconds are just not of this age... just think how much software you know where the core is nearly 20 years old.. The only older system I know is the COBOL env. my bank uses behind the scenes... LOL Commented Jun 14, 2017 at 18:18
  • Regarding your comment "because jquery has to be backward compatible to IE8 or something," you will be pleased to find that jQuery 2.0 and later have dropped support for IE8 and earlier: jQuery Core: Version 1.9 and Beyond. Commented Jun 15, 2017 at 9:16
  • Updated code for easier implementation Commented Jun 16, 2017 at 20:44
8

The new way of displaying HTML based on data in list(item) was possible using using JSLink. However the 'new' way to do this is by using SPFx Extensions. It's currently in preview.

You should use a FieldCustomizers. As MS states: "it can be used to provide modified views to data for fields within a list.". You could start here for more information.

1
  • 1
    A toolchain of 4 tools and 20 lines of code to put some simple HTML in a page no one masters yet (including MVPs). It is like wanting to get some hot water and having to build a coal plant in your yard so you can heat a gigantic stove in you professional kitchen just to make a cup of tea..... There you have the reason why, after 12 years mainly doing only Front-end, I left the Microsoft world for the (W3C standard) WebComponents world. Commented Jun 20, 2017 at 6:29
7

You can put in a support ticket to request an extension of the feature until 9/10. While it's not a long-term solution, this will hopefully give Microsoft enough time to enable the admin setting that is available for the on-prem version.

2
  • Incorrect information, it is September 10th, not 30th Commented Jun 14, 2017 at 6:34
  • Just an update - this took around 24 hours to get "approved" and turned back on. No issues currently.
    – JD7
    Commented Jun 15, 2017 at 20:52
7

The short answer on "How to get the same functionality back":

  1. Third party products.
  2. Client-side rendering via JS Link property of web parts. This only works for Classic mode. If you are using Classic mode, this is likely the 'best' short term answer to broken interfaces until a longer-term solutions is implemented.
  3. SPFx Extensions or customization
  4. PowerApps

Longer answers: Third party products. I've seen folks mention something called Skybow but I've never seen or used it. Only mentioning because it was mentioned by someone in the community I trust.

CSR and JSLink: There are multiple ways to implement JSLink, some via the browser (where I focused my experience - http://www.idubbs.com/blog/js-link-and-csr/ ) and via deployed coded solutions (dev only). As mentioned in other responses JSLink does NOT work in the modern experience. It will only work in the Classic mode as long as it is around. There have been NO dates announced for getting rid of Classic mode at this time. If you choose this direction it's worth noting that if you're comfortable writing script in the calculated field you'll likely be comfortable writing the code needed to get CSR to work. I've got plenty of examples in the link included here.

SPFx Extensions and customizations: This is the 'approved' development path for deployed solutions. If you are a developer and/or are creating solutions that are used across a broader scope this is likely the path you should go down.

PowerApps: This is likely the long-term replacement for solutions you used HTML or other scripting in your calculated field for (hand slap from English teacher). Embedded integration with SharePoint isn't there yet, but has been announced. You can start building the views/solutions you need, but they won't be 'really' embedded in SharePoint yet - web parts and/or other tools are coming soon (they've been announced). Much of the attention to PowerApps has been in the area of forms, but views will be a part of the capabilities as well. They've already made improvements with introduction of controls like the Data Table control. http://www.idubbs.com/blog/powerapps-and-flows/

My perspective is from power users / non-developers and try to use boilerplate examples you can cut and paste.

To make sure you get the general idea read this: JS Link – Hello World

More specific KPI-like example: JS Link – Using CSR for KPIs

2
6

What I did to get around this in SP Online, is to use JSLINK to render the HTML. This is a much more elegant solution, and easier to use than the calculated columns anyway. But I was caught completely off guard by this.

4
  • 3
    Microsoft also killed CSR for Modern Experiences...(and Microsoft is very unclear on how long Classic Experiences will be available) They want you to learn FaceBooks React code (and MVPs teach you oldskool JavaScript patterns)... Which is not OpenSource.. the React BSD license states that FaceBook can revoke the license when you piss FaceBook off... Believe me .. Microsoft made a wrong decision with SPFx Commented Jun 14, 2017 at 6:39
  • 2
    @Danny'365CSI'Engelman I'm not sure what license you're talking about, but the BSD license, which is indeed attached to the React source code has no revocation clause. It contains a requirement of credit, a reservation of trademarks, and a disclaimer of warranty. Obviously, they could change the license later, as they retain copyright, but they couldn't stop you retaining and modifying the last version released under the BSD license.
    – IMSoP
    Commented Jun 14, 2017 at 14:38
  • Programmers are programmers and lawyers are lawyers... we had to listen to the legal department.. and had to ditch React .. everything is in the W3C standard WebComponents anyway.. React is the new jQuery .. which was the hipster Framework... once upon a time ago Commented Jun 14, 2017 at 18:12
  • @Danny'365CSI'Engelman If your legal department are telling you that the BSD license is unacceptable, I'm not sure what license they would accept, because it's hard to imagine anything much less restrictive, other than a total grant of IP to you or the public domain. For that matter, it seems bizarre to worry that Facebook might relicense an open source JS library (which you could then fork), but continue to use a proprietary service from Microsoft, who could probably unilaterally withdraw your access to your own data.
    – IMSoP
    Commented Jun 15, 2017 at 8:35
5

An alternative fix for this is to create a new Hyperlink or Picture column, and populate it with the correct url for your image.

You can then use a workflow to set the correct URL for your image, and have the workflow run on item change.

It won't be quite as reactive as a calculated column, and will cause minor versions to be incremented each time it changes, but it does restore the functionality. I will try to update this post with the workflow later today.

Edit: Workflow screenshot

enter image description here

The second Update item clears the field, for when an icon is not required. I would also add some checks around this so that the value is not reset if it isn't changing.

Definitely not as easy as a calculated column, but keeps it all within the SPO environment, so is maybe less likely to break in the future!

5

The best alternate solution would be using CSR (JS Link) which is easy to customize your columns as needed. This is a most elegant way to replace the HTML code in calculated columns.

The below article gives you samples on JS Link.

Understand JS Link with samples

1
  • 1
    Don't invest too much time in learning JSLink/CSR specific stuff... Microsoft killed it.. it is not in Modern Experiences anymore.. they are replacing it with new technology...no technical details known yet and no exact timeframe given yet.. Commented Jun 15, 2017 at 19:21
5

Also long time lurker! Here is a solution I came up with.

$(document).ready(function() {
    var textholder = "";
    $("td.ms-cellstyle.ms-vb2:contains('<a href=')").each(function() {
        textholder = $(this).text();
        $(this).html(textholder);
    });
})

I have plugged it into a page as a script editor and it works. Next I'm going to try adding it as a script on the master page.

Hope this helps!

Edit: I realized that not all lists have the same classes on the td elements so it might be necessary to only use the class ms-vb2:

$(document).ready(function() {
    var textholder = "";
    $("td.ms-vb2:contains('<a href=')").each(function() {
        textholder = $(this).text();
        $(this).html(textholder);
    });
})

Also I think there would be a better to way to this by creating a unique identifier and adding it to the concatenate string as a class for the a element. For instance the concatenate formula would look like this:

=CONCATENATE("<a href='",[Hyperlink Column],"' class='ThanksMicrosoftForMakingThisHard'>",[Link Text],"</a>")

And then for the script in the masterpage you would add this to the head:

<!--SPM:<SharePoint:ScriptLink language="javascript" name="LINK TO JQUERY LIBRARY" OnDemand="true" runat="server" Localizable="false" />-->

and this right above the end body tag:

$(document).ready(function() {
    var textholder = "";
    $("td.ms-vb2:contains('ThanksMicrosoftForMakingThisHard')").each(function() {
        textholder = $(this).text();
        $(this).html(textholder);
    });
})
3
  • 3
    Don't use the old MasterPage hack. Use the way easier to maintain UserCustomActions (available since SP2010... PS. MS also killed all adding of Script in Modern Experieces.. they want you to use the whole SPFx toolchain... modern Front-End technologies I as a 100% Front-End developer took me years to master... But I am stupid.. Andrew Connell claims he can teach it to you in just a couple of video lessons Commented Jun 14, 2017 at 6:47
  • Connor's solution worked for me. Extremely upset with MS for just removing this functionality like this
    – cr1
    Commented Jun 14, 2017 at 12:16
  • I added a single file no-jQuery solution which is xxx times faster, to my post above Commented Jun 14, 2017 at 20:08
5

My workaround is to add a content editor webpart with JS included, this code:

$(document).ready(function () {
    if ($('div[fieldtype="Calculated"]').length != 0) {
        $('div[fieldtype="Calculated"]').each(function () {
            var thElement = $(this).parent();
            var indexOfThElement = thElement.parent().find('th').index(thElement);
            indexOfThElement++;
            thElement.closest('table').find('tr td:nth-child(' + indexOfThElement + ')').each(function () {
                var thisText = $(this).text();
                if (thisText != '') {
                   $(this).text('');
                   $(this).append(thisText);
                }
            });
        });
    }
});

Of course, adding JavaScript causes the view to go back to the old design.

3
  • This does not work when items are grouped
    – Marpio
    Commented Jun 14, 2017 at 11:55
  • or if we have pagination. or if we sort or filter Commented Jun 14, 2017 at 15:40
  • SetTimeout loop would help... While searching better solutions, does anyone know how to reach webpart post render events? Or how to extend all these filter group sort pagination events?
    – Paulius M
    Commented Jun 15, 2017 at 13:02
5

In response to @Danny '365CSI' Engelman : Thank you for your 'vanilla JS solution' - I have been able to get it working, but:

A clear benefit of jQuery is that it works across browsers. Your code did not work in Google Chrome (at least v48), as the querySelectorAll()-function does not return a 'pure array' (from what I have found on the interwebs).

A simple fix was to create a helper function:

function getDomNodeArray(selector, context) {
  var elemCollection = (context || document).querySelectorAll(selector);
  var elemArray = Array.prototype.slice.apply(elemCollection);
  return elemArray;
};

Which is then used as:

 var listviewtables = getDomNodeArray('.ms-listviewtable');
 var TRrows = getDomNodeArray("tr", table);

Next, I discovered that our tables do not contain <tr>'s with a selector "row", so I left the selector out.

Thirdly I changed the isHTML RegEx to "/<[a-z][\s\S]*>/".

Without your solution I would have never found a slution for our porblem, so thx! And also thx for pointing me towards the CISAR plugin for Chrome. I am a power user (and our IT department does not have enough resources to help me with such a 'small problem'), so I am very glad I can change these scripts in real time!

5

Use JSLink

  1. Create a new column named "Test" as calculated column
  2. Insert the HTML ( e.g: <div style='text-align:left !important;'><a href='/Pages/Course.aspx?name="+ctx.CurrentItem.Title+"'>"+ctx.CurrentItem.Title+"</a></div>
  3. Insert below code in the page using content edit web part

script type="text/javascript">

(function () { ExecuteOrDelayUntilScriptLoaded(_createRelatedLinks, 'clienttemplates.js'); })();

function _createRelatedLinks() {
// Initialize the variable that store the objects.
var overrideCtx = {};
overrideCtx.Templates = {};

 overrideCtx.Templates.Fields = { 
    "Test": { 
        "View": CreateRelatedLinks 
    } 
};      
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);

} function CreateRelatedLinks(ctx) { return ctx.CurrentItem.Test

}

5

There's a fairly simple solution, if you don't mind using a SharePoint Designer 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.

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 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.

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.

I have some more info on this solution here: http://techtrainingnotes.blogspot.com/2018/01/adding-html-to-sharepoint-columns-color.html

5

Much time has passed and this can now be achieved using the Column Formatting option that has appeared in SharePoint online in the column settings. It uses JSON syntax, some great examples on GitHub - https://github.com/SharePoint/sp-dev-column-formatting/tree/master/samples

1
4

Here is the official fix, at least for on-premise:

# NOTE:  =$False  NOT  ="$False"
# In SharePoint 2016 Management Shell, run...

$Web = Get-SPWebApplication http://weburl
$Val=$Web.CustomMarkupInCalculatedFieldDisabled=$False
$web.update()

# To confirm it is now false...

$Web.CustomMarkupInCalculatedFieldDisabled

Link at Microsoft about CustomMarkupInCalculatedFieldDisabled: https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

3
  • As you state it to be an official fix, I suggest to add a reference/source for the claim.
    – moe
    Commented Aug 16, 2017 at 14:41
  • According to Msft, it will remain configurable in on prem so this is only an issue for SP Online. (See quote in question).
    – matt
    Commented Sep 7, 2017 at 15:25
  • I understand that this is also supposed to be available in SharePoint 2013 as I can run the PowerShell that you posted without error, however my HTML still does not render. I'm using SharePoint Foundation 2013. Does anyone know if it is supposed to work in Foundation? Commented Oct 18, 2021 at 17:32
2

For SharePoint Online, the alternatives are to embed some JavaScript on the page or to use the new SharePoint PnP Fx Extensions. If you have your own SharePoint Server, you can use the API [i.e. through PowerShell] to modify the Web object's CustomMarkupInCalculatedFieldDisabled property -- see Mark Gable's response.

I needed a little bit more robust solution, so I created a SharePoint Extension to do this. You can view it on GitHub. The instructions there should be able to get you up and running in a couple minutes. I find it much easier to manage than the JavaScript alternative.

1

This works in 2019!

@MarkOrange already linked to the documentation for this approach above, but I thought I'd add a concrete example.

I have a SharePoint list column containing a string of the form latitude,longitude. The formatting below styles the text with CSS and uses formulas to change the displayed text and makes it a clickable link that takes you to a Google map with a pin at the coordinates from that column's value.

The formula for displayed text is in txtContent and the formula for the href is in href

In my list I clicked on the column header for my latlong column, then chose Column Settings > Format This Column

Then in the menu that opens on the right, I pasted this JSON:

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
   "elmType": "a",
   "txtContent": "= 'See on map: '+@currentField",
   "style": {
       "text-decoration": "underline",
       "color": "blue"
   },
   "attributes": {
      "target": "_blank",
      "href": "= 'https://maps.google.com/?q='+@currentField+'&ll='+@currentField+'&z=21'"
   }
}
0

I wrote the following for classic list view. Tested in SP2019, works like a charm. Save the following script as js file to your site collections style library and include the link in list view JS Link box.

// SCRIPT TO FIND "<" TAG IN TABE CELL AND CONVERT CONTENT TO HTML
$( document ).ready(function() {
    $(".ms-listviewtable tbody tr:first td").each(function(i) {
        if($(this).is(':contains("<")'))  {
            index = i + 1;
            $('.ms-listviewtable td:nth-child('+index+')').each(function () {
                var appendHTML = $(this).text();
                $(this).empty();
                $(this).append($(appendHTML));
            });
        }
    });
  });

Downside to this approach is that you would have to add this to every view. But you could also add it to the master page. User preference depending.

Hope this helps someone.

Not the answer you're looking for? Browse other questions tagged or ask your own question.