JavaScript Code

How to color rows based on values in Google Spreadsheets

Well, if it isn't completely obvious – I haven't written a post on my blog in a very long time. Call it writer's cramp, call it being too busy, whatever, I guess I've just been too lazy!

Anyhow, it is my goal to get back to writing regularly. I'll be posting:

  1. more coding tips
  2. Internet Marketing tips
  3. games to play
  4. random stuff (like food, ok?)

So, without further delay, here's my first new programming tip. It's for Google Spreadsheets, the incredibly powerful online spreadsheet application. Not only is it extremely powerful, collaborative and free, it also allows you to make it even more powerful by using scripts. This is entirely based on JavaScript.

We needed a script that would change the font color in each row based on the value in the last column of the row. You could use this for grading, scoring, anything – it just gives you a quick way to visually differentiate which entries score higher/lower. So, here's the code to put into your Code Editor:


function onOpen(){
  var menuEntries = [ 
    {name: "Colorize", functionName: "changeColor_"},
    ];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Colorize", menuEntries);
  changeColor_();
}
 
function changeColor_() {
  var doc = SpreadsheetApp.getActiveSheet();
  var totalRow = doc.getLastRow();
  var totalColumn = doc.getLastColumn(); 
  var cell;
  
  for (var xx=2; xx<=totalRow; xx++)
  { 
    value = doc.getRange(xx,totalColumn).getValue();
    
    var thecolor = "#000";
    if(value<=10)
    {
      thecolor = "#ccc";
    }
    else if(value>=80&&value<100)
    {
      thecolor = "#060";
    }
    else if(value==100)
    {
      thecolor = "#808080";
    }
    
    for(var yy=totalColumn; yy>=1; yy--)
    {
      cell = doc.getRange(xx,yy);
      cell.setFontColor(thecolor);
    }
  }
  
} ​

By default, in the code above, if the score is weak (below 10) the row is greyed out. If it's 80 or higher it's good, so it's green, and the rest are left at black. Of course, you can make these whatever you want.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image