Sample: Long hyperlinks

The default hyperlink column has a limited number of characters that can be used, and this will get you into trouble when linking to, for example, OneNote pages or any other link with a lot of parameters.

Using the following code, you can easily turn a “multiple lines of text” column into a useful list with hyperlinks.

Let’s do this in three steps, but you can also go to the Final code straightaway.

The basics

The most important part of this code is that we can turn anything into a hyperlink using the lines below, everything else is just added to make it easier and prettier.

“elmType”: “a”,
“attributes”: {
“href”: “https://www.exceltrainer.be”
}

Let’s look at the following code.

What does the code do

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "attributes": {
    "href": "@currentField",
    "target": "_blank",
    "class": "ms-fontColor-themePrimary"
  },
  "txtContent": "=if( @currentField =='','', 'Link...')",
  "style": {
    "cursor": "pointer",
    "text-decoration": "none",
    "display": "=if( @currentField =='','none', '')"
  }
}

What does the code do:

• It will create a link – “elmType”: “a”
• It uses the contents of the field itself as the address – “href”: “@currentField”
• The link will open in a new tab – “target”: “_blank”
• The text that will be displayed is the word “link…” unless there is no link provided – “txtContent”: “=if( @currentField ==”,”, ‘Link…’)”
• The cursor will change to indicate that the text is a link – “cursor”: “pointer”
• The default underline of a link will be removed – “text-decoration”: “none”
• If no link was supplied, nothing is show – “display”: “=if( @currentField ==”,’none’, ”)

Issues with the code

Although the code above will work, there are a few issues that need to be resolved:

• It is hard to change the contents of cell because each time you click in the cell to change the address of the hyperlink, it opens the hyperlink.
• The word “Link…” is not especially useful.

Alternative text

To add an alternative text to replace “link”, we need to introduce this text in the field itself and use a separator to distinguish the text from the hyperlink.
In a lot of formatting languages the [ ] are used to make that difference, so that is what I will be using here as well.

Links should be introduced in the following format [Text to display]URL, so for example [Create a menu based on page titles] https://exceltrainer.be/sharepoint/603/
Please note that the opening has no purpose, but I think it is more logical to write it this way.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "attributes": {
    "href": "=if( indexOf(@currentField , ']') == -1 , @currentField, substring(@currentField, indexOf(@currentField , ']') +1 , lastIndexOf(@currentField + '^', '^')))",
    "target": "_blank",
    "class": "ms-fontColor-themePrimary"
  },
  "txtContent": "=if( indexOf(@currentField , ']') == -1 ,@currentField ,substring(@currentField, 1 , indexOf(@currentField , ']') ) )",
  "style": {
    "cursor": "pointer",
    "text-decoration": "none",
    "display": "=if( @currentField =='','none', '')"
  }
}

If the alternative is not introduced, so only the hyperlink, then the code should be ignored. That is why both lines start with if( indexOf(@currentField , ‘]’) == -1 ,@currentField .

“href”: “=if( indexOf(@currentField , ‘]’) == -1 , @currentField, substring(@currentField, indexOf(@currentField , ‘]’) +1 , lastIndexOf(@currentField + ‘^’, ‘^’)))” – The link is the part of the cell contents that starts after on the first position after the location of (index of) the “]” plus one and will continue until it reaches the end of the contents. : lastIndexOf(@currentField + ‘^’, ‘^’).

“txtContent”: “=if( indexOf(@currentField , ‘]’) == -1 ,@currentField ,substring(@currentField, 1 , indexOf(@currentField , ‘]’) ) )” – The displayed text will start after the first position and continue until it finds the first “]”.

Being able to edit the link

To allow easier access to the edit options of the link we will add additional content using code.

As shown in the code below, we first start a “div” and use the flex – justify-content to have both elements appear on the opposite side of the line.
The first child is the original element containing the hyperlink.
The second child will contain the button with the option to change the properties.

Final Code

Links should be introduced in the following format [Text to display]URL, so for example [Create a menu based on page titles] https://exceltrainer.be/sharepoint/603/.

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "style": {
      "display": "flex",
      "justify-content": "space-between"
    },
    "children": [
      {
        "elmType": "a",
        "attributes": {
          "href": "=if( indexOf(@currentField , ']') == -1 , @currentField, substring(@currentField, indexOf(@currentField , ']') +1 , lastIndexOf(@currentField + '^', '^')))",
          "target": "_blank",
          "class": "ms-fontColor-themePrimary ms-bgColor-themeLight--hover"
        },
        "txtContent": "=if( indexOf(@currentField , ']') == -1 ,@currentField ,substring(@currentField, 1 , indexOf(@currentField , ']') ) )",
        "style": {
          "cursor": "pointer",
          "text-decoration": "none",
          "display": "=if( @currentField =='','none', 'inline-block')",
          "padding": "2px 15px",
          "border-radius": "15px"
        }
      },
      {
        "elmType": "span",
        "customRowAction": {
          "action": "editProps"
        },
        "attributes": {
          "iconName": "Edit",
          "class": "ms-fontSize-s ms-bgColor-themeLight ms-bgColor-neutralTertiary--hover"
        },
        "style": {
          "display": "inline-block",
          "cursor": "pointer",
          "margin": "0 5px",
          "padding": "4px",
          "border-radius": "15px"
        }
      }
    ]
  }