Lista rozwijana w Arkuszach Google, wygodna rzecz. Skonfigurowali Państwo sprawdzanie poprawności danych i komórka przyjmuje tylko wartości z zakresu. Jest jednak pewien haczyk: można wybrać tylko jedną pozycję. A co, jeśli w zadaniu potrzebny jest tag „pilne, budżetowo, design", czyli trzy wartości w jednej komórce? Standardowymi środkami, nie da się.

W praktyce ta potrzeba pojawia się stale: kategoryzacja wydatków, tagi do zadań, listy uczestników. Za każdym razem otwierać edytor i dopisywać wartości po przecinku, to strata czasu. Rozwiązaniem jest mały skrypt w Google Apps Script, który dodaje panel boczny z checkboxami dla dowolnego zakresu sprawdzania poprawności danych.

Poniżej konfiguracja krok po kroku: od utworzenia pliku skryptu do gotowego menu „Scripts" w Państwa arkuszu. Kod został sprawdzony na rzeczywistych arkuszach, działa bez dodatkowych uprawnień i nie wymaga znajomości programowania.

💡 Szybki przegląd:

• Proszę skonfigurować sprawdzanie poprawności danych dla komórki według zakresu i wybrać pokazywanie ostrzeżenia zamiast odrzucania danych
• Proszę utworzyć dwa pliki w edytorze skryptów: multi-select.gs z logiką oraz dialog.html z interfejsem panelu bocznego
• Proszę zapisać projekt, odświeżyć arkusz, w menu pojawi się pozycja Scripts
• Proszę wybrać komórkę ze sprawdzaniem poprawności danych, uruchomić skrypt i zaznaczyć potrzebne wartości checkboxami
• Proszę kliknąć Select, komórka wypełni się wybranymi pozycjami oddzielonymi przecinkami

Krok 1: Przygotowujemy arkusz i sprawdzanie poprawności danych

Proszę otworzyć Arkusz Google, z którym Państwo pracują. Proszę zaznaczyć docelową komórkę (lub zakres) i ustawić sprawdzanie poprawności danych: Dane → Skonfiguruj sprawdzanie poprawności danych. W polu „Reguły" proszę wybrać „Wartości z zakresu" i wskazać listę, z której będą pobierane opcje.

Ważna kwestia: proszę nie włączać „Odrzucaj dane", jeśli jest włączone, skrypt nie będzie mógł zapisać w komórce kilku wartości oddzielonych przecinkami. Wystarczy pokazywać ostrzeżenie.

Jeśli nie ma gotowego zakresu, proszę utworzyć osobny arkusz „Słownik" i wypisać tam wszystkie dozwolone wartości w kolumnie. Proszę odwołać się do tego zakresu w sprawdzaniu poprawności danych.

Krok 2: Tworzymy skrypt Apps Script

W menu Arkusza: Rozszerzenia → Apps Script. Otworzy się edytor, czysta karta z pustym plikiem. Przede wszystkim utworzymy część serwerową.

Proszę kliknąć Plik → Utwórz → Plik skryptu. Proszę nazwać go multi-select.gs i wkleić kod:

1
function onOpen(e) {
2
  SpreadsheetApp.getUi()
3
    .createMenu('Scripts')
4
    .addItem('Multi-select for this cell...', 'showDialog')
5
    .addToUi();
6
}
7
 
8
function showDialog() {
9
  var html = HtmlService.createTemplateFromFile('dialog').evaluate();
10
  SpreadsheetApp.getUi()
11
    .showSidebar(html);
12
}
13
 
14
var valid = function() {
15
  try {
16
    return SpreadsheetApp.getActiveRange()
17
      .getDataValidation()
18
      .getCriteriaValues()[0]
19
      .getValues();
20
  } catch(e) {
21
    return null;
22
  }
23
};
24
 
25
function 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
}

Co tu się dzieje: onOpen dodaje pozycję „Multi-select for this cell…" do menu użytkownika arkusza. showDialog otwiera panel boczny z interfejsem HTML. Funkcja valid pobiera listę dozwolonych wartości ze sprawdzania poprawności danych aktywnej komórki. Natomiast fillCell zbiera zaznaczone checkboxy i zapisuje je w komórce oddzielone przecinkami.

Proszę zwrócić uwagę: i.substr(0, 2) filtruje tylko te parametry, których nazwa zaczyna się od ch, są to identyfikatory checkboxów z formularza HTML. Pozostałe parametry są ignorowane.

Proszę kliknąć Plik → Zapisz (lub Ctrl+S). Podczas pierwszego zapisywania skrypt poprosi o uprawnienia, to normalne, bez nich nie będzie mógł odczytywać danych komórki i pokazywać panelu bocznego.

Krok 3: Dodajemy interfejs HTML

Teraz utworzymy sam panel boczny. W tym samym edytorze: Plik → Utwórz → Plik HTML. Proszę nazwać dialog.html i wkleić:

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>

Logika jest prosta: znacznik serwerowy <? var data = valid(); ?> wywołuje funkcję valid() z pliku GS i otrzymuje tablicę dozwolonych wartości. Jeśli zakres jest ustawiony, renderowane są checkboxy, po jednym dla każdej wartości. Przycisk Select wysyła formularz do fillCell, przycisk Refresh validation ponownie odczytuje sprawdzanie poprawności danych (wygodne przy przełączaniu się między komórkami).

Jeśli zaznaczona komórka nie ma sprawdzania poprawności danych, panel pokaże komunikat z linkiem do pomocy Google.

Proszę zapisać plik: Plik → Zapisz. Oba pliki (multi-select.gs i dialog.html) muszą znajdować się w jednym projekcie.

Krok 4: Uruchamiamy i korzystamy

Proszę wrócić do arkusza i odświeżyć stronę. Po kilku sekundach w pasku menu pojawi się nowa pozycja Scripts, to wynik działania onOpen.

Teraz cykl pracy:

• Proszę wybrać dowolną komórkę, dla której skonfigurowano sprawdzanie poprawności danych według zakresu.
• Proszę przejść do Scripts → Multi-select for this cell….
• Po prawej stronie otworzy się panel boczny z listą checkboxów, wszystkie wartości z Państwa zakresu sprawdzania.
• Proszę zaznaczyć potrzebne pozycje i kliknąć Select.
• Komórka wypełni się wybranymi wartościami oddzielonymi przecinkami: срочно, бюджетно, дизайн.

Panelu bocznego nie trzeba zamykać. Wystarczy kliknąć inną komórkę (również ze sprawdzaniem poprawności danych) i kliknąć Refresh validation, lista checkboxów zaktualizuje się dla nowej komórki.

Na zrzucie ekranu wynik: komórka ze sprawdzaniem poprawności danych, panel boczny z aktywnymi checkboxami i menu Scripts na pasku. Dokładnie tak rozwiązanie wygląda w działaniu.

Krok 5: Co można ulepszyć

Podstawowy skrypt rozwiązuje zadanie „wybierz kilka wartości z listy rozwijanej" i dla większości scenariuszy to wystarcza. Ale jeśli pracują Państwo z arkuszem intensywnie, jest kilka usprawnień:

• Dodać „Select All" / „Deselect All". Dwa przyciski w formularzu HTML, które programowo zaznaczają lub odznaczają wszystkie checkboxy. Kilka linijek w JavaScript i oszczędność kilkunastu kliknięć na dużym zakresie.
• Zamienić przecinek na inny separator. W funkcji fillCell wiersz s.join(', ') łączy wartości przecinkiem. Jeśli w Państwa danych przecinek jest częścią wartości, proszę zamienić na ; lub |.
• Automatyczne odświeżanie przy zmianie komórki. Zamiast ręcznego klikania „Refresh validation" można podpiąć wyzwalacz na zdarzenie wyboru komórki (onSelectionChange), ale to wymaga nieco bardziej złożonego kodu i ręcznego ustawienia wyzwalacza.

Kod źródłowy skryptu, adaptacja rozwiązania Ołeksandra Iwanowa, udostępniona przez Arthura Attwella na GitHub Gist. Tam również można przejrzeć dyskusję i ulepszenia od społeczności.

Poniżej instrukcja wideo po angielsku, jeśli wygodniej jest Państwu oglądać niż czytać:

⁉️🤔 Często zadawane pytania

Skrypt nie pojawia się w menu po zapisaniu. Co robić?

Proszę odświeżyć stronę arkusza (F5 lub Ctrl+R). Jeśli to nie pomogło, proszę sprawdzić, czy funkcja nazywa się dokładnie onOpen (wielkość liter ma znaczenie) i czy w edytorze skryptów nie ma błędów: Widok → Logi pokaże stack trace. Czasami pomaga zamknięcie edytora skryptów i otwarcie go ponownie.

Panel boczny otwiera się, ale nie ma checkboxów, jest tylko komunikat o Data Validation.

Oznacza to, że zaznaczona komórka nie ma sprawdzania poprawności danych według zakresu. Proszę wybrać komórkę, dla której konfigurowali Państwo sprawdzanie w Kroku 1. Jeśli sprawdzanie jest skonfigurowane dla zakresu komórek, a nie dla jednej, proszę upewnić się, że aktywna jest właśnie ta komórka, która wchodzi w skład zakresu.

Czy można używać skryptu na kilku arkuszach jednego pliku?

Tak. Skrypt jest powiązany z kontenerem (plikiem arkusza), a nie z konkretnym arkuszem. Sprawdzanie poprawności danych działa na poziomie arkusza, proszę skonfigurować je na potrzebnych arkuszach, a panel boczny będzie pobierał wartości z aktywnej komórki niezależnie od arkusza.

Po wybraniu wartości komórka pokazuje błąd „Nieprawidłowa wartość".

Włączyli Państwo „Odrzucaj dane" w ustawieniach sprawdzania poprawności danych. Proszę wrócić do Dane → Skonfiguruj sprawdzanie poprawności danych, wybrać „Pokaż ostrzeżenie" zamiast „Odrzucaj dane". Jeśli dane są krytyczne, proszę zostawić ostrzeżenie, nie blokuje ono zapisu przez skrypt.

Skrypt prosi o dostęp do konta Google, czy to bezpieczne?

Tak. Uprawnienia są potrzebne dla SpreadsheetApp.getActiveRange() (odczyt danych komórki) oraz SpreadsheetApp.getUi() (wyświetlanie menu i panelu bocznego). Skrypt działa tylko wewnątrz Państwa arkusza i nie ma dostępu do innych plików na Dysku. Pełna lista uprawnień jest widoczna w oknie autoryzacji.

Jaki skrypt wielokrotnego wyboru wstawić do swojego arkusza

Opisane rozwiązanie to minimalny, działający szkielet. Dwa pliki, żadnej zewnętrznej biblioteki, zrozumiała logika. Do ewidencji wydatków, tagowania zadań i wszelkich scenariuszy, gdzie w jednej komórce trzeba zapisać kilka wartości ze słownika, w zupełności wystarcza.

Jeśli pracują Państwo z Arkuszami Google intensywnie, proszę zajrzeć do dokumentacji Apps Script. Możliwości wykraczają daleko poza checkboxy: automatyczne wysyłanie e-maili przy zmianie komórki, generowanie dokumentów według szablonu, integracja z Google Calendar. Proszę zacząć od tego skryptu, a tam, kto wie, może i własne automatyzacje Państwo napiszą.