How to create calculated hyperlink in SharePoint Online list
“How can I create a calculated hyperlink in a SharePoint online list? A hyperlink that uses info from another column.”
“The way this was done in the past is no longer allowed for SPO.”
In Modern SharePoint online you can’t create a hyperlink inside calculated column anymore. You might think it’s another piece of lost functionality, but there’s a much more powerful alternative. The possibility to define custom column formatting using JSON.
Maybe you see formatting just as some fancy fonts and colors, but in this case there are much wider possibilities. JSON formatting in SharePoint enables you even to change data displayed in a column. You can completely change what users will see, including creation of hyperlinks.
Building the hyperlink
Prerequisite: you need a column whose content you’ll replace with the hyperlink. Its data will be still in the list, but it won’t be visible to the users, they’ll see only what you’ll define in the JSON.
The JSON formatting is accessible directly from the list view: Column settings > Format this column > Advanced mode.
The following piece of JSON code will replace the column data with a hyperlink.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "a",
"txtContent": "[$Title]",
"attributes": {
"target": "_blank",
"href": "='https://www.google.com/' + @currentField + '/' + [$Title]"
}
}The most important parts of the code are:
- “txtContent”: what text would you like to display in the column?
- “attributes”: “href”: what url should be behind the displayed text? You can see in the example that the url is built from multiple pieces.
- [$Title]: a placeholder for the Title column, you can use any other column in the list in format [$<column name>].
- @currentField: reference to the current field, instead of [$<current column name>] you can use just @currentField.
Now modify the JSON to your needs, paste it into the column formatting, and save it to turn your column into a hyperlink.
Limitations
There’re a few minor limitations though. First of all, the formatting works only in Modern experience, you’ll lose the functionality if you switch to classic.
Second, you must use column internal name as the <column name>, otherwise you’ll get an empty field without any error message. There’s no validation for the JSON.
The code above will build a hyperlink even if there’s no link on the background. To build the link only if there’s a value you should extend the code by conditional “style” attribute.
Summary
The JSON formatting gives you a lot of possibilities, not only for building hyperlinks but to create easy to read lists with extensive functionality. This post was only about building hyperlinks, so if you’d like to learn more about doing fancy stuff with your lists, check the Microsoft article on the column formatting.