Связанные выпадающие списки в табличках Google

Ты получишь возможность сделать в табличках Google свои связанные выпадающие списки:

Всего в несколько простых шагов.

Шаг 1. Скачай себе файл с примером

Перейдя по ссылкам ниже, ты получишь доступ к готовым файлам:

1. Базовая версия (описана в видео  )
2. Новая версия — доработанная и улучшенная.

Совет. Эти файлы открыты для просмотра, но не для редактирования. Поэтому сразу создай свою копию: меню Файл → Создать копию.  Далее об этом я расскажу подробнее.

Сравни 2 версии скрипта:

		Базовая версия	Новая версия
	средняя оценка пользователей	★★★☆☆ - 3,4	★★★★★ - 4,7
		оценить	оценить
	Среднее время выполнения в секундах	0,3	0,4
	бесплатно и не для продажи
	Легко увеличить к-во связанных выпадающих списков
	автоматически заполняет единственное значение
	Работает с дублями
	Не нужно сортировать исходную таблицу
	Работает с числами и с текстом
	Работает с дробными десятичными числами
	функция "умного" удаления
	Работает с датами

Ты можешь попробовать обе версии, а так же получить известие в случае появления новых версий. Ключевые вопросы по проекту я постарался осветить ниже. Жду комментариев под статьей: интересуют твои пожелания, отзывы и мысли.

#	?	=	Вопросы + Ответы
01	?	=	Быстродействие Сразу возникает вопрос: а каков максимальный объем данных можно загрузить на лист с данными? Есть ли предел, выше которого скрипт работать уже перестанет? Ответ, разумеется, предел есть! Но он зависит не от скрипта, а от скорости работы табличек в целом. Я рекомендую использовать скрипт с данными:  не более 5 000 строк для максимально комфортного использования. Так ты не почувствуешь, что скрипт заметно тормозит.  Можно и больше. Но всё зависит от мощности твоего компьютера. К тому же, верю, в будущем сервера и технологии табличек будут становиться всё быстрее. Я тестировал скрипт с табличкой в 200 000 строк. И он по-прежнему работал, хотя и медленно. Жду теперь твои результаты тестов скорости.
02	?	=	Почему не выходит редактировать файл с примером Доступ на редактирование файла с готовым скриптом я не даю, т.к. скрипт нужен всем. Ты откроешь мой пример и обнаружишь, что менять в нем ничего нельзя. Но можно зайти в меню «файл → Создать копию» и тогда уже это будет твоя копия. Делай с ней, что хочешь, используй, дорабатывай под свои нужды, пиши мне свои отзывы и пожелания, наиболее частые из которых я обязательно учту при дальнейшей разработке проекта.
03	?	=	Где находится скрипт Настройка скрипта вообще не составит тебе труда. Для начала зайди в место, где этот скрипт находится. Для этого можно нажать меню «Инструменты → редактор скриптов». А еще, если ты пользуешься браузером Chrome, ты можешь воспользоваться сочетанием клавиш: [Alt + T + E], которое можно запомнить по первым буквам названия меню на английском Tools → Editor. Совет: используй разные сочетания клавиш в табличках Google (и не только). Это значительно ускорит тебе работу. Но продолжим. Когда ты зайдешь в редактор скриптов, ты там увидишь текст моего скрипта. Он длинный: Но тебе потребуется только поменять настройки, которые я специально разместил в самом начале скрипта. Тут потребуется хорошо потрудится: изменить пару цифр и букв, думаю, справишься ;) Для справки. К каждой таблице Goolge может быть привязан свой набор скриптов. Для использования того же скрипта в другом файле, необходимо скопировать весь текст кода в редактор скриптов другого файла.
04	?	=	Что конкретно менять в скрипте Итак, ты уже открыл редактор скриптов и хочешь поменять настройки «под себя». Там так и будет написано: «Изменить настройки». Давай разберёмся по пунктам. var TargetSheet = 'Основной' // имя листа, на котором настроены выпадающие списки ↑ Вместо слова «Основной» вставь имя рабочего листа, на котором ты будешь делать связанные выпадающие списки. var LogSheet = 'Данные' // имя листа с данными для списка ↑ Создай лист с исходными данными для списка, а его название впиши в скрипт. var NumOfLevels = 4 // количество уровней выпадающего списка ↑ В нашем примере 4 связанных выпадающих списка. Если нужно, замени их количество на свое, впиши вместо 4 свое число. var lcol = 2; // номер колонки слева, с которой начинается первый список; // A = 1, B = 2, etc. ↑ Впиши номер колонки, с которой начинается твоя таблица. var lrow = 2; // номер строки, начиная с которой срабатывает список ↑ Шапка таблицы не должна затрагиваться скриптом, поэтому впиши сюда номер строки, с которой начинаются твои данные.
05	?	=	Как приготовить данные Данные формируются на отдельном листе. В моем примере имеется 4 уровня вложенных списков: Планета → Страна → Континент → Город.  При желании можно добавить еще парочку в конце: Район → Улица,  или в начале: Вселенная → Галактика. Как тебе сделать свой список? Просто сделай его по примеру моего: Пропиши имена категорий-уровней таблицы в первой строчке, начиная с клетки A1. Начни заполнять с последнего уровня: выпиши все возможные варианты. Двигаясь к первому уровню, вводи все значения, не оставляя пустых ячеек. Некоторые названия придётся повторить, это необходимо для правильной работы скрипта. Когда данные будут готовы, можно двигаться дальше. В будущем, их можно пополнять новыми данными. Отлично! Данные готовы, осталось совсем немного настроить таблицу. Не спеши сразу заполнять все данные, для тестирования достаточно нескольких строк. Помни, что данные должны быть едиными: под ними ничего не записывай,  ячейки в них не объединяй.
06	?	=	Как сделать первый выпадающий список? Первый список нужно будет настроить вручную. Полученные данные нельзя сразу использовать для создания первого выпадающего списка. В моем примере первый уровень данных содержит названия планет, и они повторяются т.к. одна планета содержит несколько континентов, стран и так далее. А нам нужен список уникальных значений. Для получения списка уникальных значений, используй формулу: = UNIQUE(A2:A) Она очень простая и выдает список уникальных значений. Эту формулу рекомендую написать на 4 колонки правее основных данных: Либо вообще размести ее на новом листе: = UNIQUE(Данные!A2:A) После того, как ты получишь список, переходи на свой рабочий лист. Выдели диапазон ячеек, в котором будет находиться будущий выпадающий список. Иди в меню Данные → Проверка данных... и выбери там свой список. Совет. При выборе данных для выпадающего списка ты можешь учесть необходимость в добавлении новых значений в дальнейшем. Если данные содержатся в диапазоне A1:A20, то ты можешь выбрать бо́льший диапазон для проверки данных: A1:A100. Пустые ячейки будут игнорироваться. После этой настройки связанные выпадающие списки уже можно использовать! Теперь поговорим о приятных деталях.
07	?	=	Как увеличить количество связанных списков Об этом я уже упомянул ранее. Для этого зайди в тело скрипта (Инструменты → редактор скриптов) и поменяй там одно число: var NumOfLevels = 4 // количество уровней выпадающего списка ↑ В нашем примере 4 связанных выпадающих списка. Вместо 4 поставь свое число. И, конечно, не забудь под это изменить данные для выбора значений выпадающих списков. Как видишь, это сделать очень легко.
08	?	=	Как скрипт автоматически заполняет единственное значение Если по категории, выбранный тобой элемент из выпадающего списка имеет единственные значения в подчиненных списках, то нет смысла давать их на выбор. Например, мы выбрали часть света = "Азия". В справке "Азии" соответствует только одна страна = "Китай ", а для "Китая" найден лишь один город = "Пекин". Вот, что произойдет при выборе "Азии": скрипт автоматически вставит "Китай" и "Пекин" в нужные ячейки. Правда, удобно?
09	?	=	Как скрипт работает с дублями Часто бывает необходимость создания списка позиций с типовыми параметрами: цвет, размер, модель, ценовая категория и прочее. Эти значения могут повторяться от одной группы к другой. Получаются дубли. Хорошая новость! В текущей версии скрипта списки могут содержать дубли, они будут нормально считываться.
10	?	=	Нужно ли сортировать исходную таблицу Нет. Твои исходные данные могут быть сортированы в произвольном порядке. Это может быть удобно. Добавляя новые данные тебе не нужно беспокоиться о порядке строк. К тому же легче автоматизировать процесс. Совет. Новые данные можно заполнять вручную, а можно автоматизировать: например, при помощи другого скрипта, или с помощью форм Google. Работа с другими типами данных Пока реализована работа со следующими типами данных: [Мама мыла раму] = простой текст [100500] = числа [3,14159265356] = в том числе дробные,  [100/500;100;Текст] = а так же группы данных, состоящих как из текста, так и из чисел [01.08.2050] = дата и время — пока в разработке. Если у тебя возникает проблема при работе с числами, обязательно напиши мне об этом в комментариях!
11	?	=	Про функцию «умного» удаления Мне надоели остатки старых кусков проверки данных, которые появляются, когда я хочу удалить что-то в своих списках. Поэтому я настроил это: Во-первых, как видишь, можно автоматически копировать часть списков, а остальные подтянутся. Ну и, выбрав первый уровень списков и нажав [Delete], ты можешь удалить все следы списков, которые были правее.
12	?	=	Лучше один раз увидеть Это видео немного устарело, но описание в нем вполне подойдет и для последней версии скрипта:
13	?	=	Обратная связь Жду комментариев. Наиболее частым вопросом пользователей является: Как сделать несколько связанных списков одновременно? Сделал файл для таких случаев: Связанные выпадающие списки Google №3.2 В тексте скрипта меняйте настройки в самом начале, как и ранее, только теперь таких настроек может быть множество, в том числе на разных листах. Важно соблюсти правило: если исходные данные для списков отличаются, размещать их нужно на отдельных листах.