Блог

[Моя цель] Используя скрипты Google Apps и Google Sheets, я хочу создать выпадающий список, зависящий от второго уровня, где, если я выберу значение из первого выпадающего списка, это позволит мне выбрать другое значение из следующего выпадающего списка. После выбора второго выпадающего списка я хочу иметь возможность устанавливать 6 значений в каждом столбце. Например, на скриншоте «Таблица плана питания», если я выберу «Протеин» для столбца B, в столбце C будет доступен другой выпадающий список для выбора ингредиента. В данном случае это «Куриная грудка». Теперь, что я также пытаюсь сделать, это автоматически заполнить столбец D-H при выборе значения для столбца C. Примечание: Ингредиенты поступают из таблицы на отдельном листе под названием «База данных продуктов», к которой я также приложил скриншот.

[Проблема] Я не могу найти способ автоматического заполнения столбца D-H при выборе значения для столбца C на листе «План питания».

[Что мне удалось сделать до сих пор] Я смог настроить код с помощью видео на YouTube (ссылка:https://www.youtube.com/watch?v=s-I8Z4nTDakamp;list=PL32yvu15_0Tha8QZBTyAF0NCAreowqOFJ ), но я не понимаю, как устанавливать значения для каждого столбца.

[Скриншоты]

• Скриншот таблицы плана питания: введите описание изображения здесь
• Скриншот таблицы базы данных продуктов питания: введите описание изображения здесь

[Код]

 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 amp;amp; c === firstLevelColumn amp;amp; r > 6) {
    applyFirstLevelValidation(val, r);
  } else if(wsName === wsNameNutritionPlan amp;amp; c === secondLevelColumn amp;amp; 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 amp;amp; 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);
}
  

Я считаю, что ваша цель заключается в следующем.

• При изменении значения выпадающего списка столбца «B» в разделе «План питания», выпадающий список добавляется в столбец «D» в той же строке. При этом условии, когда значение выпадающего списка столбца «C» в «Плане питания» изменяется, вы хотите поместить значения «Базы данных продуктов питания» в столбцы «D» — «H».
  • В этом случае значения извлекаются из таблицы «База данных продуктов питания». Строка совпадает со значениями столбцов «B» и «C» выпадающих списков.

Точки модификации:

• Я думаю, что в вашем скрипте при запуске функции applyFirstLevelValidation(val, r) правило проверки данных не добавляется. Потому что applyValidationToCell(listToApply, cell) не используется в этой функции.
• Если вы хотите поместить значения столбцов «D» в «H» при изменении выпадающего списка столбца «C», требуется изменить функцию applySecondLevelValidation(val, r) .
  • Я думаю, что можно использовать значения filteredFoodDatabase .
• Я думаю, что дублированный сценарий можно обобщить.
• В вашем случае, я думаю, что при изменении выпадающего списка может потребоваться также очистить значения ячеек в столбцах с «D» на «H».

Когда вышеуказанные пункты будут отражены в вашем скрипте, это будет выглядеть следующим образом.

Модифицированный скрипт:

В функции applyFirstLevelValidation(val, r) , пожалуйста, измените следующим образом.

 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);
  }
}
  

В функции applySecondLevelValidation(val, r) , пожалуйста, измените следующим образом.

 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 amp;amp; f[1] === val});
    if (filteredFoodDatabase.length > 0) {
      var [,,...values] = filteredFoodDatabase[0];
      ws.getRange(r, thirdLevelColumn, 1, values.length).setValues([values]);
    }
  }
}
  

• В этом случае значения помещаются в ячейки вместо проверки данных.

Примечание:

• Пожалуйста, используйте скрипт с V8 runtime.
• В этой модификации предполагается, что из ваших образцов изображений комбинация столбцов «A» — «B» в «Базе данных продуктов питания» уникальна в каждой строке.