Funkcje niestandardowe w Arkuszach Google

Arkusze Google oferują setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Jeśli to nie wystarczy, możesz użyć Google Apps Script, aby napisać funkcje niestandardowe, np. przeliczać metry na mile lub pobierać z internetu treści na żywo, a potem używać ich w Arkuszach Google tak samo jak funkcji wbudowanych.

Pierwsze kroki

Funkcje niestandardowe są tworzone przy użyciu standardowego kodu JavaScript. Jeśli dopiero zaczynasz przygodę z JavaScriptem, na Codecademy znajdziesz świetny kurs dla początkujących. (Uwaga: ten kurs nie został opracowany przez Google i nie jest z nim powiązany).

Oto prosta funkcja niestandardowa o nazwie DOUBLE, która mnoży wartość wejściową przez 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Jeśli nie wiesz, jak pisać w JavaScript, i nie masz czasu na naukę, sprawdź sklep z dodatkami, aby zobaczyć, czy ktoś inny nie utworzył już potrzebnej Ci funkcji niestandardowej.

Tworzenie funkcji niestandardowej

Aby napisać funkcję niestandardową:

1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
2. Wybierz kolejno Rozszerzenia > Apps Script.
3. Usuń cały kod z edytora skryptów. W przypadku funkcji DOUBLE powyżej wystarczy skopiować kod i wkleić go do edytora skryptów.
4. U góry kliknij Zapisz save.

Teraz możesz użyć funkcji niestandardowej.

Pobieranie funkcji niestandardowej z Google Workspace Marketplace

Google Workspace Marketplace oferuje kilka funkcji niestandardowych jako dodatki do Arkuszy Google. Aby użyć tych dodatków lub je poznać:

1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
2. U góry kliknij Dodatki > Pobierz dodatki.
3. Gdy otworzy się Google Workspace Marketplace, kliknij pole wyszukiwania w prawym górnym rogu.
4. Wpisz „funkcja niestandardowa” i naciśnij Enter.
5. Jeśli znajdziesz dodatek z funkcją niestandardową, który Cię interesuje, kliknij Zainstaluj, aby go zainstalować.
6. Może pojawić się okno dialogowe z informacją, że dodatek wymaga autoryzacji. Jeśli tak jest, uważnie przeczytaj powiadomienie, a następnie kliknij Zezwól.
7. Dodatek stanie się dostępny w arkuszu kalkulacyjnym. Aby użyć dodatku w innym arkuszu kalkulacyjnym, otwórz ten arkusz i u góry kliknij Dodatki > Zarządzaj dodatkami. Znajdź dodatek, którego chcesz użyć, i kliknij Opcje more_vert > Użyj w tym dokumencie.

Korzystanie z funkcji niestandardowej

Po napisaniu funkcji niestandardowej lub zainstalowaniu jej z Google Workspace Marketplace możesz jej używać tak samo jak funkcji wbudowanej:

1. Kliknij komórkę, w której chcesz użyć funkcji.
2. Wpisz znak równości (=), a następnie nazwę funkcji i dowolną wartość wejściową, np. =DOUBLE(A1), i naciśnij Enter.
3. W komórce na chwilę pojawi się symbol Loading..., a potem wynik.

Wskazówki dotyczące funkcji niestandardowych

Zanim napiszesz własną funkcję niestandardową, zapoznaj się z kilkoma wskazówkami.

Nazwa

Oprócz standardowych konwencji nazewnictwa funkcji JavaScriptu pamiętaj o tych zasadach:

• Nazwa funkcji niestandardowej musi różnić się od nazw funkcji wbudowanych, takich jak SUM().
• Nazwa funkcji niestandardowej nie może kończyć się podkreśleniem (_), które w Apps Script oznacza funkcję prywatną.
• Nazwę funkcji niestandardowej należy zadeklarować za pomocą składni function myFunction(), a nie var myFunction = new Function().
• Wielkość liter nie ma znaczenia, chociaż nazwy funkcji arkusza kalkulacyjnego są tradycyjnie pisane wielkimi literami.

Argumenty

Podobnie jak funkcja wbudowana, funkcja niestandardowa może przyjmować argumenty jako wartości wejściowe:

• Jeśli wywołasz funkcję z odwołaniem do pojedynczej komórki jako argumentem (np. =DOUBLE(A1)), argumentem będzie wartość komórki.

• Jeśli wywołasz funkcję z odwołaniem do zakresu komórek jako argumentem (np. =DOUBLE(A1:B10)), argumentem będzie dwuwymiarowa tablica wartości komórek. Na przykład na zrzucie ekranu poniżej argumenty w =DOUBLE(A1:B2) są interpretowane przez Apps Script jako double([[1,3],[2,4]]). Pamiętaj, że przykładowy kod DOUBLE z powyższego przykładu należy zmodyfikować, aby akceptował tablicę jako dane wejściowe.

  
  

• Argumenty funkcji niestandardowych muszą być deterministyczne. Oznacza to, że wbudowane funkcje arkusza kalkulacyjnego, które za każdym razem zwracają inny wynik, np. NOW() lub RAND(), nie są dozwolone jako argumenty funkcji niestandardowej. Jeśli funkcja niestandardowa próbuje zwrócić wartość na podstawie jednej z tych niestabilnych funkcji wbudowanych, będzie wyświetlać Loading... w nieskończoność.

Wartości zwracane

Każda funkcja niestandardowa musi zwracać wartość do wyświetlenia, taką jak:

• Jeśli funkcja niestandardowa zwraca wartość, jest ona wyświetlana w komórce, z której została wywołana.
• Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości te przepełniają sąsiednie komórki, o ile są one puste. Jeśli spowoduje to zastąpienie istniejącej zawartości komórek, funkcja niestandardowa zwróci błąd. Przykład znajdziesz w sekcji Optymalizowanie funkcji niestandardowych.
• Funkcja niestandardowa nie może wpływać na komórki inne niż te, do których zwraca wartość. Innymi słowy, funkcja niestandardowa nie może edytować dowolnych komórek, tylko te, z których jest wywoływana, i komórki sąsiadujące. Aby edytować dowolne komórki, użyj menu niestandardowego do uruchamiania funkcji.
• Wywołanie funkcji niestandardowej musi zostać zwrócone w ciągu 30 sekund. Jeśli nie, w komórce wyświetli się symbol #ERROR!, a notatka do komórki będzie zawierać symbol Exceeded maximum execution time (line 0)..

Typy danych

Arkusze Google przechowują dane w różnych formatach w zależności od ich charakteru. Gdy te wartości są używane w funkcjach niestandardowych, Apps Script traktuje je jako odpowiedni typ danych w JavaScript. Oto najczęstsze problemy:

• Godziny i daty w Arkuszach stają się obiektami Date w Apps Script. Jeśli arkusz kalkulacyjny i skrypt używają różnych stref czasowych (rzadki problem), funkcja niestandardowa będzie musiała to skompensować.
• Wartości czasu trwania w Arkuszach również stają się Date obiektami, ale praca z nimi może być skomplikowana.
• Wartości procentowe w Arkuszach stają się liczbami dziesiętnymi w Apps Script. Na przykład komórka o wartości 10% staje się w Apps Script 0.1.

Autouzupełnianie

Arkusze Google obsługują autouzupełnianie funkcji niestandardowych, podobnie jak w przypadku funkcji wbudowanych. Podczas wpisywania nazwy funkcji w komórce zobaczysz listę wbudowanych i niestandardowych funkcji, które pasują do wpisywanego tekstu.

Funkcje niestandardowe pojawią się na tej liście, jeśli ich skrypt zawiera tag JsDoc @customfunction, jak w DOUBLE() przykładzie poniżej.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Zaawansowane

Korzystanie z usług Google Apps Script

Funkcje niestandardowe mogą wywoływać określone usługi Google Apps Script, aby wykonywać bardziej złożone zadania. Na przykład funkcja niestandardowa może wywołać usługę Language, aby przetłumaczyć angielskie zdanie na hiszpański.

W przeciwieństwie do większości innych typów skryptów Apps Script funkcje niestandardowe nigdy nie proszą użytkowników o autoryzację dostępu do danych osobowych. W związku z tym mogą wywoływać tylko usługi, które nie mają dostępu do danych osobowych, a mianowicie:

Jeśli funkcja niestandardowa zwraca komunikat o błędzie You do not have permission to call X service., usługa wymaga autoryzacji użytkownika i nie można jej używać w funkcji niestandardowej.

Aby użyć usługi innej niż wymienione powyżej, utwórz menu niestandardowe, które uruchamia funkcję Apps Script, zamiast pisać funkcję niestandardową. Funkcja wywoływana z menu w razie potrzeby poprosi użytkownika o autoryzację, a następnie może korzystać ze wszystkich usług Apps Script.

Udostępnianie

Funkcje niestandardowe są początkowo powiązane z arkuszem kalkulacyjnym, w którym zostały utworzone. Oznacza to, że funkcji niestandardowej napisanej w jednym arkuszu nie można używać w innych arkuszach, chyba że zastosujesz jedną z tych metod:

• Kliknij Rozszerzenia > Apps Script, aby otworzyć edytor skryptów, a następnie skopiuj tekst skryptu z oryginalnego arkusza kalkulacyjnego i wklej go do edytora skryptów w innym arkuszu.
• Utwórz kopię arkusza kalkulacyjnego zawierającego funkcję niestandardową, klikając Plik > Utwórz kopię. Gdy arkusz kalkulacyjny zostanie skopiowany, wszystkie dołączone do niego skrypty również zostaną skopiowane. Każdy, kto ma dostęp do arkusza kalkulacyjnego, może skopiować skrypt. (Współpracownicy, którzy mają tylko dostęp do wyświetlania, nie mogą otworzyć edytora skryptów w oryginalnym arkuszu kalkulacyjnym. Gdy jednak utworzy kopię, stanie się jej właścicielem i będzie mógł zobaczyć skrypt).
• Opublikuj skrypt jako dodatek do Edytora Arkuszy Google.

Optymalizacja

Za każdym razem, gdy w arkuszu kalkulacyjnym jest używana funkcja niestandardowa, Arkusze Google wykonują oddzielne wywołanie serwera Apps Script. Jeśli arkusz kalkulacyjny zawiera dziesiątki (lub setki, a nawet tysiące!) wywołań funkcji niestandardowych, ten proces może być dość powolny. W przypadku niektórych projektów z wieloma lub złożonymi funkcjami niestandardowymi może wystąpić tymczasowe opóźnienie w wykonywaniu.

Jeśli więc planujesz użyć funkcji niestandardowej wiele razy w przypadku dużego zakresu danych, rozważ zmodyfikowanie jej tak, aby akceptowała zakres jako dane wejściowe w postaci dwuwymiarowej tablicy, a następnie zwracała dwuwymiarową tablicę, która może przepełnić odpowiednie komórki.

Na przykład pokazaną powyżej funkcję DOUBLE() można przekształcić tak, aby akceptowała pojedynczą komórkę lub zakres komórek:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Powyższe podejście wykorzystuje metodę map obiektu Array JavaScriptu do metody na dwuwymiarowej tablicy komórek, aby uzyskać każdy wiersz, a następnie dla każdego wiersza ponownie używa map, aby zwrócić podwojoną wartość każdej komórki. Zwraca dwuwymiarową tablicę zawierającą wyniki. W ten sposób możesz wywołać funkcję DOUBLE tylko raz, ale obliczyć ją dla dużej liczby komórek jednocześnie, jak pokazano na zrzucie ekranu poniżej. (To samo można osiągnąć za pomocą zagnieżdżonych instrukcji if zamiast wywołania map).

Podobnie funkcja niestandardowa poniżej skutecznie pobiera treści na żywo z internetu i wykorzystuje dwuwymiarową tablicę do wyświetlania 2 kolumn wyników za pomocą tylko jednego wywołania funkcji. Jeśli każda komórka wymagałaby własnego wywołania funkcji, operacja trwałaby znacznie dłużej, ponieważ serwer Apps Script musiałby za każdym razem pobierać i parsować plik danych XML.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Techniki te można stosować w przypadku niemal każdej funkcji niestandardowej, która jest wielokrotnie używana w arkuszu kalkulacyjnym, chociaż szczegóły implementacji będą się różnić w zależności od działania funkcji.