Rippmenüü Google Sheetsis on mugav funktsioon. Seadistad andmete valideerimise ja lahter aktsepteerib ainult vahemikust pärinevaid väärtusi. Kuid on üks konks: korraga saab valida ainult ühe elemendi. Aga mis siis, kui ülesanne vajab silti urgent, budget, design, kolme väärtust ühes lahtris? Tavaliste tööriistadega pole see võimalik.

Praktikas tekib see vajadus pidevalt: kulude kategoriseerimine, ülesannete märgendamine, osalejate loetlemine. Iga kord redaktori avamine ja komadega eraldatud väärtuste lisamine raiskab aega. Lahenduseks on väike Google Apps Script, mis lisab külgriba koos märkeruutudega mis tahes andmete valideerimise vahemiku jaoks.

Allpool on samm-sammuline seadistus: skriptifaili loomisest kuni valmis „Scripts" menüüni sinu arvutustabelis. Koodi on testitud päris arvutustabelitel, see töötab ilma täiendavate õigusteta ega nõua programmeerimisoskusi.

💡 Kiire ülevaade:

• Seadista lahtrile andmete valideerimine vahemiku alusel ja vali sisendi blokeerimise asemel hoiatuse kuvamine
• Loo skriptiredaktoris kaks faili: multi-select.gs loogikaga ja dialog.html külgriba liidesega
• Salvesta projekt, värskenda arvutustabelit ja menüüribale ilmub Scripts menüüpunkt
• Vali andmete valideerimisega lahter, käivita skript ja märgi soovitud väärtused märkeruutudega
• Klõpsa Select ja lahter täidetakse valitud elementidega, mis on eraldatud komadega

1. Samm: arvutustabeli ja andmete valideerimise ettevalmistamine

Ava Google'i arvutustabel, millega töötad. Vali sihtlahter (või vahemik) ja seadista andmete valideerimine: Data → Data validation. Väljal „Criteria" vali „List from a range" ja määra loend, millest valikuid pakutakse.

Oluline punkt: ära luba valikut „Reject input", sest kui see on sees, ei saa skript lahtrisse mitut komaga eraldatud väärtust kirjutada. Piisab hoiatuse kuvamisest.

Kui sul pole valmis vahemikku, loo eraldi „Reference" leht ja loetle seal ühes veerus kõik lubatud väärtused. Viita sellele vahemikule andmete valideerimises.

2. Samm: Apps Scripti loomine

Arvutustabeli menüüs: Extensions → Apps Script. Redaktor avaneb puhta vahelehe ja tühja failiga. Kõigepealt loome serveripoolse osa.

Klõpsa File → New → Script file. Nimeta see multi-select.gs ja kleebi kood:

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
}

Mis siin toimub: onOpen lisab arvutustabeli kohandatud menüüsse kirje „Multi-select for this cell…". showDialog avab külgriba HTML-liidesega. Funktsioon valid eraldab aktiivse lahtri andmete valideerimisest lubatud väärtuste loendi. Ja fillCell kogub märgitud märkeruudud ja kirjutab need komadega eraldatult lahtrisse.

Märkus: i.substr(0, 2) filtreerib ainult need parameetrid, mille nimed algavad ch-ga, need on HTML-vormi märkeruutude identifikaatorid. Teisi parameetreid eiratakse.

Klõpsa File → Save (või Ctrl+S). Esimesel salvestamisel küsib skript õigusi, see on normaalne, ilma nendeta ei saa see lahtriandmeid lugeda ega külgriba kuvada.

3. Samm: HTML-liidese lisamine

Nüüd loome külgriba enda. Samas redaktoris: File → New → HTML file. Nimeta see dialog.html ja kleebi:

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>

Loogika on lihtne: serveripoolne silt <? var data = valid(); ?> kutsub GS-failist funktsiooni valid() ja saab lubatud väärtuste massiivi. Kui vahemik on määratud, renderdatakse märkeruudud, iga väärtuse kohta üks. Nupp Select saadab vormi fillCell-ile, nupp Refresh validation loeb andmete valideerimise uuesti (mugav lahtrite vahel liikumisel).

Kui valitud lahtril pole andmete valideerimist, kuvab paneel teate koos lingiga Google'i abisse.

Salvesta fail: File → Save. Mõlemad failid (multi-select.gs ja dialog.html) peaksid olema samas projektis.

4. Samm: käivitamine ja kasutamine

Mine tagasi arvutustabelisse ja värskenda lehte. Mõne sekundi pärast ilmub menüüribale uus Scripts menüüpunkt, see on onOpen tulemus.

Nüüd töövoog:

• Vali suvaline lahter, millel on vahemiku alusel andmete valideerimine seadistatud.
• Mine Scripts → Multi-select for this cell….
• Paremal avaneb külgriba märkeruutude loendiga, kõik väärtused sinu valideerimisvahemikust.
• Märgi soovitud elemendid ja klõpsa Select.
• Lahter täidetakse valitud väärtustega, mis on eraldatud komadega: urgent, budget, design.

Külgriba ei pea sulgema. Lihtsalt klõpsa teist lahtrit (samuti andmete valideerimisega) ja klõpsa Refresh validation, märkeruutude loend uueneb uue lahtri jaoks.

Ekraanipildil on tulemus: andmete valideerimisega lahter, aktiivsete märkeruutudega külgriba ja Scripts menüü menüüribal. Täpselt nii näebki lahendus töös välja.

5. Samm: mida saab paremaks teha

Põhiline skript lahendab ülesande „vali ripploendist mitu väärtust" ja enamiku stsenaariumide jaoks sellest piisab. Aga kui töötad arvutustabeliga intensiivselt, on paar täiendust:

• Lisa „Select All" / „Deselect All". Kaks nuppu HTML-vormis, mis programmeeritult märgivad või tühjendavad kõik märkeruudud. Paar rida JavaScriptis ja hoiad suure vahemiku puhul kokku kümneid klikke.
• Asenda koma teise eraldajaga. Funktsioonis fillCell ühendab rida s.join(', ') väärtused komaga. Kui sinu andmed sisaldavad väärtuse osana komasid, asenda ; või |-ga.
• Automaatne värskendamine lahtri muutmisel. Selle asemel, et käsitsi klõpsata „Refresh validation", saad lahtri valiku sündmusele (onSelectionChange) külge panna trigeri, kuid see nõuab veidi keerukamat koodi ja trigeri käsitsi paigaldamist.

Skripti lähtekood on Alexander Ivanovi lahenduse adaptsioon, mille postitas Arthur Attwell GitHub Gisti. Sealt leiad ka kogukonna arutelu ja täiendused.

Allpool on ingliskeelne videoõpetus, kui eelistad lugemisele vaatamist:

⁉️🤔 Korduma kippuvad küsimused

Skript ei ilmu pärast salvestamist menüüsse. Mida teha?

Värskenda arvutustabeli lehte (F5 või Ctrl+R). Kui see ei aita, kontrolli, et funktsiooni nimi oleks täpselt onOpen (tõstutundlik) ja et skriptiredaktoris poleks vigu: View → Logs näitab veajälge. Mõnikord aitab skriptiredaktori sulgemine ja uuesti avamine.

Külgriba avaneb, kuid märkeruute pole, ainult teade Data Validationi kohta.

See tähendab, et valitud lahtril pole vahemiku alusel andmete valideerimist. Vali lahter, millele sa 1. sammus valideerimise seadistasid. Kui valideerimine on seadistatud pigem lahtrivahemikule kui ühele, veendu, et aktiivne lahter on täpselt see, mis vahemikku kuulub.

Kas skripti saab kasutada ühe arvutustabeli mitmel lehel?

Jah. Skript on seotud konteineriga (arvutustabel), mitte konkreetse lehega. Andmete valideerimine töötab lehe tasemel, seadista see vajalikel lehtedel ja külgriba korjab väärtused aktiivsest lahtrist sõltumata lehest.

Pärast väärtuste valimist näitab lahter viga „Invalid value".

Sa lubasid andmete valideerimise seadetes „Reject input". Mine tagasi Data → Data validation, vali „Reject input" asemel „Show warning". Kui andmed on kriitilised, jäta hoiatus, see ei blokeeri skripti poolset kirjutamist.

Skript küsib juurdepääsu Google'i kontole, kas see on ohutu?

Jah. Õigused on vajalikud SpreadsheetApp.getActiveRange() (lahtriandmete lugemine) ja SpreadsheetApp.getUi() (menüü ja külgriba kuvamine) jaoks. Skript töötab ainult sinu arvutustabeli sees ja tal pole juurdepääsu teistele Drive'i failidele. Täielik õiguste loend on nähtav autoriseerimisaknas.

Millist mitmikvaliku skripti oma arvutustabelisse paigaldada

Kirjeldatud lahendus on minimaalne töötav raamistik. Kaks faili, mitte ühtegi välist teeki, selge loogika. Kulude jälgimiseks, ülesannete märgendamiseks ja kõigiks stsenaariumideks, kus on vaja ühte lahtrisse kirjutada mitu väärtust viiteloendist, on see enam kui piisav.

Kui töötad Google Sheetsiga palju, vaata Apps Scripti dokumentatsiooni. Võimalused ulatuvad märkeruutudest palju kaugemale: automaatne e-kirjade saatmine lahtri muutumisel, dokumentide genereerimine mallidest, integreerimine Google Calendariga. Alusta sellest skriptist ja kes teab, võib-olla kirjutad ise oma automatiseeringud.