Arkusze Google mają setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Gdy te funkcje nie wystarczą, możesz użyć Google Apps Script do napisania niestandardowych funkcji, np. by przeliczyć metry na mile, lub pobierać treści na żywo z internetu, a potem używać ich w Arkuszach Google tak jak za pomocą wbudowanej funkcji.
Pierwsze kroki
Funkcje niestandardowe tworzy się za pomocą standardowego kodu JavaScript. Jeśli nie masz doświadczenia z JavaScriptem, w Codecademy znajdziesz świetny 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ść 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 umiesz pisać JavaScriptu i nie masz czasu na naukę, sprawdź w sklepie z dodatkami, czy ktoś inny nie stworzył już potrzebnej Ci funkcji niestandardowej.
Tworzenie funkcji niestandardowej
Aby napisać funkcję niestandardową:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- Kliknij menu Rozszerzenia > Apps Script.
- Usuń cały kod w edytorze skryptów. W przypadku powyższej funkcji
DOUBLEskopiuj kod i wklej go do edytora skryptów. - U góry kliknij Zapisz .
Teraz możesz korzystać z funkcji niestandardowej.
Pobieram funkcję niestandardową z Google Workspace Marketplace
Google Workspace Marketplace oferuje kilka funkcji niestandardowych jako dodatki do Arkuszy Google. Aby używać tych dodatków lub je przeglądać:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- U góry kliknij Dodatki > Pobierz dodatki.
- Po otwarciu 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, przeczytaj uważnie powiadomienie i kliknij Zezwól.
- Dodatek stanie się dostępny w arkuszu kalkulacyjnym. Aby użyć dodatku w innym arkuszu kalkulacyjnym, otwórz go i u góry kliknij Dodatki > Zarządzaj dodatkami. Znajdź dodatek, którego chcesz użyć, i kliknij Opcje > Użyj w tym dokumencie.
Korzystanie z funkcji niestandardowej
Gdy utworzysz funkcję niestandardową lub zainstalujesz ją z poziomuGoogle Workspace Marketplace, możesz jej używać jak funkcji wbudowanej:
- Kliknij komórkę, w której chcesz użyć funkcji.
- Wpisz znak równości (
=), a po nim nazwę funkcji i dowolną wartość wejściową, np.=DOUBLE(A1), i naciśnij Enter. - 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 pamiętaj o tych kwestiach:
- Nazwa funkcji niestandardowej musi być inna niż nazwy funkcji wbudowanych, 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 nievar myFunction = new Function(). - Wielkość liter nie ma znaczenia, ale nazwy funkcji arkusza kalkulacyjnego 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 funkcję w odniesieniu do jednej 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 argumentu (np.
=DOUBLE(A1:B10)), argumentem będzie dwuwymiarowa tablica wartości komórek. Na przykład na zrzucie ekranu poniżej argumenty w języku=DOUBLE(A1:B2)są interpretowane przez Apps Script jakodouble([[1,3],[2,4]]). Pamiętaj, że przykładowy kodDOUBLEz powyższego trzeba zmodyfikować, by akceptował tablicę jako dane wejściowe.
Argumenty funkcji niestandardowej muszą być deterministyczne. Oznacza to, że wbudowane funkcje arkusza kalkulacyjnego, które za każdym razem zwracają inny wynik (np.
NOW()lubRAND()), nie są dozwolone jako argumenty funkcji niestandardowej. Jeśli funkcja niestandardowa próbuje zwrócić wartość na podstawie jednej z tych funkcji wbudowanych, wyświetliLoading...w nieskończoność.
Zwracane wartości
Każda funkcja niestandardowa musi zwracać wartość do wyświetlenia, na przykład:
- Jeśli funkcja niestandardowa zwróci wartość, wyświetli się ona w komórce, z której funkcja została wywołana.
- Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości wypełniają sąsiadujące komórki, o ile są one puste. Jeśli spowodowałoby to zastąpienie istniejącej zawartości komórki przez tablicę, funkcja niestandardowa zwróci błąd. Przykład znajdziesz w sekcji o optymalizacji 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ć wybranych komórek, a jedynie komórki, z których została wywołana, i sąsiadujące z nimi komórki. 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, komórka wyświetli 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. Gdy te wartości są używane w funkcjach niestandardowych, Apps Script traktuje je jako odpowiedni typ danych w języku JavaScript. Oto najczęstsze obszary dezorientacji:
- 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 też stają się 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 z wartością
10%zmieni się w0.1w Apps Script.
Autouzupełnij
Arkusze Google obsługują autouzupełnianie w przypadku funkcji niestandardowych, podobnie jak w przypadku funkcji wbudowanych. Podczas wpisywania nazwy funkcji w komórce pojawi się lista funkcji wbudowanych i niestandardowych, 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 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 w celu wykonania bardziej złożonych zadań. Funkcja niestandardowa może na przykład wywoływać usługę Language (Język), aby przetłumaczyć wyrażenie z języka angielskiego na hiszpański.
W przeciwieństwie do większości innych typów skryptów Apps Script niestandardowe funkcje nigdy nie proszą użytkowników o autoryzację dostępu do danych osobowych. W związku z tym mogą dzwonić tylko do usług, które nie mają dostępu do danych osobowych, a w szczególności do:
| Zgodne usługi | Notatki |
|---|---|
| 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 |
| Maps | Może wyznaczać trasy, 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żytkownika w funkcji niestandardowej. |
| Arkusz kalkulacyjny | Tylko do odczytu (może używać większości metod get*(), ale nie set*()).Nie można otwierać innych arkuszy kalkulacyjnych ( SpreadsheetApp.openById() ani 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., oznacza to, że usługa wymaga autoryzacji użytkownika i dlatego nie można jej użyć w funkcji niestandardowej.
Aby skorzystać z usługi innej niż wymienione powyżej, utwórz menu niestandardowe, które uruchamia funkcję Apps Script, zamiast pisać funkcję niestandardową. Funkcja uruchamiana z poziomu menu w razie potrzeby prosi użytkownika o autoryzację i 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 zapisanej w jednym arkuszu kalkulacyjnym 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 kalkulacyjnego i wklej go w edytorze skryptów innego arkusza kalkulacyjnego.
- Utwórz kopię arkusza kalkulacyjnego zawierającego funkcję niestandardową, klikając Plik > Utwórz kopię. Podczas kopiowania arkusza kalkulacyjnego kopiowane są też wszystkie dołączone do niego skrypty. Każdy, kto ma dostęp do arkusza kalkulacyjnego, może skopiować jego skrypt. Współpracownicy, którzy mają uprawnienia tylko do wyświetlania, nie mogą otworzyć edytora skryptów w pierwotnym arkuszu kalkulacyjnym. Jednak po utworzeniu kopii staje się ona właścicielem kopii i ma dostęp do skryptu).
- Opublikuj skrypt jako dodatek do edytora Arkuszy Google.
Optymalizacja
Za każdym razem, gdy w arkuszu kalkulacyjnym używana jest funkcja niestandardowa, Arkusze Google wywołują osobne wywołanie serwera Apps Script. Jeśli arkusz kalkulacyjny zawiera dziesiątki (albo setki lub tysiące wywołań funkcji niestandardowych), ten proces może być dość powolny.
Jeśli więc planujesz wielokrotnie korzystać z funkcji niestandardowej na dużym zakresie danych, rozważ jej modyfikację, aby akceptowała zakres jako dane wejściowe w postaci dwuwymiarowej tablicy, a następnie zwraca dwuwymiarową tablicę, która może się pojawić w odpowiednich komórkach.
Na przykład pokazaną powyżej funkcję DOUBLE() można ponownie napisać, aby akceptował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;
}
W powyższym podejściu wykorzystuje się metodę map obiektu Array JavaScriptu do rekurencyjnego wywoływania funkcji DOUBLE w przypadku każdej wartości w dwuwymiarowej tablicy komórek. Zwraca ona dwuwymiarową tablicę z wynikami. Dzięki temu można wywołać funkcję DOUBLE tylko raz i obliczyć wynik dla dużej liczby komórek jednocześnie, jak widać na zrzucie ekranu poniżej. (To samo możesz osiągnąć za pomocą zagnieżdżonych instrukcji if zamiast wywołania map).
Podobnie poniższa funkcja niestandardowa skutecznie pobiera treści na żywo z internetu i używa dwuwymiarowej tablicy do wyświetlania 2 kolumn z wynikami za pomocą jednego wywołania funkcji. Gdyby każda komórka wymagała osobnego wywołania funkcji, operacja trwałaby znacznie dłużej, ponieważ serwer Apps Script musiałby za każdym razem pobierać i analizować kanał 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 zastosować do niemal każdej funkcji niestandardowej, która jest używana wielokrotnie w arkuszu kalkulacyjnym. Szczegóły implementacji będą się jednak różnić w zależności od działania funkcji.