Avaa pudotusvalikko Google Sheetsissä on kätevä ominaisuus. Määritä tietojen validointi, niin solu hyväksyy vain arvot tietyltä alueelta. Mutta siinä on yksi mutta: voit valita vain yhden kohteen. Entä jos tehtäväsi tarvitsee tunnisteen "kiireellinen, budjetti, suunnittelu", kolme arvoa yhdessä solussa? Vakiotyökaluilla se ei onnistu.

Käytännössä tämä tarve tulee vastaan jatkuvasti: kulujen luokittelu, tehtävien tägääminen, osallistujien listaaminen. Editorin avaaminen joka kerta ja arvojen lisääminen pilkuilla eroteltuna tuhlaa aikaa. Ratkaisu on pieni Google Apps Script, joka lisää sivupalkin, jossa on valintaruudut mille tahansa tietojen validointialueelle.

Alla on vaiheittainen ohje: skriptitiedoston luomisesta valmiiseen "Scripts"-valikkoon taulukossasi. Koodi on testattu oikeilla taulukoilla, toimii ilman lisäoikeuksia eikä vaadi ohjelmointitaitoja.

💡 Pikaopas:

• Määritä solulle tietojen validointi alueen perusteella ja valitse varoituksen näyttäminen syötteen hylkäämisen sijaan
• Luo kaksi tiedostoa skriptieditorissa: multi-select.gs, jossa on logiikka, ja dialog.html, jossa on sivupalkin käyttöliittymä
• Tallenna projekti, päivitä taulukko, niin Scripts-valikkokohta ilmestyy
• Valitse solu, jossa on tietojen validointi, suorita skripti ja valitse haluamasi arvot valintaruuduilla
• Klikkaa Select, niin solu täyttyy valituista kohteista pilkuilla eroteltuna

Vaihe 1: Valmistele taulukko ja tietojen validointi

Avaa Google-taulukko, jonka parissa työskentelet. Valitse kohdesolu (tai -alue) ja määritä tietojen validointi: Data → Data validation. Valitse "Criteria"-kentässä "List from a range" ja määritä lista, josta vaihtoehdot haetaan.

Tärkeä huomio: älä ota käyttöön "Reject input" -toimintoa, sillä jos teet niin, skripti ei pysty kirjoittamaan useita pilkulla erotettuja arvoja soluun. Varoituksen näyttäminen riittää.

Jos sinulla ei ole valmista aluetta, luo erillinen "Reference"-välilehti ja listaa kaikki sallitut arvot siellä yhteen sarakkeeseen. Viittaa tähän alueeseen tietojen validoinnissa.

Vaihe 2: Luo Apps Script

Taulukon valikossa: Extensions → Apps Script. Editori avautuu puhtaalla välilehdellä ja tyhjällä tiedostolla. Luomme ensin palvelinpuolen osan.

Klikkaa File → New → Script file. Nimeä se multi-select.gs ja liitä koodi:

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
}

Mitä tässä tapahtuu: onOpen lisää "Multi-select for this cell…" -kohdan taulukon mukautettuun valikkoon. showDialog avaa sivupalkin HTML-käyttöliittymällä. valid-funktio purkaa sallittujen arvojen listan aktiivisen solun tietojen validoinnista. Ja fillCell kerää valitut valintaruudut ja kirjoittaa ne soluun pilkuilla eroteltuna.

Huom: i.substr(0, 2) suodattaa vain ne parametrit, joiden nimet alkavat ch-merkeillä, nämä ovat HTML-lomakkeen valintaruutujen tunnisteita. Muut parametrit ohitetaan.

Klikkaa File → Save (tai Ctrl+S). Ensimmäisellä tallennuskerralla skripti pyytää käyttöoikeuksia, tämä on normaalia, ilman niitä se ei pysty lukemaan solutietoja ja näyttämään sivupalkkia.

Vaihe 3: Lisää HTML-käyttöliittymä

Nyt luomme itse sivupalkin. Samassa editorissa: File → New → HTML file. Nimeä se dialog.html ja liitä:

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>

Logiikka on yksinkertainen: palvelinpuolen tagi <? var data = valid(); ?> kutsuu valid()-funktiota GS-tiedostosta ja saa taulukon sallituista arvoista. Jos alue on määritetty, valintaruudut renderöidään, yksi kutakin arvoa kohden. Select-painike lähettää lomakkeen fillCell-funktiolle, Refresh validation -painike lukee tietojen validoinnin uudelleen (kätevää solujen välillä vaihdettaessa).

Jos valitussa solussa ei ole tietojen validointia, paneeli näyttää viestin, jossa on linkki Googlen ohjeeseen.

Tallenna tiedosto: File → Save. Molempien tiedostojen (multi-select.gs ja dialog.html) tulee olla samassa projektissa.

Vaihe 4: Suorita ja käytä

Palaa taulukkoon ja päivitä sivu. Parin sekunnin kuluttua valikkoriville ilmestyy uusi Scripts-kohta, tämä on onOpen-funktion tulos.

Nyt työnkulku:

• Valitse mikä tahansa solu, jolle on määritetty tietojen validointi alueen perusteella.
• Mene kohtaan Scripts → Multi-select for this cell….
• Oikealle avautuu sivupalkki, jossa on lista valintaruuduista, kaikki arvot validointialueeltasi.
• Valitse haluamasi kohteet ja klikkaa Select.
• Solu täyttyy valituista arvoista pilkuilla eroteltuna: urgent, budget, design.

Sivupalkkia ei tarvitse sulkea. Klikkaa vain toista solua (jossa on myös tietojen validointi) ja klikkaa Refresh validation, valintaruutulista päivittyy uudelle solulle.

Kuvakaappauksessa tulos: solu, jossa on tietojen validointi, sivupalkki aktiivisine valintaruutuineen ja Scripts-valikko valikkorivillä. Juuri tältä ratkaisu näyttää toiminnassa.

Vaihe 5: Mitä voi parantaa

Perusskripti ratkaisee tehtävän "valitse useita arvoja pudotusvalikosta", ja useimpiin skenaarioihin tämä riittää. Mutta jos työskentelet taulukon kanssa intensiivisesti, tässä on pari parannusta:

• Lisää "Select All" / "Deselect All". Kaksi painiketta HTML-lomakkeessa, jotka ohjelmallisesti valitsevat tai poistavat valinnan kaikista valintaruuduista. Pari riviä JavaScriptiä, ja säästät tusinan klikkauksia suurella alueella.
• Korvaa pilkku toisella erottimella. fillCell-funktiossa rivi s.join(', ') yhdistää arvot pilkulla. Jos tietosi sisältävät pilkkuja osana arvoa, korvaa merkillä ; tai |.
• Automaattinen päivitys solun vaihtuessa. Sen sijaan, että klikkaat manuaalisesti "Refresh validation", voit liittää triggerin solun valintatapahtumaan (onSelectionChange), mutta tämä vaatii hieman monimutkaisempaa koodia ja triggerin manuaalista asennusta.

Skriptin lähdekoodi on sovitus Alexander Ivanovin ratkaisusta, jonka Arthur Attwell julkaisi GitHub Gistissä. Sieltä löydät myös yhteisön keskustelua ja parannuksia.

Alla on video-opastus englanniksi, jos katsominen on lukemista mieluisampaa:

⁉️🤔 Usein kysytyt kysymykset

Skripti ei ilmesty valikkoon tallennuksen jälkeen. Mitä teen?

Päivitä taulukkosivu (F5 tai Ctrl+R). Jos se ei auta, tarkista, että funktion nimi on täsmälleen onOpen (kirjainkoko merkitsee), ja ettei skriptieditorissa ole virheitä: View → Logs näyttää pinon jäljityksen. Joskus auttaa skriptieditorin sulkeminen ja uudelleenavaaminen.

Sivupalkki avautuu, mutta valintaruutuja ei ole, vain viesti Data Validationista.

Tämä tarkoittaa, että valitussa solussa ei ole tietojen validointia alueen perusteella. Valitse solu, jolle määritit validoinnin Vaiheessa 1. Jos validointi on määritetty solualueelle yhden solun sijaan, varmista, että aktiivinen solu on juuri se, joka kuuluu alueeseen.

Voiko skriptiä käyttää useilla välilehdillä yhdessä taulukossa?

Kyllä. Skripti on liitetty säiliöön (taulukkoon), ei tiettyyn välilehteen. Tietojen validointi toimii välilehtitasolla, määritä se tarvittaville välilehdille, niin sivupalkki poimii arvot aktiivisesta solusta välilehdestä riippumatta.

Arvojen valinnan jälkeen solu näyttää virheen "Invalid value".

Otit käyttöön "Reject input" -toiminnon tietojen validoinnin asetuksissa. Palaa kohtaan Data → Data validation, valitse "Show warning" "Reject input" -vaihtoehdon sijaan. Jos data on kriittistä, jätä varoitus, se ei estä skriptin suorittamaa kirjoitusta.

Skripti pyytää pääsyä Google-tiliin, onko tämä turvallista?

Kyllä. Oikeuksia tarvitaan funktioille SpreadsheetApp.getActiveRange() (solutietojen lukeminen) ja SpreadsheetApp.getUi() (valikon ja sivupalkin näyttäminen). Skripti toimii vain taulukkosi sisällä eikä sillä ole pääsyä muihin Driven tiedostoihin. Täydellinen lista oikeuksista näkyy valtuutusikkunassa.

Mikä monivalintaskripti kannattaa asentaa taulukkoosi

Kuvattu ratkaisu on minimaalinen toimiva runko. Kaksi tiedostoa, ei yhtään ulkoista kirjastoa, selkeä logiikka. Kulujen seurantaan, tehtävien tägäämiseen ja kaikkiin skenaarioihin, joissa sinun täytyy kirjoittaa useita arvoja viitelistasta yhteen soluun, se on enemmän kuin riittävä.

Jos työskentelet Google Sheetsin kanssa paljon, tutustu Apps Script -dokumentaatioon. Mahdollisuudet ulottuvat paljon valintaruutuja pidemmälle: automaattinen sähköpostin lähetys solun muuttuessa, dokumenttien luominen malleista, integrointi Google Kalenteriin. Aloita tästä skriptistä, ja kuka tietää, saatat kirjoittaa omia automaatioitasi.