Значение из диапозона, в зависимости от выбора пользователем в Google Sheets

Question

Имеются категории, с подгруппами/подкатегориями. Между собой они настроены соотношением идентификаторов на отдельной странице Классификаторе.

Основная категория выбирается с помощью выпадающего списка, который настроен в ячейке с помощью команды "Настроить проверку данных". (В данный момент "Подкатегория1, Подкатегория2 не выводятся, на скриншоте показаны для примера, как должны быть.")

Необходимо в ячейке "Подкатегория, в зависимости от выбранной программы обучения" предлагать пользователю список в зависимости от выбора основной категории "Программа обучения".

Возможно ли это сделать с помощью правил "Ваша формула"? Или как то иначе?

Какую туда формулу вписать, либо намекните направление.

Answer 1

Только формулами и проверками данных эту задачу можно решить, но это слишком трудоемко - по мере увеличения количества подкатегорий книга будет стремительно усложняться.

Более приемлемым решением будет скрипт, который будет запускаться по событию onEdit и формировать список значений для ячейки правее только что отредактированной (изменённой):

function onEdit(event) {
  var TargetSheet = 'Work' // Имя листа с выпадающими списками
  //  Активный лист это TargetSheet?
  var ts = event.range.getSheet();
  var sname = ts.getName();
  if (sname != TargetSheet) {  return }
  
  changeDataValidation(event.range.getCell(1,1));
}

function changeDataValidation(current_cell, all_values) {
//--------------------------------------------------------------------------------------
// Просто изменяет DataValidationCriteria для следующей ячейки после редактируемой
//--------------------------------------------------------------------------------------
  var NumOfLevels = 4 // Количество уровней вложенности категорий (для примера из вопроса - 4, но может быть и больше)
  var lcol = 2; //Первая колонка для валидации; A=1, B=2 и т.д.
  var lrow = 2; //Первая строка, с которой нужно формировать выпадающие списки, выше - заголовки таблицы
  // Итак, диапазон дропбоксов - от ячейки (lrow,lcol) до ячейки (lastRow,lcol+NumOfLevels-1) на листе TargetSheet
  var LogSheet = 'Dictionary' // Лист с данными для списков, словарь, справочник
  var frstRowOfData = 1 // Первая строка справочника и первая колонка 
  var frstColOfData = 1 // Здесь они установлены в 1, но на самом деле могут быть любыми
  //--------------------------------------------------------------------------------------
    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ls = ss.getSheetByName(LogSheet); // Получить лист (весь лист!) с данными для списков 
  
  // Какие значения нужно отобразить сейчас?
  var srow = current_cell.getRow(); 
  var scol = current_cell.getColumn(); // Координаты активной ячейки (activeCell)
  // Это не заголовок таблмцы? Эиа ячека в нужном диапазоне?
  if (srow < lrow) { return }
  if (scol < lcol) { return }
  var next_level = scol-lcol+2
  if (next_level > NumOfLevels) { return }
  
  var filled_range = current_cell.offset(0, lcol-scol, 1, scol-lcol+1);
  var filter_val = filled_range.getValues();
  
  if (all_values == undefined) {
        var source_range = ls.getRange(frstRowOfData, frstColOfData, ls.getLastRow()-frstRowOfData+1, NumOfLevels);
        all_values = source_range.getValues();
  }
  
  var new_filter_array=[];
  var is_need = false;
  for (var i=0; i<all_values.length; i++){
    is_need = true;
    for (var j=0; j<filter_val[0].length; j++){
      if (all_values[i][j] != filter_val[0][j]){
        is_need = false;
        break;
      }
    }
    if (is_need == true) {
      new_filter_array.push(all_values[i][next_level-1]);
    }
  }
  var unique = new_filter_array.filter( onlyUnique ).sort();
  var new_cell = current_cell.offset(0,1);

  new_cell.setDataValidation(null)
  if (new_filter_array.length > 0) {
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);
    dv.requireValueInList(unique, true);
    new_cell.setDataValidation(dv);
  }
  if (unique.indexOf(new_cell.getValue()) < 0) {
    new_cell.setValue(null);
  }
  if (unique.length == 1) {
    new_cell.setValue(unique[0]);
  }
changeDataValidation(new_cell, all_values);
}

function onlyUnique(value, index, self) { 
    return self.indexOf(value) === index;
}

Одна из особенностей предлагаемого решения - если в формируемом списке всего одно значение (и выбирать-то нет из чего), то ячейка заполняется автоматически и скрипт срабатывает опять, чтобы заполнить список для следующей ячейки. Получается, что некоторые строки, если справочник не очень большой, можно заполнить выбрав только одно-два первых значения - остальные поля заполнит скрипт.

БЛОГ НА HUSL

Значение из диапозона, в зависимости от выбора пользователем в Google Sheets

Ответы (1 шт):