Funkcje niestandardowe w Arkuszach Google

Arkusze Google oferują setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Jeśli te funkcje nie spełniają Twoich potrzeb, możesz użyć Google Apps Script do napisania funkcji niestandardowych, np. do konwertowania metrów na mile lub pobierania treści na żywo z Internetu, a następnie używać ich w Arkuszach Google tak jak wbudowanych funkcji.

Pierwsze kroki

Funkcje niestandardowe są tworzone za pomocą standardowego kodu JavaScript. Jeśli dopiero zaczynasz korzystać z JavaScriptu, Codecademy oferuje ś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 podawaną wartość 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ć kod JavaScript i nie masz czasu, aby się tego nauczyć, odwiedź sklep z dodatkami, aby sprawdzić, czy ktoś inny nie stworzył 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 menu Rozszerzenia > Apps Script.
  3. usunąć kod w edytorze skryptu, W przypadku funkcji DOUBLE powyżej wystarczy skopiować i wklejć kod do edytora skryptu.
  4. U góry kliknij Zapisz .

Teraz możesz używać funkcji niestandardowej.

Pobieranie funkcji niestandardowej z  Google Workspace Marketplace

Google Workspace Marketplace zawiera kilka funkcji niestandardowych w postaci dodatków do Arkuszy Google. Aby korzystać z tych dodatków lub je przeglądać:

  1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
  2. U góry kliknij Dodatki > Pobierz dodatki.
  3. Gdy otworzy się strona Google Workspace Marketplace, w prawym górnym rogu kliknij pole wyszukiwania.
  4. Wpisz „niestandardowa funkcja” i naciśnij Enter.
  5. Jeśli znajdziesz interesujący Cię dodatek z funkcją niestandardową, kliknij Zainstaluj, aby go zainstalować.
  6. Może pojawić się okno z informacją, że dodatek wymaga autoryzacji. W takim przypadku uważnie przeczytaj powiadomienie, a potem 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  > Użyj tylko w tym dokumencie.

Korzystanie z funkcji niestandardowej

Po napisaniu funkcji niestandardowej lub zainstalowaniu jej z poziomuGoogle Workspace Marketplacemożesz z niej korzystać tak samo łatwo jak z 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ą, na przykład =DOUBLE(A1), i naciśnij klawisz Enter.
  3. Komórka chwilowo wyświetli wartość Loading..., a następnie zwróci 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 JavaScript pamiętaj o tych kwestiach:

  • Nazwa funkcji niestandardowej musi się różnić od nazw wbudowanych funkcji, takich jak SUM().
  • Nazwa funkcji niestandardowej nie może kończyć się znakiem podkreślenia (_), który w Apps Script oznacza funkcję prywatną.
  • Nazwa funkcji niestandardowej musi być zadeklarowana 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ę, podając jako argument odwołanie do pojedynczej komórki (np. =DOUBLE(A1)), argumentem będzie wartość tej komórki.

  • Jeśli wywołasz funkcję, podając jako argument odwołanie do zakresu komórek (np. =DOUBLE(A1:B10)), argument będzie dwuwymiarowym tablicą wartości komórek. Na przykład na poniższym zrzucie ekranu argumenty w pozycji =DOUBLE(A1:B2) są interpretowane przez Apps Script jako double([[1,3],[2,4]]). Pamiętaj, że przykładowy kod funkcji DOUBLE z powyższego przykładu trzeba zmodyfikować, aby przyjmował tablicę jako dane wejściowe.


  • Argumenty funkcji niestandardowej muszą być deterministyczne. Oznacza to, że wbudowane funkcje arkuszy kalkulacyjnych, które zwracają inny wynik za każdym razem, gdy są obliczane (np. NOW() lub RAND()), nie mogą być argumentami funkcji niestandardowej. Jeśli funkcja niestandardowa próbuje zwrócić wartość na podstawie jednej z tych zmiennych wbudowanych funkcji, wyświetli się Loading...w nieskończoność.

Zwracane wartości

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

  • Jeśli funkcja niestandardowa zwraca wartość, wyświetla się ona w komórce, z której została wywołana.
  • Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości te przelewają się do sąsiednich komórek, o ile są puste. Jeśli spowoduje to zastąpienie istniejącej zawartości komórki, funkcja niestandardowa zwróci błąd. Przykład znajdziesz w sekcji optymalizowania 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 komórki, z których jest wywoływana, oraz sąsiednie komórki. Aby edytować dowolne komórki, użyj menu niestandardowego, aby uruchomić funkcję.
  • Wywołanie niestandardowej funkcji musi zwrócić wynik w ciągu 30 sekund. Jeśli nie, komórka zawiera wartość #ERROR!, a jej notatki – wartość 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, AppsScript traktuje je jako odpowiednie typy danych w JavaScriptzie. Oto najczęstsze problemy:

  • Czasy i daty w Arkuszach stają się obiektami Date w Apps Script. Jeśli arkusz kalkulacyjny i skrypt używają różnych stref czasowych (co jest rzadkim problemem), funkcja niestandardowa musi to uwzględnić.
  • Wartości czasu trwania w Arkuszach stają się też obiektami Date, 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 komórką o wartości 0.1.

Autouzupełnianie

Arkusze Google obsługują autouzupełnianie w przypadku funkcji niestandardowych w podobny sposób jak w przypadku wbudowanych funkcji. Gdy wpisujesz nazwę funkcji w komórce, wyświetla się lista funkcji wbudowanych i niestandardowych, które pasują do tego, co wpisujesz.

Funkcje niestandardowe pojawią się na tej liście, jeśli ich kod zawiera tag JsDoc @customfunction, jak w przykładzie DOUBLE() 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 w celu wykonywania bardziej złożonych zadań. 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ą one wywoływać tylko usługi, które nie mają dostępu do danych osobowych, a dokładnie:

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 przypadku funkcji niestandardowych
Mapy Może obliczać trasy, ale nie może wyświetlać map
Usługi getUserProperties() uzyskuje tylko właściwości właściciela arkusza kalkulacyjnego. Redaktorzy arkuszy kalkulacyjnych nie mogą ustawiać właściwości użytkownika w funkcji niestandardowej.
Arkusz kalkulacyjny Tylko do odczytu (można używać większości metod get*(), ale nie set*()).
Nie można otwierać innych arkuszy kalkulacyjnych (SpreadsheetApp.openById() lub SpreadsheetApp.openByUrl()).
Pobieranie adresu 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, więc nie można jej używać w funkcji niestandardowej.

Aby korzystać z usługi innej niż wymienione powyżej, zamiast pisać funkcję niestandardową, utwórz menu niestandardowe, które uruchamia funkcję Apps Script. Funkcja wywoływana z menu poprosi użytkownika o autoryzację, jeśli będzie to konieczne, i będzie mogła korzystać ze wszystkich usług Apps Script.

Udostępnianie

Funkcje niestandardowe są powiązane z arkuszem kalkulacyjnym, w którym zostały utworzone. Oznacza to, że funkcji niestandardowej napisanej w jednym arkuszu nie można użyć 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 w edytorze skryptu innego arkusza kalkulacyjnego.
  • Utwórz kopię arkusza kalkulacyjnego zawierającego funkcję niestandardową, klikając Plik > Utwórz kopię. Podczas kopiowania arkusza kalkulacyjnego są również kopiowane wszystkie skrypty do niego dołączone. 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 skryptu w pierwotnym arkuszu kalkulacyjnym). Jednak po utworzeniu kopii staje się on właścicielem kopii i może zobaczyć skrypt.
  • Opublikuj skrypt jako Edytor dodatku w Arkuszach Google.

Optymalizacja

Za każdym razem, gdy w arkuszu kalkulacyjnym jest używana funkcja niestandardowa, Arkusze Google wysyłają osobne żądanie do serwera Apps Script. Jeśli arkusz kalkulacyjny zawiera dziesiątki (setki lub tysiące!) wywołań funkcji niestandardowych, ten proces może być dość powolny.

Jeśli więc planujesz wielokrotne używanie funkcji niestandardowej na dużym zakresie danych, rozważ zmodyfikowanie jej tak, aby przyjmowała zakres jako dane wejściowe w postaci tablicy dwuwymiarowej, a następnie zwracała tablicę dwuwymiarową, która może się przelewać do odpowiednich komórek.

Na przykład funkcję DOUBLE() pokazaną powyżej można zmodyfikować tak, aby przyjmowała pojedynczą komórkę lub zakres komórek w ten 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;
}

Powyższe podejście korzysta z metody map obiektu Array w JavaScriptzie, aby rekurencyjnie wywoływać funkcję DOUBLE dla każdej wartości w dwudwumiarowej tablicy komórek. Zwraca tablicę dwuwymiarową zawierającą wyniki. W ten sposób możesz wywołać funkcję DOUBLE tylko raz, ale wykonać obliczenia dla dużej liczby komórek jednocześnie, jak pokazano na poniższym zrzucie ekranu. (to samo można osiągnąć za pomocą zagnieżdżonych instrukcji ifzamiast wywołania funkcji map).

Podobnie funkcja niestandardowa poniżej skutecznie pobiera treści na żywo z Internetu i używa tablicy dwuwymiarowej do wyświetlania 2 kolumn wyników za pomocą tylko 1 wywołania funkcji. Jeśli każda komórka wymagałaby własnego wywołania funkcji, operacja zajęłaby znacznie więcej czasu, ponieważ serwer Apps Script musiałby za każdym razem pobierać i analizować 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;
}

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