SharePoint Online: Format Calculated Column as Hyperlink
Requirement: Format Calculated Column markup as Hyperlink.
How to Format Calculated Column as Link using HTML Markup?
Using calculated column formulas to frame hyperlinks and setting the column type to “Number” renders the column markup as HTML. E.g., I’ve used this formula to link project documents with the Projects list.
=CONCATENATE("<a href='https://crescent.sharepoint.com/sites/projects/documents/'",[ProjectID],">",[Project Name],"</a>")
Well, not anymore in SharePoint Online as per the Microsoft announcement Handling HTML markup in SharePoint calculated fields. SharePoint Online renders HTML markup as text instead of HTML!
So, How to render Calculated columns as HTML markups in SharePoint Online? Here is how:
- Make sure the list is in New experience, Format the calculated column
- Use this JSON to format the column markup as HTML.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "a",
"txtContent": "Project Documents",
"attributes": {
"target": "_blank",
"href": "='https://crescent.sharepoint.com/sites/projects/' + @currentField"
}
}
Similarly, you can refer to any other fields in the list in JSON as:
"href": "='https://crescent.sharepoint.com/sites/projects/' + [$Project_x0020_Name]"
This JSON renders the text as HTML markup
Here is the Microsoft documentation on column formatting: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
Format HTML Markup in Classic Sites
On Classic pages, edit the page in the browser, add a script editor web part and place this script in it.
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
var textholder = "";
$("td.ms-vb2:contains('<a href=')").each(function() {
textholder = $(this).text();
$(this).html(textholder);
});
})
</script>
How about SharePoint 2016/2019 On-Premises?
While the above steps work for SharePoint On-premises, You can configure it at web application level settings using PowerShell.
$WebApp = Get-SPWebApplication https://YOUR-web-app-url
$WebApp.CustomMarkupInCalculatedFieldDisabled=$False
$WebApp.update()
Error in =CONCAT with closing single quotation.
Hello Mr.Rajack, thank you very much for this article. This solves my issue for creating the edit item hyperlink to launch the Power App form in a SharePoint Online list using a classic page view.
But I have one problem. When I navigate to the second page of the view to see the next 30 items (from item 31 on), the jquery script doesn’t transform the calculated field to html anymore. The calculated field stays as text. The jquery script only translates the calculated field to html on the first view page that shows item 1 – 30.
I check in Edit Page that the jquery script still exists on the classic view page. Do you know why and how to solve the issue for the second page view?
Thank you very much.
Regards,
Judith
Great solution. Will it work on a kanban board? On my board the link does not show.
Is there a way to conditionally leave it blank if there is no data in the field for the link?
This did the trick for me…if(@currentField != ”
In sharepoint 2013, If I wanted to create calculated columns that were hyperlinks would I follow these same steps?
For SharePoint On-Premises, You have to update a web application property!
$WebApp = Get-SPWebApplication https://YOUR-web-app-url
$WebApp.CustomMarkupInCalculatedFieldDisabled=$False
$WebApp.update()
Thanks alot
Hi –
thank you for this code, it’s really neat!
I am an a Classic view and I can’t get this to work for the list when the view if Grouped by. Any suggestions?
I have updated the article for Classic UI. Please try now!
How to add a list field in the “txtContent”: “Project Documents” section instead of a text
Say, If you want to display the value of “ProjectName” field instead of static text, use: “txtContent”: “[$ProjectName]”
Thanks very helpful, but please fix the code, single quotation shouldn’t be there in the first parameter of concatenate function, which is after this documents/’ instead, it should be in third parameter like this “‘>”
=CONCATENATE(“<a href=’https://crescent.sharepoint.com/sites/projects/documents/’ >”,[Project Name],”</a>”)
I can’t quite get this to work. I left the calculated column as is and even tried using some text rather than column variables and a hardcoded URL in my json and my value displays blank.
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “a”,
“txtContent”: “TEST”,
“attributes”: {
“target”: “_blank”,
“href”: “=’https://finance.yahoo.com/quote/'”
}
}
Thanks for this. Worked well