How to create a second level dependent drop down list that can set values to multiple columns with Google Apps Scripts

Question

[My Goal] Using Google Apps Scripts and Google Sheets, I want to create a second level dependent drop down list, where if I select a value from the first drop down list, it'll allow me to select another value from the next drop down list. After selecting the second drop down list, I want to be able to set 6 values across each column. For example, under "Nutrition Plan Sheet" screenshot, if I select "Protein" for Column B, another drop down list will be available under Column C to select the ingredient. Which in this case is "Chicken Breast." Now, what I'm also trying to do is to have Column D-H to be auto-filled when I select a value for Column C. Note: The ingredients are coming from a table under a separate sheet called "Food Database," which I've also attached a screenshot.

[The issue] I'm unable to figure out a way to auto-fill the Column D-H when selecting a value for Column C under the "Nutrition Plan" sheet.

[What I've been able to do so far] I was able to setup a code with the help from a YouTube video (link: https://www.youtube.com/watch?v=s-I8Z4nTDak&list=PL32yvu15_0Tha8QZBTyAF0NCAreowqOFJ), but I don't understand how to set values to each Column.

[Screenshots]

Nutrition Plan Sheet Screenshot: enter image description here
Food Database Sheet Screenshot: enter image description here

[Code]

var wsNameNutritionPlan = "Nutrition Plan";
var wsNameFoodDatabase = "Food Database";
var firstLevelColumn = 2;
var secondLevelColumn = 3;
var thirdLevelColumn = 4;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsNameNutritionPlan);
var wsFoodDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsNameFoodDatabase);
var foodDatabase = wsFoodDatabase.getRange(3, 2, wsFoodDatabase.getLastRow()-2, 7).getValues();

function onEdit(e) {
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  
  if(wsName === wsNameNutritionPlan && c === firstLevelColumn && r > 6) {
    applyFirstLevelValidation(val, r);
  } else if(wsName === wsNameNutritionPlan && c === secondLevelColumn && r > 6) {
    applySecondLevelValidation(val, r);
  }
}

function applyFirstLevelValidation(val, r){
  if(val === "") {
    ws.getRange(r, secondLevelColumn).clearContent();
    ws.getRange(r, secondLevelColumn).clearDataValidations();
    ws.getRange(r, thirdLevelColumn).clearContent();
    ws.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    ws.getRange(r, secondLevelColumn).clearContent();
    ws.getRange(r, thirdLevelColumn).clearContent();
    ws.getRange(r, thirdLevelColumn).clearDataValidations();
    var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === val});
    var listToApply = filteredFoodDatabase.map(function(f) {return f[1]});
    var cell = ws.getRange(r, secondLevelColumn);
  }
}

function applySecondLevelValidation(val, r){
  if(val === "") {
    ws.getRange(r, thirdLevelColumn).clearContent();
    ws.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    ws.getRange(r, thirdLevelColumn).clearContent();
    var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
    var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === firstLevelColValue && f[1] === val});
    var listToApply = filteredFoodDatabase.map(function(f) {return f[2]});
    var cell = ws.getRange(r, thirdLevelColumn);
    applyValidationToCell(listToApply, cell);
  }
}

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

Tanaike Tanaike · Accepted Answer · 2020-09-13T08:28:52

I believe your goal as follows.

When the value of drop down list of the column "B" in "Nutrition Plan" is changed, the drop down list is added to the column "D" in the same row. Under this condition, when the value of drop down list of the column "C" in "Nutrition Plan" is changed, you want to put the values of "Food Database" to the columns "D" to "H".
- In this case, the values are retrieved from the sheet of "Food Database". The row is the same with the values of columns "B" and "C" of the drop down lists.

Modification points:

In your script, I think that when the function of applyFirstLevelValidation(val, r) is run, no data validation rule is added. Because applyValidationToCell(listToApply, cell) is not used in this function.
When you want to put the values of columns "D" to "H" when the drop down list of the column "C" is changed, it is required to modify the function of applySecondLevelValidation(val, r).
- I think that the values of filteredFoodDatabase can be used.
I think that the duplicated script can be summarized.
In your case, I think that when the drop down list is changed, it might be required to also clear the cell values in the columns "D" to "H".

When above points are reflected to your script, it becomes as follows.

Modified script:

At the function of applyFirstLevelValidation(val, r), please modify as follows.

function applyFirstLevelValidation(val, r){
  ws.getRange(r, secondLevelColumn, 1, 6).clearContent();
  ws.getRange(r, secondLevelColumn).clearDataValidations();
  if(val != "") {
    var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === val});
    var listToApply = filteredFoodDatabase.map(function(f) {return f[1]});
    var cell = ws.getRange(r, secondLevelColumn);
    applyValidationToCell(listToApply, cell);
  }
}

At the function of applySecondLevelValidation(val, r), please modify as follows.

function applySecondLevelValidation(val, r){
  ws.getRange(r, thirdLevelColumn, 1, 5).clearContent();
  if(val != "") {
    var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
    var filteredFoodDatabase = foodDatabase.filter(function(f) {return f[0] === firstLevelColValue && f[1] === val});
    if (filteredFoodDatabase.length > 0) {
      var [,,...values] = filteredFoodDatabase[0];
      ws.getRange(r, thirdLevelColumn, 1, values.length).setValues([values]);
    }
  }
}

In this case, the values are put to the cells instead of the data validation.

Note:

Please use the script with V8 runtime.
In this modification, it supposes that from your sample images, the combination of the columns "A" to "B" in "Food Database" are the unique in each row.

How to create a second level dependent drop down list that can set values to multiple columns with Google Apps Scripts

1 Answers

Modification points:

Modified script:

Note: