Всем привет!
На днях столкнулся с задачей, при которой, в зависимости от значений одной ячейки, необходимо выводить выпадающий список в соседней ячейке. Решения "из-под коробки" Google не предусмотрел, однако, такую задачу можно было решать через использование Apps Script Google.
За основу был взят скрипт, который рассматривался в видео https://www.youtube.com/watch?v=cT4P7GBht_8, но, как следует из комментариев под ним – остро не хватает пояснений к коду скрипта. Что мы сегодня и сделаем.
Для начала откроем нашу Google.Таблицу выберем вкладку «Расширения» и Apps Script
Вместо стартовой заготовки вставляем наш код:
И не забываем сохранить проект:
Может потребоваться разрешение к вашим данным:
И разрешаем доступ:
После разрешения доступа, рекомендуется обновить страницу со скриптом (например, нажав клавишу F5).
Сама функция с двумя зависимыми списками (вы можете как убрать 2-й зависимый список, так и добавить еще больше зависимостей)
function onEdit() { //событие onEdit срабатывает при изменении на сайте
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName("Итог"); //Указываем название вкладки, где будет функционировать скрипт зависимых выпадающих список.
let sheedDb = ss.getSheetByName("База1"); //Указываем название вкладки, где будут лежать зависимые списки.
let sheedDb2 = ss.getSheetByName("База2"); //Указываем название вкладки, где будут лежать вторые зависимые списки.
let sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); //Фиксируем имя активной вкладки
let ar = sheet.getActiveCell();
if(ar.getColumn()==1 && sheetName == "Итог") { //1 - № колонки с которой работает скрипт (если изменения будут на любой другой колонке, то ничего не произойдет) + дополнительная проверка по активной вкладке
let valToFind = ar.getValue();
ar.offset(0, 1).clearContent().clearDataValidations(); //очищаем ячейку справа (где должен появляться зависимый выпадающий список) от ячейки, значение которого было изменено.
ar.offset(0, 2).clearContent().clearDataValidations(); //очищаем ячейку справа справа (при наличие 2й зависимости) (где должен появляться зависимый выпадающий список) от ячейки, значение которого было изменено. Если будут еще зависимости они добавляются ниже
let values = sheedDb.getRange(1,1,1,sheedDb.getLastColumn()).getValues(); //просматриваем вкладку с базой списков, цифры далее: номер строки (ряда), номер колонки, количество строк, количество колонок (в нашем примере количество колонок – динамическое))
let col = values[0].indexOf(valToFind)+1;
let range = sheedDb.getRange(2, col, 6,1); // 2 - строка, с которой идёт счет, 6 - количество строк, если услуг будет больше надо увеличивать.
let validation = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
ar.offset(0, 1).setDataValidation(validation); //соседняя (правая) колонка
}else if(ar.getColumn()==2) { //2 - № колонки с которой работает скрипт 2й зависимости (если изменения будут на любой другой колонке, то ничего не произойдет)
let valToFind = ar.getValue();
ar.offset(0, 1).clearContent().clearDataValidations(); //очищаем ячейку справа (где должен появляться зависимый выпадающий список) от ячейки, значение которого было изменено.
let values = sheedDb2.getRange(1,1,1,sheedDb2.getLastColumn()).getValues(); //просматриваем вкладку с базой списков, цифры далее: номер строки (ряда), номер колонки, количество строк, количество колонок (в нашем примере количество колонок – динамическое))
let col = values[0].indexOf(valToFind)+1;
let range = sheedDb2.getRange(2, col, 6,1); // 2 - строка, с которой идёт счет, 6 - количество строк, если услуг будет больше надо увеличивать.
let validation = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
ar.offset(0, 1).setDataValidation(validation); //соседняя (правая) колонка
} else {return} //если не 1 и не 2 колонки изменились - не делаем ничего.
}
Данные для зависимых выпадающих списков размечаются следующим способом:
Заголовок колонки - это значение предыдущего выпадающего списка, значения под ним - это зависимые значения нового выпадающего списка.
Таблица с демонстрационными возможностями скрипта доступна по ссылке.
Я привык к старому формату выпадающего списка «стрелка»
Частые вопросы по зависимым выпадающим спискам
1. Сколько зависимостей можно сделать?
Количество зависимых выпадающих списков не ограничено
2. Как поменять номер активной колонки, по которой будет срабатывать скрипт?
В скрипте за номер активной колонки отвечает функция ar.getColumn() (в варианте с двумя зависимостями это 8 и 17 строчки, соответственно 1-я и 2-я колонки).
3. Функция не показывает больше 6 значений в зависимом выпадающем списке, что делать?
За количество вариаций в выпадающем списке отвечает переменная range (строчки 14 и 22 в примере), увеличьте в .getRange(2, col, 6,1) значение 6 на необходимое вам значение.
4. Можно ли не удалять значение зависимого списка в случае изменения основного?
Да, можно, за удаление значений отвечают функции .clearContent().clearDataValidations(); (строчки 10,11 и 19 в примере кода).
Если есть вопросы - пишите! Лучшая благодарность - это ваши пальцы вверх, подписка на канал и комментарии.