Simple conditional formatting in Google Sheets Script Editor

Question

I am trying to create some basic conditional formatting rules with Scripts instead of the conditional formatting editor in sheets, because when a new row is added or removed, it breaks the rules already set. For example, when a row is deleted, the condition may be for the range A:C but then it adds "A1:C5,A6:C898". This causes some rows to be skipped by the rules so I am hoping a script will solve this.

So I want to simply change the cell background to green if the cell text is exactly "Y". I want to change it to red if text is exactly "N". I have other rules that I want to use but I am having trouble with the basics on this.

What I have so far in my script:

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("PRECLOSING");
  var range = sheet.getRange("E:E,I:J,M:M,P:P,S:V,AB:AC,AF:AF"); 
  range.activate();

  var values = rangeY.getValues();

  //for each row that data is present
  for(var i = 0; i < values.length; i++) {
    var cell = sheet.getRange(i + 1, 2);

      if ( values == "X" )
         {
           cell.setBackground('black');
           return;  
         } else {
           cell.setBackground('white');
         }
   }
}

Gerardo Gerardo · Accepted Answer · 2015-09-25T21:07:05

I made some modifications to your code. The function getRange i don't think it can accept the ranges in that way, instead i think it can get just ranges that are continue (eg. A1:E10)

For that I created an array with the ranges you need and then I loop through them.

The function "getValues" returns a two dimensional array so you will need a second loop to get the actual value from the cell.

Then as you already have the range, you need to get the cell inside that range and not a new complete range.

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("PRECLOSING");;
  var columns = ['E:E','I:J','M:M','P:P','S:V','AB:AC','AF:AF'];

  for(var col in columns){ //loop to iterate the desired ranges

    var range = sheet.getRange(columns[col]); 
    range.activate();

    var values = range.getValues();

    //for each row that data is present
    for(var i = 0; i < values.length; i++) { //loop to iterate the rows
      for( var j = 0; j< values[i].length ; j++){   //loop to iterate the columns 
        var cell = range.getCell(i+1, j+1);      
        if ( values[i][j] == "X" )
         {
           cell.setBackground('black');
         }              
      } 
     }
   }
}

Hope this helps.

Simple conditional formatting in Google Sheets Script Editor

1 Answers