Trying To Change Color Of An Entire Row Based On Found Text In One Google Sheets Cell

Question

I found some very simple Apps Script code online that scans for a set value (MONDAY) in a Google sheets cell and then changes the color of the cell if it finds a match.

I am trying to modify the code to change color of the entire row if it finds a match, not just the individual cell.

I used the logger to determine that I am trying to pass setBackgroundRGB a number instead of a range. I just can't figure out how to get the range of the entire row instead of just the cell.

function rowColorChanger() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var findString = 'MONDAY';
  var textFinder = sheet.createTextFinder(findString);
  var items = textFinder.findAll();
  items.forEach(cell=>{
    var row = cell.getRow();          //IF YOU REMOVE THIS LINE IT BREAKS AND I DON'T UNDERSTAND WHY
    var col = cell.getColumn();       //IF YOU REMOVE THIS LINE IT BREAKS AND I DON'T UNDERSTAND WHY
    Logger.log(row);                  //THIS RETURNS A NUMERICAL VALUE IN THE LOG
    Logger.log(col);                  //THIS RETURNS A NUMERICAL VALUE IN THE LOG
    Logger.log(cell);                 //THIS LOGS AS: "Range"
    cell.setBackgroundRGB(255,255,0); //THIS WORKS BUT ONLY CHANGES THE INDIVIDUAL CELL
    row.setBackgroundRGB(255,255,0);  //THIS DOES NOT WORK (THIS IS MY ATTEMPT TO CHANGE THE ROW)
  })
}

I feel like there has to be some really simple change that I am missing. I've tried many, many different things, and other code, but none of what I try works.

Answer 1

Completely agree with doubleunary. You can set a simple formula that will be applied to each cell of the range that's being formatted. And you can "play" with anchoring (this simbol -> $) to apply that to an entire row or column. The formula can return true or false. But it also works with numbers, 0 is false and >1 is true. So you can set a MATCH (that will return the number of column where MONDAY is (or not)

Answer 2

    var row = cell.getRow();
    row.setBackgroundRGB(255,255,0);

The code does not work because the variable row holds an integer while setBackgroundRGB() is a Range method. Use Sheet.getRange(), like this:

  const columnEnd = sheet.getLastColumn();
    // ...
    const row = cell.getRow();
    cell.getSheet().getRange(row, 1, 1, columnEnd).setBackgroundRGB(255, 255, 0);

The whole thing would probably be easier to implement with a conditional formatting custom formula rule that changes cell fill colors dynamically rather than a script that only changes cell fill colors when you run it.