2

I'm looking for a way to copy my data validation the following way.
If validation for Main sheet A1 is:
Cell Range: MainSheet!J1
List from range: ListValues!1:1

I need validation for main sheet A2 to be:
List from range:ListValues!2:2

I can't seem to figure out how to do this. The only way it seems would be to manually change every cell's data validation.

bumped to the homepage by Community 1 hour ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.