In Dec22 Google changed how data validation is maintained [Release note].1

I have thousands of cells using different ranges for validation, and it's now impossible to maintain.

I want to change them to use named range, eg. PickGenius is a named range pointing to C1:C5, which contains a list smart people. If I want to expend the list, I just modify the named range and all my pick lists reflect the change.

You can create data validation through GUI to use a named range, see Named Range in validation However, if I try to do this through code, the destination (C1:C5) is stored rather than the name (PickGenius).

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var myRange = sheet.getRange(1,1);  
  var myRules = myRange.getDataValidations();
  var myRule=myRules[0][0];
  if (myRule !== null){
    var myVals=myRule.getCriteriaValues();
    var myrange = myVals[0].getA1Notation();    
  var newrange = ss.getRangeByName("PickGenius"); 
  var newrule = SpreadsheetApp.newDataValidation().requireValueInRange(newrange).build();

If you create a named range, run this, then goto Data>Data Validation, click on the rule, and it lists the destination as: =Sheet1!$C$1:$C$5 Is there any way to create Data Validation with a named range through code, as I have literally thousands to replace.

Thanks wonderful people.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.