Funkcje niestandardowe w Arkuszach Google

Zadbaj o dobrą organizację dzięki kolekcji Zapisuj i kategoryzuj treści zgodnie ze swoimi preferencjami.

Arkusze Google mają setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Jeśli chcesz, za pomocą Google Apps Script możesz pisać funkcje niestandardowe, np. przekształcać metry na mile lub pobierać treści na żywo z internetu, a potem używać ich w Arkuszach Google tak jak wbudowanych funkcji.

Pierwsze kroki

Funkcje niestandardowe są tworzone przy użyciu standardowego kodu JavaScript. Jeśli korzystasz z JavaScriptu po raz pierwszy, Codecademy oferuje świetne kursy dla początkujących. (Uwaga: ten kurs nie został utworzony 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:

function DOUBLE(input) {
  return input * 2;
}

Jeśli nie wiesz, jak pisać JavaScript, i nie masz czasu na naukę, sprawdź sklep uzupełniający, aby zobaczyć, czy ktoś inny utworzył już niestandardową funkcję.

Tworzenie funkcji niestandardowej

Aby zapisać funkcję niestandardową:

  1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
  2. Wybierz pozycję menu Rozszerzenia > Apps Script.
  3. Usuń dowolny kod w edytorze skryptów. Aby uzyskać dostęp do powyższych funkcji DOUBLE, skopiuj kod i wklej go do edytora skryptów.
  4. U góry kliknij Zapisz .

Teraz możesz używać funkcji niestandardowej.

Pobieram funkcję niestandardową z Google Workspace Marketplace

Google Workspace Marketplace oferuje kilka niestandardowych funkcji dodatków do Arkuszy Google. Aby korzystać z tych dodatków:

  1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
  2. U góry kliknij Dodatki > Pobierz dodatki.
  3. Po otwarciu narzędzia Google Workspace Marketplace kliknij pole wyszukiwania w prawym górnym rogu.
  4. Wpisz "funkcja niestandardowa&quot i naciśnij Enter.
  5. Jeśli znajdziesz dodatek niestandardowy, który Cię interesuje, kliknij Zainstaluj, aby go zainstalować.
  6. W oknie może pojawić się komunikat, że dodatek wymaga autoryzacji. Jeśli tak, przeczytaj uważnie powiadomienie, a potem kliknij Zezwól.
  7. Dodatek staje się dostępny w arkuszu kalkulacyjnym. Aby użyć dodatku w innym arkuszu kalkulacyjnym, otwórz ten arkusz i u góry kliknij Dodatki i zarządzanie dodatkami. Znajdź dodatek, którego chcesz używać, i kliknij Opcje > Użyj w tym dokumencie.

Korzystanie z funkcji niestandardowej

Gdy utworzysz funkcję niestandardową lub zainstalujesz ją z funkcjiGoogle Workspace Marketplace, będzie to tak proste w użyciu jak wbudowana:

  1. Kliknij komórkę, w której chcesz użyć tej funkcji.
  2. Wpisz znak równości (=), po którym następuje nazwa funkcji i dowolna wartość wejściowa – na przykład =DOUBLE(A1) – i naciśnij Enter.
  3. Komórka za chwilę wyświetli się Loading..., a potem zwróci wynik.

Wytyczne dotyczące funkcji niestandardowych

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

Nazwa

Oprócz standardowych konwencji nazewnictwa funkcji JavaScript zwróć uwagę na te kwestie:

  • Nazwa funkcji niestandardowej musi różnić się od nazw funkcji wbudowanych, np. SUM().
  • Nazwa funkcji niestandardowej nie może kończyć się znakiem podkreślenia (_), który oznacza funkcję prywatną w Apps Script.
  • Nazwę funkcji niestandardowej należy zadeklarować, używając składni function myFunction(), a nie var myFunction = new Function().
  • Wielkie litery nie mają znaczenia, chociaż nazwy funkcji arkuszy kalkulacyjnych są zwykle 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 argumentu (np. =DOUBLE(A1:B10)), argument będzie dwuwymiarowym tablicam wartości. 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 góry musi zostać zmodyfikowany, aby akceptował tablicę jako dane wejściowe.


  • Argumenty funkcji niestandardowych muszą być deterministyczne. To oznacza, że wbudowane funkcje arkuszy kalkulacyjnych, które zwracają inny wynik przy każdym obliczaniu (np. NOW() czy RAND()), nie są dozwolone jako argumenty funkcji niestandardowej. Jeśli funkcja niestandardowa spróbuje zwrócić wartość na podstawie jednej z tych wbudowanych funkcji zmiennych, wyświetli się Loading... bez końca.

Wartości zwracane

Każda funkcja niestandardowa musi zwrócić wartość do wyświetlenia, np.:

  • Jeśli funkcja niestandardowa zwróci wartość, wyświetli się w komórce, z której została wywołana.
  • Jeśli funkcja niestandardowa zwróci dwuwymiarową tablicę wartości, wartości będą wypełniać sąsiednie komórki, o ile te komórki będą puste. Jeśli spowoduje to zastąpienie bieżącej zawartości komórki przez funkcję niestandardową, zostanie zwrócony błąd. Przykład znajdziesz w sekcji o optymalizowaniu funkcji niestandardowych.
  • Funkcja niestandardowa nie może wpływać na komórki inne niż te, do których zwraca wartość. Inaczej mówiąc, funkcja niestandardowa nie może edytować dowolnych komórek – tylko te, z których pochodzi, i sąsiadujące z nimi komórki. Aby edytować dowolne komórki, użyj funkcji menu niestandardowego.
  • Niestandardowe wywołanie funkcji musi zostać zwrócone w ciągu 30 sekund. Jeśli się nie pojawi, w komórce pojawi się błąd: Internal error executing the custom function.

Typy danych

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

  • Daty i godziny w Arkuszach stają się obiektami daty w Apps Script. Jeśli arkusz kalkulacyjny i skrypt korzystają z różnych stref czasowych (rzadki problem), funkcja niestandardowa musi zrekompensować.
  • Wartości dotyczące czasu trwania w Arkuszach też stają się obiektami Date, ale korzystanie z nich może być skomplikowane.
  • W Apps Script wartości procentowe w Arkuszach stają się wartościami dziesiętnymi. Na przykład komórka o wartości 10% staje się 0.1 w Apps Script.

Autouzupełnianie

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

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

/**
 * 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 Apps Script

Funkcje niestandardowe mogą wywoływać określone usługi Apps Script, aby wykonywać bardziej złożone zadania. Funkcja niestandardowa może na przykład wywołać usługę Language, aby przetłumaczyć wyrażenie w języku angielskim 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 rezultacie mogą oni dzwonić tylko do usług, które nie mają dostępu do danych osobowych, a konkretnie:

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ć (bardzo rzadko)
JDBC
Język
Zablokuj działa, ale nie jest szczególnie przydatna w funkcjach niestandardowych,
Mapy Może obliczać trasę, ale nie może wyświetlać map
Usługi getUserProperties() pobiera tylko właściwości właściciela arkusza kalkulacyjnego. Edytorzy arkuszy kalkulacyjnych nie mogą ustawiać właściwości użytkowników w funkcji niestandardowej.
Arkusz kalkulacyjny Tylko do odczytu (może korzystać z większości metod get*(), ale nie set*()).
Nie można otworzyć innych arkuszy kalkulacyjnych (SpreadsheetApp.openById() lub SpreadsheetApp.openByUrl()).
Pobieranie adresów URL
Narzędzia
XML;

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

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

Dzielenie przychodów

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

  • Kliknij Rozszerzenia > Apps Script, aby otworzyć edytor skryptów, a następnie skopiuj tekst skryptu z oryginalnego arkusza i wklej go w edytorze arkuszy kalkulacyjnych.
  • Utwórz kopię arkusza kalkulacyjnego zawierającą funkcję niestandardową, klikając Utwórz plik i utwórz kopię. Podczas kopiowania arkusza kalkulacyjnego skopiowane są również dołączone do niego skrypty. Każdy, kto ma dostęp do arkusza kalkulacyjnego, może skopiować ten skrypt. (Współpracownicy, którzy mają uprawnienia tylko do wyświetlania, nie mogą otwierać edytora skryptów w pierwotnym arkuszu kalkulacyjnym. Osoba, która robi kopię, staje się jej właścicielem i widzi skrypt).
  • Opublikuj skrypt jako dodatek do Arkuszy Google.

Optymalizacja

Za każdym razem, gdy w arkuszu kalkulacyjnym używana jest funkcja niestandardowa, Arkusze Google wykonują osobne wywołanie do serwera Apps Script. Jeśli Twój arkusz kalkulacyjny zawiera dziesiątki (lub setki tysięcy) wywołań funkcji niestandardowych, ten proces może być dość długi.

Dlatego gdy planujesz używać funkcji niestandardowej wielokrotnie w dużym zakresie danych, możesz zmodyfikować tę funkcję w taki sposób, aby akceptowała zakres jako dane w postaci tablicy dwuwymiarowej, a następnie zwracał dwuwymiarowy tablica, który może dostać się do odpowiednich komórek.

Na przykład poniższa funkcja DOUBLE() może zostać przepisana tak, aby zaakceptowała pojedynczą komórkę lub zakres komórek w następujący sposób:

/**
 * 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;
}

W przypadku powyższego podejścia użyto obiektu map JavaScriptu do wywołania rekurencyjnego DOUBLE dla każdej wartości w dwuwymiarowej tablicy komórek. Zwraca tablicę dwuwymiarową zawierającą wyniki. Dzięki temu możesz wywołać funkcję DOUBLE tylko raz, ale obliczać ją dla dużej liczby komórek jednocześnie, tak jak na zrzucie ekranu poniżej. (To samo możesz zrobić dzięki zagnieżdżonym instrukcjom if zamiast wywołania map).

Analogicznie funkcja niestandardowa poniżej efektywnie pobiera treści na żywo z internetu i korzysta z tablicy dwuwymiarowej do wyświetlania 2 kolumn wyników z pojedynczym wywołaniem funkcji. Jeśli każda komórka wymaga własnego wywołania funkcji, operacja zajmie znacznie więcej czasu, ponieważ serwer Apps Script będzie musiał pobrać i przeanalizować plik XML za każdym razem.

/**
 * 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ć do niemal każdej funkcji niestandardowej używanej w arkuszu kalkulacyjnym, jednak szczegóły implementacji mogą się różnić w zależności od działania funkcji.