“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.
Hi Tom,
first of all Thank you for those instructions. I had to research for a while before finding your solution.
Nevertheless, I still have an open question:
Is/ How is it possible to use Collums with other datatypes like integers or even choice list items?
King regards,
Josh
Hello Josh,
you can use any column in the JSON formatting, it’s not limited to a specific column type and the usage is always the same. If you want to reference the current column, use the @currentField notation, if you want to reference some other column then you have to use [$ColumnInternalName].
I’d like to make the clickable hyperlink in a Sharepoint list form. I can insert the hyperlink in the JSON header of the SP form (same as footer), but can’t do the same with the body part. So I’ve tried with calculated, hyperlink, formatted text column but can’t make it to appear clickable in a new item list form…
Hello Alonso,
I don’t think you can add a clickable hyperlink among the fields on the form.
what if the SharePoint column you are referencing to build the URL has spaces and other characters? I dont see a encodeURL function or something like that to replace these symbols in the Conditional column
Hello David,
you’re referencing the column always by its internal name that has no spaces nor special characters: https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/
Hi Tom,
I’m trying to use these links in a list, but using gallery mode view. The links works fine in the list directly, but not when I add the list view to a section on a page. The gallery view only renders totally when I editing the page, or in a first access. When I reload the page, or in other times the list is showed with all the items, but all blank, without text.
Do you have any idea what could be happening?
Hello Eduardo,
I’ve never seen that issue, in my environment it works as expected. Couldn’t that be some browser/cache issue? Or some custom code on the pages?
Thank you so much for this article. Guided me perfectly through the steps even I’ve never customized a Sharepoint Online site before.
Hi,
Very interesting.
Now I would like to open a file with the Office URI Scheme format.
I cannot add it to an hyperlink column in SharePoint. So I am trying your way.
So the href part looks like this
“href”: “=’ms-word:ofv|u|https://xxx.sharepoint.xxx/sites/my site/myfile.docx/’ + @currentField + ‘/’ + [$Title]”
This does not even display the Title in the column.
Any idea?
Or maybe you know how to use the Office URI Scheme to be able to add that to a column that creates an hyperlink.
Thanks,
Hello Christophe,
I never used Office URI Scheme in a hyperlink, I’d try to build the url step by step to make sure that it shows something (just to validate that it works), and if it stops working once you add the ‘ms-word:ofv|u|’ part then SharePoint probably won’t allow that.
Another limitation is that If you want to have a new column formatted with dependencies to another column, that other column must be visible, not hidden.
In my example I have a list with due dates or expiration dates.
On the SP start page I want to have a smaller view showing only the Item name and number of “Days left” (green within 120 days, yellow within 60 days and red within 8 days)
That “Days left” is dependent on the date column with expiry dates. The formatted column will not work if the date column is hidden.
When calculated column was allowed, a view could show correct info even if the original data columns was hidden. Now a formatted view with dependencies to another columns get unnecessary clutted.
Ahoj Tomáši,
děkuji za další skvělý a srozumitelný manuál.
Mám problém s uživatelem. Když otevře SP seznam v několika oknech, JSON formátování “zafunguje” pouze jednou. To znamená , když v druhém okně najede myší na další lokaci, která by se měla otevřít v google maps, nevidí výsledek calculated column ale pouze toto :
Celý odkaz : https://www.google.com/maps?q=52.4883566,13.4141422
Co vidí uživatel : https://www.google.com/maps?q=,
Zeměpisná šířka a délka jsou údaje ze dvou sloupců, které se spojí dohromady díky funkci CONCATE a JSON. Už jsi se něky setkal s podobným problémem ?
Ahoj,
nesetkal, tohle jsem zatim u nikoho resit nemusel.
Thanks Tom for posting this. Here is one easy to understand scenario for everyone. You have a column that contains email addresses of non-tenant people. You would like these addresses to be clickable to make it easy to send the emails. If you simply use the prefix of mailto: and append the email address that is in that column by using @currentField, the solution is done. The only issue is that the field is hard to edit if you choose edit in gridview.
{
“$schema”: “https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json”,
“elmType”: “a”,
“txtContent”: “@currentField”,
“attributes”: {
“target”: “_blank”,
“href”: “=’mailto:’ + @currentField”
}
}