在Google Apps脚本中定义多个范围 [英] Define multiple ranges in Google Apps Script

问题描述

我很难在GAS中定义多个范围.

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

现在，正如我在日志中所见，正在很好地创建所需范围.但是，很明显，单元格范围所需的GAS格式不同，因为我的范围未定义.此外，我试图找出在GAS中编写范围的精确可接受的方法.如果我将范围设为"C9:C11"，则脚本可以正常运行.如果我输入类似"C9，C10"或"C9"，"C10"等的列表，则不会.多个范围("C9:C11"，"C13:C14")也都没有，等等……不太确定我该怎么写

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

实例化RangeList 是用字符串引用Array来完成工作表上的范围，例如[ "A1:A10", "C2", "D6:E8", "R5C9:R100C9" ].因此，看来您当前的checkRange已经具有所需的格式.

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.

您的消费代码如下:

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屋！