Changing color of cell and font

Question

It's my first time working with Google scripts. I want to change the background color of a row and text when the row contains certain values. I've had some success using this piece of code:

function onEdit() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var rows = sheet.getRange('a1:z');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++)
  {
    var n = i+1;
    var backgroundColor;
    var textColor;

    if(values[i].indexOf('Won'))
    {
      backgroundColor = 'red';
      textColor = 'blue';
    }
    else if(values[i].indexOf('Lost'))
    {
      backgroundColor = 'green';
      textColor = 'yellow';
    }

    sheet.getRange('a'+n+':z'+n).setBackgroundColor(backgroundColor);    
    sheet.getRange('a'+n+':z'+n).setFontColor(textColor); 
  }
}

So now rows containing Won turn green with yellow text, and rows containing Lost turn red with blue text.

A couple of problems:

1. Every empty row also turn red- how do I fix this?
2. My if/else function seems to be inverted, yet it works perfectly. The way I understand it is that if the row contains Won it should actually turn red with blue text. Can anyone help me understand this part better?

Nippon

Answer 1

I tried this and it worked for me, but superb answer by redpandasuit (in the comments below) for anyone who want to try it through conditional formatting. Here is the code version (Google scripts):

function onEdit() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('ZB Squash 2015');
  var rows = sheet.getRange('a1:z');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++)
  {
    var n = i+1;
    var backgroundColor;
    var textColor;


      if(values[i].indexOf('Won') == -1)
      {
        backgroundColor = 'red';
        textColor = 'yellow';
      }

      if(values[i].indexOf('Lost') == -1)
      {
        backgroundColor = 'green';
        textColor = 'yellow';
      }

      if(values[i].indexOf('Won') && values[i].indexOf('Lost'))
      {
        backgroundColor = 'white';
      }

    sheet.getRange('a'+n+':z'+n).setBackgroundColor(backgroundColor);    
    sheet.getRange('a'+n+':z'+n).setFontColor(textColor); 
  }
}

Rows where the user won now turns green, rows where the user lost turns red, and all others remains white.

Nippon

Although this approach works, @redpandasuit's answer is way more efficient for large number of rows. – Chilt

Answer 2

I would try using conditional formatting to solve this issue. To do so highlight your desired rows, right click them and select Conditional Formatting.

In the drop down box select Custom formula and try

=RegExMatch($A1,"(?i)Won")

In this formula the A1 signifies which column you will be entering the text in. Change it as need be.

you can then select the text and background colour from the options available next to the formula box and select your range to add constraints to what is coloured.

Then repeat the process and add a second custom formula for "Lost"

=RegExMatch($A1,"(?i)Lost")

These formulas can be further customized to have multiple trigger words, case sensitivity and much more. Check out this link for ideas: https://support.google.com/a/answer/1371417?hl=en

I hope this helps:)

Exotic

Hi, I've tried something similar before. The problem is it only changes the text and background of the cell with the "one/lost value" in, and not the entire row. – Nippon

Hey again. I did it for you. Try it out! Type "win" or "lose" in the win/lose column and the row will light up! If you go into conditional formating you can see how the magic happens. Note the ranges and the use of multiple ranges to control which cells in the row are coloured and which are not. I hope this helps docs.google.com/spreadsheets/d/… – Exotic