google-apps-script - 从 Google Apps 脚本中的范围获取范围

Question

有没有办法从范围中获取子范围？

IE。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var fullRange = ss.getRange("A:P");

我可以从 fullRange 获得一个范围吗？ ?

上下文是我使用数据验证根据范围动态设置数据验证下拉列表。

示例:我有两个单元格 - 第一个是带有类别列表的下拉列表，第二个是带有依赖于第一个单元格中选择的类别的子类别列表的下拉列表。

我实现这一点的方式是基于类别选择，我使用基于该类别选择的子类别列表填充隐藏行。然后我使用 requireValueInRange为该子类别单元格设置数据验证。

它工作正常，除了运行速度超慢之外，我正在努力想办法让它更快。我猜它缓慢的原因之一是因为我正在使用 getRange在循环中获得正确的 requireValueInRange .所以我试图拉一个子范围，而不是每次都重新查询范围。

function setDataValidations() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var categoryRange = ss.getRange("A:A");
  var subCatCells = ss.getRange("B:B");

  var subCatRules = subCatCells.getDataValidations();

  var rangeLength = categoryRange.getValues().length;

  for (var i = 1; i < rangeLength; i++ ){
    var catCell = categoryRange.getCell(i, 1);
    var subCatOptions = ss.getRange("'subcats'!A" + i + ":P" + i);
    var subCatRule = SpreadsheetApp.newDataValidation().requireValueInRange(subCatOptions, true).build();
  }
  catCells.setDataValidations(subCatRules);
}

Answer 1

Range#getCell 是如何引用 Range来自现有 Range ，所以你已经在做你要求做的事情了。正如您所注意到的，使用 Range#setDataValidations 优于逐个单元格设置，因为它是一种批处理方法。鉴于您使用 requireValueInRange 验证规则，无法避免获取额外的 Range s。

但是，对于具有明确定义关系的特定用例，通过使用 RangeList 可以更有效地获取它们。 s。一个 RangeList是批量收购Range ，通常用于不相交的 Range 被同等对待的用途。

function setDVs() {
  const wb = SpreadsheetApp.getActive();
  const catSheetName = "the name of the sheet that has the dropdowns",
      sheet = wb.getSheetByName(catSheetName),
      maxDVs = sheet.getLastRow();

  // Create the A1-notation or R1C1-notation Arrays identifying the ranges we need.
  const subCatNotations = [];
  const subCatSheetName = "the name of the sheet that has the ranges with required values"
  for (var r = 1; r <= maxDVs; ++r)
    subCatNotations.push("A" + r + ":P" + r); // 1 row, cols A:P

  // Range#setDataValidations requires an Array of Array of DataValidations.
  // Thus, wrap the new rule in a new array if updating a single column.
  const new_dvs = wb.getSheetByName(subCatSheetName)
      .getRangeList(subCatNotations).getRanges()
      .map(function (subCatRange) {
        return [
          SpreadsheetApp.newDataValidation().requireValueInRange(subCatRange, true).build()
        ];
      });

  // Batch-apply the new rules to the `catSheetName` sheet.
  if (new_dvs.length && new_dvs[0].length)
    sheet.getRange(1, 1, new_dvs.length, new_dvs[0].length).setDataValidations(new_dvs); // col A
}

引用

RangeList

setDataValidations

requireValueInRange

Array#map