Lekce 7 - Google Apps Script - Získání externích dat - Kurzy ČNB III

V minulé lekci, Google Apps Script - Získání externích dat - Kurzy ČNB II, jsme získali data ze serveru ČNB jako textový řetězec.

Dnes budeme zpracovávat kurzovní lístek a uložíme si ho 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++){ // cyklus 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;
}

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 '|' 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(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áme 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() 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í jsou š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 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_prazne_bunky(), o kterých jsme zatím nemluvili a které se volají po vložení dat.

nastav_vzhled()

Po funkci nastav_vzhled() 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 další možnosti formátování.

smaz_prazne_bunky()

A ještě si ukážeme funkci smaz_prazne_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 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_prazne_bunky(sheet) {
  var data_range = sheet.getDataRange(); // vyber oblast dat
  var posl_radek = data_range.getLastRow(); // číslo posledního řádku
  var posl_sloupec = data_range.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ůže zajistit metoda sheet.getDataRange(). Ta vybere kompletní oblast obsahující data. Nezáleží na tom, jestli jsou v datech prázdné řádky nebo sloupce, tato metoda vždy vrátí oblast, ve které jsou všechny buňky obsahující nějakou hodnotu.

A pozor, levý horní roh oblasti vždy začíná buňkou A1, i kdyby byla první data třeba až ve sloupci F.

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 a v tabulce se přesvědčte, jestli se kurzy správně ukládají. 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ě.

Líbí se vám Apps Script a tento seriál?
A dočetli jste až do konce?
Pak nezapomeňte na hodnocení

A na závěr malý domácí úkol. Tipněte si, co se zapíše do tabulky o víkendu, kdy se kurzy nevyhlašují, a napište to do komentářů.