Is there a way to get a subrange from a range?
ie.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var fullRange = ss.getRange("A:P");
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.
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.
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);
}