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ć Apps Script, aby napisać funkcje niestandardowe, a następnie używać ich w Arkuszach tak samo jak funkcji wbudowanych.

Przykłady funkcji niestandardowych znajdziesz w tych samouczkach:

Pierwsze kroki

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

Oto 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 do Google Workspace, aby zobaczyć, czy ktoś inny nie utworzył już potrzebnej Ci funkcji niestandardowej.

Tworzenie funkcji niestandardowej

Aby napisać funkcję niestandardową:

Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach.
Wybierz kolejno Rozszerzenia > Apps Script.
Usuń cały kod z edytora skryptów. W przypadku funkcji DOUBLE pokazanej wcześniej skopiuj i wklej kod do edytora skryptów.
U góry kliknij Zapisz .

Teraz możesz użyć funkcji niestandardowej.

Pobieranie funkcji niestandardowej z Google Workspace Marketplace

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

Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach.
U góry kliknij Dodatki > Pobierz dodatki.
Gdy otworzy się Google Workspace Marketplace, kliknij pole wyszukiwania w prawym górnym rogu.
Wpisz „funkcja niestandardowa” i naciśnij Enter.
Jeśli znajdziesz dodatek z funkcją niestandardową, który Cię interesuje, kliknij Zainstaluj, aby go zainstalować.
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.
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 > Użyj w tym dokumencie.

Używanie funkcji niestandardowej

Gdy napiszesz funkcję niestandardową lub zainstalujesz ją z Google Workspace Marketplace, możesz jej używać tak samo jak funkcji wbudowanej:

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

Wskazówki dotyczące funkcji niestandardowych

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

Nazewnictwo funkcji

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 argumentu (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 opisany wcześniej 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ć symbol Loading... w nieskończoność.
Aby wywołać ponowne obliczenie, musisz przekazać odwołanie do komórki lub zakresu komórek bezpośrednio jako argument funkcji niestandardowej. W przeciwnym razie funkcja niestandardowa nie zostanie ponownie obliczona, dopóki nie edytujesz funkcji lub nie zmienisz wartości komórki, do której się odwołuje. Jeśli w funkcjach niestandardowych używasz metody getValue, pamiętaj, że zakres, do którego się odwołujesz, nie jest bezpośrednio przekazywany jako argument do funkcji niestandardowej.

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 spowodowałoby to zastąpienie istniejącej zawartości komórki, funkcja niestandardowa zgłosi 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ę #ERROR!, a notatka do komórki będzie miała wartość Exceeded maximum execution time (line 0)..

Typy danych

Arkusze 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 musi 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 Google 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 obsługują autouzupełnianie funkcji niestandardowych podobnie jak funkcji wbudowanych. Podczas wpisywania nazwy funkcji w komórce wyświetla się lista wbudowanych i niestandardowych funkcji pasujących do wpisywanego tekstu.

Funkcje niestandardowe pojawiają się na tej liście, jeśli ich skrypt zawiera tag JSDoc @customfunction, jak w DOUBLE()przykładzie.

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

Zaawansowane

W tej sekcji znajdziesz zaawansowane tematy dotyczące funkcji niestandardowych.

Korzystanie z usług Google Apps Script

Funkcje niestandardowe mogą wywoływać określone usługi 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:

Zgodne usługi	Uwagi
Pamięć podręczna	Działa, ale nie jest szczególnie przydatna w funkcjach niestandardowych
w kodzie HTML,	Może generować kod HTML, ale nie może go wyświetlać (rzadko przydatne).
JDBC
Język
Zablokuj	Działa, ale nie jest szczególnie przydatna w funkcjach niestandardowych
Mapy	Możesz wyznaczać trasy, ale nie możesz wyświetlać map
Usługi	`getUserProperties()` otrzymuje tylko właściwości właściciela arkusza kalkulacyjnego. Edytujący arkusz kalkulacyjny nie mogą ustawiać właściwości użytkownika w funkcji niestandardowej.
Arkusz kalkulacyjny	Tylko do odczytu (może używać większości metod `get()`, ale nie `set()`). Nie może otwierać innych arkuszy kalkulacyjnych (`SpreadsheetApp.openById()` ani `SpreadsheetApp.openByUrl()`).
Pobieranie adresu URL	Dostęp do zasobów w internecie przez pobieranie adresów URL.
Narzędzia
XML;

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ż te na powyższej liście, utwórz menu niestandardowe, które uruchamia funkcję Apps Script zamiast funkcji niestandardowej. Funkcja wywoływana z menu w razie potrzeby prosi użytkownika o autoryzację, a następnie może korzystać ze wszystkich usług Apps Script.

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 kalkulacyjnym 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 uprawnienia 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.

Wszystkie skrypty powiązane z kontenerem mają te same listy dostępu co ich kontenery. Oznacza to, że każda osoba z uprawnieniami do edycji arkusza kalkulacyjnego może też edytować dołączony do niego kod Apps Script. Więcej informacji znajdziesz w artykule na temat dostępu do skryptów powiązanych.

Optymalizacja

Za każdym razem, gdy w arkuszu kalkulacyjnym jest używana funkcja niestandardowa, Arkusze wykonują osobne 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ć powolny. W przypadku niektórych projektów z wieloma lub złożonymi funkcjami niestandardowymi może wystąpić tymczasowe opóźnienie w wykonaniu.

Jeśli więc planujesz użyć funkcji niestandardowej wiele razy w przypadku dużego zakresu danych, zmodyfikuj ją 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łniać odpowiednie komórki.

Na przykład pokazaną wcześniej funkcję DOUBLE() można zmodyfikować 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;
}

To podejście wykorzystuje metodę map obiektu Array JavaScriptu 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. Możesz to samo osiągnąć za pomocą zagnieżdżonych instrukcji if zamiast wywołania map.

Podobnie ta funkcja niestandardowa skutecznie pobiera treści na żywo z internetu i wyświetla 2 kolumny wyników w tablicy dwuwymiarowej 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 analizować plik 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 różnią się w zależności od działania funkcji.