CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsvalidation
Jared_C
Jared_C

Reputation: 669

In Apps Script, is it possible to access the range used in a data validation?

I know you can use .getDataValidation() or .getDataValidations() to access the rules set on a data validated cell, but is it possible to access the actual range that a validation is referencing?

For example, if SHEET is my active sheet and cell A1 is a data validated cell that has a criteria of "List from a range", and that range is Sheet2!B2:B100, can I return this range programmatically?

  var rules = SHEET.getRange("A1").getDataValidations();  
  for (var i = 0; i < rules.length; i++) {
    for (var j = 0; j < rules[i].length; j++) {
      var rule = rules[i][j];

      if (rule != null) {
        var criteria = rule.getCriteriaType();
        var args = rule.getCriteriaValues();
      }
    }
  }

Upvotes: 1

Views: 2237

Answers (1)

AdamL
AdamL

Reputation: 24609

Yes, getCriteriaValues() will return a 2-element array, where the first element is the range object you are requiring, and the second element is a boolean value signifying whether there is a drop-down arrow in the cell.

https://developers.google.com/apps-script/reference/spreadsheet/data-validation#getCriteriaValues()

Upvotes: 2

Related Questions