在 Google Apps 脚本中定义多个范围 [英] 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.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

解决方案

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

这篇关于在 Google Apps 脚本中定义多个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！