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.