Arkusze Google mają setki wbudowanych funkcji, takich jak AVERAGE, SUM i VLOOKUP. Jeśli masz za mało potrzeb, możesz skorzystać z Google Apps Script, aby napisać funkcje niestandardowe, np. przekształcić metry na mile lub pobrać treści na żywo z internetu, a następnie używać ich w Arkuszach Google tak samo jak wbudowana funkcja.
Pierwsze kroki
Funkcje niestandardowe tworzy się przy użyciu standardowego kodu JavaScript. Jeśli nie masz doświadczenia z JavaScriptem, Codecademy oferuje świetny kurs dla początkujących. (Uwaga: kurs nie został opracowany przez Google i nie jest powiązany z Google).
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 wiesz, jak pisać JavaScript i nie masz czasu na naukę, sprawdź sklep z dodatkami, by dowiedzieć się, czy ktoś inny utworzył już wymaganą funkcję niestandardową.
Tworzenie funkcji niestandardowej
Aby napisać funkcję niestandardową:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- Wybierz pozycję menu Rozszerzenia > Apps Script.
- Usuń dowolny kod w edytorze skryptów. W przypadku powyższej funkcji
DOUBLEwystarczy skopiować kod i wkleić go w edytorze skryptów. - U góry kliknij Zapisz .
Teraz możesz używać funkcji niestandardowej.
Uzyskiwanie funkcji niestandardowej z Google Workspace Marketplace
Google Workspace Marketplace Zapewnia kilka niestandardowych funkcji dostępnych jako dodatki do Arkuszy Google. Aby korzystać z tych dodatków:
- Utwórz lub otwórz arkusz kalkulacyjny w Arkuszach Google.
- U góry kliknij Dodatki > Pobierz dodatki.
- Gdy otworzy się Google Workspace Marketplace, kliknij pole wyszukiwania w prawym górnym rogu.
- Wpisz „funkcja niestandardowa” i naciśnij Enter.
- Jeśli znajdziesz dodatek niestandardowy, który Cię interesuje, kliknij Zainstaluj, aby go zainstalować.
- W oknie dialogowym może się pojawić informacja, ż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 ten arkusz i u góry kliknij Dodatki > Zarządzaj dodatkami. Znajdź dodatek, którego chcesz używać, i kliknij Opcje > Użyj tylko w tym dokumencie.
Korzystanie z funkcji niestandardowej
Po napisaniu własnej funkcji lub zainstalowaniuGoogle Workspace Marketplacemożesz z łatwością korzystać z funkcji wbudowanej:
- Kliknij komórkę, w której chcesz użyć funkcji.
- 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. - W komórce pojawi się komunikat
Loading..., a następnie wynik zostanie zwrócony.
Wytyczne dotyczące funkcji niestandardowych
Zanim napiszesz własną funkcję niestandardową, zapoznaj się z tymi wskazówkami.
Nazwa
Oprócz standardowych konwencji nazewnictwa funkcji JavaScriptu pamiętaj o tych kwestiach:
- 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. - Nazwa funkcji niestandardowej musi być podana w składni
function myFunction(), a nievar myFunction = new Function(). - Wielkość liter nie ma znaczenia, ale nazwy funkcji arkusza kalkulacyjnego są 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 argumentem (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 tablicą 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 kodDOUBLEod góry należy zmodyfikować, aby akceptował tablicę jako dane wejściowe.
Argumenty funkcji niestandardowej muszą być deterministyczne. Oznacza to, że wbudowane funkcje arkusza kalkulacyjnego, które zwracają inny wynik przy każdym obliczeniu, np.
NOW()lubRAND(), nie są dozwolone jako argumenty funkcji niestandardowej. Jeśli funkcja niestandardowa próbuje zwrócić wartość na podstawie jednej z tych wbudowanych funkcji zmiennych, będzie wyświetlać ją bez określonej datyLoading....
Zwracane wartości
Każda funkcja niestandardowa musi zwracać wartość do wyświetlenia, na przykład:
- Jeśli funkcja niestandardowa zwraca wartość, wyświetli się w komórce, z której funkcja została wywołana.
- Jeśli funkcja niestandardowa zwraca dwuwymiarową tablicę wartości, wartości przylegają do sąsiednich komórek, o ile są one puste. Jeśli spowodowałoby to zastąpienie tablicy przez istniejącą zawartość komórki, funkcja niestandardowa zwróci błąd. Przykład znajdziesz w sekcji poświęconej optymalizacji funkcji niestandardowych.
- Funkcja niestandardowa nie może wpływać na komórki inne niż te, dla których zwraca wartość. Oznacza to, że funkcja niestandardowa nie może edytować komórek arbitralnych, wyłącznie komórek, z których korzysta, i sąsiednich komórek. Aby edytować komórki dowolne, uruchom funkcję za pomocą menu niestandardowego.
- Wywołanie funkcji niestandardowej musi zostać zwrócone w ciągu 30 sekund. W przeciwnym razie 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. Gdy 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 data w Apps Script. Jeśli arkusz kalkulacyjny i skrypt korzystają z różnych stref czasowych (rzadki problem), funkcja niestandardowa musi skompensować ten błąd.
- Wartości czasu trwania w Arkuszach także 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 w Apps Script komórka o wartości
10%staje się0.1.
Autouzupełnianie
Arkusze Google obsługują autouzupełnianie w przypadku funkcji niestandardowych tak jak wbudowane funkcje. 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 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 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ę Language, by tłumaczyć angielskie wyrażenie na hiszpański.
W przeciwieństwie do większości innych skryptów Apps Script funkcje niestandardowe nigdy nie proszą użytkowników o autoryzację dostępu do danych osobowych. W rezultacie mogą dzwonić tylko w przypadku 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 sprawdza się w przypadku funkcji niestandardowych |
| w kodzie HTML, | Może generować kod HTML, ale nie może go wyświetlać (rzadko przydatny) |
| JDBC | |
| Język | |
| Zablokuj | Działa, ale nie sprawdza się w przypadku funkcji niestandardowych |
| Mapy | Może wyznaczyć 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żytkownika w funkcji niestandardowej. |
| Arkusz kalkulacyjny | Tylko do odczytu (może używać większości metod get*(), ale nie set*()).Nie można otworzyć 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, dlatego nie można jej używać w funkcji niestandardowej.
Aby użyć 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 kalkulacyjnym, 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 innych arkuszy.
- Utwórz kopię arkusza kalkulacyjnego zawierającego funkcję niestandardową, klikając Plik > Utwórz kopię. Podczas kopiowania arkusza kalkulacyjnego wysyłane są również wszystkie dołączone do niego skrypty. Każdy, kto ma dostęp do arkusza kalkulacyjnego, może skopiować skrypt. (Współtwórcy, którzy mają tylko uprawnienia do wyświetlania, nie mogą otwierać edytora skryptów w oryginalnym arkuszu kalkulacyjnym. Kopia jest jednak ich własnością i może zobaczyć skrypt).
- Opublikuj skrypt jako dodatek do edytora Arkuszy Google.
Optymalizacja
Za każdym razem, gdy w arkuszu kalkulacyjnym jest używana funkcja niestandardowa, Arkusze Google wykonują osobne wywołanie serwera Apps Script. Jeśli arkusz kalkulacyjny zawiera dziesiątki (lub setki tysięcy) wywołań funkcji niestandardowych, ten proces może być dość wolny.
Dlatego jeśli planujesz wielokrotnie używać funkcji niestandardowej w dużym zakresie danych, możesz ją zmodyfikować w taki sposób, aby akceptował zakres danych w postaci tablicy dwuwymiarowej, a następnie zwracałby dwuwymiarowy tablica, który mógłby przemieścić się w odpowiednich komórkach.
Na przykład funkcja DOUBLE() opisana powyżej może zostać przepisana w taki sposób, 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;
}
Powyższe podejście wykorzystuje metodę map obiektu JavaScript Array do rekurencyjnego wywoływania DOUBLE każdej wartości w dwuwymiarowej tablicy komórek. Zwraca dwuwymiarową tablicę zawierającą wyniki. Dzięki temu możesz wywoływać funkcję DOUBLE tylko raz, ale obliczać ją dla dużej liczby komórek jednocześnie (jak widać na zrzucie ekranu poniżej). (Możesz to zrobić w przypadku zagnieżdżonych instrukcji if zamiast wywołania map).
Analogicznie funkcja poniżej poniżej pobiera treści na żywo z internetu i używa dwuwymiarowej tablicy do wyświetlania 2 kolumn wyników za pomocą jednego wywołania funkcji. Jeśli każda komórka wymaga własnego wywołania funkcji, operacja będzie trwała znacznie dłużej, ponieważ serwer Apps Script będzie musiał za każdym razem pobierać i analizować plik 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 prawie każdej funkcji niestandardowej używanej w arkuszu kalkulacyjnym, ale szczegóły implementacji będą się różnić w zależności od ich działania.