问题描述
是否可以从某个范围中获取一个子范围?
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.
它运行正常，但运行SUPER的速度较慢，但我正在尝试找出一种使速度更快的方法.我猜它慢的原因之一是因为我在循环中使用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 优于单元格-cell设置，因为它是批处理方法.假设您使用 验证规则，无法避免获取其他Range.
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.
但是，对于存在明确定义关系的特定用例，可以通过使用 RangeList s. RangeList是批量获取的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脚本中的范围获取范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！