V minulé lekci jsme získali data ze serveru ČNB jako textový řetězec. Budeme pokračovat jeho zpracováním a uložením do tabulky.
Zpracování kurzovního lístku
Data z ČNB v textovém tvaru máme, zkusíme si napsat funkci, která je rozporcuje do tvaru vhodného k uložení do tabulky. Může vypadat například takto.
function porcuj_data(kurzy, datum){
var radky = String(kurzy).split('\n');//rozdělí celý text podle znaku pro nový řádek
var prvni_radek = String(radky.shift());//z pole odeber první řádek a pro jistotu ho převeď na text
var dilky = prvni_radek.split(' ');//rozděl podle mezery, jako první část by mělo být datum kurz. lístku
if(dilky[0] != datum){//začátek kurzovního lístku se neshoduje s datem, které potřebujeme
return false;//funkce nevrátí data, ale hodnotu false
}
var radek;
var arr_vyst = [];//pole na výstupní data
for(var i = 0;i < radky.length; i++){//cykl přes všechny zbývající řádky (první řádek už v poli není)
radek = String(radky[i]).split('|');//rozděl řádek na dílky podle znaku |
if(radek.length == 5){//kontrola, jestli řádek obsahuje správný počet položek
arr_vyst.push(radek)//přidej řádek do pole
}
}
return arr_vyst
}
Funkci si samozřejmě můžete najít přímo ve zdrojovém kódu, který máte nasdílený.
Většina kódu je asi jasná již z komentářů. Nejprve se zbavíme prvního řádku a zkontrolujeme, jestli je na něm správné datum. Pokud ne, funkce končí a vrátí false. Jinak projdeme zbývající řádky, rozdělíme každý z nich podle znaku “svislítko”| a postupně je přidáme do výstupního pole, které funkce vrátí.
Kontrola, jestli je počet dílků pět, je z důvodu, že na konci vráceného textu je prázdný řádek, který po rozdělení bude obsahovat jen jeden dílek a havarovalo by nám vložení dat do tabulky.
Obdobně bychom mohli například hlídat délku řádku a řádek s nulovou délkou přeskočit.
Zápis kurzů na list tabulky
Na samotný zápis ani nebudeme samostatnou funkci psát. Napíšeme si rovnou funkci, která provede celou operaci načtení a uložení najednou a bude později volána časovým spouštěčem.
function zpracuj_kurzy(){
var dnes = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd.MM.yyyy');//dnešní datum v českém tvaru DD.MM.RRRR
var sheet = zaloz_list(table_id, dnes);//založ list, nebo vrať existující
sheet.clear();//smaž obsah i formátování
var kurzy_txt = nacti_data_z_cnb(kurzy_url, dnes);//získej kurzy jako text
var kurzy = porcuj_data(kurzy_txt, dnes);//rozděl a vrať ve formátu pro vložení do tabulky
if(kurzy === false){//na začátku dat není datum dnes
var range = sheet.getRange(1,1);//vyber buňku A1
range.setValue('Kurzy nenalezeny');//zapiš do ní text
}else{
var range = sheet.getRange(1,1, kurzy.length, 5);//vyber oblast pro data
range.setValues(kurzy);//vlož data
nastav_vzhled(sheet);//uprav vzhled listu
smaz_prazne_bunky(sheet);//smaž prázdné buňky v tabulce
}
}
Z komentářů by snad měla být funkčnost jasná, přidám jen několik poznámek.
Vidíme první použití knihovny Utilities, která nám z javascriptového objektu Date vyrobí řetězec ve tvaru DD.MM.RRRR.
Metoda sheet.clear() komplet vyčistí list, vymaže jak data, tak i případné formátování buněk, velikost písma, orámování, podbarvení atd.
Možnosti mazání na listu jsou samozřejmě mnohem širší. Lze mazat samostatně data a zachovat formátování metodou clearContents(), smazat formátování a ponechat data pomocí clearFormats() nebo pomocí clearNotes() smazat pouze poznámky.
Pokud funkce porcuj_data() vrátí false, zapíšeme tuto informaci na list do buňky A1.
V případě, že jsou data načtena správně, vložíme je do tabulky příkazem range.setValues(kurzy).
Před vlastním vložením dat je třeba vybrat oblast (range), která musí počtem řádků a sloupců odpovídat množství dat. Jakmile velikost oblasti a dat nesouhlasí, vkládání dat skončí chybou.
Vidíme, že ve funkci se výběr oblasti provádí takto
var range = sheet.getRange(1, 1, kurzy.length, 5);
čili počet řádků určíme z délky pole kurzy, ale počet sloupců máme natvrdo zadaný. V případě, že by ČNB do kurzovního lístku například přidala další sloupec, skript by nám přestal fungovat.
Podobně, pokud by se v kurzovním lístku vyskytla chyba a na některém řádku byly jen 4 sloupce, vkládání by havarovalo, protože počet sloupců máme nastavený na 5.
Ještě si ukážeme funkce nastav_vzhled() a smaz_prazdne_bunky(), o kterých jsme zatím nemluvili, a které se volají po vložení dat.
Funkce nastav_vzhled
Po funkci budeme chtít zvýraznit první řádek, nastavit šířku sloupců a pár dalších drobností. Možnosti formátování jsou samozřejmě daleko větší. V podstatě cokoliv, co lze v tabulce nastavit ručně, je možné nastavit i pomocí skriptu. Naše funkce by mohla vypadat třeba takto.
function nastav_vzhled(sheet) {
sheet.autoResizeColumns(1, 5); // přizpůsobí šířku sloupců A - E aktuálnímu obsahu
sheet.setFrozenRows(1); // první řádek zůstane na místě při rolování tabulkou
var range = sheet.getRange(1, 1, 1, 5); // vyber oblast hlavičky
range.setBackground('#cccccc'); // nastav hlavičce světle šedé pozadí
range.setFontWeight("bold"); // nastav hlavičce tučné písmo
}
Funkčnost je asi jasná z komentářů, případně si funkci upravte a vyzkoušejte si další možnosti formátování.
Funkce smaz_prazdne_bunky
Každý nově založený list tabulky má sloupce A — Z a 1000 řádků. Další sloupce i řádky je samozřejmě možné celkem libovolně přidávat. Limitovaní jsme pouze celkovým počtem buněk tabulky, který je momentálně 5 milionů.
Do tohoto limitu se samozřejmě naše skladování kurzů bez problémů vejde, ale pokud budete uvažovat o větší aplikaci v Google Workspace (dříve G Suite), která bude pro ukládání dat využívat tabulky, je dobré o limitu vědět.
S tím souvisí i to, co by měla dělat naše funkce. Pokud víme, že na listu více dat prostě nebude, můžeme nevyužité sloupce a řádky vymazat. Jen musíme dát pozor, abychom nemazali i místa, kde máme data. Funkce může vypadat třeba takto.
function smaz_prazdne_bunky(sheet) {
var posl_radek = sheet.getLastRow(); // číslo posledního řádku
var posl_sloupec = sheet.getLastColumn(); // číslo posledního sloupce
var smaz_radku = sheet.getMaxRows() - posl_radek - 1; // kolik řádků smazat
var smaz_sloupcu = sheet.getMaxColumns() - posl_sloupec - 1; // kolik sloupců smazat
if(smaz_radku > 0){ // je co mazat?
sheet.deleteRows(posl_radek + 1, smaz_radku);
}
if(smaz_sloupcu > 0){ // je co mazat?
sheet.deleteColumns(posl_sloupec + 1, smaz_sloupcu);
}
}
Abychom nesmazali žádná data nám pomůžou zajistit metody listu getLastRow() a getLastColumn(), které vrací číslo posledního řádku a sloupce, které ještě obsahují data.
Pak využijeme metody sheet.getMaxRows() a sheet.getMaxColumns(), které vrací celkový počet řádků a sloupců na listu a spočteme si kolik sloupců a řádků je třeba smazat. Počty o jeden snížíme, takže vpravo by nám měl zůstat jeden volný sloupec a dole jeden volný řádek.
Podmínky testující počet řádků a sloupců ke smazání potřebujeme pro případy, kdy funkci pro daný list zavoláme opakovaně. První volání řádky a sloupce smaže a při druhém volání by se skript pokoušel odstranit 0 řádků a 0 sloupců a havaroval by.
No a máme hotovo. Zkuste si funkci zpracuj_kurzy několikrát spustit ručně a v tabulce se přesvědčte, jestli se kurzy správně ukládají. Případně můžete list s dnešním datem smazat a funkci znovu spustit. Pokud je vše v pořádku, měl by se list znovu vytvořit a naplnit daty.
Časový spouštěč
Na závěr založíme časový spouštěč, který bude funkci zpracuj_kurzy spouštět každý den, řekněme v 16:00. Z menu vybereme volbu Upravit -> Spouštěče aktuálního projektu a vpravo dole klikneme na tlačítko Přidat spouštěč. Postup jsme již probrali v lekci o spouštěčích.
V panelu nastavíme parametry podle následujícího obrázku a vytvoříme spouštěč.
Samozřejmě, že pro otestování spouštěče není třeba čekat do druhého dne. Jednoduše si vytvořte spouštěč, který poběží každou minutu, a po otestování zda vše funguje mu nastavte interval jedenkrát denně.