Skip to content

Все для WordPress, веб-розробки — і не тільки

👌 Множинний вибір у Google Таблицях: скрипт для комірок із перевіркою даних
Програми та сервісиВеб-розробка
5 хв1,093 слів

👌 Множинний вибір у Google Таблицях: скрипт для комірок із перевіркою даних

Випадний список у Google Таблицях, зручна штука. Налаштували перевірку даних, і комірка приймає лише значення з діапазону. Але є одна заковика: вибрати можна тільки один пункт. А якщо в задачі потрібен тег «терміново, бюджетно, дизайн», три значення в одній комірці? Стандартними засобами, ніяк.

На практиці ця потреба виникає постійно: категоризація витрат, теги до задач, списки учасників. Щоразу відкривати редактор і дописувати значення через кому, гаяти час. Розв'язання, маленький скрипт на Google Apps Script, який додає бічну панель із чекбоксами для будь-якого діапазону перевірки даних.

Нижче, покрокове налаштування: від створення файлу скрипту до готового меню «Scripts» у вашій таблиці. Код перевірено на реальних таблицях, працює без додаткових дозволів і не потребує знання програмування.

💡 Швидкий огляд:

  • Налаштуйте перевірку даних для комірки за діапазоном і виберіть показ попередження замість відхилення вводу
  • Створіть два файли в редакторі скриптів: multi-select.gs із логікою та dialog.html з інтерфейсом бічної панелі
  • Збережіть проєкт, оновіть таблицю, у меню з'явиться пункт Scripts
  • Виберіть комірку з перевіркою даних, запустіть скрипт і позначте потрібні значення чекбоксами
  • Натисніть Select, комірка заповниться вибраними пунктами через кому

Крок 1: Готуємо таблицю та перевірку даних

Відкрийте Google Таблицю, з якою працюєте. Виділіть цільову комірку (або діапазон) і задайте перевірку даних: Дані → Налаштувати перевірку даних. У полі «Правила» виберіть «Значення з діапазону» та вкажіть список, з якого братимуться варіанти.

Важливий момент: не вмикайте «Відхиляти ввід», якщо увімкнути, скрипт не зможе записати в комірку кілька значень через кому. Достатньо показувати попередження.

Якщо готового діапазону немає, створіть окремий аркуш «Довідник» і перелічіть там усі допустимі значення у стовпчик. Посилайтеся на цей діапазон у перевірці даних.

Крок 2: Створюємо скрипт Apps Script

У меню Таблиці: Розширення → Apps Script. Відкриється редактор, чиста вкладка з порожнім файлом. Перш за все створимо серверну частину.

Натисніть Файл → Створити → Файл скрипту. Назвіть його multi-select.gs і вставте код:

1function onOpen(e) {
2  SpreadsheetApp.getUi()
3    .createMenu('Scripts')
4    .addItem('Multi-select for this cell...', 'showDialog')
5    .addToUi();
6}
7 
8function showDialog() {
9  var html = HtmlService.createTemplateFromFile('dialog').evaluate();
10  SpreadsheetApp.getUi()
11    .showSidebar(html);
12}
13 
14var valid = function() {
15  try {
16    return SpreadsheetApp.getActiveRange()
17      .getDataValidation()
18      .getCriteriaValues()[0]
19      .getValues();
20  } catch(e) {
21    return null;
22  }
23};
24 
25function fillCell(e) {
26  var s = [];
27  for (var i in e) {
28    if (i.substr(0, 2) == 'ch') s.push(e[i]);
29  }
30  if (s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
31}

Що тут відбувається: onOpen додає пункт «Multi-select for this cell…» до користувацького меню таблиці. showDialog відкриває бічну панель з HTML-інтерфейсом. Функція valid витягує список допустимих значень із перевірки даних активної комірки. А fillCell збирає позначені чекбокси та записує їх у комірку через кому.

Зверніть увагу: i.substr(0, 2) фільтрує лише ті параметри, чиє ім'я починається з ch, це ідентифікатори чекбоксів з HTML-форми. Решта параметрів ігноруються.

Натисніть Файл → Зберегти (або Ctrl+S). Під час першого збереження скрипт запитає дозволи — це нормально, без них він не зможе читати дані комірки та показувати бічну панель.

Крок 3: Додаємо HTML-інтерфейс

Тепер створимо саму бічну панель. У тому ж редакторі: Файл → Створити → HTML-файл. Назвіть dialog.html і вставте:

1<div style="font-family: sans-serif;">
2  <? var data = valid(); ?>
3  <form id="form" name="form">
4    <? if (Object.prototype.toString.call(data) === '[object Array]') { ?>
5      <? for (var i = 0; i < data.length; i++) { ?>
6        <? for (var j = 0; j < data[i].length; j++) { ?>
7          <input type="checkbox"
8                 id="ch<?= '' + i + j ?>"
9                 name="ch<?= '' + i + j ?>"
10                 value="<?= data[i][j] ?>">
11          <?= data[i][j] ?><br>
12        <? } ?>
13      <? } ?>
14    <? } else { ?>
15      <p>This cell has no
16        <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.
17      </p>
18    <? } ?>
19    <input type="button" value="Select"
20           onclick="google.script.run.fillCell(this.parentNode)" />
21    <input type="button" value="Refresh validation"
22           onclick="google.script.run.showDialog()" />
23  </form>
24</div>

Логіка проста: серверний тег <? var data = valid(); ?> викликає функцію valid() із GS-файлу та отримує масив допустимих значень. Якщо діапазон задано, рендеряться чекбокси, по одному на кожне значення. Кнопка Select надсилає форму до fillCell, кнопка Refresh validation перечитує перевірку даних (зручно під час перемикання між комірками).

Якщо виділена комірка не має перевірки даних, панель покаже повідомлення з посиланням на довідку Google.

Збережіть файл: Файл → Зберегти. Обидва файли (multi-select.gs і dialog.html) мають лежати в одному проєкті.

Крок 4: Запускаємо та користуємося

Поверніться до таблиці й оновіть сторінку. За кілька секунд у рядку меню з'явиться новий пункт Scripts, це результат роботи onOpen.

Тепер робочий цикл:

  • Виберіть будь-яку комірку, для якої налаштовано перевірку даних за діапазоном.
  • Перейдіть у Scripts → Multi-select for this cell….
  • Праворуч відкриється бічна панель зі списком чекбоксів, усі значення з вашого діапазону перевірки.
  • Позначте потрібні пункти та натисніть Select.
  • Комірка заповниться вибраними значеннями через кому: срочно, бюджетно, дизайн.

Бічну панель можна не закривати. Просто клацніть іншу комірку (теж із перевіркою даних) і натисніть Refresh validation, список чекбоксів оновиться під нову комірку.

Бічна панель скрипта з чекбоксами для вибору значень

На скриншоті, результат: комірка з перевіркою даних, бічна панель з активними чекбоксами та меню Scripts у рядку. Саме так рішення виглядає в роботі.

Крок 5: Що можна покращити

Базовий скрипт розв'язує задачу «вибрати кілька значень із випадного списку», і для більшості сценаріїв цього достатньо. Але якщо ви працюєте з таблицею інтенсивно, є кілька доопрацювань:

  • Додати «Select All» / «Deselect All». Дві кнопки в HTML-формі, які програмно встановлюють або знімають усі чекбокси. Кілька рядків на JavaScript, і економія десятка кліків на великому діапазоні.
  • Замінити кому на інший роздільник. У функції fillCell рядок s.join(', ') склеює значення через кому. Якщо у ваших даних кома є частиною значення, замініть на ; або |.
  • Автооновлення під час зміни комірки. Замість ручного натискання «Refresh validation» можна повісити тригер на подію вибору комірки (onSelectionChange), але це потребує трохи складнішого коду та встановлення тригера вручну.

Вихідний код скрипту, адаптація рішення Олександра Іванова, викладена Arthur Attwell на GitHub Gist. Там же можна переглянути обговорення та доопрацювання від спільноти.

Нижче, відеоінструкція англійською, якщо зручніше дивитися, а не читати:

⁉️🤔 Часті запитання

Скрипт не з'являється в меню після збереження. Що робити?

Оновіть сторінку таблиці (F5 або Ctrl+R). Якщо не допомогло, перевірте, чи функція називається саме onOpen (регістр важливий), і чи немає в редакторі скриптів помилок: Вид → Логи покаже stack trace. Іноді допомагає закрити редактор скриптів і відкрити заново.

Бічна панель відкривається, але чекбоксів немає, лише повідомлення про Data Validation.

Це означає, що виділена комірка не має перевірки даних за діапазоном. Виберіть комірку, для якої ви налаштовували перевірку на Кроці 1. Якщо перевірку налаштовано на діапазон комірок, а не на одну, переконайтеся, що активною є саме та комірка, яка входить до діапазону.

Чи можна використовувати скрипт на кількох аркушах однієї таблиці?

Так. Скрипт прив'язаний до контейнера (таблиці), а не до конкретного аркуша. Перевірка даних працює на рівні аркуша, налаштуйте її на потрібних аркушах, і бічна панель підхоплюватиме значення з активної комірки незалежно від аркуша.

Після вибору значень комірка показує помилку «Неправильне значення».

Ви увімкнули «Відхиляти ввід» у налаштуваннях перевірки даних. Поверніться до Дані → Налаштувати перевірку даних, виберіть «Показувати попередження» замість «Відхиляти ввід». Якщо дані критичні, залиште попередження, воно не блокує запис скриптом.

Скрипт запитує доступ до Google-акаунту — це безпечно?

Так. Дозволи потрібні для SpreadsheetApp.getActiveRange() (читання даних комірки) та SpreadsheetApp.getUi() (відображення меню та бічної панелі). Скрипт працює лише всередині вашої таблиці та не має доступу до інших файлів на Диску. Повний перелік дозволів видно у вікні авторизації.

Який скрипт множинного вибору поставити у свою таблицю

Описане рішення, мінімальний робочий каркас. Два файли, жодної зовнішньої бібліотеки, зрозуміла логіка. Для обліку витрат, тегування задач і будь-яких сценаріїв, де в одну комірку потрібно записати кілька значень із довідника, його вистачає з головою.

Якщо ви працюєте з Google Таблицями щільно, зазирніть у документацію Apps Script. Можливості виходять далеко за межі чекбоксів: автоматичне надсилання листів у разі зміни комірки, генерація документів за шаблоном, інтеграція з Google Calendar. Почніть із цього скрипту, а там, гляди, і свої автоматизації напишете.

Редакція
РедакціяРедакційна команда→ Усі публікації

Професійна команда авторів та редакторів, що спеціалізуються на створенні якісного контенту.