Google Apps Script
Základy - bude doplněno
Hotové skripty
Skripty k využití (Kurzy ČNB - zpracování do tabulek)
Abyste s konkrétním typem souboru mohli na Google Disku pracovat, musí být k Disku připojená aplikace, která to umí.
Ve výchozím stavu jsou k Disku připojeny programy pro práci s Tabulkami, Dokumenty, Prezentacemi, Nákresy a Formuláři. Pokud potřebujete pracovat s dalšími typy souborů, budete si muset k Disku připojit další aplikace.
Připojení programu Google Apps Script (potřebujete pokud chcete psát skripty, stejný postup platí i pro další programy.
- Na Disku klikněte vlevo nahoře na tlačítko Přidat, vyberte poslední položku Více a pak opět poslední volbu Připojit další aplikace.
- Vyberete kategorii Od Google > vybrat aplikaci Google Apps Script.
- Aplikaci k Disku připojíte kliknutím na tlačítko PŘIPOJIT.
- Každá připojená aplikace má přístup k vašim datům.
Jak zakázat aplikaci přístup na váš Disk?
Na Disku klikněte na ikonu ozubeného kola vpravo nahoře a na panelu vyberte položku Nastavení. Objeví se vám panel ve kterém na levé straně vyberte položku Správa aplikací. Najděte program, který již nechcete používat, vpravo rozbalte volbu možnosti a vyberte Odpojit od Disku. Odpojení ještě potvrďte v následujícím dialogu kliknutím na Odpojit. Aplikace zmizela ze seznamu a byl jí odebrán přístup k vašim datům.
Otevření tabulky
- var tabulka = SpreadsheetApp.getActive();
skript přibalený k tabulce, tabulku jsme mohli získat výše uvedenou metodou;
"vezmi právě aktivní tabulku", tedy tu, ke které je skript připojený - Skript jako samostatný soubor - žádnou připojenou tabulku nemá:
možnosti:
najít TABULKU podle jejího ID,
najít tabulku podle adresy (URL).
function otevri_tabulku() {
var url = '1AU_D4Ao7hwAbnl8AY-20ZS3gG0jFtgiCUOTt1PG51Vk';
var tabulka = SpreadsheetApp.openByUrl(id);
return tabulka
}
function otevri_tabulku() {
var url = 'https://docs.google.com/spreadsheets/d/
1AU_D4Ao7hwAbnl8AY-20ZS3gG0jFtgiCUOTt1PG51Vk/
edit#gid=1818389852';
var tabulka = SpreadsheetApp.openByUrl(url);
return tabulka
}
URL i ID jsme napevno zadali do funkce, ale funkce by měla ID dostat jako parametr. Stejný skript pak může fungovat univerzálně a ukládat data do libovolné tabulky, nebo několika různých tabulek. Jedinou podmínkou je, aby měl k tabulce přístup a mohl do ní zapisovat. Jinak je jedno, komu tabulka patří, nebo na kterém Disku leží. Pokud skript přístup nemá - tabulku nedokáže otevřít a skript havaruje.
function otevri_tabulku(id) {
return SpreadsheetApp.openById(id)
}
Abychom id nebo url tabulky neměli zapsané přímo ve funkci, založíme si globální proměnnou, do které si id tabulky uložíme. Globální proměnná je automaticky dostupná kdekoli ve skriptu:
var TABLE_ID = '1AU_D4Ao7hwAbnl8AY-20ZS3gG0jFtgiCUOTt1PG51Vk
';
Pro proměnné, které se nemění se používají názvy psané velkými písmeny.
V tabulce potřebujeme vytvořit list s názvem shodným s dnešním datem. Napíšeme si funkci, která jako parametry dostane dnešní datum a id tabulky. Vrátí pak list s názvem podle data. Tabulka nemůže mít 2 listy se shodným názvem.
function zaloz_list(dnes, tabulka) {
var sheet = tabulka.getSheetByName(dnes); // najdi v tabulce list podle jména
if(sheet == null) { // list neexistuje
sheet = tabulka.insertSheet(dnes); // vytvoř list
}
return sheet; // vrať list
}
O víkendech se kurzy nevyhlašují, proto nám pro sobotu a neděli ČNB vrátí páteční kurz. ČNB aktualizuje kurzy v pracovní dny po 14:30, dopoledne vrací kurz předchozího dne. Více info zde.
ID tabulky i adresu serveru ČNB si uložíme do globální proměnné. Začátek skriptu bude vypadat takto:
var table_id = 'id_vasi_tabulky_s_kurzy';
var kurzy_url = 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt';
Vlastní načtení dat z ČNB - funkce:
function nacti_data_z_cnb(url, datum) {
var url = url || 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt';
var datum = datum || '01.09.2021';
var url_cele = url + '?date=' + datum;
var resp = UrlFetchApp.fetch(url_cele);
var data = resp.getContentText();
return data;
}
- Funkce má 2 vstupní parametry: url a datum.
Parametr url je základní adresa stránky ČNB
datum je text ve tvaru DD.MM.RRRR. - Operátor || je logický operátor OR, to znamená: "do proměnné url ulož hodnotu url, která došla jako první parametr a pokud parametr url není definovaný, ulož tam text 'https://www.cnb.cz/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/denni_kurz.txt' ".
- Další řádek do proměnné datum uloží parametr datum nebo text '01.09.2021';
- Na dalším řádku sestavíme výslednou adresu, kterou budeme volat.
- Pro komunikaci s libovolnou externí adresou slouží v Apps Scriptu knihovna UrlFetchApp. Pomocí ní můžeme data jak přijímat, tak i odesílat. Zavolá externí adresu a do proměnné resp uloží výsledek. Bude to objekt třídy HTTPResponse, zjednodušeně řečeno kompletní odpověď volaného serveru. Obecně může mít odpověď serveru nejrůznější formát, může to být obrázek, text, XML a podobně. Podle toho je poté třeba odpověď zpracovat. Zde jde o prostý text.
- Text získáme metodou resp.getContentText() a uložíme ho do proměnné data, kterou na posledním řádku funkce vrátí.
Zpracování dat
Data ze serveru v textovém tvaru máme, napíšeme funkci, která je rozporcuje do tvaru vhodného k uložení do tabulky:
- Nejprve rozdělíme text podle znaku pro nový řádek
- Zbavíme se prvního řádku a zkontrolujeme, jestli je na něm správné datum. Pokud ne, funkce končí a vrátí false.
- 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
Napíšeme funkci, která provede celou operaci načtení a uložení najednou a bude později volána časovým spouštěčem:
- Použijeme knihovnu 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.
- Metoda clearContents() - smaže samostatně data a zachová formátování.
- Metoda clearFormats() - smaže formátování a ponechá data.
- Metoda clearNotes() - smaže 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ě, vybereme oblast pro data (musí počtem řádků a sloupců odpovídat množství dat - jinak chyba) a vložíme je do tabulky příkazem range.setValues(kurzy).
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.
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ů.
Pokud 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.
Metoda sheet.getDataRange() 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.
Levý horní roh oblasti vždy začíná buňkou A1, i kdyby byla první data třeba až ve sloupci F.
Metody sheet.getMaxRows() a sheet.getMaxColumns(), vrací celkový počet řádků a sloupců na listu.
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.
Časový spouštěč
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ěč.
Vybrat funkci ke spuštění: zpracuj_kurzy
ybrat implementaci ke spuštění: Head
Vyberte zdroj události: Řízeno časem
Vyberte typ časového spouštěče: Počítadlo dní
Vyberte denní dobu: 16:00 - 17:00
Nastavení oznámení o chybě: Dostávat oznámení každý den
Funkce pro vytvoření tabulky bez kontrol:
