How can I set a cell's background color using an array[x][y] indication on google script-CodePudding

I'm trying to create a function that will set a background color of a cell if it stands in some conditions using an array to get the cell position

The logic is as follows: if the priority of the task is urgent and the time duration is higher than 4, than color the "time duration" relative cell in red.

Here is the google sheet table:

For that I'm using a sub function called "FindColumn" that its purpose is the bring me the X and Y positions of the needed columns.

After that I'm using the array "values" to check the above condition. When the conditions are found i want to change the color and for that I'm trying to use the setBackground function but, if i understood correctly, this function cant run over an array.

Is there a way i can color the cell using this function or maybe other function? Maybe there is a way that i can transform my array to a range that setBackground can use?

Thanks a lot

Note: I don't want to use conditional formatting for that.

Note that according to this table, cell 'B4' should be marked as red since "Edit chart" task is matched with the above condition

Here is the main function that should paint the cell:

function verificationTest() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastCell = activeSheet.getRange(activeSheet.getMaxRows(), activeSheet.getMaxColumns());
var searchRange = activeSheet.getRange("A1:Z");
var values = searchRange.getValues();
var columnIndex = FindColumn("Priority");
var columnIndex2 = FindColumn("Time Duration");
//console.log ("columnIndex: " columnIndex   ", columnIndex2: " columnIndex2)
 for (x = 0; x < values.length; x  ) {
   if (values[x][columnIndex].includes("Urgent")) {
    console.log ("includes");
    if(values[x][columnIndex2]>4) {
    console.log("bad");
    values[x][columnIndex2].setBackground('red');
    
    }
   }
 }
}

Here is the function that returns the X and Y positions of a requested header:

function FindColumn (lookfor) {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastCell = activeSheet.getRange(activeSheet.getMaxRows(), activeSheet.getMaxColumns());
var searchRange = activeSheet.getRange("A1:Z1");
var values = searchRange.getValues();
for (x = 0; x < values.length; x  ) {
    for (y = 0; y < values[x].length; y  ) {
    if(values[x][y]== lookfor){
      return (y)
      break;
    
  }
}
}
}

Thanks in advance

CodePudding user response：

To set the background of cells in an array, use