TrueJournals

Recently, I was working on a project which involved generating a spreadsheet of a bunch of changes I was making to some information.  As part of this, I wanted to color code the rows of the spreadsheet: green would mean OK, yellow would mean a change, and red would mean a problem.  I could do all this formatting manually… but I decided to find a way to make Excel do it for me.  Enter conditional formatting!

Conditional formatting in Excel is very, very robust.  For this project, I needed to change a row’s background color and font color based on three possibilities:

1. If Column E has any text, the row should be red.
2. If Column A and Column D contain different values (case-insensitive), the row should be yellow.
3. Otherwise, the row should be green.

Unfortunately for me, Excel’s conditional formatting works on a per-cell basis.  However, Excel does provide the ability to choose conditional formats based on a formula!  After some fiddling with Excel functions, I was able to generate formulas to check different cells in the row, then apply the conditional formatting based on that.  I ended up with these three formulas, executed in this order, and corresponding with the above three conditions:

1. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0,ROW()>1)
2. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))), ROW()>1)
3. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0, CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0)

Wow!  First, notice all of these start with an AND function.  AND evaluates the conditions given, performs a logical AND on all of them, and returns a boolean true or false based on that.  Explanation of the conditions:

1. Two conditions:
• CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0 checks for text in Column E. If there is nothing in the cell, the value is 0.
• ROW()>1 checks if this cell is NOT in the first row.  The first row is my header row, and I don’t need that one color coded.
2. Two conditions:
• CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))) checks if the contents of the cell in Column A and the contents of the cell in Column D are different. The <> operator is “not equals”, and Excel seems to automatically do a case-insensitive comparison.
• ROW()>1 checks if this cell is NOT in the first row.
3. Two conditions:
• CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0 ensures that Column E is empty. (Again, the value is 0 if there is nothing in the cell)
• CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0 ensures that there is some value in Column A. If there’s no value in Column A, there’s no point in any highlighting.

Alright… So, with all those conditions set up, text is automatically highlighted based on what values are entered. This made editing the spreadsheet really easy: simply type in values, and the highlighting is automatic. If I go back and change something later, the highlighting changes without me needing to do anything.

Next, however, I decided that it might be useful to bring this spreadsheet into Google Docs. This would allow me to easily share the document with my supervisor, and he can share it with the relevant people that need the information.  Upon importing the spreadsheet in Google Docs, my highlighting went away.  As it turns out, Google Docs does support conditional formatting, but not via a formula.

So, I now have two options: perform the highlighting manually (tedious), or figure out a workaround.  Since I enjoy tinkering out things, and I knew about the Google Apps scripting engine, I decided to see if I could figure out a workaround.  A lot of tinkering and documentation later, I have the following script:

function getTableRange(ss, activeSheet) {
  if(activeSheet == "renaming") {
    return ss.getRange("A2:E"+ss.getLastRow());
  } else {
    return null;
  }
}

function getColorsRenaming(cell1, cell4, cell5) {
  // Abstract this function out so we can easily add different colors if we want.
  var colors = new Array(2); // bgColor = colors[0], textColor = colors[1]

  if(cell5 != "") {
    // "Notes" cell has a value -- red
    colors[0] = "#ffc7ce";
    colors[1] = "#9c0006";
  } else if(cell1 != cell4) {
    // Cell 1 and 4 contain different values -- yellow
    colors[0] = "#ffeb9c";
    colors[1] = "#9c6500";
  } else {
    // No notes and they're the same -- green!
    colors[0] = "#c6efce";
    colors[1] = "#006100";
  }

  return colors;
}

function firstRun() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tableRange = getTableRange(ss);
  var bgColors = new Array(tableRange.getNumRows());
  var textColors = new Array(tableRange.getNumRows());
  for(var row = 0; row < tableRange.getNumRows(); row++) {
    bgColors[row] = new Array(tableRange.getNumColumns());
    textColors[row] = new Array(tableRange.getNumColumns());

    var bgColor = "#FFFFFF";
    var textColor = "#000000";

    var cell1 = tableRange.getCell(row+1, 1).getValue().toLowerCase();
    var cell4 = tableRange.getCell(row+1, 4).getValue().toLowerCase();
    var cell5 = tableRange.getCell(row+1, 5).getValue();

    var colors = getColorsRenaming(cell1, cell4, cell5);

    for(var col = 0; col < tableRange.getNumColumns(); col++) {
      bgColors[row][col] = colors[0];
      textColors[row][col] = colors[1];
    }
  }

  tableRange.setBackgroundColors(bgColors);
  tableRange.setFontColors(textColors);
}

function onEdit(event) {
  var ss = SpreadsheetApp.getActive();
  var editedCell = ss.getActiveCell();
  var editedRow = editedCell.getRow();

  var tableRange = getTableRange(ss, SpreadsheetApp.getActiveSheet().getName().toLowerCase());

  // editedRow-1 below because tableRange starts at row 2, but editedRow is absolute
  var cell1 = tableRange.getCell(editedRow-1, 1).getValue().toLowerCase();
  var cell4 = tableRange.getCell(editedRow-1, 4).getValue().toLowerCase();
  var cell5 = tableRange.getCell(editedRow-1, 5).getValue();

  if(SpreadsheetApp.getActiveSheet().getName().toLowerCase() == "renaming") {
    var colors = getColorsRenaming(cell1, cell4, cell5);
  } else {
    // Unfortunately, if row colors aren't uniform, this'll cause issues...
    // Hmmm... I'll come back to that if I decide something like that should happen
    var colors = [tableRange.getCell(editedRow-1, 1).getBackgroundColor(), tableRange.getCell(editedRow-1, 1).getFontColor()];
  }
  var bgColors = new Array(1);
  var textColors = new Array(1);
  bgColors[0] = new Array(tableRange.getNumColumns());
  textColors[0] = new Array(tableRange.getNumColumns());
  for(var col = 0; col < tableRange.getNumColumns(); col++) {
    bgColors[0][col] = colors[0];
    textColors[0][col] = colors[1];
  }

  var row = SpreadsheetApp.getActiveSheet().getRange(editedRow, 1, 1, tableRange.getNumColumns());
  row.setBackgroundColors(bgColors);
  row.setFontColors(textColors);

}

A couple things to note here:

1. The first run function exists solely to do the first run of highlighting.  Since I imported the document without the highlighting, I needed something to perform the initial functionality before going forward.
2. The onEdit function is run every time the spreadsheet is edited. It finds the highlighted cell, grabs the correct values from the row, figures out and sets the colors.
3. getTableRange and getColorsRemaining are simply helper functions, since this functionality is used in both firstRun and onEdit.

I know there’s not a lot more documentation about the code, but it’s pretty readable thanks to Google’s nice APIs.  However, this successfully updates the highlighting any time a row is edited. The onEdit function ONLY checks the row of the last edited cell for efficiency’s sake.  Even so, it’s not as smooth as Excel.  It takes a moment before the row is actually updated, but that’s fine for my sake.

By tweaking this script, it should be pretty simply to adapt this sort of conditional formatting via formula for any situation.  Unfortunately, Google doesn’t provide Excel’s formula entry for conditional formatting, so this is the next best thing.