TrueJournals

Conditional Formatting in Google Docs

by on Jun.05, 2012, under technology

Recently, I was working on a project which involved generating aspreadsheetof 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, veryrobust. 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 notvia 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 &lt; 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 &lt; 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 &lt; 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, thissuccessfullyupdates 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.

:,

6 Comments for this entry

  • Prakhar

    Hi,

    Although you have given the script, you have not written anywhere how to use it. I have an open spreadsheet in google docs. Plz tell where and how do I have to write this script?

  • TrueJournals

    Prakhar,
    When you have a spreadsheet open, click the “Tools” menu and select “Script editor…”. You’ll also have to setup the on edit trigger — Resources > Current script triggers. Run onEdit for the event Spreadsheet, On Edit.

  • Vitt4300

    keep getting Missing ) in parenthetical. (line 35

  • TrueJournals

    @Vitt4300 Ahh! Seems like my blog doesn’t like the code. The < on line 35 should be replaced with a “less than” symbol ( < ). It looks like this is the only place that substitution was made.

  • Vanessa

    Hi there,

    Thanks for sharing this script! I also got the issue with line 35. I can’t see the symbol < symbol anywhere on that line so I can't replace it.

    This is what I can see:

    for(var row = 0; row < tableRange.getNumRows(); row++) {

    Any idea what I'm missing here?

    Many thanks!

  • Vanessa

    Well as soon as I posted the message, I looked at my post and could see the (<)

    For some reason it was showing as (alt;)on the script but pasting it here sorted the issue.

    Thanks

1 Trackback or Pingback for this entry

Leave a Reply

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...