Podstawy Apps Script w Arkuszach Google – część 1: makra i funkcje niestandardowe

1. Witamy w Apps Script

Co to jest Apps Script?

Apps Script to platforma do szybkiego tworzenia aplikacji, która umożliwia automatyzację, dostosowywanie i rozszerzanie możliwości Google Workspace. Dzięki Apps Script możesz oszczędzać czas i wysiłek, usprawniając uciążliwe lub złożone zadania w Google Workspace.

Funkcje Apps Script obejmują:

• Wbudowane usługi Apps Script umożliwiają odczytywanie, aktualizowanie i manipulowanie danymi aplikacji Google Workspace za pomocą skryptów.
• Skrypty możesz tworzyć w edytorze kodu w przeglądarce Apps Script. Nie musisz instalować ani uruchamiać oprogramowania do tworzenia kodu.
• Możesz projektować interfejsy użytkownika dla edytorów Google Workspace, które umożliwiają aktywowanie skryptów bezpośrednio z tych edytorów za pomocą elementów menu, okien dialogowych i pasków bocznych.

Ta playlista z ćwiczeniami z podstaw Apps Script w Arkuszach Google zawiera podstawowe informacje o Apps Script i sposobach jego wykorzystania do ulepszania Arkuszy Google. Ten moduł Codelab skupia się na nauce podstaw Apps Script.

Usługa Arkusze

Za pomocą Apps Script możesz rozszerzać funkcje Arkuszy Google, aby oszczędzać czas i wysiłek. Apps Script udostępnia usługę Arkusze, która umożliwia skryptom interakcję z plikami Arkuszy Google i zawartymi w nich danymi. Za pomocą tej usługi możesz zautomatyzować te typowe zadania związane z arkuszami kalkulacyjnymi:

• Utwórz lub zmodyfikuj arkusz kalkulacyjny.
• odczytywać i aktualizować dane, formuły i formatowanie komórek;
• Tworzenie niestandardowych przycisków i menu.
• importować i eksportować dane z innych aplikacji Google lub źródeł zewnętrznych;
• Udostępniaj arkusze kalkulacyjne i kontroluj dostęp do nich.

Czego się nauczysz

Ta playlista zawiera wszystkie tematy, które musisz poznać, aby zacząć korzystać z Apps Script w Arkuszach Google:

1. Makra i funkcje niestandardowe
2. Arkusze kalkulacyjne, arkusze i zakresy
3. Praca z danymi
4. Formatowanie danych
5. Tworzenie wykresów i prezentacji danych w Prezentacjach

Te ćwiczenia z programowania należy czytać w kolejności, więc zacznij od tego i wykonuj je po kolei, aby jak najlepiej wykorzystać możliwości nauki.

Przejdź do następnej sekcji, aby dowiedzieć się więcej o zawartości tego laboratorium.

2. Wprowadzenie

Witamy w pierwszym laboratorium z tej playlisty. Z tego laboratorium dowiesz się, jak używać Apps Script w Arkuszach Google. W tym Codelabie skupimy się na 2 kluczowych pojęciach: makrach i funkcjach niestandardowych.

Makro to seria zarejestrowanych działań w Arkuszach Google. Po nagraniu makra możesz je aktywować, aby później powtórzyć te działania za pomocą elementu menu lub skrótu klawiszowego. Własne makra możesz tworzyć i aktualizować zarówno w Arkuszach Google, jak i w edytorze kodu Apps Script.

W edytorze kodu Apps Script możesz też tworzyć funkcje niestandardowe. Podobnie jak w przypadku wbudowanych funkcji Arkuszy (np. SUM lub AVERAGE) możesz używać Apps Script do pisania własnych funkcji niestandardowych do prostych i niszowych operacji (takich jak konwersje lub łączenie ciągów znaków). Po utworzeniu możesz wywoływać te funkcje w Arkuszach tak samo jak funkcje wbudowane. Funkcje niestandardowe można też stosować w pisanych przez siebie formułach komórek, łącząc je w razie potrzeby z innymi funkcjami.

Poniżej znajdziesz informacje o koncepcjach i wymaganiach związanych z tym kursem.

Czego się nauczysz

• Jak utworzyć skrypt do Arkuszy Google
• Jak poruszać się po edytorze Apps Script.
• Jak tworzyć i aktualizować makra.
• Jak utworzyć pierwszą funkcję niestandardową w Arkuszach.

Czego potrzebujesz

• Podstawowa znajomość JavaScript
• Podstawowa znajomość Arkuszy Google.
• Możliwość odczytywania arkuszy w notacji A1.

Uwaga: Apps Script jest oparty na języku JavaScript. Jeśli dopiero zaczynasz przygodę z JavaScriptem, na platformie Codecademy znajdziesz bezpłatny kurs Wprowadzenie do JavaScriptu. Ten kurs nie został opracowany przez Google, ale powinien zawierać wszystkie potrzebne informacje.

To już koniec wprowadzenia. Aby rozpocząć pracę z makrami, przejdź do następnej sekcji.

3. Tworzenie makra w Arkuszach

Podczas pracy z arkuszami kalkulacyjnymi często wykonujesz powtarzalne czynności, takie jak kopiowanie wartości komórek, formatowanie czy tworzenie formuł. Może to być nużące i prowadzić do błędów. Aby zautomatyzować powtarzalne działania, Arkusze Google udostępniają makra. Makra umożliwiają „nagrywanie” serii działań w arkuszu. Za pomocą nagranego makra możesz powtarzać te same działania w innych miejscach arkusza kalkulacyjnego, naciskając prosty skrót klawiszowy.

Z tej sekcji dowiesz się, jak utworzyć makro w Arkuszach. W następnej sekcji dowiesz się, jak tworzyć makra za pomocą Apps Script.

Zanim zaczniesz

Zanim przejdziesz dalej, musisz mieć arkusz kalkulacyjny z danymi. Przygotowaliśmy dla Ciebie arkusz danych: kliknij ten link, aby skopiować arkusz danych, a następnie kliknij Utwórz kopię.

Kopia przykładowego arkusza do użycia zostanie umieszczona w folderze na Dysku Google i będzie nosić nazwę „Kopia 10 najbardziej dochodowych filmów (2018)”.

Tworzenie makra

Gdy masz już arkusz kalkulacyjny, możesz nagrać makro w Arkuszach Google. W tym przykładzie utworzysz makro, które sformatuje wiersz nagłówka danych. w ten sposób:

1. Kliknij komórkę A1, aby umieścić kursor w wierszu. To jest wiersz nagłówka.
2. W menu wybierz Rozszerzenia> Makra> Nagrywaj makra.

Po rozpoczęciu nagrywania Arkusze Google zapamiętują wszystkie działania wykonywane w arkuszu kalkulacyjnym: wyróżnianie komórek, dodawanie danych, przełączanie się między arkuszami, formatowanie itp. Te działania staną się „skryptem”, który będzie powtarzany po zapisaniu i późniejszym aktywowaniu makra.

3. W oknie Makro wybierz Odwołanie względne.

Kluczowe pojęcia: makra mogą używać w Arkuszach dwóch typów odwołań do konkretnych komórek. Odwołania bezwzględne są przypisane do dokładnych zarejestrowanych lokalizacji (np. komórka A5), a odwołania względne są stosowane przy użyciu bieżącego zaznaczenia użytkownika jako punktu wyjścia (np. czwarta komórka w dół od bieżącego zaznaczenia).

W tym laboratorium kodu musisz używać odwołań względnych do makr.

4. Wybierz wiersz 1.

5. Zmień kolor wypełnienia górnego wiersza z białego na ciemny magenta 3.

6. Zmień kolor tekstu w górnym wierszu z czarnego na biały.

7. Aby pogrubić tekst, naciśnij Ctrl+B (lub Cmd+B w systemie macOS).
8. Aby zablokować górny wiersz, kliknij Widok > Zablokuj > 1 wiersz.

9. W oknie makra kliknij Zapisz. Pojawi się nowe okno z prośbą o nadanie nazwy makru. Wpisz nazwę „Nagłówek” i kliknij Zapisz.

W interfejsie Arkuszy utworzono makro specjalizujące się w formatowaniu nagłówków.

Aktywowanie makra

Aby zastosować nowe makro w Arkuszach, wykonaj te czynności:

1. Aby utworzyć arkusz, kliknij Dodaj arkusz .

2. W nowym arkuszu dodaj tekst do komórek A1:C2. Możesz skorzystać z przykładowych danych wejściowych poniżej:

3. Zaznacz pierwszy wiersz.

4. Aby zastosować makro do wybranego obszaru, kliknij Rozszerzenia> Makra> Nagłówek.
5. Autoryzuj makro, postępując zgodnie z instrukcjami wyświetlanymi na ekranie.

Uwaga: jeśli używasz konta gmail.com, przy pierwszym użyciu skryptu może się też pojawić okno z informacją o niezweryfikowanej aplikacji. Google używa tej informacji, aby ostrzegać użytkowników, którzy mogą korzystać z kodu pochodzącego od nieznanych lub niezaufanych autorów. Jeśli widzisz to okno, możesz kontynuować, ponieważ jesteś autorem skryptu. Aby kontynuować autoryzację skryptu, postępuj zgodnie z instrukcjami wyświetlanymi na ekranie. Proces uzyskiwania tego uprawnienia nazywa się autoryzacją.

6. Powtórz krok 4, aby ponownie uruchomić makro (autoryzacja zatrzymuje pierwsze wykonanie).

Gratulacje! Wiesz już, jak stosować makra w Arkuszach. Arkusz kalkulacyjny powinien wyglądać tak:

Makra umożliwiają wydajne tworzenie arkuszy kalkulacyjnych, a w następnej części tego samouczka dowiesz się, jak zwiększyć ich możliwości. Oto sekret: podczas nagrywania makra tak naprawdę piszesz kod Apps Script. Arkusz kalkulacyjny tworzy kod pasujący do działań makra. W następnej sekcji dowiesz się, jak modyfikować kod bezpośrednio w edytorze Apps Script w przeglądarce.

4. Makra w edytorze skryptów

Podczas tworzenia makra Arkusze Google zapisują Twoje działania jako funkcję Apps Script. Gdy aktywujesz makro, Arkusze Google wywołują funkcję Apps Script, aby zastosować te działania w tej samej kolejności.

Edytor skryptów

Po utworzeniu makra możesz sprawdzić jego kod. Aby wyświetlić skrypt makra, kliknij Rozszerzenia> Apps Script, aby otworzyć edytor kodu w przeglądarce dla Apps Script.

Edytor skryptów umożliwia pisanie kodu w Apps Script i uruchamianie tych skryptów na serwerach Google.

Uwaga: makra i skrypty tworzone w ramach tego kursu są dołączane do pliku Arkuszy Google, w którym działają, i można do nich w każdej chwili uzyskać dostęp z menu Arkuszy Rozszerzenia > Apps Script. Skrypty dołączone do arkusza kalkulacyjnego Google w ten sposób są nazywane powiązanymi z kontenerem.

Analiza macros.gs

Sprawdź bieżący skrypt. Arkusze utworzyły plik skryptu macros.gs podczas nagrywania makra Header, wypełniając go odpowiednią funkcją Apps Script o nazwie Header. Gdy aktywujesz makro Header, Arkusze uruchomią tę funkcję.

Zapoznaj się ze strukturą funkcji makra w Apps Script na poniższym obrazie. Jeśli nagrywasz kroki w innej kolejności lub klikasz w arkuszu kalkulacyjnym podczas nagrywania, kod może wyglądać nieco inaczej.

Pierwszy wiersz to komentarz adnotacji, który wpływa na autoryzację:

/** @OnlyCurrentDoc */

Większość skryptów prosi użytkownika o przyznanie uprawnień przed uruchomieniem. Te uprawnienia określają, na co użytkownik zezwala skryptowi. Jeśli w projekcie skryptu znajduje się komentarz @OnlyCurrentDoc, Apps Script prosi tylko o uprawnienia do uzyskiwania dostępu do bieżącego arkusza kalkulacyjnego i jego aktualizowania. Bez tego komentarza Apps Script poprosiłby o uprawnienia do uzyskiwania dostępu do wszystkich arkuszy kalkulacyjnych użytkownika i ich aktualizowania. Jeśli pracujesz tylko z jednym plikiem, zawsze warto dodać tę adnotację. Rejestrator makr automatycznie dodaje ten komentarz.

Aby dowiedzieć się, jak Apps Script reprezentuje instrukcje makra, możesz przyjrzeć się funkcji:

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

Ten kod jest uruchamiany po aktywowaniu makra Header. Po znaku function etykieta Header() określa nazwę funkcji i jej parametry. Pamiętaj, że funkcja Header() nie wymaga parametrów, ponieważ funkcje makr w Apps Script nie potrzebują danych wejściowych. Klamry zawsze obejmują treść funkcji w Apps Script.

W kolejnych modułach z tej playlisty znajdziesz wyjaśnienia dotyczące klas i pojęć związanych z tworzeniem makra. Na razie możesz zapoznać się z opisami kodu poniżej, aby uzyskać ogólne pojęcie o jego komponentach i ich roli w tworzeniu makra. Spójrz na pierwszy wiersz:

var spreadsheet = SpreadsheetApp.getActive();

W tym przypadku getActive() zwraca obiekt reprezentujący bieżący aktywny plik arkusza kalkulacyjnego w Arkuszach i przypisuje go do nowej zmiennej spreadsheet.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

Te linie odpowiadają czynności kliknięcia pierwszego wiersza, aby go wyróżnić. Nazywamy to aktywacją. Pierwszy wiersz zapisuje bieżący arkusz w zmiennej sheet, a drugi wiersz pobiera cały pierwszy wiersz za pomocą metody getRange(), a następnie wywołuje activate(), aby go aktywować. Pierwszy wiersz jest określany za pomocą numerów wierszy i kolumn. Wywołanie spreadsheet.getCurrentCell().getRow() zwraca numer bieżącego wiersza, a sheet.getMaxColumns() zwraca maksymalną liczbę kolumn w arkuszu.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

Ten fragment kodu staje się bardziej złożony. Aby skutecznie wywoływać metody za pomocą spreadsheet, kod umieszcza 3 metody w getActiveRangeList(), aby zapobiec wielokrotnemu wywoływaniu tej metody spreadsheet. W miarę pisania kolejnych skryptów w Apps Script będziesz coraz lepiej poznawać tę konwencję wywoływania wielu metod w jednej klasie (znaną też jako łączenie metod). Na razie możesz przeczytać krótkie wyjaśnienia każdej metody w bloku kodu:

• getActiveRangeList() zwraca bieżący aktywny RangeList w spreadsheet. W tym przypadku jest to po prostu pierwszy wiersz aktywowany przez poprzednią linię.
• Metody setBackground(color) i setFontColor(color) zmieniają atrybuty koloru komórek w aktywnym zakresie.
• setFontWeight(fontWeight) dostosowuje grubość czcionki w komórkach w aktywnym zakresie.

Ostatnia linia blokuje pierwszy wiersz makra:

spreadsheet.getActiveSheet().setFrozenRows(1);

To skrypt wygenerowany podczas nagrywania makra. Nie przejmuj się nieznanymi terminami ani metodami wymienionymi powyżej. Opis ma na celu zapoznanie Cię z niektórymi pomysłami, na których skupia się Apps Script w typowych funkcjach makr, oraz z tematami, które będą poruszane w przyszłych ćwiczeniach z programowania.

W następnej sekcji skupimy się na manipulowaniu kodem funkcji Header(), aby pokazać, jak za pomocą edytora skryptów można jeszcze bardziej spersonalizować makra.

Dostosowywanie makr za pomocą Apps Script

W edytorze Apps Script pojawi się makro utworzone wcześniej w Arkuszach Google. Dostosowując zawartość treści funkcji, możesz jeszcze bardziej dostosować instrukcje makra, aby wykonywało inne lub dodatkowe działania. Poniższe ćwiczenia pokazują różne sposoby manipulowania makrami za pomocą edytora skryptów.

Zmień komórki, których dotyczy problem

Załóżmy, że chcesz zmodyfikować makro, aby wpływało tylko na pierwsze 10 kolumn pierwszego wiersza, a nie na cały wiersz. Możesz usunąć makro i nagrać je ponownie. Jednak za pomocą edytora Apps Script możesz wprowadzać te zmiany bezpośrednio. Oto jeden ze sposobów:

1. W edytorze skryptów zastąp sheet.getMaxColumns() wartością 10. Ta zmiana wpłynie na zakres komórek, na które makro będzie miało wpływ w arkuszu kalkulacyjnym.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

2. Aby zapisać skrypt, kliknij Zapisz .
3. Aby zmienić nazwę projektu, wpisz „Makra i funkcje niestandardowe” jako nową nazwę projektu i kliknij Zmień nazwę.
4. Aby utworzyć arkusz, w Arkuszach kliknij Dodaj arkusz .

5. W edytorze skryptów na liście funkcji wybierz Header i kliknij Uruchom.

W nowym arkuszu powinien pojawić się ten wynik:

Po zmodyfikowaniu aktywnego lub docelowego zakresu makro będzie teraz wpływać tylko na część pierwszego wiersza. Wiele metod Apps Script przyjmuje jako parametr zakres lub notację A1, aby określić, na których komórkach mają być wykonywane działania.

Następnie dowiedz się, jak dostosować kolory makr.

Zmienianie kolorów makra

Aby ułatwić projektowanie schematu kolorów makr lub innych elementów w Arkuszach, Apps Script może modyfikować kolor wypełnienia lub tekstu zakresu. Aby dowiedzieć się, jak dostosować kolory makra, postępuj zgodnie z instrukcjami poniżej.

Te instrukcje dotyczą zmiany koloru tła makra:

1. W Arkuszach wróć do arkusza zawierającego oryginalne dane (Arkusz 1).
2. Kliknij pierwszy wiersz, aby go podświetlić.
3. W edytorze skryptów zastąp kolor tła #4c1130 kolorem #afeeee. Te wartości reprezentują różne kolory w notacji trójkowej szesnastkowej.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

4. Aby zapisać skrypt, kliknij Zapisz .
5. Na liście funkcji wybierz Header i kliknij Uruchom.

W Arkuszach kolor tła pierwszych 10 kolumn w pierwszym wierszu zmieni się na niestandardowy kolor turkusowy:

Zmieniając notację koloru szesnastkowego w parametrach setBackground(color) z #4c1130 (ciemny magenta 3) na #afeeee (jasny turkus, opcja niedostępna w domyślnym menu kolorów Arkuszy), zmieniasz atrybut koloru tła makra.

Kolory tła zostały zmienione przez makro. Jeśli chcesz też zmienić kolor tekstu, zmień drugi kod koloru.

1. W Arkuszach kliknij pierwszy wiersz, aby upewnić się, że jest nadal zaznaczony.
2. W edytorze skryptów zastąp kolor czcionki #ffffff wartością #191970. Spowoduje to ustawienie koloru czcionki na granatowy.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

3. Aby zapisać skrypt, kliknij Zapisz .
4. Na liście funkcji wybierz Header i kliknij Uruchom.

Wróć do Arkuszy, aby zobaczyć, że kolor tekstu w wierszu nagłówka jest teraz granatowy.

Już wiesz, że makra to w zasadzie działania w Arkuszach zapisane jako kod Apps Script. W następnej sekcji znajdziesz kolejny sposób, w jaki Apps Script może Ci pomóc w pracy z Arkuszami Google: funkcje niestandardowe.

5. Napisz pierwszy skrypt: funkcje niestandardowe

Podobnie jak większość aplikacji do obsługi arkuszy kalkulacyjnych Arkusze Google mają kilka wbudowanych funkcji formuł, np. =SUM(), które umożliwiają szybkie obliczenia na danych w arkuszu kalkulacyjnym. Funkcje niestandardowe to po prostu funkcje, które określasz za pomocą Apps Script. Po zdefiniowaniu funkcji niestandardowej możesz jej używać w dowolnym miejscu arkusza kalkulacyjnego, tak jak funkcji wbudowanej.

W tej sekcji dowiesz się, jak utworzyć w Apps Script funkcję niestandardową, która będzie przeliczać waluty.

Tworzenie pliku skryptu

Korzystając z tego samego arkusza kalkulacyjnego i projektu skryptu z sekcji Makra, wykonaj te instrukcje, aby dowiedzieć się, jak utworzyć nowy skrypt (którego możesz później użyć do utworzenia pierwszej funkcji niestandardowej):

1. Aby utworzyć plik Apps Script, wróć do edytora skryptów.
2. Obok pozycji Pliki kliknij Dodaj plik  > Skrypt.
3. Nazwij nowy plik skryptu customFunctions i naciśnij Enter. (Apps Script automatycznie dodaje do nazwy pliku skryptu rozszerzenie .gs).

W edytorze pojawi się nowa karta o nazwie customFunctions.gs.

Teraz, gdy masz już skrypt przeznaczony specjalnie do funkcji niestandardowych, możesz wypełnić go kodem.

Przeliczanie dolarów amerykańskich na franki szwajcarskie

Załóżmy, że chcesz zmodyfikować dane dotyczące „10 najbardziej dochodowych filmów 2018 roku”, aby wyświetlać nie tylko wartości przychodów na całym świecie w dolarach amerykańskich, ale także we frankach szwajcarskich. Dzięki funkcjom niestandardowym możesz to łatwo zrobić. W tym ćwiczeniu pokazujemy, jak utworzyć funkcję niestandardową, która będzie matematycznie przekształcać wartości w dolarach na wartości we frankach.

Zanim napiszesz pierwszą funkcję niestandardową, zmodyfikuj zbiór danych, aby funkcja mogła wyświetlać prawidłowe dane wyjściowe. Aby to zrobić:

1. W Arkuszach kliknij prawym przyciskiem myszy kolumnę H.
2. W menu, które się pojawi, kliknij Wstaw 1 z prawej.

3. Oznacz kolumnę „Worldwide_Gross (Swiss francs)” w komórce I1.

Masz teraz kolumnę, w której możesz przechowywać wyniki niestandardowej funkcji konwersji. Następnie możesz użyć edytora skryptów, aby utworzyć pierwszą funkcję niestandardową.

4. W pliku customFunctions.gs zastąp kod dla myFunction() tym kodem:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Ten kod przeliczy dolary amerykańskie na franki szwajcarskie. Wypróbuj instrukcje poniżej i zobacz, jak uruchomić funkcję niestandardową w arkuszach.

5. Aby zapisać skrypt, kliknij Zapisz .
6. W Arkuszach wybierz komórkę I2.
7. Na pasku funkcji wpisz =USDTOCHF(H2).

Aby zastosować formułę do pozostałych komórek w kolumnie:

8. Przesuń kursor do prawego dolnego rogu komórki I2 i kliknij mały niebieski kwadracik (gdy kursor będzie wskazywać kwadracik, zmieni się w ).
9. Przeciągnij niebieskie pole w dół, aby zaznaczyć zakres I3:I11.

W kolumnie I znajdują się teraz wartości w frankach szwajcarskich odpowiadające wartościom w dolarach amerykańskich w kolumnie H.

Gratulacje, udało Ci się utworzyć pierwszą funkcję niestandardową. W następnej sekcji wyjaśniamy kod, z którego składa się USDTOCHF().

Analiza USDTOCHF()

Początkowe komentarze opisują cel kodu:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

Bloki komentarzy takie jak ten są często używane w programowaniu do wyjaśniania działania funkcji.

W tym komentarzu możesz wyróżnić 2 części: opis funkcji (przeliczanie dolarów na franki) i adnotacje opisujące parametry funkcji i typ zwracanej wartości.

Dzięki adnotacjom Apps Script korzysta z JSDoc, aby ułatwić dokumentowanie kodu i tworzenie podpowiedzi autouzupełniania. Poniżej znajdziesz informacje o tym, jak poszczególne adnotacje używane w USDTOCHF() pomagają w tworzeniu skryptów Google Apps Script:

• @param: możesz użyć adnotacji @param, aby opisać każdy parametr przekazywany do funkcji.
• @return: Możesz użyć adnotacji @return, aby opisać, co zwraca funkcja.
• @customfunction: w komentarzu do dokumentu każdej funkcji niestandardowej należy zawsze dodać @customfunction. Ta adnotacja informuje Arkusze, że mają autouzupełniać Twoją funkcję niestandardową tak samo, jak autouzupełniają wbudowane funkcje, gdy wpisujesz nazwę funkcji w komórce, jak widać poniżej:

Zwróć uwagę, że tekst wyświetlany w wyskakującym okienku autouzupełniania jest dokładnie taki sam jak tekst opisu umieszczony w bloku komentarza. Aby ułatwić korzystanie z funkcji niestandardowych, zadbaj o to, aby tworzone przez Ciebie opisy były dobrze napisane i wyczerpujące.

Następnie skup się na kodzie w funkcji USDTOCHF():

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Jak wspomnieliśmy wcześniej, funkcja USDTOCHF() pobiera zmienną liczbową dollars, mnoży ją przez stały kurs wymiany i zwraca wartość przekonwertowaną na franki szwajcarskie w zmiennej liczbowej swissFrancs. Parametr wejściowy to wartość znajdująca się w komórce określonej podczas dodawania funkcji niestandardowej do komórki. W tym przykładzie kwoty wejściowe w dolarach pochodzą z kolumny H. Wartość wyjściowa swissFrancs jest umieszczana w komórce funkcji (w tym przykładzie w kolumnie I).

Funkcje niestandardowe mogą działać z wartościami liczbowymi lub tekstowymi, co zobaczysz w następnej sekcji.

Łączenie prefiksu ciągu znaków

Załóżmy, że chcesz, aby wynik liczbowy funkcji USDTOCHF() zawierał prefiks franka szwajcarskiego CHF. Możesz to zrobić za pomocą Apps Script, używając operatora łączenia (+),, jak pokazano w tych instrukcjach:

1. W edytorze skryptów zmień adnotację @return, aby zwracała ciąg znaków zamiast liczby.
2. Zmień return swissFrancs na return 'CHF' + swissFrancs.

Operator + dodaje ciąg znaków CHF na początku wartości zawartej w swissFrancs. Kod powinien teraz wyglądać tak:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

3. Aby zapisać skrypt, kliknij Zapisz .

Ciąg znaków franka szwajcarskiego jest teraz przedrostkiem wartości w kolumnie I:

Funkcja niestandardowa nie tylko przelicza dolary amerykańskie na franki szwajcarskie, ale też wyświetla walutę z prefiksem tekstowym.

Zaawansowane: pobieranie danych zewnętrznych

To dobry początek w przypadku podstawowej funkcji niestandardowej, ale ten przykład zakłada, że kurs wymiany dolarów na franki szwajcarskie jest stały. Załóżmy, że chcesz używać bieżącego kursu wymiany, aby po każdym ponownym załadowaniu arkusza wartości były przeliczane na podstawie aktualnego kursu. Aby to zrobić, musisz mieć możliwość sprawdzenia aktualnego kursu wymiany. Nie jest to informacja łatwo dostępna w Arkuszach Google, ale na szczęście możesz ją uzyskać za pomocą Apps Script.

Aby uzyskać aktualny kurs wymiany franka szwajcarskiego na dolara amerykańskiego, możesz użyć kodu podobnego do tego:

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

Ten kod pobiera aktualny kurs wymiany z serwera informacji finansowych za pomocą interfejsu API kursu wymiany firmy zewnętrznej. Możesz to zrobić za pomocą usług Apps Script, takich jak UrlFetchApp i CacheService. Te zaawansowane koncepcje wykraczają poza zakres tego konkretnego laboratorium, ale możesz zacząć dostrzegać wszechstronność Apps Script w automatyzacji złożonych zadań w Arkuszach Google.

Wytyczne dotyczące funkcji niestandardowych

Gratulujemy ukończenia ćwiczeń dotyczących funkcji niestandardowych. Podczas korzystania z funkcji niestandardowych w projektach należy pamiętać, że mają one pewne ograniczenia. Poniżej znajdziesz podsumowanie ograniczeń opisanych w przewodniku Funkcje niestandardowe w Arkuszach Google:

• Nie twórz funkcji niestandardowych, które wymagają autoryzacji użytkownika. Zamiast tego twórz własne funkcje do wykonywania prostszych zadań, takich jak obliczenia na podstawie przykładowych danych, edytowanie tekstu itp. Więcej informacji znajdziesz w artykule Korzystanie z usług Apps Script.
• Nie nadawaj funkcji niestandardowej nazwy takiej samej jak inna wbudowana funkcja ani nie kończ nazwy podkreśleniem. Zapoznaj się z wytycznymi dotyczącymi nazw.
• Nie przekazuj argumentów zmiennych do funkcji niestandardowych. Do funkcji niestandardowych możesz przekazywać jako argumenty tylko wartości deterministyczne (stałe). Przekazywanie argumentów zmiennych, np. wyniku funkcji =RAND(), spowoduje nieprawidłowe działanie funkcji niestandardowej. Zapoznaj się z wytycznymi dotyczącymi argumentów.
• Nie twórz funkcji, których wykonanie trwa dłużej niż 30 sekund. Jeśli potrwa to dłużej, wystąpi błąd, dlatego kod funkcji powinien być prosty i mieć ograniczony zakres. Obliczenia wykonywane w funkcjach niestandardowych powinny być jak najprostsze. Zapoznaj się z wytycznymi dotyczącymi zwracanych wartości.

Możesz teraz ulepszać arkusze kalkulacyjne, korzystając z edytora skryptów do pracy z makrami i tworzenia funkcji niestandardowych. W następnej sekcji możesz sprawdzić, czego się nauczyłeś(-aś), i dowiedzieć się, co możesz zrobić, aby udoskonalić swoje umiejętności tworzenia skryptów.

6. Podsumowanie

Ukończono pierwszy kurs podstawowy z zakresu podstaw Apps Script w Arkuszach Google. Tworząc i edytując makra i funkcje niestandardowe w Arkuszach, poznajesz podstawowe pojęcia związane z Apps Script. Więcej informacji o Apps Script znajdziesz w kolejnych ćwiczeniach z programowania.

Czy ten codelab był przydatny?

Tak Nie

Omówione zagadnienia

• podstawowe pojęcia związane z Apps Script,
• Jak poruszać się po edytorze skryptów.
• Jak tworzyć i aktualizować makra Arkuszy.
• Jak tworzyć funkcje niestandardowe w Arkuszach Google.

Kluczowe terminy

• Odwołanie bezwzględne: ustawienie nagrywania makra, które określa, że nagrane działania makra są stosowane do komórek użytych podczas nagrywania.
• Aktywny (stan): wskazuje, że określony arkusz kalkulacyjny, arkusz, zakres lub komórka jest obecnie wyświetlana lub zaznaczana przez użytkownika arkusza kalkulacyjnego.
• Apps Script: platforma do szybkiego tworzenia aplikacji oparta na JavaScript, która ułatwia rozszerzanie aplikacji Google Workspace i automatyzowanie pracy.
• Autoryzacja: proces przyznawania przez użytkownika uprawnień, które umożliwiają Apps Script dostęp do danych użytkownika w odpowiednich usługach Google lub wykonywanie innych działań w imieniu użytkownika.
• Skrypt powiązany z kontenerem: dowolny skrypt powiązany z dokumentem Google Workspace, takim jak Arkusz lub Dokument Google, i utworzony w nim.
• Funkcje niestandardowe: zdefiniowane przez użytkownika formuły w Apps Script używane w arkuszach kalkulacyjnych do prostych operacji.
• JSDoc język znaczników używany do pisania notatek wyjaśniających w plikach źródłowych opartych na JavaScript.
• Makro: skrót od „makroinstrukcja”. Nagrana seria działań w Arkuszach Google, którą możesz powtórzyć za pomocą elementu menu lub skrótu klawiszowego.
• Łańcuch metod: idiom programistyczny służący do wywoływania wielu wywołań metod na obiekcie, oparty na tym, że każde wywołanie metody zwraca obiekt.
• Zakres: grupa co najmniej 1 sąsiadujących ze sobą komórek w arkuszu.
• Odwołanie względne: ustawienie nagrywania makra określające, że nagrane działania makra są stosowane do komórek względem bieżącego zaznaczenia komórki.
• Edytor skryptów: edytor kodu w przeglądarce do Apps Script.
• Arkusz: pojedyncza strona arkusza kalkulacyjnego (podobna do skoroszytów w programie Microsoft Excel). Czasami używane w odniesieniu do pliku Arkuszy Google na Dysku Google.
• SpreadsheetApp: ta klasa jest klasą nadrzędną usługi Arkusze i stanowi punkt wyjścia dla kodu, który odczytuje dane Arkuszy Google lub nimi manipuluje.
• Arkusz kalkulacyjny: plik Arkuszy Google znajdujący się na Dysku Google.
• Usługa Arkuszy Google: usługa Apps Script, która umożliwia skryptom tworzenie plików Arkuszy Google, uzyskiwanie do nich dostępu i modyfikowanie ich.

Więcej informacji o Apps Script znajdziesz w dokumentacji Apps Script.

Co dalej?

W kolejnym module z tej playlisty przedstawiamy podstawowe klasy i terminologię usługi Arkusze w Apps Script. Ta usługa umożliwia precyzyjne kontrolowanie wartości i prezentacji danych w Arkuszach Google za pomocą Apps Script.

Kolejny codelab znajdziesz w sekcji Arkusze kalkulacyjne, Arkusze i zakresy.