Applying Google Apps Script for Dynamic Data Validation to Existing Sheet

Question

So I'm using this script (credit to Chicago Computer Classes) for populating dynamic data validation of a Google Sheets cell based on what the user entered in a different cell.

For example, if they enter the sport "Football" in one cell, the next cell has data validation for "CFB, CFL, or NFL" but if they enter "Basketball" in the first cell then the second cell's data validation changes to "ABL, CBB, NBA, or WNBA" for examples.

The script is working fantastic and you are welcome to play with the sheet here

However ... here's my problem:

I have an existing spreadsheet with 9000 rows of data. I would like to apply this new data validation scheme to this spreadsheet. The script is triggered with the onEdit() function which works great when you are entering things one row at a time. But if I try to copy and paste a whole bunch of rows in the first column, only the first row of the second column triggers the onEdit and gets the new data validation while all the other rows of the second column are unchanged. I've also tried to "Fill Down" or "Fill Range" on the first column and they have the same result where the first row in the selected range gets the new data validation but the rest of the selection is unchanged.

And while it would work just fine if I was manually entering rows, I really don't feel like doing that 9000 times :)

How do I modify the script to trigger the function with data that's copy/pasted or filled down?

Thanks!

Script here:

function onEdit(){
  var tabLists = "Leagues";
  var tabValidation = "2018";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);

  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 6 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){

    activeCell.offset(0, 1).clearContent().clearDataValidations();

    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();

    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;

    if(makeIndex != 0){

        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);

     }  

  }

}

Diego Diego · Accepted Answer · 2018-08-07T06:40:49

You should use the event object, which will provide you with the range that was edited. What you're doing now is looking only at the "active cell", which doesn't leverage the benefits of the event object, and can also lead to bugginess when you make rapid changes.

Using the event object, when you make an edit to multiple cells at once (from copy/paste), you can then loop through the range and set your validations.

function onEdit(e) {
  var editedRange = e.range;
  var ss = editedRange.getSheet();
  var tabValidation = "2018";
  if(editedRange.getColumn() == 6 && editedRange.getRow() > 1 && ss.getSheetName() == tabValidation) {
    var tabLists = "Leagues";
    var tabListsSheet = e.source.getSheetByName(tabLists);
    var makes = tabListsSheet.getRange(1, 1, 1, tabListsSheet.getLastColumn()).getValues(); // This won't change during execution, so call only once
    var activeCell = editedRange.getCell(1,1); // Start with the first cell
    var remainingRows = editedRange.getHeight();
    while(remainingRows > 0) {
      var cellValue = activeCell.getValue();
      activeCell.offset(0, 1).clearContent().clearDataValidations(); // Always clear content & validations
      if (cellValue != "") { // Add validations if cell isn't blank
        var makeIndex = makes[0].indexOf(cellValue) + 1;
        if(makeIndex != 0) {
          var validationRange = tabListsSheet.getRange(3, makeIndex, tabListsSheet.getLastRow()-2);
          var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
        }  
      }   
      activeCell = activeCell.offset(1, 0); // Get the next cell down
      remainingRows--; // Decrement the counter
    }
  }
}

Applying Google Apps Script for Dynamic Data Validation to Existing Sheet

1 Answers