Lekce 9— práce s šablonami — Diplomy II

Sumarizace výsledků

V minulé lekci jsme si připravili tabulku pro zápis bodů a šablonu pro diplom.
Teď si musíme rozmyslet jak získáme součty bodů za jednotlivé disciplíny.
Můžeme použít klasické vzorce, tedy například do buňky C2 zapsat vzorec

='Běh v pytlích'!C2 + 'Hod na cíl'!C2 + 'Střelba'!C2 + 'Opičí dráha'!C2

Vidíte, že vzorec obsahuje názvy jednotlivých listů s disciplínami a vždy adresu stejné buňky ve které bude počet bodů.
Vzorec pak rozkopírujeme ve sloupci C všem dětem a máme hotovo. V tomto konkrétním případě by bylo použití vzorců daleko jednodušší než psát skripty.
Ale protože se učíme Apps Script, ukážeme si jak totéž udělat pomocí skriptů.

Skripty budeme používat pouze s touto konkrétní tabulkou, můžeme je proto přibalit k tabulce.

V menu zvolte Nástroje — Editor skriptu jak jsme už probírali v první lekci skript si pojmenujte a můžeme začít.

Jak budeme body za disciplíny sčítat? Nejprve budeme potřebovat seznam všech listů, na kterých jsou hodnocení disciplín.

Najít je můžeme více způsoby, například najít všechny listy tabulky a vypustit list Celkem. Nesmíme ovšem později list přejmenovat.

Další možností by mohlo být řídit se podle pořadí listů, první list Celkem vypustit. A opět, pokud bychom třeba i omylem změnili pořadí listů, přestaly by skripty fungovat.

Já jsem listy s disciplínami obarvil (klik na záložce listu a volba Změnit barvu).

Skript pak projde všechny listy tabulky a zjistí jejich barvu metodou sheet.getTabColor(). Pokud jsme barvu nenastavili, vrací tato metoda null, tu bude vracet u listu Celkem. Všechny ostatní listy s barvou zařadíme do seznamu, který budeme sčítat.

Funkce, která seznam listů s disciplínami pak může vypadat třeba takto.

function najdi_discipliny() {
 var sheet;
 var arr_sheety = [];//pole, které bude funkce vracet
 var ss = SpreadsheetApp.getActive();//vezmi aktivní tabulku
 var sheety = ss.getSheets();//seznam všech listů tabulky
 for(var i=0; i < sheety.length; i++){//cykl přes všechny listy
 if(sheety[i].getTabColor() != null){//pokud má list nastavenou barvu, bereme ho jako list s disciplínou
 arr_sheety.push(sheety[i]);//do pole si ukládáme jednotlivé listy
 }
 }
 return arr_sheety;
}

Jak budeme body jednotlivým soutěžícím sčítat? Mohli bychom napsat funkci, která by přečetla obsah buňky C2 na všech listech se soutěžemi, hodnoty sečetla a zapsala součet do buňky C2 na listu Celkem, tedy Čestmíru Hanzelkovi. A stejným způsobem by sečetla body i pro ostatní závodníky.

Funkce by samozřejmě fungovala, ale nebyla by příliš rychlá, protože čtení z tabulky i zápis je poměrně pomalá operace.
A námi napsaná funkce by postupně přečetla 24 x 4 tedy hodnotu z 96 buněk, provedla výpočet a provedla 24 zápisů výsledků, celkem tedy 120 operací čtení nebo zápisu.

S rostoucím počtem soutěží a soutěžících by počet operací rychle rostl a doba běhu funkce se dál zpomalovala.
V našem konkrétním případě by to nevadilo a součet by byl dostatečně rychlý, ale ukážeme si jak to udělat správně, tedy jak počet čtení i zápisů do tabulky minimalizovat.

Takže si napíšeme funkci, která na listu jedné soutěže načte všechny body najednou, spočítá výsledky a všechny najednou zapíše na list Celkem. Místo 96 operací čtení a 24 zápisů se tak dostaneme na 4 operace čtení a jeden zápis.
Stejný postup doporučuje Google vždy, načíst všechna potřebná data do paměti, provést výpočty a úpravy a pokud možno v jednom kroku zapsat zpět.

Naše funkce by pak mohla vypadat třeba takto.

function secti_body(){
 var sheet_data, sheet, i, j, puv_hodnota;
 var sheet_celkem = SpreadsheetApp.getActive().getSheetByName(setup.sheet_soucty_name);//list se součty
 var pocet_radku = sheet_celkem.getLastRow() - 1;//počet řádků s daty - 1 (první řádek)
 var arr_soucty = [];//pole pro průběžné součty
 var sheety = najdi_discipliny();//najdi listy s disciplínami
 for(i=0; i < sheety.length; i++){
 sheet = sheety[i];
 sheet_data = sheet.getRange(2,3, pocet_radku, 1).getValues();//načte výsledky všech lidí z listu
 for(j=0; j < pocet_radku; j++){
 puv_hodnota = arr_soucty[j] || 0;
 arr_soucty[j] = puv_hodnota + Number(sheet_data[j][0]);
 }
 }
 var arr_final = [];
 for(j=0; j < pocet_radku; j++){
 arr_final.push([arr_soucty[j]]);
 }
 sheet_celkem.getRange(2,3, pocet_radku, 1).setValues(arr_final);
}

Trochu si vysvětlíme jednotlivé kroky.

Nejprve si podle názvu najdeme list se součty, název listu máme v proměnné setup.

var sheet_celkem = SpreadsheetApp.getActive().getSheetByName(setup.sheet_soucty_name);

Dál si zjistíme počet soutěžících, čili počet řádků na listu Celkem. Pomůže nám s tím metoda getLastRow(), kterou už jsme dříve použili a víme, že vrací číslo posledního obsazeného řádku v tabulce.

V našem případě vrátí hodnotu 25. Počet o jednu snížíme, protože první řádek v tabulce zabírá hlavička s názvy.

var pocet_radku = sheet_celkem.getLastRow() - 1;

Dál si připravíme pole, do kterého si budeme body za jednotlivé disciplíny sčítat.

var arr_soucty = [];

Funkcí, kterou jsme si už připravili si načteme seznam listů s disciplínami.

var sheety = najdi_discipliny();

No a můžeme se pustit do sčítání. Použijeme 2 cykly, jeden přes všechny listy v poli sheety, tedy přes všechny disciplíny.

V něm pak provedeme druhý cyklus, který do našeho pole arr_soucty přičte každému závodníkovi body za danou disciplínu

for(i=0; i < sheety.length; i++){
  sheet = sheety[i];
  sheet_data = sheet.getRange(setup.start_row, setup.start_col, pocet_radku, 1).getValues();
  for(j=0; j < pocet_radku; j++){
    puv_hodnota = arr_soucty[j] || 0;
    arr_soucty[j] = puv_hodnota + Number(sheet_data[j][0]);
  }
}

V cyklu přes listy, tedy nejprve načteme body všech závodníků

sheet_data = sheet.getRange(setup.start_row, setup.start_col, pocet_radku, 1).getValues();

Vidíte, že začátek oblasti máme uložený v proměnné setup, abychom v případě úprav tabulky nemuseli změny provádět přímo ve funkci. Stejné souřadnice pak využijeme i při zápisu součtů.

Teď v cyklu projedeme načtené body a přičteme je do součtového pole.

for(j=0; j < pocet_radku; j++){
  puv_hodnota = arr_soucty[j] || 0;//vezmi mezisoučet, pokud neexistuje vezmi 0
  arr_soucty[j] = puv_hodnota + Number(sheet_data[j][0]);
}

Za povšimnutí stojí asi jen to, jak získáme hodnotu z pole sheet_data. Pole, které získáme metodou getValues() je vždy dvojrozměrné, stejně jako oblast, ze které data čteme.
Jednotlivé prvky pole sheet_data tedy nejsou přímo hodnoty bodů, ale opět pole obsahující jeden prvek (data jsme četli z jednoho sloupce).

Pole sheet_data tedy vypadá například takto.

[[20],[15],[7],[0],[11],[13],[2]]

Proto počet bodů získáme takto

sheet_data[j][0]

Po skončení obou cyklů máme v poli arr_soucty již všechny součty, ale nemůžeme ho přímo zapsat do tabulky.
Důvod je stejný, máme normální pole, ale pro zápis do tabulky potřebujeme stejný tvar jaký jsme získali při čtení, tedy pole o délce 24 prvků, kde každý prvek bude pole o dvou prvcích, první bude součet bodů za jednotlivé disciplíny, druhý prvek bude obsahovat text Ano nebo Ne podle výše součtu vzhledem k nastavenému limitu.

K převodu opět použijeme jednoduchý cyklus, kdy do pole arr_final vložíme jednotlivá pole o zmíněných dvou prvcích.

var arr_final = [];
for(j=0; j < pocet_radku; j++){
  soucet = arr_soucty[j]
  diplom = soucet >= limit ? 'Ano' : 'Ne';
  arr_final.push([soucet, diplom]);
}

Teď už má pole arr_final správný tvar a rozměry, takže na listu Celkem vybereme oblast pro vložení a hodnoty z arr_final tam metodou setValues() zapíšeme.

sheet_celkem.getRange(setup.start_row, setup.start_col, pocet_radku, 2).setValues(arr_final);

Máme sečtené body a v další lekci si ukážeme jak z šablony vyrobit dokument, doplnit do něj údaje konkrétního dítěte a vyrobit z něj PDF soubor.