SharePoint Online List view formatting : Highlight a row with color if today is the value of a datefield

This is not as straightforward as one would assume.  If you try to compare the value of the date field to the @now variable, it doesn't work.  You have to do a bit of comparison with subtracting milliseconds from the @now.  I had a date field called 'Est DC' in a SharePoint list, see below for my solution.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "additionalRowClass": "=if([$Est_x0020_DC]  <= @now  && [$Est_x0020_DC]  >= @now - 86400000, 'sp-field-severity--good', '')"
}

Hope that helps someone.

Creating the Status Page, Part 3: Displaying the Graph

We are still not quite through with all the web requests to get data.  We still need to do a few rest calls to the SharePoint list to return the data for the graph.  This graph is a click through graph so we will need a couple of requests for data to get it all.

We need to know if this is a graph that has been clicked on or just the default graph.  I do this by setting some querystring parameters in the url of the page and checking the values.

let teamName = getQueryVariable("team");

let dateVar = getQueryVariable("dateVar");

I then determine which graph data I'm going to need and crafting the URI.

if (dateVar == null)
{
formattedURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbyTitle('Daily Service Review')/Items/?$top=9999&$select=ID,Team,DSR_x0020_Color,DSR_x0020_Letter_x0020_Grade,DSR_x0020_Date&$filter= DSR_x0020_Date ge datetime'" + formatDate(subDays(new Date(), 60)) + "T00:00:00Z' and DSR_x0020_Date le datetime'" + formatDate(addDays(new Date(), 1)) + "T00:00:00Z'"; 
}
else{
formattedURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbyTitle('Daily Service Review')/Items/?$select=ID,Team,DSR_x0020_Color,DSR_x0020_Letter_x0020_Grade,DSR_x0020_Date&$filter= DSR_x0020_Date ge datetime'" + formatDate(addDays(new Date(dateVar), 1)) + "T00:00:00Z' and DSR_x0020_Date le datetime'" + formatDate(addDays(new Date(dateVar), 2)) + "T00:00:00Z'"; 

}

A couple of things about the above code.  SharePoint doesn't normally return more than 100 items from a REST call.  the "top=9999" brings back the few hundred items that are needed for the graph. The first part of the If statement grabs the last 60 days worth of data, where as the else statement just grabs one day of information.  I had a bit of trouble getting the dates to format and return correctly for the SharePoint API, but you can just use what I did and it should work fine.

Next the ajax call.  I loop through the results, do some averaging, and push the results to individual arrays for use later.

$.ajax({
url: formattedURL,
method: "GET",  
    headers: { "Accept": "application/json; odata=verbose" },  
    success: function (data) {  
dataObject = data.d.results;

let startDate = new Date(dataObject[0].DSR_x0020_Date).format("M/d");
let tempTeam = "No Team Yet";
Array.prototype.forEach.call(data.d.results, function(dataObject){

        if (currentDay != new Date(dataObject.DSR_x0020_Date).format("M/d") && currentDay != null)
        {
if (tempColor != "Grey")
{

teamObject.push(tempTeam);
dsrColor.push(tempColor);
dsrLetter.push(convertedLetter);
dsrDate.push(tempDate);
       
temp = daysTotal/numOfAreas;
dsrAverage.push(Math.floor(temp));
//console.log(`Day: ${currentDay} Days Running Total: ${daysTotal} and Number of Areas: ${numOfAreas} Current Team: ${tempTeam} DSR Average: ${temp}`);
            }
daysTotal = 0;
numOfAreas = 0;

        }

        
currentDay = new Date(dataObject.DSR_x0020_Date).format("M/d");
tempTeam = dataObject.Team;
tempColor = dataObject.DSR_x0020_Color;
tempLetter = dataObject.DSR_x0020_Letter_x0020_Grade;
tempDate = new Date(dataObject.DSR_x0020_Date).format("M/d");
convertedLetter = convertLetterToNumber(dataObject.DSR_x0020_Letter_x0020_Grade);
daysTotal = daysTotal + convertedLetter;
numOfAreas = numOfAreas + 1;
if (tempColor != "Grey")
{
switch (tempTeam)
{
case "Server":
dsrServer.push(convertedLetter);
dsrServerID.push(dataObject.ID);
break;
case "Network":
dsrNetwork.push(convertedLetter);
dsrNetworkID.push(dataObject.ID);
break;
case "Security":
dsrSecurity.push(convertedLetter);
dsrSecurityID.push(dataObject.ID);
break;
case "Database":
dsrDatabase.push(convertedLetter);
dsrDatabaseID.push(dataObject.ID);
break;
case "Data Movement":
dsrDataMovement.push(convertedLetter);
dsrDataMovementID.push(dataObject.ID);
break;
case "Collaboration":
dsrCollaboration.push(convertedLetter);
dsrCollaborationID.push(dataObject.ID);
break;
case "Voice":
dsrVoice.push(convertedLetter);
dsrVoiceID.push(dataObject.ID);
break;
case "Monitoring":
dsrMonitoring.push(convertedLetter);
dsrMonitoringID.push(dataObject.ID);
break;
case "iSeries/pSeries":
dsriSeries.push(convertedLetter);
dsriSeriesID.push(dataObject.ID);
break;
case "EUC":
dsrEUC.push(convertedLetter);
dsrEUCID.push(dataObject.ID);
break;
case "DC Operations":
dsrDCOps.push(convertedLetter);
dsrDCOpsID.push(dataObject.ID);
break;
}
}            
});
if (tempColor != "Grey")
{
teamObject.push(tempTeam);
dsrColor.push(tempColor);
dsrLetter.push(convertedLetter);
dsrDate.push(tempDate);
       
temp = daysTotal/numOfAreas;
dsrAverage.push(Math.floor(temp));
            }
    //backfill
                for(i=spCount.length; i < dsrAverage.length; i++){
                    spCount.push(0);
                    spOpen.push(0);
                    spClosed.push(0);
                }
                spCount.reverse();
                spOpen.reverse();

                spClosed.reverse();    

Finally, we will use some jQuery to set up the graph.  I included the HighCharts JavaScript library in my SharePoint masterpage, but you may want to just add a call to in in your code.

if (dateVar==null)
{
$('#backButton').hide();
$('#container').highcharts({
chart: {
type: 'line'
},
title: {
text: 'Technology Services Daily Grade'
},
xAxis: {
categories: dsrDate
},
yAxis: [{
title: {
text: 'Grade'
}
},{
        title: {
text: 'Tickets',
            style: {
                color: '#ff00ff'
            }
},
        labels: {
            style: {
                color: '#ff00ff'
            }
        },
        opposite: true
    }],

series: 
[{
name: '# of Zendesk Tickets',
data: spCount,
visible: true,
color: '#ff00ff',
        yAxis: 1,
cursor: 'pointer',
            events: {
                click: function (event) {
                    location.href = "https://yourzendeskuri/agent/reporting/overview/period:0/selected:created_count,solve_count";
}
}
},
    {
name: '# of Open Tickets',
data: spOpen,
visible: true,
color: '#f7b6f9',
        yAxis: 1,
cursor: 'pointer',
            events: {
                click: function (event) {
                    location.href = "https://yourzendeskuri/agent/reporting/overview/period:0/selected:created_count,solve_count";
}
}
},
    {
name: '# of Solved Tickets',
data: spClosed,
visible: true,
color: '#734575',
        yAxis: 1,
cursor: 'pointer',
            events: {
                click: function (event) {
                    location.href = "https://yourzendeskuri/agent/reporting/overview/period:0/selected:created_count,solve_count";
}
}
},
    {
name: 'Average Grade for All Areas',
data: dsrAverage,
visible: true,
color: '#000000',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Server",
data: dsrServer,
color: '#18476c',
visible: false,
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Network",
data: dsrNetwork,
visible: false,
color: '#26434b',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Security",
data: dsrSecurity,
visible: false,
color: '#0072ad',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Database",
data: dsrDatabase,
visible: false,
color: '#51af46',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Data Movement",
data: dsrDataMovement,
visible: false,
color: '#7ab642',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Collaboration",
data: dsrCollaboration,
visible: false,
color: '#007d6a',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Voice",
data: dsrVoice,
visible: false,
color: '#f0a93f',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "Monitoring",
data: dsrMonitoring,
visible: false,
color: '#fac922',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "iSeries/pSeries",
data: dsriSeries,
visible: false,
color: '#d74639',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "EUC",
data: dsrEUC,
visible: false,
color: '#4e4f4f',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
},
{
name: "DC Operations",
data: dsrDCOps,
visible: false,
color: '#c4c2bd',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Pages/home.aspx?dateVar=" + formatDate(new Date(combinedDate));
}
}
}]
});
}
else
{
$('#backButton').show();
$('#container').highcharts({
chart: {
type: 'column'
},
title: {
text: 'Technology Services Daily Grade by Area'
},
xAxis: {
categories: dsrDate
},
yAxis: {
title: {
text: 'Grade'
}
},

series: 
[
{
name: "Server",
data: dsrServer,
color: '#18476c',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrServerID + "&Source=" + window.location.href;
}
}
},
{
name: "Security",
data: dsrSecurity,
color: '#0072ad',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrSecurityID + "&Source=" + window.location.href;
}
}
},
{
name: "Network",
data: dsrNetwork,
color: '#26434b',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrNetworkID + "&Source=" + window.location.href;
}
}
},
{
name: "Database",
data: dsrDatabase,
color: '#51af46',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrDatabaseID + "&Source=" + window.location.href;
}
}
},
{
name: "Data Movement",
data: dsrDataMovement,
color: '#7ab642',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrDataMovementID + "&Source=" + window.location.href;
}
}
},
{
name: "Collaboration",
data: dsrCollaboration,
color: '#007d6a',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrCollaborationID + "&Source=" + window.location.href;
}
}
},
{
name: "Voice",
data: dsrVoice,
color: '#f0a93f',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrVoiceID + "&Source=" + window.location.href;
}
}
},
{
name: "Monitoring",
data: dsrMonitoring,
color: '#fac922',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrMonitoringID + "&Source=" + window.location.href;
}
}
},
{
name: "iSeries/pSeries",
data: dsriSeries,
color: '#d74639',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsriSeriesID + "&Source=" + window.location.href;
}
}
},
{
name: "EUC",
data: dsrEUC,
color: '#4e4f4f',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrEUCID + "&Source=" + window.location.href;
}
}
},
{
name: "DC Operations",
data: dsrDCOps,
color: '#c4c2bd',
cursor: 'pointer',
            events: {
                click: function (event) {
var d = new Date();
var n = d.getFullYear();
var combinedDate = event.point.category + "/" + n;
                    location.href = "https://yoursharepointsite/Lists/Daily%20Service%20Review/DispForm.aspx?ID=" + dsrDCOpsID + "&Source=" + window.location.href;
}
}
}


]

});
}
},  
error: function (err) {  
console.log("error: " + JSON.stringify(err));  
}  

}); 

Series Links

• Overview
• Part 1: Powershell
• Part 2: Ajax web calls
• Part 3: Displaying the Graph