从GoogleApps脚本中获取范围

Range#getCell是一种Range从现有内引用a 的方式Range，因此您已经在执行要求的操作。您要注意的是，使用Range#setDataValidations优于逐个单元设置，因为它是批处理方法。鉴于您使用了requireValueInRange验证规则，因此无法避免获取其他Ranges。

但是，对于使用明确定义的关系的特定用例，可以通过使用RangeLists 来更有效地获取它们。A RangeList是批量获取的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