Customise SharePoint Online Columns Using JSON – Column Formatter
Recently, the column formatting feature rolled-out to my tenant and I was really excited to explore this. We have few sites where SharePoint site has been used as a Project Management tool with project and risk registers. In these lists JavaScript / client side rendering (JS Link) was used to render the status and risk score columns. Because JS Link is not supported in the modern experience, we had to use classic experience to ensure columns are rendering properly.
If you are in the first release program you should see the ‘format this column’ link in the menu which you right click the desire column:
Or you can also reach to Custom Formatter text-box by clicking edit column under list setting. I noticed that for currency data type column ‘Format this column’ was not available so I had to use edit column option.
When you click ‘format this column’. A right hand panel will open where users can enter JSON reference to format the column. There are some good example provided to help users create their own formatting. It is certainly not designed for the end-users but rather power-users / developers.
Here are the screenshots for my project lists. The JSON files are available on my GitHub repository feel free to use / improve.
Project Register where ‘Current Status’ column is formatted based on the RYAG status.
Risk register where there are two columns formatted i) ‘Risk Score’ (a calculated column) is formatted based on the condition and ii) ‘Risk Status Level’ (Internal field name is ‘Change’) is formatted with icons / colors. You can use other icon from Office Graph .
I hope to explore and learn more options in the coming weeks. Meanwhile, happy SharePointing!
22 Comments
Marc D Anderson · November 11, 2017 at 14:34
Nice. If you come up with anything you think might be of general use (samples, tips, tricks), be sure to contribute it to the repo at https://github.com/SharePoint/sp-dev-column-formatting.
M.
SharePointer · November 11, 2017 at 15:14
Thanks Marc! I would love to contribute. JSON files are available here: https://github.com/sohailmerchant/spo-columnformatting. I suppose I need to find a way to upload my these files to this existing repo.
Ofer Gal · June 2, 2019 at 18:24
Did anyone succeeded in coloring a Calendar with JSON?
I Tried it did not work See https://stackoverflow.com/questions/56408487/title-column-of-event-does-not-show-the-json-formatting-in-a-calendar-list-in-365
Chris · November 20, 2017 at 15:59
Does this work in classic view?
SharePointer · November 20, 2017 at 20:34
As far as I know no. For classic view you will have to use the old CSR technique with JavaScript.
Rob Bradley · November 28, 2017 at 22:39
I have used some JSON formatting in a List and it works great, but then when adding the list view web part to a modern page, the formatting doesn’t work. Is there a way for the list view web par to adopt the list formatting?
SharePointer · December 1, 2017 at 20:26
Hi Rob, This feature is not yet supported, but I understand that Microsoft is working on this.
Xavier Filion-Simon · December 6, 2017 at 19:50
So disappointing, I work so hard on my JSON format and nobody can see it as a WebPart. Sharepoint can be so frustrating….
SharePointer · December 8, 2017 at 20:57
I am using CSR for classic and also added JSON so in case user get to ‘modern experience’ they have similar experience.
Jessica · January 11, 2018 at 13:22
Hi there,
Are you able to use JSON to format the width of a column in a document library? I want to make my first column ‘Title’ wider, so that every time I go into my document library, I don’t need to adjust the size of this column to see the full name of my documents
SharePointer · January 12, 2018 at 17:24
I don’t think it is possible as there is a set width on the header of the columns which acts as the max value. You can try this:
{"$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"width": "300px",
"white-space": "normal"
}
Iain Thomson · December 10, 2018 at 15:10
Hi Jessica,
I would suggest you don’t use JSON for this, there is no need. if you want to adjust the width of a column then do so then go to the same place you create a view, then save all items as all items this will save your column width, at least for the all items view.
I hope that you would have got this sorted by now though.
Jan · February 1, 2018 at 15:18
Hi! I am trying to use some of above code for an email template that my colleagues can use to send out bookings, using a mailto-link. The email subject and body are populated by the booking info (e.g. service type, number of people, etc.) written in the same line. It all works fine, except for the date/time field. How can I get the date (written in the column “Date”) to appear in the email template? This is what I have so far:
{
“$schema”: “http://columnformatting.sharepointpnp.com/columnFormattingSchema.json”,
“debugMode”: true,
“elmType”: “div”,
“children”: [
{
“elmType”: “span”,
“style”: {
“padding-right”: “8px”,
“font-weight”: “bold”
},
“txtContent”: “@currentField.lookupValue”
},
{
“elmType”: “a”,
“attributes”: {
“iconName”: “EditMail”,
“class”: “sp-field-quickActions”,
“href”: {
“operator”: “+”,
“operands”: [
“mailto:”,
“[$Supplier_x003a_Email]”,
“?subject=Booking: “,
“@currentField.lookupValue”,
” – “,
“[$Service_x0020_Type]”,
“&body=Hello,\r\n\r\nPlease book and confirm the following:\r\n\r\n”,
“Booking ref.: “,
“@currentField.lookupValue”,
“\r\n”,
“Group: “,
“[$Title]”,
“\r\n”,
“Service: “,
“[$Service_x0020_Type]”,
“\r\n”,
“PAX: “,
“[$PAX_x0020_booked]”,
“\r\n”,
“\r\nThanks.”
]
}
}
}
]
}
It is my understanding that I have to add this somewhere:
{
“elmType”: “div”,
“txtContent”: {
“operator”: “toLocaleString()”,
“operands” : [“[$Date]”]
}
}
Any help would be greatly appreciated.
SharePointer · February 9, 2018 at 21:21
Is it giving any error or doesn’t work? I think you will need to convert date into string.
zazzzi40 · February 7, 2018 at 13:27
When using the above example for column width I get the following error, Please enter valid column-formatting JSON.
SharePointer · February 9, 2018 at 21:18
This means that you don’t the correct JSON. You can validate JSON to ensure that you are not missing any brackets. Happy to review the code if you can share.
Angama · February 7, 2018 at 20:53
If i may share there is a great tool to try column formatting, done in React !
https://thechriskent.com/2017/12/21/column-formatting-client-side-web-part-column-formatter/
I’ve also shared column formatting available options with screenshots in one glance (source : github)
http://bit.ly/sponlinecolumnformatting
Hope it helps
Jeff ANGAMA
SharePointer · February 9, 2018 at 21:26
Yes, I am aware of the tool @thechriskent has developed. Really useful.
Luis · February 21, 2018 at 02:16
Hi mate, any idea in how to validate against an empty column value using json? I am using “==” but it does not work as the column is null “Failure: Title was not found on the data object”
SharePointer · March 5, 2018 at 19:25
Please could you share exact code. You will need operator = and empty string could be check with “”.
Bimal · June 18, 2019 at 12:24
Can we create a template for the column formats? so that we don’t have write/paste the JSON file, instead just check the available template? If yes, how?
SharePointer · August 20, 2019 at 13:15
You can’t do that but you can create a site column with formatting which can be used across the site, so no need to copy JSON again and again.