Define multiple ranges in Google Apps Script

Question

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.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);

My range is actually defined by another function:

var checkRange = [];

  for(var i = 0; i<checkboxes.length; i++) {
  if(checkboxes[i]) {

  checkRange.push('C' + (i+9));
  Logger.log(checkRange);
  }
  }

Now, the required range is being created nicely as I can see in my logs. However, clearly the format that GAS required for cell ranges is different as my range is not defined. Furthermore, I have tried to work out the precise acceptable way of writing a range in GAS. If I put a range like 'C9:C11' the script works fine. If I put a list like 'C9, C10' or 'C9', 'C10' etc. it does not. Neither do multiple ranges ('C9:C11', 'C13:C14') etc... not quite sure how I need to write this

Answer 1

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.

Answer 2

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);
});