How do I apply conditional formatting to cells based upon the result of the .getValues( ) method in Google Apps Script?

klekaelly 0
Viewed 25

I am writing this script to change the formatting of a row of checkboxes that total 0 selections. In my data validation, "true" = 1 and "false" = 0. The range is represented as an object [ ][ ].

The logic below determines which rows need to be formatted, but how do I actually apply the formatting to this row? I am having trouble accessing the row's formatting because I am working at the value level with the resulting object [ ] [ ].

Here is what the end result should look like: Camping Gear Tally

(I recognize there is probably an easy way to do this with conditional formatting built in, but I'm just trying to get better at coding)

function updateFormatting() {
    var cellValues = SpreadsheetApp.getActiveSheet()
        .getRange("C2:I37")
        .getValues();

    for (let i = 0; i < cellValues.length; i++) {
        let rowTotal = 0;

        for (let j = 0; j < cellValues[i].length; j++) {
            rowTotal += cellValues[i][j];
        }

        if (rowTotal == 0) {
            /* Apply formatting to this row */
        }
    }
}

Thank you for your guidance!

google-sheets google-apps-script javascript
1 Answers

I believe your goal is as follows.

  • You want to set the background color of the rows that all checkboxes of columns "C" to "I" are unchecked.
  • In your situation, the values of the checked and the unchecked checkboxes are 1 and 0, respectively.

Modification points:

  • When the values of the checked and the unchecked checkboxes are 1 and 0, respectively, when those values are retrieved by getValues, those are retrieved as the string type. So, in your script, when rowTotal += cellValues[i][j]; is run, the values are added as the string. Please be careful about this.
  • About /* Apply formatting to this row */, when the script for setting the cell format is put in a loop, the process cost will be high.

When these points are reflected in your script, how about the following modification?

Modified script:

function updateFormatting() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cellValues = sheet.getRange("C2:I37").getValues();
  var ranges = [];
  for (let i = 0; i < cellValues.length; i++) {
    let rowTotal = 0;
    for (let j = 0; j < cellValues[i].length; j++) {
      rowTotal += Number(cellValues[i][j]);
    }
    if (rowTotal == 0) {
      var row = i + 2;
      ranges.push(`C${row}:I${row}`);
    }
  }
  sheet.getRangeList(ranges).setBackground("#fce5cd");
}
  • In this modification, the range list is created in a loop. And, it is used with sheet.getRangeList(ranges).setBackground("#fce5cd") and the background color of rows are set as #fce5cd.

Reference:

Tanaike0
Related