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.