Ovládněte Apps Script a vylepšete tabulkový editor Googlu

Kdybych se zeptal, jakým způsobem používá běžný člověk tabulkový procesor excelovského typu, nejspíše bych se dozvěděl, že takový uživatel vytváří jen jednoduché seznamy hodnot a ty vynáší na graf. Opačný extrém buduje v Excelu rozsáhlé kontingenční tabulky a tabulkový procesor tak spíše připomíná správce profesionální databáze.

Zde končí znalosti tabulkových procesorů u většiny domácích uživatelů

Dnes si zahrajeme na trošku pokročilejšího uživatele, který chce univerzálněji pracovat s daty. Právě to je ta nejsilnější moc tabulkových procesorů a jejich vestavěných funkcí. Když je chytře poskládáte za sebe, spočítáte prakticky cokoliv. Jenže kdo se v tom má vyznat?

Řešením může být rozšíření editoru o nějaké to pokročilé makro, respektive doplněk. Umí to úplně všechny rozšířené alternativy Excelu, my se dnes ale podíváme směrem vzhůru do oblak na tabulkový procesor, který je součástí úložiště Google Drive.

Apps Script a netradiční vestavěné funkce

Oproti ostatním totiž nabízí relativně velmi snadnou rozšiřitelnost pomocí prostého Javascriptu v rámci technologie Apps Script. Apps Script umožňuje vytvářet jednoduché doplňky s využitím několika vestavěných knihoven. Tyto miniaplikace pak mohou spolupracovat napříč službami Googlu, nás dnes ale bude zajímat pouze skriptování pro zdejší tabulkový editor, napíšeme si totiž novou funkci, kterou budeme moci použít pro výpočet v libovolné buňce.

Tabulkový procesor Googlu ve výchozím stavu obsahuje opravdu ohromné množství vestavěných funkcí včetně několika exotických, které k výpočtu samy používají Apps Script. Můžete tak vyzkoušet třeba integrovaný Google překladač a to pomocí funkce GoogleTranslate("text";"z jazyka";"do jazyka"). Když tedy budete mít v buňce A1 třeba text „Ahoj, jak se máš“ a do druhé buňky napíšete =GoogleTranslate(A1; "cs";"en"), po klepnutí na Enter se během několika okamžiků objeví překlad do angličtiny. Takto můžete kombinovat celou čtyřicítku podporovaných řečí a to pomocí jejich standardizovaných kódů ISO 639-1. A jen tak na okraj, středníky mezi parametry používejte v případě, pokud máte tabulkový editor v českém rozhraní. V anglickém rozhraní se parametry oddělují čárkou.

 
Použití vestavěné funkce pro překlad textu, která je postavená na Apps Scriptu

Podobným způsobem můžete použít třeba funkci =GoogleFinance("GOOG";"price"), která zase do buňky vloží aktuální cenu akcií Googlu, a tabulkový procesor z Mountain View si dokonce poradí i s vložením hodnot z RSS, k čemuž slouží funkce ImportFeed. Kompletní seznam všech vestavěných funkcí najdete na této adrese.

Vyhledávání ve veřejné galerii skriptů a jeden z nich, který vyšťourá informace z Facebooku

Pokud vám nebude stačit ani tento výčet, je po ruce právě Apps Script. K dispozici je galerie veřejně publikovaných skriptů, takže můžete vyzkoušet třeba dílo Martina Hassmana, který veřejně publikoval mimo jiné skript Facebook Like Analytics. Po instalaci skriptu budete moci použít nové funkce které třeba zjistí počet sdílení pro dané adresy na Facebooku. Galerii skriptů najdete snadno. Klepněte v tabulkovém procesoru v menu na Nástroje a v nabídce vyberte Galerie skriptů.

Vlastní makro na převod měn za dvě minuty

Když nenajdete hledanou funkci ani v galerii, přichází řada konečně na váš programovací um. Pokud pro vás není prostý Javascript absolutní španělskou vesnicí, za dvě minuty bude hotovo.

A to bez nadsázky – svědčí o tom alespoň tento příklad, ve kterém si napíšu funkci =prevedEuroNaKoruny(euro). Pokud tuto funkci použijete nad libovolnou buňkou, která obsahuje číslo, makro se na pozadí spojí se serverem České národní banky, stáhne si aktuální kurzovní lístek, zjistí z něho kurz eura a do cílové buňky vypíše sumu v korunách.

 
Náš převodník měny z eura na koruny přebírá kurz devizový kurz ČNB

Kdybyste chtěli, může mít funkce třeba druhý parametr, ve kterém by uživatel určil konkrétní měnu, ale to je už nad rámec tohoto elementárního příkladu.

Jak na to

Slíbil jsem, že bude za dvě minuty hotovo, takže si otevřete nový sešit, v menu klepněte na Nástroje, zde vyberte Správce skriptů a v otevřeném dialogu klepněte na Nový. Zobrazí se další okno s několika šablonami skriptů pro různé situace. My chceme napsat jen novou funkci pro tabulkový procesor, z nabídky tedy vyberte Prázdný projekt, díky čemuž se konečně dostanete do jednoduchého vývojového prostředí.

  
Jak vytvořit skript přímo z otevřeného dokumentu tabulkového procesoru

Nyní v Javascriptu vytvořím kýženou funkci prevedEuroNaKoruny(euro). To je všechno, žádná omáčka okolo. Stačí soubor uložit, a pokud se nyní vrátíte do Správce skriptů, po obnově byste už měli vidět funkci v seznamu a bez další aktivace ji můžete používat přímo nad buňkou.

function prevedEuroNaKoruny(euro){
  if(typeof euro != "number") throw "Vstupní hodnota musí být číslo";
  return euro*ziskejKurz_();
}

function ziskejKurz_(){
  var data = UrlFetchApp.fetch("http://www.cnb.cz/cs/financni_trhy/" + 
      "devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt");
  var kurz = data.getContentText().split("\n")[7].split("|")[4].replace(",",".");
  return parseFloat(kurz);
}

Kód je sice velmi jednoduchý, ale přesto se podívám na jeho části. V samotné klíčové funkci si všimněte kontroly, jestli je vstupní hodnota číslo. Pokud nebude, funkce nahlásí chybu. Samotná logika je pak uložená v druhé funkci ziskejKurz_(). Ta se vám v seznamu funkcí ve Správci skriptů nezobrazuje, je totiž soukromá, čehož jsem dosáhl podtržítkem na konci jejího názvu.

Editor kódu Apps Script – až bude hotovo, skript uložím a zavřu okno

V této funkci jsem pak použil první z mnoha vestavěných knihoven Apps Scriptu, tedy UrlFetchApp a její metodu fetch(). Nedělá nic jiného, než že stáhne data na dané adrese. Jelikož se jedná o textový výstup ČNB, dekóduji jej jako prostý text pomocí metody getContentText(), no a další metody v kódu už pouze vyřežou z textu hodnotu konkrétního kurzu. Pomocí dalších knihoven můžete přistupovat k Gmailu, mapám, kalendářům, grafům aj.

Dokument už našel náš nový skript. Nyní mohu správce zavřít a přímo nad buňkami používat náš převodník měn.

Kdybych chtěl, mohl bych získaný text uložit do nějaké trvalejší proměnné, aby se nemusel seznam kurzů stahovat při každém volání funkce prevedEuroNaKoruny třeba v tabulce, kde bych chtěl tento převod aplikovat na poněkud rozsáhlejší seznam hodnot, ale to je už práce zase pro někoho jiného.

Hotovo. Tečka. Během několika minut jsem si napsal rychlý převodník měn pro tabulkový editor, který má k dispozici vždy aktuální kurz. Důležitější je ale to, že si mohu podobným způsobem rychle napsat funkci, která provede nějaký sofistikovanější výpočet, který bych musel jinak v buňce skládat pomocí hromady za sebou jdoucích vestavěných funkcí.

Pouze makro pro konkrétní dokument

Ještě připomenu, že tvorbou skriptu přímo z otevřeného dokumentu vlastně vytvářím makro, které bude fungovat pouze v tomto dokumentu. Pokud budete chtít vytvořit skript i pro ostatní, musíte ho publikovat v galerii a odtud nainstalovat. Pokud si chcete nechat kód jen pro sebe, můžete prázdný dokument s makrem používat třeba jako šablonu.

Díky Apps Scriptu je tedy nejen tabulkový editor snadno rozšiřitelný prakticky o libovolnou funkci a to pomocí relativně jednoduchého Javascriptu na několika řádcích.