Conditional Formatting in JavaScript like Excel and other spreadsheet solutions

This is something that I am working on today. The requirements are very simple:

• Should be using only three colours.
  • Red for high.
  • Yellow for medium.
  • Green for low.
• There can be about 7+ cases as I have imagined:
  1. All unique values.
  2. One unique value.
  3. Two unique values.
  4. Three unique values. (Remember, there are only three colours).
  5. Crazy Variants:
     1. Highest number and many lower value numbers.
     2. Highest number, lowest number, and other numbers are mid-sized single value.
     3. Two highest numbers, lowest number, and other numbers are mid-sized single value.
• The requirements are like:
  • If there's one or less unique values, no colour needs to be applied.
  • If there are two unique values, the highest value should be in red.
  • If there are three unique values, the highest value should be in red, lowest in green, and others in amber.
  • If more than three unique values: use a "bucket sort" of colours logic.

Thinking of a way to establish a colour distribution across all these different cases, I come with another problem:

• I have to traverse column-wise data and not the <td>s.
• The table is rendered with DataTables.

Solutions

Starting with a pure JavaScript logic as follows:

1. Loop through the rows.
2. Find the right column.
3. Get the unique values from the list of table.
4. Create a map of the values and their colours.
5. Loop through the table and apply the colours from the map according to their value.

Creating the map of colours

Well, I would say we follow a bucket sorting method for determining how the three colours should be distributed.

1. Get all the unique values from the list of values in the column.
2. Sort all the unique values in descending order.
3. Check for the number of unique values. Only proceed if there is at least two distinct values.
4. When there are only two values, hard code the red to the highest value and don't colour the lowest value.
5. When there are three values, well, again hard code this way:
   
   • Red for the first value.
   • Yellow for the next value.
   • Green for the final value.
6. If there are more than three unique values, we use the Bucket Sorting technique. It's not exactly the bucket sorting but something similar. It goes as follows:
   
   1. Since we have three colours, divide the number of elements by three and get the integral value, which is the quotient.
   2. Let's create an array of three elements to store the three colours bearing elements' count. Currently we'll store the quotient value.
   3. Now, let's find the remainder by using the % (modulus) operator.
   4. If the remainder is a non-zero, we need to add the extra (those that are not divisible by three) one by one starting from the highest to the lowest.
   5. Technically, there can be either only 1, or 2, where if it is 1, the highest value gets incremented, and if it's 2, both the highest and medium gets incremented.
   6. With the above values, we apply the colour-coding accordingly for the unique numbers.
   7. With the above way, we add more numbers to the highest, then medium and the lowest (well, it becomes equal by the time it gets here, i.e., it's a perfect number that's divisible by three, so equally shared between three levels).
7. Once the values are identified, an object will be created with the values will become the keys of the object and their values will indicate whether it is a either of high, medium, low and the appropriate class is applied to the <td> based on its contents.

The above algorithm can be broken into pieces and I have written the following JavaScript (well commented code):

for (var c = 1; c <= 8; c++) {  
  // Working on Case #C.
  // Get all the values in an array.
  var colVals = getColumnValues("table", c);
  log(colVals);
  // Get all the unique values in the array.
  colVals = colVals.filter(function (value, index, self) {
    return self.indexOf(value) === index;
  });
  // Sorting all the values by descending order.
  colVals.sort(function (a, b) {
    return b - a;
  });
  log(colVals);
  var colourMapping = {};
  if (colVals.length < 2)
    // System Decision: Only less than 2 values found. No colouring.
  else if (colVals.length == 2) {
    // System Decision: Hard Coding Values. Two colours.
    colourMapping[colVals[0]] = "high";
    log(colVals[0] + " is coloured red.\n" + colVals[1] + " is not coloured.");
  } else if (colVals.length == 3) {
    // System Decision: Hard Coding Values. Three colours.
    colourMapping[colVals[0]] = "high";
    colourMapping[colVals[1]] = "medium";
    colourMapping[colVals[2]] = "low";
    log(colVals[0] + " is coloured red.\n" + colVals[1] + " is coloured amber.\n" + colVals[2] + " is coloured green.");
  } else {
    // System Decision: Implementing Bucket Method.
    var quotient = parseInt(colVals.length / 3);
    var mapping = [quotient, quotient, quotient];
    for (var m = 0, reminder = colVals.length % 3; reminder > 0; reminder--, m++)
      mapping[m]++;
    // The first (mapping[0]) elements will be red.
    // The next (mapping[1]) elements will be amber.
    // The rest (mapping[2]) elements will be green.
    for (var i = 0; i < mapping[0]; i++)
      colourMapping[colVals[i]] = "high";
    for (; i < mapping[0] + mapping[1]; i++)
      colourMapping[colVals[i]] = "medium";
    for (; i < mapping[0] + mapping[1] + mapping[2]; i++)
      colourMapping[colVals[i]] = "low";
  }
  if (Object.keys(colourMapping).length) {
    // System Decision: Verbose:
    log(colourMapping);
    // Applying the colours for column C!
    addClassToColumnCells ("table", c, colourMapping);
    // System Decision: Successfully applied colours for column C.
  }
}

Utility Functions

I was planning to make my life easier, so added some helper functions for the following:

• Logging the activity to an On Screen Debugger.
• Getting the column values as an array from a table selector.
• Adding a class based on the value (bespoke code for this).

On Screen Debugger

This one formats the messages similar to our console's log, warn and error, we have normal message, a system message and a section completion message.

Also, if the type of the given variable isn't a primitive type, it uses special rendering for arrays and objects.

pre {border: 1px solid #ccc; padding: 3px; font-family: 'Monaco', 'Consolas', monospace;}  
pre span {color: #f90; text-shadow: 0 0 1px rgba(0,0,0,0.5);}  

function log (stuff) {  
  if (typeof stuff == "object" && !(stuff instanceof Array))
    document.querySelector("pre").innerHTML += "\n" + JSON.stringify(stuff, null, 2);
  else if (stuff instanceof Array)
    document.querySelector("pre").innerHTML += "\n[" + stuff.join(", ") + "]";
  else
    document.querySelector("pre").innerHTML += "\n" + stuff;
}

Fetch Column Values

This takes in two parameters. One of the most inefficient scripts I have ever written, which loops through the whole list of rows and finds the column and fetches the integral value and pushes into an array and finally returns the array.

function getColumnValues (tableSelector, colN) {  
  colN = colN || 1;
  colN--;
  var myTable = document.querySelector(tableSelector);
  var values = [];
  for (var i = 1, n = myTable.rows.length; i < n; i++)
    // Assuming all the values are numbers. Don't use this function for 
    values.push(parseFloat(myTable.rows[i].cells[colN].innerHTML.trim()));
  return values;
}

This uses the HTML's Table API to get the values. The notable API functions that are used here are HTMLTableElement.rows[] and HTMLTableRowElement.cells.

Function to Add Class to the Rows

The following function just walks through the rows and gets the cells[n]th value.

function addClassToColumnCells (tableSelector, colN, valObj) {  
  colN = colN || 1;
  colN--;
  var myTable = document.querySelector(tableSelector);
  for (var i = 1, n = myTable.rows.length; i < n; i++) {
    curTD = myTable.rows[i].cells[colN];
    curVal = curTD.innerHTML.trim();
    if (typeof valObj[curVal] !== "undefined")
      curTD.classList.add(valObj[curVal]);
  }
}

Final Code

The final code can be played around here:

See the Pen EvVVbX by Praveen Kumar (@praveenscience) on CodePen.

My next challenge is implementing the same thing in DataTable.