SharePoint Online

Recently my customer asked me to create a dashboard, to monitor completion of tasks in a workflow. The idea was to highlight completed, on track and overdue tasks with different colors. First I thought about using SharePoint list with Modern Experience, and column formatting. But then I was asked, not only to highlight rows for tasks, but also their grouping header – as there were many tasks created per each group of tasks and the customer wanted to see status of a whole group ad-hoc, without a need to drill down.

What is also significant here is a fact, that this solution couldn’t been done using SharePoint’s default Task List, as this one doesn’t have modern UI implemented yet. My first idea was to create a separate list and using the same workflow move tasks information there.

However, this is not possible if selecting an option “Wait for tasks completion” option. Not going much into details – using a custom list was not a solution either, as because time required for items to be updated was too long.

The solution

I eventually decided to use the default Workflow Tasks list, and to add some custom JavaScript on top of it.

The purpose of a script was to go through all tasks in the, for each group, and when in any group at least one overdue task is found, then not only highlight that task’s row, but as well the grouping header.

Obviously, I thought about JSLink script. I’ve been searching for something similar over the internet, however the only solutions I were able to find were related to formatting only individual rows, not touching the headers. So I decided, the JSLink is not a solution for me and chose the jQuery.

Challenges

During developing of a script I realized, that this is not an easy job, because of the following:

  1. By default all groups are collapsed. And if they are collapsed then now list items are loaded – it physically doesn’t exist.
  2. Even if I chose the default groups behavior (in list’s view settings) to be “expanded”, then loading all tasks can take some time.
  3. Moreover when working with grouped view, SharePoint remembers state of expanded/ collapsed groups when using a list, so couple of groups can be expanded on load, when others are collapsed.
  4. When expanding a group, items are loaded asynchronously and I found no information over the internet, whether there is any event that is saying, that items are loaded.

How I did it

To be able to highlight groups’ headers I had to review every task within every group. To do that, I had to expand every group. For that I decided to add a “click” event to all “a” nodes, that has spans with a class: “ms-commentexpand-iconouter” inside:

Collapsed SharePoint list DOM element

Moreover I noticed, that “tbody” nodes, that are grouping regular items, are having a parameter called “selectablerows”, which holds an information, how many items are underneath that group (this information is added once all rows in that group are loaded):

SharePoint grouped list view - selectable rows property

I decided to write a script, that first clicks all “a” nodes, that are not expanded and then waits until all items are loaded (checking in a loop, whether number of “tr” nodes is equal sum of values from “selectablerows” parameters. When it is – function returns a resolved promise, and then actions in script moves on, to colorize rows and headers, and finally to collapse all groups.

The code

First, the code processes the table’s header row, to get names of columns and their indexes. This is then used to simply get a value of a specific cell in a row, using the fields internal name. This way this is more flexible if order of columns in a view changes.

$('tr[class*="viewheadertr"] > th').each(
        function () {
            fieldsArr[$(this).children('div[class="ms-vh-div"]').attr("name")] = counter++;
        }
);

Then it calls the function to manage the tasks – as described above, to expand, process and collapse all groups:

$.manageTasks = function () {
    var tasksHideShow = $.Deferred();

    function checkRowsLoaded() {

        var rows = 0;
        $("tbody[id^='tbod']").each(function () {
            rows += ($(this).attr("selectablerows") !== undefined ? parseInt($(this).attr("selectablerows")) : 0);
        });
        var actualRows = $("tbody[id^='tbod'] > tr").length;

        if (actualRows == rows && actualRows !== 0) {
            $.colorizeRows();
            $("span[class='ms-commentcollapse-iconouter']").parent("a").click();
            return tasksHideShow.resolve();
        }

        $("span[class='ms-commentexpand-iconouter']").parent("a").click();
        setTimeout(checkRowsLoaded, 1000);
    }
    setTimeout(checkRowsLoaded, 500);

    return tasksHideShow.promise();
}

Note, it creates a Deferred object and resolves it, once number of generated rows equals sum of numbers gathered from “selectablerows” parameters. It also calls the “colorizeRows()” function, once all groups are expanded. That function iterates tasks of every tbody node grouping them (having the id starting from “tbod”). Also, if it finds out, that at least one task is overdue, it also highlights the grouping header row:

$.colorizeRows = function () {
    var DueDateCollIdx = fieldsArr["DueDate"];
    var StatusCollIdx = fieldsArr["Status"];

    $('tbody[id^="tbod"]').each(function () {
        var isAnyTaskDelayed = false;
        $(this).children('tr[class*="itmhover"]').each(
            function () {
                var cellsInRow = [];
                cellsInRow = $(this).has("td").children("td");
                var dueDate = $(cellsInRow[DueDateCollIdx]).children("span").attr("title");
                var taskStatus = $(cellsInRow[StatusCollIdx]).text();
                
                // Task overdue
                if (new Date() > new Date(dueDate) && taskStatus !== "Completed") {
                    $(this).attr("style", "background-color:#e85050");
                    isAnyTaskDelayed = true;
                }

                // Task on track - in progress
                else if (new Date() <= new Date(dueDate) && taskStatus !== "Completed") {
                    $(this).attr("style", "background-color:#9dae11");
                }

                // Task completed
                else {
                    $(this).attr("style", "background-color:#00b0ff");
                }
            }
        );

        // If any task is delayed - highlight the grouping header row
        if (isAnyTaskDelayed) {
            var groupId = $(this).attr("id");
            var parentGroupId = (groupId.substring(0, groupId.length - 1)).replace("tbod", "titl");
            $("tbody[id='" + parentGroupId + "'] > tr").attr("style", "background-color:#e85050");
        }
    });
}

In the end it returns the promise, so that the main script can move on. After it processes all tasks and collapses all groups, a spinning wheel that is appearing since the page started to load, is being hidden:

$.manageTasks().done(function () {
        $.hideSpinner();
});

The function init(), that controls execution of other functions, is triggered by the SP function:

SP.SOD.executeOrDelayUntilScriptLoaded(function () {
    $.init();
}, "sp.js");

And that’s it!

Results

You can see how the solution works below. Note, that the bigger amount of tasks you have, the longer it will take to process them, so the longer spinning wheel is going to be visible.

Loading SharePoint tasks listExpanding formatted SharePoint tasks list

Next steps

The way the solution can be developed is related mainly to handling issues, ex. in case the network connectivity is poor and time needed for loading of all tasks is way to long, or when the connection is lost – currently, the script will be loading forever.

Also I noticed, that in case groups are split into pages, when script handles all tasks and groups on one page it when user clicks button to open another page, it loads empty. For now, I have no idea why.

Last thing is related to better UX, some tuning to the look&feel layer.

 

Please leave your comments and questions below!