Need help getting the data validation criteria from “list from a range” in an apps script so I can cycle through those values

Question

I want to get the data validation criteria from "list from a range" in an apps script so I can cycle through those values. For example, in cell A5 I have data validation set up, with "criteria" being "List from a range" and the corresponding range being "'Grade K Class MTSS Profile'!B2:AH2". I want it to return "'Grade K Class MTSS Profile'!B2:AH2" so I can cycle through this range and do something with the cells contained in that range.

This is the code I have so far:

function onOpen(e) {
  SpreadsheetApp.getUi()

  .createMenu('MTSS Tools')
  .addItem('Save All Student Reports', 'saveAllStudentReports')
  .addToUi();
}


function saveAllStudentReports() {

var cell = SpreadsheetApp.getActive().getRange('A4');
var rule = cell.getDataValidation();
var ui = SpreadsheetApp.getUi();

if (rule != null) {
  var criteria = rule.getCriteriaType();
  var args = rule.getCriteriaValues();
  ui.alert("Has rule");
  ui.alert('The data validation rule is ' + criteria + args);
}
else
{
  ui.alert("NO rule");
}
}

However, I'm not getting the actual range. Ideas? Thank you very much!

Jescanellas Jescanellas · Accepted Answer · 2020-04-13T09:08:40

As you can see in the documentation:

getCriteriaValues returns:

Object[] — an array of arguments appropriate to the rule's criteria type; the number of arguments and their type match the corresponding require...() method of the DataValidation class

So args is a Range object, which means all methods from the Range class can be used, like getA1Notation:

ui.alert('The data validation rule is ' + criteria + ' ' + args[0].getA1Notation()

Need help getting the data validation criteria from “list from a range” in an apps script so I can cycle through those values

1 Answers