How can I format a column using JSON to prepend the office URI scheme to the document link?

Question

I have a custom column formatted with JSON for SharePoint. I need all of my customization to stay, however, I do need to add the following formatting to the column:

Using boolean logic, format the link based on the filetype, prepending the Office URI Scheme to open the files in office applications based on what type of office document they are. If the files are not office files, do not prepend anything to the link and open them as is.

Example: If document is WORD (docx), prepend the Office URI Scheme of ms-word:ofe|u| to the start of the link so it forces the document to open in word, rather than save to the desktop. Here is the JSON code I need modified:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "=[$Title]",
  "attributes": {
    "target": "_self",
    "href": "=[$FileRef] + '?web=0'"
  },
  "style": {
    "text-decoration": "none",
    "color": "#000",
    "font-size": "14px"
  }
}

I asked ChatGPT, and it gave me this, however, the text doesn't display in the column:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "a",
  "txtContent": "=[$Title]",
  "attributes": {
    "target": "_self",
    "href": {
      "operator": "+",
      "operands": [
        {
          "operator": "?:",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "[$File_x0020_Type]",
                "docx"
              ]
            },
            "ms-word:ofe|u|",
            {
              "operator": "?:",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    "[$File_x0020_Type]",
                    "pptx"
                  ]
                },
                "ms-powerpoint:ofe|u|",
                {
                  "operator": "?:",
                  "operands": [
                    {
                      "operator": "==",
                      "operands": [
                        "[$File_x0020_Type]",
                        "xlsx"
                      ]
                    },
                    "ms-excel:ofe|u|",
                    {
                      "operator": "?:",
                      "operands": [
                        {
                          "operator": "==",
                          "operands": [
                            "[$File_x0020_Type]",
                            "vsdx"
                          ]
                        },
                        "ms-visio:ofe|u|",
                        ""
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        },
        {
          "operator": "+",
          "operands": [
            "[$FileRef]",
            "?web=0"
          ]
        }
      ]
    }
  },
  "style": {
    "text-decoration": "none",
    "color": "#000",
    "font-size": "14px"
  }
}

Thanks for any assistance you can provide!

Answer 1

I have tried this on our SharePoint site and found the exact same issue at our end.

I think the issue is because hyperlink URL is not starting with either http or https. Hence the hyperlink / anchor tag is not generated in DOM.

It works for other file types like PDF:

Similar threads:

This has been reported by other users as well in the past. check:

1. Allow to add Office URI Schemes in SP column of type Hyperlink
2. Allow to add Office URI Schemes in SP column of type Hyperlink
3. Allow links using the Office URI Schemes on the navigation menu