1

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.

1

2 Answers 2

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)

enter image description here

3
  • Wouldn't it be more efficient to create the conditional format within the script?
    – Tedinoz
    Nov 13, 2022 at 1:39
  • Mmm, I don't know. Since Barton may had not known about this tool I thought it was interesting for him to know it. Create a conditional formatting this way can take how much?, 30 seconds each condition?, but writing a whole script (however it may be simple) at least for me takes a lot longer and is much more fallible. Even with conditional formatting you're actually seeing the result within each thing you modify of a formula. (Obviously @doubleunary was a great response and the most direct to Barton's question!!)
    – Martín
    Nov 13, 2022 at 1:49
  • Yeah. fair point.
    – Tedinoz
    Nov 13, 2022 at 2:18
1
    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.

2
  • 1
    The OP defines sheet as SpreadsheetApp.getActiveSpreadsheet(), but getRange(row, column, numRows, numColumns) is a "Sheet" method (not a "Spreadsheet" method). Add const cellSheet = cell.getSheet() and change the last line to cellSheet.getRange(row, 1, 1, columnEnd).setBackgroundRGB(255, 255, 0).
    – Tedinoz
    Nov 13, 2022 at 3:02
  • Thanks. Edited the answer. Nov 13, 2022 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.