Duplicate sheet references original sheet's range for validation

Question

When you set up an in-cell drop down list using Data Validation that references an existing range of cells within your spreadsheet as the criteria, by clicking Data > Validation and choosing List from a range, a problem arises when you duplicate the sheet and want to rely on a more localised functionality.

As an example, see this screenshot of the Data Validation window on an original sheet:

The expected outcome is that cell A1 will have in-cell drop down list functionality that will list the contents of the non-blank cells B1:B8 as separate items in that list.

The problem shows up when you duplicate the sheet and expect the duplicate's cell A1 to reference the new, duplicated sheet's B1:B8 for items in its drop-down list.

In this screenshot, notice how the duplicate's Data Validation window is referencing the original sheet's B1:B8 for its items, i.e. Sheet1!B1:B8, rather than referencing a more local range, the duplicate's B1:B8.

A way to achieve the desired outcome is to manually choose the local, duplicate's range, but ideally, when a sheet's Data Validation is duplicated, it would not add the Sheet1 and try to persist the reference to the original's range, and ideally one would not have to manually fix every Data Validation in the sheet, every time they make a duplicate.

To be clear: the duplicate's B1:B8 will be different from the original's, and is why Google Sheet's decision to have duplicated Data Validations persist references to the original sheet would not work in my situation.

The question: How can I duplicate a sheet that has Data Validation as described, and have the range referenced in the duplicate be local, rather than automatically referencing the original sheet's range?

---

User 'Carrie Rhodes' had the exact same problem and described it here in a Google Docs Help Forum, though it did not yield any helpful answers.

The reason I mention that is in case my question was not clear enough, and you wanted to read the same problem worded differently by someone else.

Answer 1

This is not really specific to sheet duplication: range references in data validation rules are always treated as absolute by Google Sheets, nothing is relative to current cell position (try copying a cell with data validation to see this).

So, I wrote a script that localizes all validation-by-range rules in the current sheet, making the ranges refer to the same sheet. It is executed with a custom menu item "Localize Validation". So the whole thing is a two step process:

1. Duplicate a sheet
2. Run "Localize Validation" on the duplicate.

The script:

function localizeValidation() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  var rules = range.getDataValidations();
  for (var i = 0; i < rules.length; i++) { 
    for (var j = 0; j < rules[0].length; j++) {
      if (rules[i][j] && rules[i][j].getCriteriaType() == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
        var cValues = rules[i][j].getCriteriaValues();
        cValues[0] = sheet.getRange(cValues[0].getA1Notation());
        rules[i][j] = rules[i][j].copy().withCriteria(SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE, cValues).build();
      }
    }
  }
  range.setDataValidations(rules);
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Localize Validation",  functionName: "localizeValidation"}]);
}