Funkcje niestandardowe w Arkuszach Google

Arkusze Google oferują setki funkcji wbudowanych funkcji, takich jak AVERAGE, SUM oraz VLOOKUP W innych przypadkach do Twoich potrzeb, możesz użyć Google Apps Script do napisania niestandardowych funkcji – na przykład, aby przeliczyć metry na mile lub pobrać treści na żywo z internetu – i używać ich w Arkuszach Google, tak samo jak w przypadku funkcji wbudowanych.

Pierwsze kroki

Funkcje niestandardowe tworzy się za pomocą standardowego kodu JavaScript. Jeśli jesteś nowym użytkownikiem JavaScript, Codecademy zapewnia doskonały kurs dla początkujących. Uwaga: ten kurs nie został stworzony przez Google i nie jest z nim powiązany.

Oto prosta funkcja niestandardowa o nazwie DOUBLE, która mnoży wartość argumentu wartość wejściową o 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ć JavaScript i nie masz czasu, żeby uczyć się, sklep z dodatkami by sprawdzić, czy ktoś inny nie utworzył już danej funkcji niestandardowej.

Tworzenie funkcji niestandardowej

Aby napisać funkcję niestandardową:

  1. Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
  2. W menu wybierz Rozszerzenia >. Apps Script.
  3. Usuń cały kod w edytorze skryptów. W przypadku powyższej funkcji DOUBLE po prostu skopiuj kod i wklej go do edytora skryptów.
  4. U góry kliknij Zapisz .

Teraz możesz korzystać z funkcji niestandardowej.

Pobieram funkcję niestandardową z Google Workspace Marketplace

Usługa Google Workspace Marketplace oferuje kilka niestandardowych rozwiązań działa jako dodatków do Arkuszy Google. Aby używać 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 Google Workspace Marketplace kliknij pole wyszukiwania w prawym górnym rogu.
  4. Wpisz „funkcja niestandardowa” i naciśnij Enter.
  5. Jeśli znajdziesz interesujący Cię dodatek z funkcją niestandardową, kliknij Zainstaluj. aby ją zainstalować.
  6. Może pojawić się okno dialogowe z informacją, że dodatek wymaga autoryzacji. Jeśli tak, dokładnie przeczytaj powiadomienie i kliknij Zezwól.
  7. Dodatek stanie się dostępny w arkuszu kalkulacyjnym. Aby korzystać z dodatku w inny arkusz kalkulacyjny, otwórz go i u góry kliknij dodatki > Zarządzaj dodatkami. Znajdź dodatek, którego chcesz używać, i kliknij Opcje > Użyj w tym dokument.

Korzystanie z funkcji niestandardowej

Po zapisaniu funkcji niestandardowej lub zainstalowaniu jej Google Workspace Marketplace, jest równie łatwa w użyciu funkcja wbudowana:

  1. Kliknij komórkę, w której chcesz użyć funkcji.
  2. Wpisz znak równości (=), a po nim nazwę funkcji i dowolną wartość wejściową – na przykład =DOUBLE(A1) – i naciśnij Enter.
  3. W komórce za chwilę wyświetli się Loading..., a potem zwróci wynik.

Wytyczne dotyczące funkcji niestandardowych

Przed utworzeniem własnej funkcji niestandardowej warto poznać kilka wskazówek.

Nazwa

Oprócz standardowych konwencji nazewnictwa funkcji JavaScript należy musisz wiedzieć, że:

  • Nazwa funkcji niestandardowej musi być inna niż nazwy funkcji wbudowanych funkcji, takich jak SUM()
  • Nazwa funkcji niestandardowej nie może kończyć się podkreśleniem (_), które oznacza funkcję prywatną w Apps Script.
  • 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 w arkuszu kalkulacyjnym są tradycyjnie pisane wielkimi literami.

Argumenty

Podobnie jak w przypadku funkcji wbudowanej, funkcja niestandardowa może przyjmować argumenty jako wartości wejściowe:

  • Jeśli wywołasz swoją funkcję w odniesieniu do pojedynczej komórki jako argumentu (np. =DOUBLE(A1)), argumentem będzie wartość komórki.

  • Jeśli wywołasz funkcję w odniesieniu do zakresu komórek jako (np. =DOUBLE(A1:B10)), argument będzie dwuwymiarowym tablica komórek . Na przykład na zrzucie ekranu poniżej argumenty w metodzie =DOUBLE(A1:B2) są interpretowane przez Apps Script jako double([[1,3],[2,4]]) Zwróć uwagę na przykładowy kod strony DOUBLE z powyższego musi być zmodyfikowano tak, aby akceptował tablicę jako dane wejściowe.


  • Argumenty funkcji niestandardowej muszą być deterministyczny. Ten to wbudowane funkcje arkusza kalkulacyjnego, które za każdym razem zwracają inny wynik. obliczone przez nie – takie jak NOW() lub RAND() – nie są dozwolone jako argumenty do funkcji niestandardowej. Jeśli funkcja niestandardowa próbuje zwrócić wartość na podstawie jedną z tych zmiennych wbudowanych, wyświetli ona Loading... bez ograniczeń czasowych.

Zwracane wartości

Każda funkcja niestandardowa musi zwracać wartość do wyświetlenia, na przykład:

  • Jeśli funkcja niestandardowa zwraca wartość, jest ona wyświetlana w komórce. z którego funkcja została wywołana.
  • Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości pojawią się w sąsiednich komórkach, dopóki są puste. Gdyby to spowoduje zastąpienie istniejącej zawartości komórki przez tablicę, funkcja niestandardowa spowoduje błąd. Zobacz na przykład sekcję poświęconą optymalizacji 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, a jedynie komórek, z których jest wywoływana, i komórek sąsiednich. Aby edytować dowolne komórki: użyj menu niestandardowego do uruchomienia funkcji.
  • Wywołanie funkcji niestandardowej musi zostać zwrócone w ciągu 30 sekund. Jeśli tak nie jest, parametr komórka zawiera #ERROR!, a notatka w komórce to Exceeded maximum execution time (line 0).

Typy danych

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

  • Godziny i daty w Arkuszach stają się Date (data) w Apps Script. Jeśli arkusz kalkulacyjny i tag używa różnych stref czasowych (rzadki problem), funkcja niestandardowa wynagradzać.
  • Wartości czasu trwania w Arkuszach stają się też obiektami Date, ale Współpraca z nimi może być skomplikowana.
  • Wartości procentowe w Arkuszach stają się liczbami dziesiętnymi w Apps Script. Dla: na przykład komórka o wartości 10% stanie się w Apps Script 0.1.

Autouzupełnianie

Arkusze Google obsługują autouzupełnianie w przypadku funkcji niestandardowych, podobnych do funkcji funkcjami wbudowanymi. Gdy wpisz nazwę funkcji w komórce, wyświetli się lista wbudowanych które pasują do wpisanego tekstu.

Niestandardowe funkcje pojawią się na tej liście, jeśli ich skrypt zawiera parametr JsDoc @customfunction, tak 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

Używanie Google Apps Script

Funkcje niestandardowe mogą wywoływać określone usługi Google Apps Script, aby szybciej wykonywać złożone działania. zadania. Na przykład funkcja niestandardowa może wywoływać metodę usługi Język do tłumaczenia z angielskiego, na hiszpański.

W przeciwieństwie do większości innych typów skryptów Apps Script niestandardowe funkcje nigdy nie wymagają od użytkowników autoryzują dostęp do danych osobowych. Oznacza to, że mogą oni tylko dzwonić do służb. które nie mają dostępu do danych osobowych, w szczególności:

Zgodne usługi Uwagi
Pamięć podręczna Działa, ale nie jest szczególnie przydatny w funkcjach niestandardowych
w kodzie HTML, Mogą generować kod HTML, ale nie mogą go wyświetlić (rzadko przydatne)
JDBC
Język
Zablokuj Działa, ale nie jest szczególnie przydatny w funkcjach niestandardowych
Mapy Może wyznaczać trasy, ale nie może wyświetlać map.
Usługi getUserProperties() pobiera tylko właściwości elementu właściciel arkusza kalkulacyjnego. Edytorzy arkuszy kalkulacyjnych nie mogą ustawiać właściwości użytkownika w niestandardową.
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 z adresu URL
Narzędzia
XML;

Jeśli Twoja funkcja niestandardowa zwróci komunikat o błędzie You do not have permission to call X service., usługa wymaga autoryzacji użytkownika i dlatego nie może używane w funkcji niestandardowej.

Aby korzystać z usługi innej niż wymienione powyżej, utwórz menu niestandardowe, które uruchamia funkcję Apps Script zamiast pisać funkcję niestandardową. Funkcja wyzwalana z poziomu menu w razie potrzeby poprosi użytkownika o autoryzację i może w związku z tym usługi Apps Script.

Udostępnianie

Funkcje niestandardowe zaczynają się powiązane z parametrem arkusza kalkulacyjnego, w którym zostały utworzone. Oznacza to, że funkcja niestandardowa zapisana w Jeden arkusz kalkulacyjny nie może być używany w innych arkuszach kalkulacyjnych, chyba że użyjesz jednego z następujące metody:

  • Kliknij Rozszerzenia > Apps Script, aby otwórz edytor skryptów, a następnie skopiuj tekst skryptu z oryginalnego arkusza kalkulacyjnego i wklej go do edytora skryptów innego arkusza kalkulacyjnego.
  • Utwórz kopię arkusza kalkulacyjnego zawierającego funkcję niestandardową, klikając Plik > Utwórz kopię. Podczas kopiowania arkusza kalkulacyjnego wszystkie skrypty dołączone do też zostanie skopiowany. Każdy, kto ma dostęp do arkusza kalkulacyjnego, może skopiować skrypt. (Współpracownicy, którzy mają uprawnienia tylko do wyświetlania, nie mogą otworzyć edytora skryptów w oryginalnym arkuszu kalkulacyjnym. Jednak po utworzeniu kopii staje się ona właściciel kopii i widzi skrypt).
  • Opublikuj skrypt jako dodatek do edytora Arkuszy Google.
.

Optymalizacja

Za każdym razem, gdy w arkuszu kalkulacyjnym używana jest funkcja niestandardowa, Arkusze Google tworzą do serwera Apps Script. Jeśli arkusz zawiera dziesiątki (lub setek czy tysiące wywołań funkcji niestandardowych, może to być wolno.

Jeśli więc planujesz wielokrotnie korzystać z funkcji niestandardowej na dużym zakresu danych, rozważ modyfikację funkcji, tak aby akceptowała zakres w postaci dwuwymiarowej tablicy, a następnie zwraca dwuwymiarowy , które mogą pojawić się w odpowiednich komórkach.

Na przykład pokazaną powyżej funkcję DOUBLE() można zapisać tak, aby akceptowała jedną 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 powyższej metodzie map obiektu Array JavaScriptu tak, aby rekursywnie wywołanie DOUBLE dla każdej wartości w dwuwymiarowej tablicy komórek. Zwraca ono błąd dwuwymiarowej tablicy zawierającej wyniki. Dzięki temu możesz zadzwonić pod numer DOUBLE tylko raz, ale obliczyć obliczenia dla dużej liczby komórek jednocześnie, jak widać w argumencie zrzut ekranu poniżej. (To samo możesz osiągnąć z zagnieżdżonym if zamiast wywołania map).

Analogicznie poniższa funkcja niestandardowa skutecznie pobiera treści na żywo z za pomocą dwuwymiarowej tablicy przy wyświetlaniu dwóch kolumn wyników tylko jedno wywołanie funkcji. Jeśli każda komórka wymagała własnego wywołania funkcji, funkcja trwałoby znacznie dłużej, ponieważ serwer Apps Script pobierać i analizować 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, która jest używana w arkuszu kalkulacyjnym, chociaż szczegóły implementacji będą różnią się w zależności od zachowania tej funkcji.