Define Multiple Ranges In Google Apps Script

I'm having difficulty defining multiple ranges in GAS. I have the following simple function I need to perform: var dataRange = sheet.getRange(checkRange); var values = dataRange.g

Solution 1:

Performing the same operation on possibly-disjoint Ranges is most easily done with the Rangelist class, and does not require directly accessing the member Ranges. However, even if the operations are different (or conditional), a RangeList can be used to optimize the use of the Spreadsheet Service, rather than repeatedly calling Sheet#getRange.

From your code, we can determine that the goal is to operate on a set of ranges related to "true" checkboxes:

var checkRange = [];
for (var i = 0; i < checkboxes.length; i++) {
  if (checkboxes[i]) {
    checkRange.push('C' + (i+9));
    Logger.log(checkRange);
  }
}

You then (appear to) have a conditional alteration of the related range's value:

var dataRange = sheet.getRange(checkRange);
var values = dataRange.getValues();
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[i].length; j++) {
    if (values[i][j] == false) {
      values[i][j] = true; 
    }
  }
}
dataRange.setValues(values);

Instantiating a RangeList is done with an Array of string references to ranges on a sheet, e.g. [ "A1:A10", "C2", "D6:E8", "R5C9:R100C9" ]. Thus, it appears your current checkRange has the desired format already.

Your consumption code is then something like the following:

const rl = sheet.getRangeList(checkRange);
rl.getRanges().forEach(function (rg) {
  // Assumption: only single-cell ranges, based on the above checkRange code
  if (rg.getValue() == false) // matches false, null, undefined, 0, or ""
    rg.setValue(true);
});

Solution 2:

When accessing ranges from Google Apps Script each Range must be a continuous block, you can't select discrete ranges like C9:C11 and C13:C14 as a single Range object. You need to access those ranges separately.

A simple, but potentially inefficient, way to modify your existing code would be to loop over your checkRange array and access them one at a time:

for(var j = 0; j < checkRange.length; j++){
   dataRange = sheet.getRange(checkRange[j]);
   var values = dataRange.getValues();
   for (var i = 0; i < values.length; i++) {
     for (var j = 0; j < values[i].length; j++) {
       if (values[i][j] == false) {
         values[i][j] = true; 
       }
     }
   }
 }

If each range is just a single cell, you can switch getValues() for getValue(), and then you don't need to loop over your values array.

However, this will be very inefficient if you are accessing what could be one large range as a series of smaller ranges. It would be better to either use some logic to build multi-cell ranges where possible, or to read in the entire sheet as a single range (sheet.getDataRange().getValues()), and then use Array notation to access the cells you are interested in, before writing back the entire array.