Forum Discussion
Gantt Formatting in Sharepoint list
Hi All
Topic that often does the rounds, in relation to a Gantt style view in modern Sharepoint.
I have a that I try to track their capacity and ability to take on more work throughout the year. To accomplish this, I've always used a Gantt-style view (without predecessors or successors) which displays either the staff member name and then their projects over the year (displayed on a side-scrolling timeline) or a list of all projects and then a filter applied to the staff member, again on a side scrolling timeline. This allows me at a quick glance to see who has what on in any given month.
I've previously set up a Gantt view following the instructions sourced from https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Project-Tracker-SharePoint-and-Teams/td-p/1788102, however, while this was great at seeing what the entirety of the team had at any given time, for my purposes and looking at individuals, this doesn't allow filtering and certainly not by an individual.
With that in mind, I looked to the instructions created by Geert_de_Kooter at https://exceltrainer.be/sharepoint/989/ and this really got me on the right lines, though, as it worked really well for a traditional Gantt, I looked to do some more tweaked and came to this Yearly Gantt Chart which got me even closer:
however, as you can see, my result displayed items only on their relative months, damnit! So close, but still so far.
I have however, been able to go in and amend the JSON code which I've now got looking like this:
RESULT! But no :(, I've managed to get it looking good with the months and years (I'm still trying to work out if I can add a new year dynamically dependent on the highest date within the end field, but that's a different question) but alas, the start position of the bars, isn't aligning correctly with the month and year.
The code I'm using is:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "column",
"margin": "5px"
},
"children": [
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "column",
"justify-content": "space-between",
"width": "100%",
"margin-bottom": "0px"
},
"children": [
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "row",
"justify-content": "space-between",
"width": "100%",
"margin-bottom": "0px"
},
"children": [
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "column",
"width": "50%",
"text-align": "left",
"border-left": "1px solid lightgray",
"padding-left": "5px",
"margin-bottom": "0px",
"height": "50px",
"padding-top": "0px",
"padding-bottom": "0px"
},
"children": [
{
"elmType": "span",
"txtContent": "2023"
},
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "row",
"justify-content": "space-between",
"width": "100%",
"margin-bottom": "10px"
},
"children": [
{ "elmType": "span", "txtContent": "Jan", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Feb", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Mar", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Apr", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "May", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Jun", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Jul", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Aug", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Sep", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Oct", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Nov", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Dec", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } }
]
}
]
},
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "column",
"width": "50%",
"text-align": "left",
"border-left": "1px solid lightgray",
"padding-left": "5px",
"margin-bottom": "0px",
"height": "50px",
"padding-top": "0px",
"padding-bottom": "0px"
},
"children": [
{
"elmType": "span",
"txtContent": "2024"
},
{
"elmType": "div",
"style": {
"display": "flex",
"flex-direction": "row",
"justify-content": "space-between",
"width": "100%",
"margin-bottom": "3px"
},
"children": [
{ "elmType": "span", "txtContent": "Jan", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Feb", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Mar", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Apr", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "May", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Jun", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Jul", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Aug", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Sep", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Oct", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Nov", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } },
{ "elmType": "span", "txtContent": "Dec", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "10px", "padding-bottom": "10px" } }
]
}
]
}
]
}
]
},
{
"elmType": "div",
"style": {
"display": "flex",
"width": "100%",
"border": "2px solid",
"height": "20px"
},
"attributes": {
"class": "ms-fontColor-themePrimary"
},
"children": [
{
"elmType": "div",
"style": {
"position": "relative",
"cursor": "pointer",
"max-width": "=((Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date([$Start]))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'",
"width": "=((Number(Date([$End])) - Number(Date([$Start]))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'",
"left": "=((Number(Date([$Start])) - Number(Date('' + getYear([$Start]) + '/01/01'))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'"
},
"attributes": {
"class": "ms-bgColor-themePrimary ms-bgColor-themeTertiary--hover"
},
"customCardProps": {
"directionalHint": "topCenter",
"isBeakVisible": true,
"openOnEvent": "hover",
"formatter": {
"elmType": "div",
"style": {
"display": "flex",
"align-items": "center",
"justify-content": "center",
"width": "250px",
"height": "30px",
"margin": "10px",
"font-weight": "bold"
},
"attributes": {
"class": "ms-fontSize-l"
},
"children": [
{
"elmType": "div",
"children": [
{
"elmType": "span",
"txtContent": "[$Start.displayValue]"
},
{
"elmType": "span",
"style": {
"margin-left": "5px",
"margin-right": "5px"
},
"txtContent": "-"
},
{
"elmType": "span",
"txtContent": "[$End.displayValue]"
}
]
}
]
}
}
}
]
}
]
}
and I think the problem may lie in the section:
{
"elmType": "div",
"style": {
"position": "relative",
"cursor": "pointer",
"max-width": "=((Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date([$Start]))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'",
"width": "=((Number(Date([$End])) - Number(Date([$Start]))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'",
"left": "=((Number(Date([$Start])) - Number(Date('' + getYear([$Start]) + '/01/01'))) / (Number(Date('' + getYear([$Start]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'"
},
But this is the first time, I've ever looked at JSON coding (in honesty, I'm non-technical and have never looked at any coding before) and I'm very much stuck.
Can anyone help?
P.s. yes I am aware I could use other tools however, myself and the team have effective
After doing much fiddling, I'm incredibly close. The below code is displaying everything correctly for a 2 year period, progress bar correctly aligned with months and years however as soon as I go to add a third year, everything falls out of alignment, any ideas?
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "display": "flex", "flex-direction": "column", "margin": "5px" }, "children": [ { "elmType": "div", "style": { "display": "flex", "flex-direction": "column", "align-items": "center", "width": "100%", "margin-bottom": "5px" }, "children": [ { "elmType": "div", "style": { "display": "flex", "flex-direction": "row", "justify-content": "space-between", "align-items": "center", "width": "100%", "margin-bottom": "0px" }, "children": [ { "elmType": "div", "style": { "display": "flex", "flex-direction": "column", "width": "50%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "margin-bottom": "0px", "height": "40px", "padding-top": "0px", "padding-bottom": "0px" }, "children": [ { "elmType": "span", "txtContent": "=getYear([$Start])" }, { "elmType": "div", "style": { "display": "flex", "flex-direction": "row", "justify-content": "space-between", "width": "100%", "margin-bottom": "3px" }, "children": [ { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jan" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Feb" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Mar" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Apr" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "May" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jun" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jul" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Aug" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Sep" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Oct" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Nov" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Dec" } ] } ] }, { "elmType": "div", "style": { "display": "flex", "flex-direction": "column", "width": "50%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "margin-bottom": "0px", "height": "40px", "padding-top": "0px", "padding-bottom": "0px" }, "children": [ { "elmType": "span", "txtContent": "=getYear([$Start]) + 1" }, { "elmType": "div", "style": { "display": "flex", "flex-direction": "row", "justify-content": "space-between", "width": "100%", "margin-bottom": "3px" }, "children": [ { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jan" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Feb" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Mar" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Apr" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "May" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jun" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Jul" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Aug" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Sep" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Oct" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Nov" }, { "elmType": "span", "style": { "width": "100%", "text-align": "left", "border-left": "1px solid lightgray", "padding-left": "5px", "padding-top": "2px", "padding-bottom": "2px" }, "txtContent": "Dec" } ] } ] } ] } ] }, { "elmType": "div", "style": { "display": "flex", "width": "100%", "border": "2px solid", "height": "20px" }, "attributes": { "class": "ms-fontColor-themePrimary" }, "children": [ { "elmType": "div", "style": { "position": "relative", "cursor": "pointer", "max-width": "=((Number(Date('' + getYear([$End]) + '/12/31')) - Number(Date([$Start]))) / (Number(Date('' + getYear([$End]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'", "width": "=((Number(Date([$End])) - Number(Date([$Start]))) / (Number(Date('' + getYear([$End]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'", "left": "=((Number(Date([$Start])) - Number(Date('' + getYear([$Start]) + '/01/01'))) / (Number(Date('' + getYear([$End]) + '/12/31')) - Number(Date('' + getYear([$Start]) + '/01/01')))) * 100 + '%'" }, "attributes": { "class": "ms-bgColor-themePrimary ms-bgColor-themeTertiary--hover" }, "customCardProps": { "directionalHint": "topCenter", "isBeakVisible": true, "openOnEvent": "hover", "formatter": { "elmType": "div", "style": { "display": "flex", "align-items": "center", "justify-content": "center", "width": "250px", "height": "30px", "margin": "10px", "font-weight": "bold" }, "attributes": { "class": "ms-fontSize-l" }, "children": [ { "elmType": "div", "children": [ { "elmType": "span", "txtContent": "[$Start.displayValue]" }, { "elmType": "span", "style": { "margin-left": "5px", "margin-right": "5px" }, "txtContent": "-" }, { "elmType": "span", "txtContent": "[$End.displayValue]" } ] } ] } } } ] }, { "elmType": "div", "style": { "display": "flex", "width": "100%", "border": "1px solid", "height": "8px" }, "attributes": { "class": "ms-fontColor-themePrimary" }, "children": [ { "elmType": "div", "style": { "position": "relative", "width": "= '1' + '%'", "left": "= floor(((Number(@now)-Number(Date('' + getYear([$Start]) + '/1/1')))/(1000*60*60*24))/(365 * (getYear([$End]) - getYear([$Start]) + 1)) * 100) + '%'" }, "attributes": { "class": "ms-bgColor-sharedRed10" } } ] } ] }
