Data Validation Script

Question

I am using:

function datavalidation(event) {
  var ColA = 1;  
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColA) { 
  var destinationRange = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+1);

  var sourceRange = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+2,changedRange.getColumn(),10);
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange).build();
  var rules = destinationRange.getDataValidations();
  for (var i = 0; i < rules.length; i++) {
  for (var j = 0; j < rules[i].length; j++) {
  rules[i][j] = rule;
   }
 }
 destinationRange.setDataValidations(rules);
}
}

to create dropdown menus in column 2 when column 1 is edited. It's working fine for the most part but it is doing something unexpected when I remove any of the data validation from column 2. When I do this, which might be never, it runs the script on the first row of column 2 even though I did not change column 1. Just trying to understand what is going on here. Thanks

See public doc here: https://docs.google.com/spreadsheets/d/1kYncT7bOYWp73yr3Lh4LQreTpRFc8lZL3siqpP4xsvA/edit?usp=sharing

I suggest you to check this [DataValidation](https://developers.google.com/apps-script/reference/spreadsheet/data-validation) and [DataValidationBuilder](https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder) to know how the validation works in your code. It provides some sample code to know how to use it. For the problem about dropdown menus, try to check these questions [Data Validation using Apps Script](http://stackoverflow.com/questions/17686068) and [dependent drop downs in Google Sheets?](http://stackoverflow.com/questions/21744547) if it can help you. — KENdi, Jan 12 '17 at 14:33
Thanks for your suggestion. While this script appears to be working in my test sheet it does not work in other sheets. The data valadation part is correct but I I think the main problem is this: ".getRange(changedRange.getRow(),ColA+2,changedRange.getColumn(),10);" I believe I need to use a different method to set the source range. — user2969867, Jan 12 '17 at 16:36

score 0 · Answer 1 · answered Jan 16 '17 at 22:57

The method for setting the source range was incorrect.

function datavalidation(event) {
  var ColA = 1;  
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColA) { 
  var destinationRange = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+1);

  var sourceRange = event.source.getActiveSheet().getRange(changedRange.getRow(),2,1,10);
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange).build();
  var rules = destinationRange.getDataValidations();
  for (var i = 0; i < rules.length; i++) {
  for (var j = 0; j < rules[i].length; j++) {
  rules[i][j] = rule;
   }
 }
 destinationRange.setDataValidations(rules);
}
}

Data Validation Script

1 Answers1