Representing File extensions using Column formatting  (with help from Copilot)

I’ve been doing quite a bit with the new SharePoint Brand Centre. One of the prebuilt libraries in the brand centre is a template library and the nice Microsoft folk have made it so that any ‘proper’ templates stored in that library will automatically appear as company templates in the related office application, specifically Word, PowerPoint or Excel. Note that you do need to set up your Brand Centre and run a Powershell script to activate your template library as an Organisation Asset first.

When I was setting this up on my company SharePoint tenant, I realised that my existing template library contains both proper templates to do what I’ve just described, and also some documents which act as ‘kind-of’ templates. I use these as guidance documents or examples of documents rather than true templates, so they mustn’t appear as template options to Office applications via the new document dialogue. Actually, that is easy; Brand Centre and Office applications are smart enough to ignore all of the documents with a .docx, .pptx and .xlsx file extension and only use proper templates, i.e. dotx, potx, .xslt. The challenge is that I need to be able to easily visually differentiate between my two different types of template documents in the library. Essentially, I wanted a way of marking files in SharePoint with specific file types beyond what the SharePoint Type column provides.

This is how I did it…

Finding a solution

Plan A: Calculated columns

Initially, I thought I should go old school; I created a new column called ‘File extension’ and preceded to play with a variety of options to extract the template file extensions into it.

The short version is this doesn’t work. I thought I might parse the file extension out of the full file name in the [Name] column with a little use of text functions in a calculated field. I wrote the short bit of code before remembering that you can’t use the [Name] column in a calculated field. Nothing else like tried worked either.

Plan B: update a column using Power Automate

I decided I might just write a Power Automate flow to update a File Extension column. How hard could it be…

While I had fun using the new Copilot flow writing capability in Power Automate, I simply couldn’t get it to work. I think it might have been a problem with the new modern designer for Flows as the Update File Properties action wouldn’t show the library columns. I could have switched to the old designer; luckily, I had a brainwave on how I should have been approaching this before I wasted any more time.

Plan C: Column Formatting

This was the right way to have approached it from the start and proved to be surprisingly easily achieved using Copilot (I hate writing JSON, it makes my head hurt).

I jumped back into my template library and then opened Copilot in the Edge browser sidebar. My first prompt and response was:

---

Me:

“Create column formatting json to add a document template icon for dotx files”

Copilot:

To add a document template icon for .dotx files in a SharePoint column using JSON formatting, you can use the following JSON code. This code will display an icon next to the file name if the file extension is .dotx.

1. Navigate to your document library:
   • Go to the SharePoint site containing your document library.
   • Open the document library where you want to add the column formatting.
2. Format the column:
   • Click on the column header you want to format (e.g., the “Name” column).
   • Select Column settings and then Format this column.
   • Choose Advanced mode.
3. Enter the JSON code:

Copy and paste the following JSON code into the formatting box:

{

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "=if(endsWith([$FileLeafRef], '.dotx'), 'Page', '')",
        "class": "ms-fontColor-themePrimary ms-font-l"
      },
      "style": {
        "padding-right": "6px",
        "display": "=if(endsWith([$FileLeafRef], '.dotx'), 'inline', 'none')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "[$FileLeafRef]"
    }
  ]
}  

4. Save the formatting:
   • Click Save to apply the formatting.

This JSON code checks if the file name ends with .dotx and, if so, displays a document icon (Page) next to the file name. You can customize the icon and styles as needed.

---

Initially I had restricted the prompt to just look at Word documents; it would have been easier to debug this simple case. It worked perfectly, adding a page icon in front of each of the .DOTX files in the name column when I applied the column formatting as instructed.

Next, I extended the formatting, asking Copilot to include Excel and PowerPoint templates; which also worked. So I decided to get fancier, colouring the icon blue, green and orange appropriately; this also worked first time. Finally, remembering that the Microsoft Fabric icon set includes icons for the main office applications, I decided to use those instead of the page icon. My final prompt looked like this (complete with typos):

---

Me:

“casn you update it again to use the word, powerpoint and excel icons, in the colours from before”

Copilot:

Sure! Here’s the updated JSON code to display specific icons for Word, PowerPoint, and Excel template files with different colors:

1. Navigate to your document library:
   • Go to the SharePoint site containing your document library.
   • Open the document library where you want to add the column formatting.
2. Format the column:
   • Click on the column header you want to format (e.g., the “Name” column).
   • Select Column settings and then Format this column.
   • Choose Advanced mode.
3. Enter the JSON code:

Copy and paste the following JSON code into the formatting box:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "=if(endsWith([$FileLeafRef], '.dotx'), 'WordDocument', if(endsWith([$FileLeafRef], '.potx'), 'PowerPointDocument', if(endsWith([$FileLeafRef], '.xltx'), 'ExcelDocument', ''))) ",

        "class": "=if(endsWith([$FileLeafRef], '.dotx'), 'ms-fontColor-blue', if(endsWith([$FileLeafRef], '.potx'), 'ms-fontColor-orange', if(endsWith([$FileLeafRef], '.xltx'), 'ms-fontColor-green', '')))"
      },
      "style": {
        "padding-right": "6px",
        "display": "=if(endsWith([$FileLeafRef], '.dotx') || endsWith([$FileLeafRef], '.potx') || endsWith([$FileLeafRef], '.xltx'), 'inline', 'none')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "[$FileLeafRef]"
    }
  ]
}

Save the formatting:

• Click Save to apply the formatting.

This JSON code checks the file extension and applies the appropriate icon and color: a blue Word icon for .dotx files, an orange PowerPoint icon for .potx files, and a green Excel icon for .xltx files.

---

Now each of my proper template documents are neatly marked with the coloured version of the application icon, as shown. It’s rather lovely, manageable and doesn’t require any complex calculated columns or workflows. I could use the same approach for pretty much any file extension I want to highlight in a library, using any type of formatting (colour, bold, borders etc.) instead of icons.

What was impressive was how easy and quick it was using Copilot as my coding buddy.