从 Google Apps 脚本中的范围获取范围 [英] Getting a range from a range in Google Apps scripting
问题描述
有没有办法从范围中获取子范围?
Is there a way to get a subrange from a range?
即.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var fullRange = ss.getRange("A:P");
我可以从 fullRange 获取范围吗?
Can I get a range from fullRange?
上下文是我使用数据验证根据范围动态设置数据验证下拉列表.
The context is that I'm using data validation to dynamically set a data validation dropdown based on a range.
示例:我有两个单元格 - 第一个是带有类别列表的下拉列表，第二个是带有依赖于在第一个单元格中选择的类别的子类别列表的下拉列表.
Example: I have two cells - the first one is a dropdown with a list of categories, and the second is a dropdown with a list of subcategories thats dependent on the category selected in the first cell.
我实现这一点的方式是基于类别选择，我使用基于该类别选择的子类别列表填充隐藏行.然后我使用 requireValueInRange 为该子类别单元格设置数据验证.
The way I'm making this happen is based on the category selection, I populate a hidden row with the list of subcategories based on that category selection. Then I use requireValueInRange to set the data validation for that subcategory cell.
它工作正常，除了运行速度超慢，我正在努力想办法让它更快.我猜它缓慢的原因之一是因为我在循环中使用 getRange 来获取正确的 requireValueInRange.所以我试图拉一个子范围，而不是每次都重新查询范围.
It works fine, except runs SUPER slow, and I'm trying to figure out a way to make it faster. I'm guessing one of the reasons its slow is because I'm using getRange in a loop to get the proper requireValueInRange. So I'm trying to pull a subrange instead of re-querying the range every time.
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);
}
推荐答案
Range#getCell 是从现有 Range 中引用 Range 的方式，所以你已经做你要求做的事.正如您所注意到的，使用 Range#setDataValidations 优于逐个单元格设置，因为它是一种批处理方法.鉴于您使用 requireValueInRange 验证规则，没有办法避免获取额外的Ranges.
Range#getCell is how one references a Range from within an existing Range, so you're already doing what you're asking to do. As you note, using Range#setDataValidations is superior to cell-by-cell setting, as it is a batch method. Given that you use the requireValueInRange validation rule, there is no way to avoid acquiring additional Ranges.
但是，对于具有明确定义关系的特定用例，可以通过使用 RangeLists.RangeList 是批量获取的 Range，通常用于不相交的 Range 被同等对待.
However, acquiring them can be done more effectively, for specific use cases where there is a well-defined relationship, through the use of RangeLists. A RangeList is a batch-acquired Range, generally intended for uses where disjoint Ranges are to be treated equivalently.
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
}
参考资料
这篇关于从 Google Apps 脚本中的范围获取范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
