Mapping dropdown list dynamically to the conditions in Google sheets

Question

The dropdown list I want should be dynamic. So based on the options I choose in columns D and E, the dropdownlist in column E should show only the products that are in column C. Below i added the code where you can see how I have done the other dropdown lists.

var mainWsName = "Inkoop";
var wsVerkoop = "Verkoop"
var sittardWsName = "Sittard";
var merkColumn = 4;
var modelColumn = 5;
var categorieColumn = 6;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wVerkoop = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsVerkoop);
var wsSittard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sittardWsName);
var arraySittard = wsSittard.getRange(2,1,wsSittard.getLastRow()-1,3).getValues();


function categorie(){
}

function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getSheetName()

  if(wsName == mainWsName && c == merkColumn && r > 1){
      if(val === ""){
      ws.getRange(r ,modelColumn).clearContent();
      ws.getRange(r ,modelColumn).clearDataValidations();
      ws.getRange(r ,categorieColumn).clearContent();
    } else{
      ws.getRange(r ,modelColumn).clearContent();
      var filterarraySittard = arraySittard.filter(function(o){return o[0] === val});
      var listToApply = filterarraySittard.map(function(o){ return o[1]});
      var cell = ws.getRange(r ,modelColumn);
      applyValidationToCell(listToApply, cell);
    }
  }
  if(wsName == wsVerkoop && c == merkColumn && r > 1){
      if(val === ""){
      wVerkoop.getRange(r ,modelColumn).clearContent();
      wVerkoop.getRange(r ,modelColumn).clearDataValidations();
      wVerkoop.getRange(r ,categorieColumn).clearContent();
    } else{
      wVerkoop.getRange(r ,modelColumn).clearContent();
      var filterarraySittard = arraySittard.filter(function(o){return o[0] === val});
      var listToApply = filterarraySittard.map(function(o){ return o[1]});
      var cell = wVerkoop.getRange(r ,modelColumn);
      applyValidationToCell(listToApply, cell);
    }
  }  
} // end onEdit


function applyValidationToCell(list,cell){
  var rule = SpreadsheetApp.newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  cell.setDataValidation(rule);
}

Answer 1

Try this:

Code:

var dstName = 'Sheet1';
var srcName = 'Sheet2';

var sprdSheet = SpreadsheetApp.getActiveSpreadsheet();
var dstSheet = sprdSheet.getSheetByName(dstName);
var srcSheet = sprdSheet.getSheetByName(srcName);

function populateMerk() {
  var merk = dstSheet.getRange("D2:D");
  var merkList = getData("", "A");
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(merkList);
  merk.setDataValidation(rangeRule);
}

function populateModel(id, row, sheetName) {
  var model = dstSheet.getRange("E" + row);
  var modelList = getData(id, "B");
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(modelList);
  var category = model.offset(0, 1);
  var mValue = model.getValue();

  model.clearDataValidations();
  model.clearContent();
  model.setDataValidation(rangeRule);

  if (sheetName == dstName) {
    category.clearDataValidations();
    category.clearContent();
  }
  else if (sheetName == srcName) {
    model.setValue(mValue);
  }
}

function populateCategory(id, row, sheetName) {
  var category = dstSheet.getRange("F" + row);
  var categoryList = getData(id, "C");
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(categoryList);
  var cValue = category.getValue();
  
  category.clearDataValidations();
  category.clearContent();
  category.setDataValidation(rangeRule);
  
  if (sheetName == srcName) {
    category.setValue(cValue);
  }
}

function getData(srcId, endCol) {
  var srcLastRow = srcSheet.getLastRow();
  var srcData = srcSheet.getRange("A2:" + endCol + srcLastRow).getValues();
  var dataList = [];

  if (!srcId) {
    // Get unique items for merk
    dataList = srcData.flat().filter((item, i, ar) => ar.indexOf(item) === i);
  }
  else {
    srcData.forEach(function(row) {
      // Only add unique models
      if (srcId == row[0] && !dataList.includes(row[1]) && !row[2]) {
        dataList.push(row[1]);
      }
      // Only add unique categories
      else if (srcId == (row[0] + row[1]) && !dataList.includes(row[2])) {
        dataList.push(row[2]);
      }
    });
  }
  return dataList;
}

function onEdit(e) {
  var sheetName = e.source.getActiveSheet().getName();
  var range = e.range;
  var col = range.getColumn();
  var row = range.getRow();
  if (row > 1) {
    if (sheetName == dstName) {
      var merk = dstSheet.getRange(row, 4).getValue();
      var model = dstSheet.getRange(row, 5).getValue();
      if (col == 4) {
        populateModel(merk, row, sheetName);
      }
      else if (col == 5) {
        populateCategory(merk + model, row);   
      }
    }
    else if (sheetName == srcName) {
      switch (col) {
        case 1:
          populateMerk();
          break;
        case 2:
          var lastE = dstSheet.getRange("E2:E").getValues().filter(String).length;
          var merk;
          for (var i = 0; i < lastE; i++) {
            merk = dstSheet.getRange("D" + (i + 2)).getValue();
            populateModel(merk, i + 2, sheetName);
          }
          break;
        case 3:
          var lastF = dstSheet.getRange("F2:F").getValues().filter(String).length;
          var merk, model, row;
          for (var i = 0; i < lastF; i++) {
            row = i + 2;
            merk = dstSheet.getRange("D" + row).getValue();
            model = dstSheet.getRange("E" + row).getValue();
            populateCategory(merk + model, row, sheetName);
          }
          break;
      }
    }
  }
}

Sample data in Sheet2:

Outputs in Sheet1:

Note:

• The code also notes when you edit your source sheet. If updated, will update all dropdowns for that column in the destination sheet. And since there will be a lot of calls when this happens, it will be a bit slow. You can still optimize that but for now, this still works.