Google-sheets – Copy Multiple Cells with Data Validation from one Google Sheets to Another

I'm not 100% sure what the issue is, but if it is a question of how to change the range of options in data validation:

When using Data Validation, the drop downs will update automatically if you change the source-list of values that appear.

The only time you will need to re-configure the validation is if the range of the possible selections changes, or you start using a different range/sheet to validate the input cells.

Example:

options = [1, 2, 3, 4, 5]

Drop down shows:

[1, 2, 3, 4, 5]

If you want to add a value (like 6), you will need to re-do the validation because this will change the range of the options.

One can copy data validations with offset using Apps Script. The following script takes the validation rules from the first row of MainSheet and copies them to rows 2-10, with the adjustment that if the rule is "value in range", then the range is offset by the number of rows. So, if the rule in cell J1 refers to SomeSheet!C3:E3, the rule in cell below it (J2) will refer to the cell SomeSheet!C4:E4. The method offset(rows, columns) is convenient here.

function copyValidations() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mainSheet = ss.getSheetByName("MainSheet");
  var firstRow = mainSheet.getRange("1:1");
  var rules = firstRow.getDataValidations()[0];
  var newRules = rules.slice(0);
  for (var i = 1; i < 10; i++) {            // here 10 is the last row to be filled
    for (var j = 0; j < rules.length; j++) {
      if (rules[j]) {
        var criteria = rules[j].getCriteriaType();
        var args = rules[j].getCriteriaValues();
        if (criteria == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
          var newRange = args[0].offset(i, 0);        // shift down by i rows
          newRules[j] = SpreadsheetApp.newDataValidation().requireValueInRange(newRange, true).build();
        }
      }
    }
    firstRow.offset(i, 0).setDataValidations([newRules]);
  }
}

References:

• getDataValidations
• Data Validation
• Data Validation Builder
• offset