Kontrola/oprava/úprava vložených hodnot v Google Sheets díky Apps Script

Google před asi 3 měsíci v tichosti odstranil jeden z posledních nedostatků Google Sheets App Script oproti VBA u MS Excelu a tím byla faktická nemožnost kontroly vkládaných dat skriptem (nicméně existuje alespoň dost mocná možnost Data/Ověření platnosti včetně možnosti vlastního vzorce).

Problém byl, že zde sice existovala událost onEdit(e), vracející novou hodnotu, problém byl, že zde chyběla ta původní hodnota a událost onEdit nastala až po provedení změny, tj. když se vám nová hodnota z nějakého důvodu nezdála, nemohli jste vrátit původní hodnotu (vynechám ošklivý hack, kdy jste si ukládali při otevření hodnotu všech buněk a porovnávali s nimi).

A to se právě změnilo a událost onEdit nyní obsahuje i původní hodnotu a teď někdy se to i dostalo do nápovědy.

Praktický příklad – řekněme, že nechceme povolit zadání větší hodnoty než 100, v opačném případě tam chceme vrátit původní hodnotu (není to tak úplně praktický příklad, protože zrovna tohle snadno vyřešíte tím Ověřením platnosti, ale pro ilustraci to postačí).

function onEdit(e){
 var range = e.range;
 if ( e.value > 100 ) range.setValue(e.oldValue); 
}

Nebo tedy trochu jiný příklad – řekněme, že po změně hodnoty chcete do poznámky k dané buňce napsat, kdy byla změněna a jaká byla původní hodnota.

function onEdit(e){
 var range = e.range;
 var note = range.getNote();
 note = (note) ? note+"\n" : note; 
 range.setNote(note+e.oldValue + ' (do ' + Utilities.formatDate(new Date(), "GMT+1", "d. M. yyyy HH:MM:ss") + ')');
}

Jen upozorním, že onEdit se spouští pro úpravu kterékoliv buňky, tj. pakliže budete chtít vliv omezit jen pro některé buňky, musíte testovat, zdali onen e.range je mezi nimi, což je mimo téma tohohle postu (prozradím, že bohužel Apps Script nemá obdobu MS VBA Application.Intesect, takže to je netriviální a musí se na to jít přes ověřování pozice e.range vůči definovaným okrajům).

Automatické statistiky videí díky Google Sheets, Apps Script a YouTube Data API

Před pár dny jsem tu psal příspěvek o tom, jak si snadno udělat automaticky aktualizovaný přehled (nejen) vašich video kanálů s využitím Google Sheets, Apps Script a YouTube Data API. Mělo to jen jednu chybku – videím chyběly statistiky, tak to dnes napravíme.

Chybějící statistiky si vyžádaly nutnost přepsání celého skriptu, protože API YouTube.PlaylistItems neposkytuje statistická data, bylo potřeba použít další část YouTube Data API a to YouTube.Videos. Zároveň bylo potřeba skript optimalizovat, aby se nedotazoval na statistiky každého videa najednou a rovnou jsem skript také zpřehlednil.

Pokračovat ve čtení „Automatické statistiky videí díky Google Sheets, Apps Script a YouTube Data API“

Mějte přehled ve svých videích díky Google Scripts, Sheets a YouTube Data API

Možná víte, že jsem digital manažer výrobce značek Tefal, Krups, Rowenta pro ČR, SR a Maďarsko. A jedna z milých povinností, kterou mám na starosti, je i správa našich YouTube kanálů, kterých je celkem 5, plus jsou YouTube kanály našich spřátelených beauty blogerek atp.

Než jsem přišel, tak se to dělalo tak, že když někdo přidal YouTube video, tak pak vzal tu URL a napsal to do nějakého Excelu s nějakým názvem. Samozřejmě někdy zapomněl, nebyly tam popisky, náhledy atd.

Nesnáším, když se ztrácí čas prací, kterou lépe zastanou stroje a to je zrovna tento případ. Udělal jsem si proto jednoduchý Google (Sheets) Apps Script, který funguje takto:

  1. Vezme seznam všech listů v Google Tabulce a spustí nad nimi smyčku:
    • vezme název listu
    • zjistí, jestli existuje YouTube kanál, který se jmenuje jako název listu
    • když ano, smaže list a nahraje do něj seznam všech videí v daném YouTube kanálů – URL, URL obrázku, Titulek, Popis, Datum publikování
    • upraví formáty a např. funkcí IMAGE doplní aktuální obrázek videa
  2. A tenhle skript se spouští každou hodinu

Výsledkem tak je stále aktuální seznam všech videí na všech našich (resp. i cizích – libovolných zadaných) YouTube kanálech a když by někdo přidat nový kanál, tak prostě stačí přidat nový list, pojmenuje jej názvem kanálu a do hodiny se stáhne opět kompletní seznam (případně může stisknout tlačítko v menu a načte se hned). Je tam i záložka pro ruční zadávání videí, abychom měli přehled i o takových, která jsou na jiné službě nebo u někoho, u koho nepotřebujeme kompletní přehled.

Takže jak na to?

V první fázi je potřeba funkce, která stáhne seznam videí z YouTube přes standardní YouTube Service, která je součástí App Scriptu a využívá YouTube Data API.

Původně jsem se domníval, že to udělám jako funkci Tabulek, tedy že napíšu =youtube(„nazev-kanalu“) a ono to rovnou vyplní tabulku daty. Jenže jak jsem později zjistil, ve funkcích YouTube Service nefunguje, musel bych to řešit dotazováním na URL z Data API a to mi za to nestálo.

Funkce je to myslím primitivní, snad jedinou komplikací je to, že stahuji videa po 25. a stránkuji, abych se vyhnul problémům u obsáhlých kanálů. Z vrácené hodnoty (viz dokumentace k Playlistitems) vypreparuji kýžené detaily a ty ukládám do pole, které pak vracím jako výsledek funkce:

/**
* Return YouTube videos details for given channel
*
* @param {string} username YouTube channel name (username) - see channel URL
* @return Array representing key params of YouTube videos in given channel
*/
function getVideos(username) {
 var results = YouTube.Channels.list('contentDetails', {forUsername: username});
 var array = [];
 for(var i in results.items) {
 var item = results.items[i];
 // Get the playlist ID, which is nested in contentDetails, as described in the
 // Channel resource: https://developers.google.com/youtube/v3/docs/channels
 var playlistId = item.contentDetails.relatedPlaylists.uploads;
 
 var nextPageToken = '';
 
 // This loop retrieves a set of playlist items and checks the nextPageToken in the
 // response to determine whether the list contains additional items. It repeats that process
 // until it has retrieved all of the items in the list.
 while (nextPageToken != null) {
 var playlistResponse = YouTube.PlaylistItems.list('snippet', {
 playlistId: playlistId,
 maxResults: 25,
 pageToken: nextPageToken
 });
 
 for (var j = 0; j < playlistResponse.items.length; j++) {
 var playlistItem = playlistResponse.items[j];
 array.push([
 playlistItem.snippet.resourceId.videoId,
 playlistItem.snippet.thumbnails['default']['url'],
 playlistItem.snippet.title,
 playlistItem.snippet.description,
 playlistItem.snippet.publishedAt,
 
 ]);
 }
 nextPageToken = playlistResponse.nextPageToken;
 }
 }
 return array;
 }

Druhá funkce řeší to procházení všech listů v aktuální tabulce, zjištění názvů a vyplnění hodnot z YouTube:

function getSheetsVideos () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  for (var n = 0; n < sheets.length ; n++ ) {    
    var sheet = ss.getSheets()[n];
    var sheetName = sheet.getName();
    var videos = (getVideos (sheetName)); 
    if (videos.length == 0) continue;
    sheet.clear();
    sheet.appendRow(['URL','Thumbnail','Title','Description', 'Published at']) ;
    var values = [];
    for (var video in videos) {
      values.push(["https://www.youtube"+".com/watch?v="+videos[video][0],'=image("'+videos[video][1]+'")',videos[video][2],videos[video][3], new Date(videos[video][4])]);
    }
    sheet.getRange(2, 1, videos.length , 5).setValues(values)    
    sheet.getDataRange().setVerticalAlignment("middle");
    sheet.setColumnWidth(1, 320);
    sheet.setColumnWidth(2, 120);
    sheet.setColumnWidth(3, 300);
    sheet.setColumnWidth(4, 400);    
    sheet.setColumnWidth(5, 100); 
    sheet.getRange("C:D").setWrap(true);
    sheet.getRange("1:1").setFontWeight("bold");
    sheet.getRange("C:C").setFontWeight("bold");
    for(var i=2; i<=videos.length+1; i++){
      sheet.setRowHeight(i, 90);
    };
  }
}

No a to je vlastně všechno, ještě jsem přidal funkci, která při otevření sešitu vytvoří položku v menu pro aktualizaci všech videí, kdybych to potřeboval rychleji než do hodiny:

function onOpen() {
 var ui = SpreadsheetApp.getUi();
 // Or DocumentApp or FormApp.
 ui.createMenu('Aktualizace')
 .addItem('Aktualizace všech videí', 'getSheetsVideos')
 .addToUi();
}

No a pak samozřejmě nastavit automatické stahování (Zdroje / Spouštěče aktuálního projektu). Před prvním spuštěním bylo také třeba zapnout podporu YouTube Data API (Zdroje / Pokročilé služby Google) a tuším to chtělo i nějakou autorizaci. Výsledek vidíte na obrázku

Podnět pro vás: co takhle tam přidat statistiky shlédnutí videí? Kdo se do toho vrhnete? Napovím – Google Apps Script YouTube Analytics Service.

Jak na uživatelské funkce v Google Sheets (příklad funkce encodeURI)

Už jsem tu psal o vlastních funkcích/vzorcích pro MS Excel, které se od standardních VBA maker (subrutin) liší použitím Function namísto Sub a tím, že návratovou hodnotou je automaticky proměnná uvntiř této funkce, která se jmenuje stejně jako název funkce.

Uživatelské vzorce v Google Sheets se od ostatních funkcí neliší vlastně vůbec v ničem, jsou to prostě normální Google Apps Script (v podstatě Javascript) funkce a návratová hodnota se prostě řeší standardním returnem. Snadno jim lze také vytvořit nápovědu pomocí standardního JSDoc.

Bohužel většina funkcí, které někdo vytvářel a někde zveřejnil není psána správně, typickým problémem je kromě absence nápovědy také to, že nepočítají, že by někdo danou funkci použil na nějaké pole prvků a že by návratovou hodnotou také mohlo být pole prvků.

Vše ukážu na jednoduchém příkladu standardní Javascript funkce encodeURI, když byste ji chtěli použít ve vzroci v Google Sheets. Jděte do Nástroje / Editor skriptů a vložte:

/**
 * Encodes special characters, except: , / ? : @ & = + $ # (Use encodeURIComponent() to encode these characters)
 * http://www.w3schools.com/jsref/jsref_encodeuri.asp
 *
 * @param {string|Array} input The text or range of cells to be encoded.
 * @return Encoded input.
 * @customfunction
 */
function ENCODEURI(input) {
 if (input.map) { // Test whether input is an array.
 return input.map(ENCODEURI); // Recurse over array if so.
 } else {
 return encodeURI(input.toString() );
 }
}
  • Na začátku je JSDoc dokumentace, která se pak objeví v bublině, když budete funkci používat. Prvních 40 znaků je vidět už v přehledu funkcí. Nepovinná, ale velmi doporučuji
  • @param vysvětluje, jaké parametry se očekávají na vstupu. V tomto případě jeden parametr input, který může být buď string a nebo Array. Také je vidět v nápovědě
  • v @return si můžete poznačit, co očekáváte na výstupu, ale nikde to vidět není
  • @customfunction říká Google Sheets, že se jedná o uživatelskou funkci a že ji má nabízet mezi funkcemi.
  • Následuje samotná funkce v Google Apps Script / Javascriptu. Není povinné, aby byla psaná velkými písmeny, jen jsou tak psané všechny build-in funkce, tak je fajn to ctít. Tady ve script editoru jsou funkce case sensitive, v Google Sheets nikoliv, takže tam pak klidně můžete použít =encodeURI(string) jako používáte někde na webu.
  • Pak je zde jednoduchý rekurzivní hack, který využije Javascript funkci map, která zavolá vybranou funkci na každý prvek pole – v tomto případě tedy rekurzivně zavolá sebe sama.
  • A když se o pole nejedná, tak funkce převede hodnotu na string, převede jí přes Javascript funkci encodeURI a vrátí jí zpět do Google Sheets.
  • Pozor, kdybych chtěl pojmenovat funkci malými písmeny, tedy encodeURI, shodně jako je ta Javascript funkce, kterou zde volám, tak místo ní zavolá opět sebe sama a funkce se zacyklí.

Výsledek vidíte na obrázku. Všimněte si, že jsem vzorec napsal opět jen do jedné buňky, a to ve formátu B1:B, takže se propíše na všechny řádky.

vlastni-funkce

ARRAYFORMULA – důvod, proč si zamilujete Google Sheets

Mám moc rád MS Excel a jsem přesvědčen, že neexistuje lepší tabulkový kalkulátor. Ale přesto čím dál častěji používám Google Sheets a to ze dvou důvodů – snadné (a bezplatné) sdílení tabulek a funkce ARRAYFORMULA.

Ona to vlastně není až tak úplně funkce, je to jen jakýsi konstrukt, který říká, že daný vzorec se týká nějakého pole buněk, nikoliv jedné. Ano, i MS Excel má pole vzorců, jenže je zde podstatný rozdíl – u MS Excelu musíte předem označit všechny buňky, které se mají vyplnit výsledkem, u Google Sheets napíšete vzorec jen do jedné a on vyplní všechny ostatní.

Skvěle se to totiž hodí i pro řešení problému, který jsem zmínil včera, tedy když potřebujete do nějakého sloupce tabulky dát nějaké hodnoty či výpočet a chcete, aby se to automaticky počítalo u všech řádků a nemuseli řešit problémy, když nějaké řádky přesunete, přidáte, smažete.

Tedy řekněme, že máte

  • v buňkách A1 až A1000 nějaké hodnoty
  • v buňkách B1 až B1000 nějaké další hodnoty

A potřebujete do buněk C1 až C1000 dá třeba násobek hodnot v předchozích dvou sloupcích, tedy pro buňku C1 byste napsali =A1*B1

Takže vy místo toho napíšete do C1 vzorec

=ARRAYFORMULA(A1:A*B1:B)

a Google automaticky doplní všechny hodnoty. A co víc, když zkusíte nějakou hodnotu z kteréhokoliv řádku ve sloupci C smazat nebo změnti, tak ji Sheets okamžitě dopočítá zpět. Další výhodou je pak rychlost, nemáte to totiž 1000 výpočtů, ale jen jeden (byť nad tisíci řádků).

Nahrazení A1 za A1:A jsme Google Sheets řekli, aby prostě počítal od A1 až do konce sloupce A. Proto jste si asi všimli, že Sheets doplní výpočet do všech řádků, nejen do těch tisíce. Při řešení využijeme druhé úžasné funkce Google Sheets, o které si někdy povíme více a to je funkce Filter. Prostě napíšeme

=ARRAYFORMULA(FILTER(A1:A*B1:B;A1:A<>""))

Tím říkáme „Použij výpočet A1*B1 na všechny pole od A1 resp. B1 po poslední pole ve sloupci A resp. B, ale pouze pakliže příslušná buňka ve sloupci A není prázdná“.

Teď si možná říkáte, že to tak užitečné není, že toho byste dosáhli i třeba pomocí Formátovaných tabulek, které má Excel někdy od verze 2007 a které také umí vyplňovat vzorce do všech řádků. Jenže tyhle funkce v Google Sheets fungují i napříč listy. Takže si dáme další příklad:

Řekněme, že vám nějaký online systém vyjíždí CSV tabulku, kde máte seznam objednávek – ID uživatele, ID objednávky a její hodnotu. Vy si tenhle seznam natáhnete do vaší Google tabulky pomocí další skvělé funkce IMPORTDATA.

No a teď chcete mít na dalším listu seznam objednávek, které byly vyšší než 1000 Kč. Zvládnete to tímhle jedním zápisem na další list:

=ARRAYFORMULA(FILTER('List 1'!A1:C;'List 1'!C1:C>1000))

Tedy vezmi mi všechny hodnoty z A1:C, ale pouze za předpokladu, že hodnota ve sloupci C je vyšší než 1000. Úchvatné, ne? V Excelu byste to museli řešit makry či kontingenčními tabulkami, se všemi jejich nevýhodami.

A to není všechno – výstup pak můžete i seřadit podle nějakého sloupce, třeba sestupně podle posledního sloupce, díky funkci SORT

=ARRAYFORMULA(SORT(FILTER('List 1'!A1:C;'List 1'!C1:C>10);3;FALSE))

Jo a možná vás napadlo: kdybyste chtěli udělat součet všech objednávek pro jednotlivé uživatele, tak aby se jejich ID neopakovalo, můžete použít funkci UNIQUE nad sloupcem s ID uživateli a následně využít funkce SUMIF, a opět přes ARRAYFORMULA si ji natáhnout do všech řádků ke všem uživatelům.