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