Change cell background with onEdit()

user_15314111 0
Viewed 40

The goal:

  1. Get the edited checkbox
  2. Get all checkboxes in range (column 2–7, same row as edited checkbox)
  3. If all checkboxes return "TRUE", set backgrounds on range (column 1–7, same row as edited checkbox)

Seems like the code do nothing.

function onEdit(event) {
  var sheet = event.source.getActiveSheet(), //get edited sheet
      row = event.range.getRow(), // get row needed
      column = 2, // set the starting column
      test,
      userValues = [];
  
  while (column<=7) {
    if (sheet.getValue(row,column) == "TRUE") {
      test = true
    } else {
      test = false;
      break;
    }

    column++;
  }

  if (test) {
    sheet.getRange(row,1,1,7).setBackgrounds("#b6d7a8");
  }
}
google-apps-script google-sheets triggers
1 Answers

I believe your goal is as follows.

  • You have the checkboxes to the columns "B" to "G".
  • When all checkboxes from the columns "B" to "G" are checked, you want to set the background color of the columns "A" to "G" of the same row to #b6d7a8.
  • In your situation, I guessed that when all checkboxes are not checked, you might want to set the background color to the default color.

When I saw your script, getValue is used in the loop. In this case, the process cost will be high. And, when you use sheet.getRange(row,1,1,7).setBackgrounds("#b6d7a8"), you can use sheet.getRange(row,1,1,7).setBackground("#b6d7a8").

In this case, how about the following modification?

Modified script:

function onEdit(event) {
  var sheetName = "Sheet1"; // Please set the sheet name.
  var range = event.range;
  var sheet = event.source.getActiveSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart < 2 || range.columnStart > 7) return;
  var r = sheet.getRange(range.rowStart, 1, 1, 7)
  var checks = r.offset(0, 1, 1, 6).getValues()[0].every(e => e === true);
  r.setBackground(checks ? "#b6d7a8" : null);
}
  • When you use this script, please check the checkboxes the columns "B" to "G". When all checkboxes from the columns "B" to "G" are checked, the background color of columns "A" to "G" is changed to #b6d7a8.

Note:

  • This script is run by the OnEdit trigger. So please edit the cells of checkboxes from the columns "B" to "G". By this, the script is run. When you directly run this script with the script editor, an error occurs. Please be careful about this.

  • When you are not required to check the sheet name, you can also use the following script.

      function onEdit(event) {
    var range = event.range;
    var sheet = event.source.getActiveSheet();
    if (range.columnStart < 2 || range.columnStart > 7) return;
    var r = sheet.getRange(range.rowStart, 1, 1, 7)
    var checks = r.offset(0, 1, 1, 6).getValues()[0].every(e => e === true);
    r.setBackground(checks ? "#b6d7a8" : null);
  }

References:

user_71086530
Related