Arkusze Google mają setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Jeśli to Ci nie odpowiada, możesz użyć Google Apps Script do pisania niestandardowych funkcji, np. przeliczania metryk na mile lub pobierania aktualnych treści z internetu, a potem używać ich w Arkuszach Google tak samo jak funkcji wbudowanej.
Pierwsze kroki
Funkcje niestandardowe tworzy się za pomocą standardowego kodu JavaScript. Jeśli nie masz doświadczenia z językiem JavaScript, w Codecademy znajdziesz świetny kurs dla początkujących. (Uwaga: ten kurs nie został utworzony przez firmę Google i nie jest z nią 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 dodatków, czy ktoś inny nie stworzył już funkcji niestandardowej.
Tworzenie funkcji niestandardowej
Aby napisać funkcję niestandardową:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- Wybierz pozycję menu Rozszerzenia > Apps Script.
- Usuń cały kod z edytora skryptów. W przypadku powyższej funkcji
DOUBLEpo prostu skopiuj kod i wklej go do edytora skryptów. - U góry kliknij Zapisz .
Teraz możesz użyć funkcji niestandardowej.
Pobieram funkcję niestandardową z Google Workspace Marketplace
Google Workspace Marketplace udostępnia kilka niestandardowych funkcji jako dodatki do Arkuszy Google. Aby używać tych dodatków lub poznać ich możliwości:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- U góry kliknij Dodatki > Pobierz dodatki.
- Po otwarciu narzędzia Google Workspace Marketplace kliknij pole wyszukiwania w prawym górnym rogu.
- Wpisz „funkcja niestandardowa” i naciśnij Enter.
- Jeśli znajdziesz dodatek funkcji niestandardowej, który Cię interesuje, kliknij Zainstaluj, aby go zainstalować.
- W oknie dialogowym może pojawić się informacja, że dodatek wymaga autoryzacji. Jeśli tak, przeczytaj uważnie informacje i 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żywać, i kliknij Opcje > Użyj w tym dokumencie.
Korzystanie z funkcji niestandardowej
Po napisaniu funkcji niestandardowej lub zainstalowaniu jej z poziomuGoogle Workspace Marketplaceobsługa tej funkcji jest równie łatwa w użyciu jak funkcja wbudowana:
- Kliknij komórkę, w której chcesz użyć danej funkcji.
- Wpisz znak równości (
=), a po nim nazwę funkcji i dowolną wartość wejściową, na przykład=DOUBLE(A1), a następnie naciśnij Enter. - W komórce przez chwilę wyświetli się wartość
Loading..., a następnie wynik.
Wytyczne dotyczące funkcji niestandardowych
Zanim utworzysz własną funkcję niestandardową, musisz pamiętać o kilku wskazówkach.
Nazwa
Oprócz standardowych konwencji nazewnictwa funkcji JavaScript pamiętaj o tych kwestiach:
- 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óry oznacza funkcję prywatną w Apps Script. - Nazwa funkcji niestandardowej musi być zadeklarowana ze składnią
function myFunction(), a nievar myFunction = new Function(). - Wielkość liter nie ma znaczenia, chociaż nazwy funkcji arkusza kalkulacyjnego zwykle są pisane wielkimi literami.
Argumenty
Podobnie jak funkcja wbudowana, funkcja niestandardowa może przyjmować argumenty jako wartości wejściowe:
- Jeśli wywołasz swoją funkcję jako argument (np.
=DOUBLE(A1)do jednej komórki), to argumentem będzie wartość komórki. Jeśli wywołasz funkcję jako argument (np.
=DOUBLE(A1:B10)), będzie to dwuwymiarowa tablica wartości komórek. Na przykład na zrzucie ekranu poniżej argumenty w polu=DOUBLE(A1:B2)są interpretowane przez Apps Script jakodouble([[1,3],[2,4]]). Pamiętaj, że przykładowy kod dla stronyDOUBLEpowyższej trzeba będzie zmodyfikować, aby 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, takie jak
NOW()lubRAND(), nie są dozwolone jako argumenty funkcji niestandardowej. Jeśli funkcja niestandardowa spróbuje zwrócić wartość na podstawie jednej z tych zmiennych wbudowanych, bezterminowo wyświetli wartośćLoading....
Zwracane wartości
Każda funkcja niestandardowa musi zwracać do wyświetlenia wartość, na przykład:
- Jeśli funkcja niestandardowa zwraca wartość, jest ona wyświetlana w komórce, z której funkcja została wywołana.
- Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości te trafiają do sąsiednich komórek, dopóki są one puste. Jeśli spowoduje to zastąpienie zawartości komórki przez tablica, funkcja niestandardowa zgłosi błąd. Przykład znajdziesz w sekcji poświęconej optymalizacji funkcji niestandardowych.
- Funkcja niestandardowa nie może mieć wpływu na komórki inne niż te, do których zwraca wartość. Inaczej mówiąc, funkcja niestandardowa nie może edytować dowolnych komórek, a jedynie komórki, z których została wywołana, i komórki sąsiadujące z nią. Jeśli chcesz edytować wybrane komórki, użyj menu niestandardowego, aby uruchomić funkcję.
- 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. Jeśli te wartości są używane w funkcjach niestandardowych, Apps Script traktuje je jako odpowiedni typ danych w języku JavaScript. Oto najczęstsze pytania:
- Daty i godziny w Arkuszach stają się obiektami Date w Apps Script. Jeśli w arkuszu kalkulacyjnym i w skrypcie są różne strefy czasowe (rzadki problem), funkcja niestandardowa będzie musiała to zrekompensować.
- Wartości czasu trwania w Arkuszach również 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%w Apps Script zmieni się w0.1.
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 zobaczysz listę 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, 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;
}
Zaawansowany
Korzystanie z usług Google Apps Script
Funkcje niestandardowe mogą wywoływać określone usługi Google Apps Script, aby wykonywać bardziej złożone zadania. Funkcja niestandardowa może na przykład wywoływać usługę Język, aby przetłumaczyć zwrot z języka angielskiego 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. Oznacza to, że mogą oni dzwonić tylko do tych usług, 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 przypadku funkcji niestandardowych |
| w kodzie HTML, | Może generować kod HTML, ale nie może go wyświetlić (rzadko przydatny). |
| JDBC | |
| Język | |
| Zablokuj | Działa, ale nie jest szczególnie przydatny w przypadku funkcji niestandardowych |
| Mapy | Może obliczać wskazówki dojazdu, 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()
lub SpreadsheetApp.openByUrl()). |
| Pobieranie adresu URL | |
| Narzędzia | |
| XML |
Jeśli funkcja niestandardowa generuje komunikat o błędzie You do not have permission to
call X service., usługa wymaga autoryzacji użytkownika i dlatego nie może być używana w funkcji niestandardowej.
Jeśli chcesz używać usługi innej niż wymienione powyżej, utwórz menu niestandardowe, w którym uruchomi się funkcja Apps Script, zamiast tworzyć funkcję niestandardową. Funkcja wywoływana z menu w razie potrzeby poprosi użytkownika o autoryzację, dzięki czemu będzie mogła korzystać ze wszystkich usług Apps Script.
Udostępnianie
Funkcje niestandardowe są na początku 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 do edytora skryptów innego arkusza kalkulacyjnego.
- Utwórz kopię arkusza kalkulacyjnego zawierającego tę funkcję, 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ć skrypt. (Współpracownicy z uprawnieniami tylko do wyświetlania nie mogą otworzyć edytora skryptów w oryginalnym arkuszu kalkulacyjnym. ale gdy wykona kopię, stanie się jej właścicielem i będzie mieć dostęp do skryptu).
- Opublikuj skrypt jako dodatek do Edytora do Arkuszy Google.
Optymalizacja
Za każdym razem, gdy w arkuszu kalkulacyjnym jest używana funkcja niestandardowa, Arkusze Google wysyłają osobne wywołanie do serwera Apps Script. Jeśli arkusz kalkulacyjny zawiera dziesiątki (setek czy tysiące!) wywołań funkcji niestandardowych, ten proces może być dość powolny.
Dlatego, jeśli zamierzasz wielokrotnie korzystać z funkcji niestandardowej na dużym zakresie danych, możesz ją zmodyfikować tak, aby przyjmowała zakres jako dane wejściowe w postaci dwuwymiarowej tablicy, a potem zwraca dwuwymiarową tablicę, która może wychodzić do odpowiednich komórek.
Na przykład podaną powyżej funkcję DOUBLE() można napisać ponownie, aby akceptował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;
}
Powyższa metoda korzysta z metody map obiektu Array JavaScriptu, aby rekurencyjnie wywoływać DOUBLE dla każdej wartości w dwuwymiarowej tablicy komórek. Zwraca dwuwymiarową tablicę zawierającą wyniki. W ten sposób możesz wywołać funkcję DOUBLE tylko raz, ale obliczyć jej wartość dla wielu komórek jednocześnie, jak widać na zrzucie ekranu poniżej. (To samo możesz uzyskać, używając zagnieżdżonych instrukcji if zamiast wywołania map).
Poniższa funkcja niestandardowa skutecznie pobiera opublikowane treści z internetu i używa dwuwymiarowej tablicy do wyświetlania 2 kolumn z wynikami po jednym wywołaniu funkcji. Gdyby każda komórka wymagała własnego wywołania funkcji, operacja trwałaby znacznie dłużej, ponieważ serwer Apps Script za każdym razem musiał 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;
}
Te techniki można zastosować do niemal każdej funkcji niestandardowej, której używa się wielokrotnie w arkuszu kalkulacyjnym, ale szczegóły implementacji będą się różnić w zależności od działania funkcji.