Lekce 7 – získání externích dat – Kurzy ČNB III

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ě.