Getting a range from a range in Google Apps scripting

Question

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);
}

Answer 1

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.

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
}

References

• RangeList
• setDataValidations
• requireValueInRange
• Array#map

Answer 2

the example below alows to get a subRange made of all rows and only some columns.

function getRangeByCols( myRange,  fromCol,  toCol){
  firstRowIndex = myRange.getRow()
  firstColIndex = myRange.getColumn()
  rowsNumber = myRange.getHeight()
  colsNumber = myRange.getWidth()

  return myRange.getSheet().getRange(firstRowIndex,firstColIndex+fromCol-1,rowsNumber,toCol-fromCol+1)
}