This is a functioning script for dynamic data validation set based on some old youtube videos

It's slow and I'm not sure where there might be a loop that I can remove

Had never used scripts until 4 hours ago; can anyone please make this run faster

Any advice appreciated!

function onEdit() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reference Sheet");

  if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 10 && ss.getActiveCell().getRow() > 7){

    ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();

    var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue())   1;

    if(materialIndex != 0){

      var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
      var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
      ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);

    }

  }

  if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 15 && ss.getActiveCell().getRow() > 7){

    ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();

    var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue())   1;

    if(materialIndex != 0){

      var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
      var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
      ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);

    }

  }

  if(ss.getName() == "Test Sheet" && ss.getActiveCell().getColumn() == 20 && ss.getActiveCell().getRow() > 7){

    ss.getActiveCell().offset(0, 1).clearContent().clearDataValidations();

    var materials = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    var materialIndex = materials[0].indexOf(ss.getActiveCell().getValue())   1;

    if(materialIndex != 0){

      var validationRangeMU = datass.getRange(2, materialIndex, datass.getLastRow());
      var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
      ss.getActiveCell().offset(0, 1).setDataValidation(validationRuleMU);

    }

  }

CodePudding user response：

Try this:

Just taking maximum advantage of the event object

function onEdit(e) {
  var idx = [10, 15, 20].indexOf(e.range.columnStart)
  if (e.range.getSheet().getName() == "Test Sheet" && ~idx && e.range.rowStart > 7) {
    var rsh = e.source.getSheetByName("Reference Sheet");
    e.range.offset(0, 1).clearContent().clearDataValidations();
    var materials = rsh.getRange(1, 1, 1, rsh.getLastColumn()).getValues();
    var materialIndex = materials[0].indexOf(e.value)   1;
    if (materialIndex != 0) {
      var validationRangeMU = rsh.getRange(2, materialIndex, rsh.getLastRow());
      var validationRuleMU = SpreadsheetApp.newDataValidation().requireValueInRange(validationRangeMU).build();
      e.range.offset(0, 1).setDataValidation(validationRuleMU);
    }
  }
}