Highlight a row when a condition is met

Question

I would like to color in red the rows where between column F and K there is no value ' ', or the value KO.

I managed to find a script where I can delete the lines where these conditions are met, but not to color them:

   function deleteRows()
   {
     var sheet = SpreadsheetApp.getActiveSheet();
     var rows = sheet.getDataRange();
     let numRows = rows.getNumRows();
     var values ​​= rows.getValues();
 
     var rowsDeleted = 0;
     for (var i = 0; i <= numRows - 1; i++)
     {
       var row = values[i];
       if (row[5] == 'KO' || row[5] == '')
       if (row[6] == 'KO' || row[6] == '')
       if (row[7] == 'KO' || row[7] == '')
       if (row[8] == 'KO' || row[8] == '')
       if (row[9] == 'KO' || row[9] == '')
       if (row[10] == 'KO' || row[10] == '')
       {
         sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
         rowsDeleted++;
       }
     }
   }

could you help me on this? thanks in advance.

Answer 1

I believe your goal is as follows.

• You want to set the background color of rows where the columns "F" to "K" are empty or the columns "F" to "K" don't include a value of "KO".

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var colorAr = Array(lastColumn).fill("red");
  var nullAr = Array(lastColumn).fill(null);
  var range = sheet.getRange("F2:K" + sheet.getLastRow());
  var values = range.getValues().map(r => (r.join("") == "" || r.includes("KO")) ? colorAr : nullAr);
  range.offset(0, -5, values.length, values[0].length).setBackgrounds(values);
}

• In order to set the background color, setBackgrounds(color) is used.
  • In this sample, the background color is changed to "red" color. Please modify this for your actual situation.
• I thought that in your situation, when the values are retrieved from the columns "F" to "K" and the retrieved values are checked with the condition, the script might be a bit simple.

References:

• map()
• setBackgrounds(color)