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?

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.

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:)