Is there a way to mass input data validation in google sheets

Question

I'm trying to create a drop down menu with contents based on a another cell in the same row. For example if A1 = 'yes' then the drop down in B2 gives you the options of 'yes' or 'no'. I can do this I have the list data set up and to code works. The problem is I need to do this 155 times in 4 different sheets. Is there a faster way to do this than right clicking and editing the data validation rules for each cell. Here's a link to the test sheet I'm working on :

https://docs.google.com/spreadsheets/d/1rd_Ig_wpof9R_L0IiA1aZ9syO7BWxb6jvBhPqG8Jmm4/edit?usp=sharing

Answer 1

You can set data validation rules with a script, as documented here. Here's a reference for starting with Apps scripts.

I wrote a function that does approximately what you described. It works with the range B3:B157 of the sheet '9th grade' in the current spreadsheet. For each of them, it sets the validation rule to be: a value in the same row, columns B and C of sheet 'List Data'. The line with

 ....... = listData.getRange(i+3, 2, 1, 2);

will need to be modified if the source range of validation is to be different. Here, the parameters are: starting row, starting column, number of rows, number of columns. So, 2 columns starting with the second, in row numbered i+3.

function setRules() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var grade = ss.getSheetByName('9th Grade');
  var listData = ss.getSheetByName('List Data');
  var range = grade.getRange('B3:B157');
  var rules = range.getDataValidations();
  for (var i = 0; i < rules.length; i++) {
    var sourceRange = listData.getRange(i+3, 2, 1, 2);
    rules[i][0] = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange).build();
  }
  range.setDataValidations(rules);
}

Answer 2

I land in this issue for a diferent reason: "Just mass DataValidation copy (or update) in one column". Thanks, to user3717023 that bring me a light. I hope that helps someone this simplification.

function setRules() { 
  //select spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var leads = ss.getSheetByName('Leads');

  //Select correct Datavalidation
  var rangeNewDataValidation = leads.getRange('M2:M2');
  var rule = rangeNewDataValidation.getDataValidations();

  //Copy (or Update) Datavalidation in a specific (13 or 'M') column
  var newRule = rule[0][0].copy();
  Logger.log(leads.getMaxRows())
  for( var i=3; i <= leads.getMaxRows(); i++){
    var range = leads.getRange(i, 13);
    range.setDataValidations([[newRule.build()]]);
  }

}