Is there a way to do conditional data validation in Google Sheets / App Scripts from a listed data source?

Sandy Doran 0
Viewed 24

I am trying to create a data validation drop down list in column C (laptop model) that is based on the input in column B (laptop make). I have been able to figure this out by splitting the different laptop models into different tables and using the script below.

function onEdit(){
  var hardwaretab = "Hardware";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(hardwaretab);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 2 && activeCell.getRow() > 2 && activeCell.getRow() <20 && ss.getSheetName() == hardwaretab){
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
  var makes = ss.getRange(29, 1, 1, ss.getLastColumn()).getValues();
  Logger.log(makes)  
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
  Logger.log(makeIndex)  
    if(makeIndex != 0){
    
        var validationRange = ss.getRange(30, makeIndex, ss.getLastRow());
        Logger.log(validationRange)
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  
}

what I would really like is to be able to achieve the same result from an individual table instead of having to split the bands as the next step is a vlookup and I would prefer not to use a complicated IF statement (although this is currently what I have).

I am sure there is a way to use the filter function in Apps script however I haven't been able to figure it out.

Below shows a rough example of the data source I am after

Apple   MacBook Pro 13inch 2020
Apple   MacBook Pro 16-inch 
Apple   MacBook Pro 14inch 2021
Apple   MacBook Pro 16inch 2021
Apple   iMac 27
Dell    Latitude 3590
Dell    Latitude 5250
Dell    Latitude 5450
Dell    Latitude 5550
Dell    Latitude 7280
Dell    Latitude 7350
Lenovo  Thinkpad
google-sheets google-apps-script
1 Answers

If your script was working then this should work as well. It's a bit more compact and makes better use of the event object.

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Hardware" && e.range.columnStart == 2 && e.range.rowStart > 2 && e.range.rowStart < 20) {
    e.range.offset(0, 1).clearContent().clearDataValidations();
    var makes = sh.getRange(29, 1, 1, sh.getLastColumn()).getValues()[0];
    var idx = makes.indexOf(e.value);
    if (~idx) {
      var validationRange = sh.getRange(30, idx + 1, sh.getLastRow());
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      e.range.offset(0, 1).setDataValidation(validationRule);
    }
  }
}
user30hu0
Related