Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Triggers
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
  • Get help with flow
Menu

How to create calculated hyperlink in SharePoint Online list

Posted on October 4, 2020April 14, 2021 by Tom

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


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

10 thoughts on “How to create calculated hyperlink in SharePoint Online list”

  1. Josh says:
    January 18, 2022 at 4:45 pm

    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

    Reply
    1. Tom says:
      January 19, 2022 at 9:59 am

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

      Reply
  2. Alonso says:
    May 19, 2022 at 3:59 pm

    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…

    Reply
    1. Tom says:
      May 25, 2022 at 4:21 pm

      Hello Alonso,
      I don’t think you can add a clickable hyperlink among the fields on the form.

      Reply
  3. David says:
    May 19, 2022 at 7:03 pm

    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

    Reply
    1. Tom says:
      May 25, 2022 at 4:22 pm

      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/

      Reply
  4. Eduardo says:
    July 13, 2022 at 7:29 pm

    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?

    Reply
    1. Tom says:
      July 26, 2022 at 9:45 pm

      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?

      Reply
  5. GB says:
    September 12, 2022 at 2:45 pm

    Thank you so much for this article. Guided me perfectly through the steps even I’ve never customized a Sharepoint Online site before.

    Reply
  6. Christophe Raucq says:
    January 31, 2023 at 2:54 pm

    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,

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


NEW! Master the HTTP requests to SharePoint with a new cheat sheet!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Use Power Automate to forward Outlook events upon registrationJanuary 29, 2023
  • Why the condition is false for the same numbers (Power Automate)January 25, 2023
  • How to forward event invitation to other calendar (Power Automate)January 22, 2023
  • Run ‘For selected item’ flow from non-default environment (Power Automate)January 18, 2023
  • Hide button in SharePoint list after Power Automate flow startedJanuary 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes