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

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

• javascript
• jquery
• google-apps-script
• range

Share Improve this question Follow asked Sep 6, 2018 at 16:27 DevB1DevB1 1,58533 gold badges2525 silver badges5252 bronze badges 2

• 2 You can also look at RangeList – TheMaster Commented Sep 6, 2018 at 20:47
• 1 For example, is this library usful for your situation? github./tanaikech/RangeListApp – Tanaike Commented Sep 6, 2018 at 22:15

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

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

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.