0

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();    
    Logger.log(myrange);
  }
  var newrange = ss.getRangeByName("PickGenius"); 
  var newrule = SpreadsheetApp.newDataValidation().requireValueInRange(newrange).build();
  myRange.setDataValidation(newrule);
}

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.

2

0

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.