How to retain original range when updating data validation rule with google apps script

Question

I am trying to write a google apps script that updates an existing data validation rule to toggle the "Show dropdown list in cell" option. I have it mostly working, it does change the "Show dropdown list in cell" option, however it is not retaining the original criteria range, which is on a different sheet. It appears to be changing the range to use the sheet the validation rule was from instead of the other sheet. The original data validation requires a value in the range Sheet2!A2:A, but when I run the below script, it changes to require a value in the range Base!A2:A instead.

function turnOffDropDown()  {
    var baseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base");
    var templateRange = baseSheet.getRange("B2");
    var rule = templateRange.getDataValidation();
    var criteria = rule.getCriteriaType();
    var args = rule.getCriteriaValues();
    args[1] = false;
    var newRule = rule.copy().withCriteria(criteria, args).build();
    templateRange.setDataValidation(newRule);
}

Am I doing something wrong that it isn't retaining the original range, is there a way to specify the range I want on Sheet2, or is there a different way to update the "Show dropdown list in cell" option?

Diego Diego · Accepted Answer · 2018-07-06T08:28:50

This seems like a bug and should probably be reported here. I've tried your approach as well as explicitly defining the range, but it consistently replaces the criteria sheet with the sheet in which the validation is being applied, while maintaining the specified cells. (E.g., the validation is being applied to Worksheet!C1, so CriteriaValues!A1:10 becomes Worksheet!A1:10.)

As a workaround, given that you're likely not changing the criteria type, you can try creating a new validation instead using requireValueInRange(). I've modified your code to create a "Toggle Dropdown" option. This does maintain the correct range.

function toggleDropDown()  {
  var baseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base");
  var templateRange = baseSheet.getRange("B1");
  var rule = templateRange.getDataValidation();
  var args = rule.getCriteriaValues();
  rule = SpreadsheetApp.newDataValidation().requireValueInRange(args[0], !args[1]).build();
  templateRange.setDataValidation(rule);
}

How to retain original range when updating data validation rule with google apps script

1 Answers