Podstawy Apps Script w Arkuszach Google 1: makra i funkcje niestandardowe

1. Witamy w Apps Script

Co to jest Apps Script?

Apps Script to szybka platforma do tworzenia aplikacji, która umożliwia automatyzację, dostosowanie i przedłużenie możliwości Google Workspace. Dzięki Apps Script możesz zaoszczędzić czas i wysiłek, upraszczając wykonywanie uciążliwych lub złożonych zadań w Google Workspace.

Funkcje Apps Script:

• Wbudowane usługi Apps Script pozwalają odczytywać, aktualizować i modyfikować dane aplikacji w Google Workspace przy użyciu skryptów.
• Możesz tworzyć skrypty za pomocą edytora kodu w przeglądarce Apps Script i nie musisz instalować ani uruchamiać oprogramowania do programowania.
• W edytorach Google Workspace możesz projektować interfejsy pozwalające na aktywowanie skryptów bezpośrednio z poziomu elementów menu, okien i pasków bocznych.

Ta playlista „Podstawowe informacje o języku Apps Script” i „Ćwiczenia z Arkuszy Google” zawierają podstawowe informacje na temat tego skryptu i tego, jak go używać, by zwiększyć komfort korzystania z Arkuszy. Ten warsztat skupia się na nauczaniu podstaw Apps Script.

Usługa arkusza kalkulacyjnego

Za pomocą Apps Script możesz zwiększać czas w Arkuszach Google. Apps Script udostępnia usługę arkuszy kalkulacyjnych, która umożliwia interakcję interakcji z plikami Arkuszy Google i zawartych w nich danych. Przy użyciu tej usługi możesz zautomatyzować następujące typowe zadania arkusza kalkulacyjnego:

• Utwórz lub zmodyfikuj arkusz kalkulacyjny.
• Odczytywanie i aktualizowanie komórek, formuł oraz formatowania.
• Utwórz niestandardowe przyciski i menu.
• Importowanie i eksportowanie danych z innych aplikacji Google lub źródeł zewnętrznych.
• Udostępnianie i kontrolowanie dostępu do arkuszy kalkulacyjnych.

Czego się nauczysz

Ta playlista zawiera wszystkie tematy, które musisz wykonać, aby zacząć używać Apps Script w Arkuszach Google:

1. Makra i funkcje niestandardowe
2. Arkusze kalkulacyjne, arkusze kalkulacyjne i zakresy
3. Praca z danymi
4. Formatowanie danych
5. Wyświetlanie danych na wykresie i prezentacjach w Prezentacjach

Ćwiczenia z programowania na tej playliście mają być odczytywane w odpowiedniej kolejności. Zacznij więc od tego i ukończ je w sekwencjach, aby jak najlepiej poznać naukę.

Aby dowiedzieć się więcej o tym ćwiczeniu z programowania, przejdź do następnej sekcji.

2. Wstęp

Witamy na pierwszym ćwiczeniu z tej playlisty. Z tego ćwiczenia dowiesz się, jak używać Apps Script w Arkuszach Google. Szkolenie skupia się na dwóch podstawowych zagadnieniach: makrach i funkcjach niestandardowych.

Makro to seria zarejestrowanych działań w Arkuszach Google. Po zarejestrowaniu makra możesz je powtórzyć w menu lub za pomocą klawisza skrótu. Możesz tworzyć i aktualizować własne makra 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 wbudowane funkcje Arkuszy (np. SUM czy AVERAGE), za pomocą Apps Script możesz tworzyć własne funkcje niestandardowe, które ułatwią Ci wykonywanie prostych i niszowych operacji (np. konwersji lub łączenia ciągów znaków). Po utworzeniu tych funkcji możesz wywoływać je w Arkuszach tak jak wbudowaną funkcję. W formułach z komórkami możesz też używać funkcji niestandardowych, a w razie potrzeby łączyć je z innymi funkcjami.

Poniżej znajdziesz omówienie poszczególnych zagadnień i wymagań związanych z tym ćwiczeniami z programowania.

Czego się nauczysz

• Jak utworzyć skrypt w Arkuszach Google
• Poruszanie się po edytorze Apps Script.
• Tworzenie i aktualizowanie makr.
• Jak utworzyć pierwszą funkcję niestandardową Arkuszy.

Czego potrzebujesz

• Podstawowa znajomość JavaScript
• Podstawowe informacje o Arkuszach Google
• Możliwość czytania Arkuszy notacja A1

Uwaga: Apps Script opiera się na języku JavaScript. Jeśli po raz pierwszy korzystasz z JavaScriptu, na platformie Codecademy znajdziesz bezpłatny wstępny kod JavaScript. Kurs nie został stworzony przez Google, ale zawiera on wszystko, co musisz wiedzieć.

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

3. Tworzenie makra w Arkuszach

Zwykle podczas pracy w arkuszu kalkulacyjnym możesz zapętlać się w powtarzalnych czynnościach, takich jak kopiowanie wartości komórek, formatowanie, tworzenie formuł itp., co może być uciążliwe i prowadzić do błędów. Aby zautomatyzować powtarzające się działania, Arkusze Google udostępniają makra. Makra umożliwiają „rejestrowanie” i serii działań w arkuszu. Za pomocą nagranego makra możesz powtórzyć te same działania w dowolnym miejscu arkusza kalkulacyjnego, naciskając prosty klawisz skrótu.

Z tej sekcji dowiesz się, jak utworzyć makro w Arkuszach. W następnej sekcji zobaczysz, jak makra są tworzone przy użyciu Apps Script.

Zanim zaczniesz

Aby kontynuować, potrzebujesz arkusza kalkulacyjnego z danymi. Udostępniamy Ci go: kliknij ten link, aby skopiować arkusz danych, a następnie kliknij Utwórz kopię.

Kopia przykładowego arkusza kalkulacyjnego, którego możesz użyć, znajduje się w Twoim folderze na Dysku Google i jest zatytułowana „"Kopia 10 najlepszych filmów kasowych (2018)”.

Tworzenie makra

Gdy masz już arkusz kalkulacyjny do pracy, możesz zarejestrować makro w Arkuszach Google. W tym przykładzie utworzysz makro do formatowania wiersza 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 kliknij kolejno Rozszerzenia > Makra > Zarejestruj makra.

Gdy rozpoczynasz nagrywanie, Arkusze Google zapamiętują wszystkie działania wykonane w arkuszu kalkulacyjnym, takie jak zaznaczanie komórek, dodawanie danych, przełączanie się na różne arkusze, formatowanie itd. Działania te stają się „skryptem” i powtarzają się, gdy zapiszesz i aktywujesz makro później.

3. W oknie Makro wybierz Względny plik referencyjny.

Kluczowe pojęcia: makra, które mogą się odwoływać do konkretnych komórek, mogą być używane w 2 rodzajach plików referencyjnych w Arkuszach. Odwołania bezwzględne są przypisywane do samych zarejestrowanych lokalizacji (na przykład komórki A5), a odwołania względne są stosowane jako punkt początkowy (bieżący punkt początkowy użytkownika) (na przykład czwarta komórka w dół od bieżącego wyboru).

W tym ćwiczeniu poprosimy Cię o użycie względnych odniesień do makr.

4. Wybierz wiersz 1.

5. Zmień kolor górnego rzędu Koloru wypełnienia z białego na ciemny fuksja 3.

6. Zmień Kolor tekstu w górnym wierszu na biały.

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

9. W oknie makro kliknij Zapisz. W nowym oknie pojawi się prośba o nadanie makra. Wpisz nazwę &nagłówka" i kliknij Zapisz.

Korzystając z interfejsu Arkuszy i interfejsu użytkownika, tworzysz makro przeznaczone do formatowania nagłówków.

Aktywacja makra

Nowe makro możesz zastosować w Arkuszach, postępując zgodnie z tymi instrukcjami:

1. Aby utworzyć arkusz, kliknij Dodaj arkusz .

2. W nowym arkuszu dodaj tekst do elementu A1:C2. Możesz skorzystać z tych przykładowych danych:

3. Zaznacz pierwszy wiersz.

4. Aby zastosować makro do wybranego obszaru, kliknij Rozszerzenia > Makra> Nagłówek.
5. Autoryzuj makro, wykonując instrukcje wyświetlane na ekranie.

Uwaga: jeśli używasz konta gmail.com, przy pierwszym użyciu skryptu może pojawić się niezweryfikowane okno dialogowe aplikacji. Google używa tego ustawienia, aby ostrzegać użytkowników, którzy mogą używać kodu od nieznanych lub niezaufanych autorów. Jeśli widzisz to okno, oznacza to, że możesz kontynuować, ponieważ jesteś właścicielem skryptu. Aby kontynuować autoryzowanie skryptu, postępuj zgodnie z instrukcjami wyświetlanymi na ekranie. Proces uzyskiwania tych uprawnień nosi nazwę autoryzacji.

6. Powtórz krok 4, by ponownie uruchomić makro (autoryzacja spowoduje zatrzymanie pierwszego wykonania).

Gratulacje! Wiesz już, jak używać makr w Arkuszach. Twój arkusz kalkulacyjny powinien wyglądać tak:

Makra pozwalają wydajnie tworzyć arkusze kalkulacyjne. Z następnej części tego ćwiczenia dowiesz się, jak można jeszcze bardziej poprawić działanie makr. Sekret: tutaj rejestrujesz makro, a tak naprawdę piszesz skrypt Apps Script. Za kulisami kod tworzy kod zgodny z działaniami makra. Z następnej sekcji dowiesz się, jak bezpośrednio zmodyfikować kod w edytorze Apps Script w przeglądarce.

4. Makra w edytorze skryptu

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

Edytor skryptów

Po utworzeniu makra możesz spojrzeć na jego kod. Aby wyświetlić skrypt makra, kliknij Rozszerzenia> Apps Script, co spowoduje otwarcie edytora kodu przeglądarki dla Apps Script.

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

Uwaga: makra i skrypty tworzone w tym module ćwiczeń są dołączone do pliku Arkuszy Google, na którym pracują. Znajdziesz je w każdej chwili w menu Rozszerzenia w Arkuszach Google Apps. Skrypty dołączone do tego arkusza Google są nazywane kontenerami powiązanymi.

Analiza kolumny macro.gs

Sprawdź bieżący skrypt. Podczas rejestrowania makra Header w Arkuszach został utworzony plik skryptu macros.gs, który wypełnia odpowiednią funkcję Apps Script o nazwie Header. Po aktywacji makra Header ta funkcja działa w Arkuszach.

Spójrz na poniższy obraz, aby zapoznać się ze strukturą funkcji makr w Apps Script. Jeśli kroki zostały zapisane w innej kolejności lub użytkownik kliknął arkusz podczas nagrywania, Twój kod może wyglądać nieco inaczej.

Pierwszy wiersz to adnotacja, która wpływa na autoryzację:

/** @OnlyCurrentDoc */

Większość skryptów wymaga od użytkownika uzyskania pewnych uprawnień przed uruchomieniem. Te uprawnienia określają, co użytkownik może robić za pomocą skryptu. Gdy w projekcie skryptu znajduje się komentarz @OnlyCurrentDoc, Apps Script prosi tylko o uprawnienia i dostęp do bieżącego arkusza kalkulacyjnego. Bez tego komentarza Apps Script będzie prosić o dostęp do wszystkich arkuszy kalkulacyjnych użytkownika i ich aktualizowanie. Najlepiej zastosować tę adnotację tylko wtedy, gdy używasz tylko jednego pliku. Rejestrator makr automatycznie dodaje komentarz.

Aby dowiedzieć się, jak Apps Script prezentuje makra, możesz spojrzeć na tę funkcję:

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, gdy aktywujesz makro Header Po function etykieta Header() definiuje nazwę funkcji i jej parametry. Zwróć uwagę, że Header() nie wymaga żadnych parametrów, ponieważ funkcje makro w Apps Script nie wymagają danych wejściowych. Nawiasy klamrowe zawsze umieszczają treść funkcji w Apps Script.

Późniejsze ćwiczenia z tej playlisty odpowiadają na klasy i koncepcje związane z tworzeniem makra. Możesz teraz zapoznać się z poniższymi opisami kodu, aby ogólnie zorientować się w komponentach i ich rolach w tworzeniu makra. Pierwszy wiersz:

var spreadsheet = SpreadsheetApp.getActive();

W tym przypadku obiekt getActive() zwraca obiekt reprezentujący obecny aktywny plik arkusza kalkulacyjnego w Arkuszach i ustawia go jako nową zmienną spreadsheet.

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

Odpowiadają one działaniu kliknięcia pierwszego wiersza. Jest to tzw. aktywacja. Pierwszy wiersz zawiera bieżący arkusz w zmiennej sheet, a drugi wiersz pobiera cały pierwszy wiersz za pomocą metody getRange(), a następnie wywołuje metodę activate(), by ją aktywować. Pierwszy wiersz jest określany za pomocą konkretnych 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 łączy 3 metody w getActiveRangeList(), aby zapobiec zbędnemu wywoływaniu kodu za pomocą tej metody spreadsheet więcej niż raz. Kodując w Google Apps Script, zaznajomisz się z tą konwencją wywoływania wielu metod w jednej klasie (tzw. łańcuch metod). Na razie możesz zapoznać się z krótkimi wyjaśnieniami dotyczącymi poszczególnych metod w bloku kodu:

• getActiveRangeList() zwraca obecny aktywny RangeList w spreadsheet. W takim przypadku jest to po prostu pierwszy wiersz aktywowanego poprzedniego wiersza.
• Metody setBackground(color) i setFontColor(color) zmieniają atrybuty kolorów komórek w aktywnym zakresie.
• setFontWeight(fontWeight) dostosowuje grubość czcionki w komórkach w aktywnym zakresie.

Ostatni wiersz blokuje pierwszy wiersz makra:

spreadsheet.getActiveSheet().setFrozenRows(1);

Skrypt wygenerowany podczas rejestrowania makra. Nie przejmuj się nieznanymi terminami ani metodami wymienionymi powyżej. Opis ma na celu omówienie niektórych pomysłów, których skupia się na Apps Script w typowej funkcji makro, oraz tematów, którymi interesują się przyszłe ćwiczenia z programowania.

W następnej sekcji skupimy się na manipulowaniu kodem funkcji Header() w celu pokazania, jak możesz wykorzystać edytor skryptów do dalszej personalizacji makr.

Dostosowywanie makr za pomocą Apps Script

Edytor Apps Script zawiera makro utworzone wcześniej w Arkuszach Google. Dostosowując zawartość treści funkcji, możesz dodatkowo dostosować makro i wykonać inne lub dodatkowe działania. Poniżej znajdziesz różne sposoby manipulowania makrami w edytorze skryptów.

Zmień odpowiednie komórki

Załóżmy, że chcesz zmienić makro, tak aby miało ono wpływ tylko na pierwsze 10 kolumn pierwszego wiersza, a nie na cały wiersz. Możesz usunąć makro i zarejestrować je ponownie Jednak za pomocą edytora Apps Script możesz wprowadzić te zmiany bezpośrednio. Aby to zrobić:

1. W edytorze skryptów zastąp sheet.getMaxColumns() tekstem 10. Zmiana spowoduje zmianę zakresu komórek 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, kliknij Dodaj arkusz .

5. W edytorze skryptów wybierz Header i kliknij Uruchom.

W nowym arkuszu powinny pojawić się takie wyniki:

Jeśli zmienisz zakres aktywny lub docelowy, makro obejmie teraz tylko część pierwszego wiersza. Wiele metod Apps Script używa zakresu lub notacji A1 jako parametru określającego, które komórki mają być obsługiwane.

Czas nauczyć się dostosowywać kolory makr.

Zmiana kolorów makra

Aby ułatwić schemat kolorów w makrach lub innych elementach w Arkuszach, Apps Script może zmieniać kolor wypełnienia lub tekstu. Aby dowiedzieć się, jak dostosować kolory makra, wykonaj poniższe instrukcje.

Te instrukcje dotyczą zmiany koloru tła makra:

1. W Arkuszach przełącz się z powrotem na arkusz zawierający oryginalne dane (Arkusz 1).
2. Kliknij pierwszy wiersz, aby go zaznaczyć.
3. W edytorze skryptu zastąp kolor tła #4c1130 wartością #afeeee. Wartości te reprezentują różne kolory w notacji szesnastkowej 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. Z listy funkcji wybierz Header i kliknij Uruchom.

W Arkuszach pierwsze tło 10 pierwszych kolumn w pierwszym wierszu zmienia kolor na niestandardowy turkusowy:

Przełączenie notacji koloru w zapisie szesnastkowym w parametrach setBackground(color) z #4c1130 (ciemnoczerwony lub jasny turkusowy) na #afeeee (jasnoturkusowy, opcja niedostępna w Arkuszach i na domyślnym menu kolorów) spowoduje zmianę atrybutu kolorów makra.

Udało Ci się zmienić kolor tła ustawiony przez makro. Jeśli chcesz zmienić też kolor tekstu, zmień drugi kod koloru.

1. Kliknij pierwszy wiersz w Arkuszach, aby sprawdzić, czy jest on nadal podświetlony.
2. W edytorze skryptu zastąp kolor czcionki #ffffff wartością #191970. Powoduje to, że makro ustawia ciemnogranatowy kolor czcionki.

/** @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. Z listy funkcji wybierz Header i kliknij Uruchom.

Wróć do Arkuszy, aby sprawdzić, jak kolor tekstu w wierszu nagłówka jest granatowy.

Teraz wiesz, jak makra są potraktowane jako działania Arkuszy, rejestrowane jako kod Apps Script. W następnej sekcji możesz zobaczyć, jak Apps Script może pomóc w pracy z Arkuszami Google: funkcje niestandardowe.

5. Kodowanie pierwszego skryptu: funkcje niestandardowe

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

Ta sekcja pokazuje, jak utworzyć w Apps Script funkcję niestandardową, która będzie prowadzić do konwersji pieniężnej.

Tworzenie pliku skryptu

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

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

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

Po utworzeniu skryptu przeznaczonego specjalnie do funkcji niestandardowych możesz go wypełnić kodem.

Przeliczaj frank szwajcarski na dolary amerykańskie

Załóżmy, że chcesz zmodyfikować dane listy „10 najwyższych filmów brutto z 2018 roku”; w celu wyświetlenia nie tylko wartości brutto na całym świecie, ale również we frankach szwajcarskich. Dzięki funkcjom niestandardowym możesz to łatwo zrobić. W tym ćwiczeniu pokazujemy, jak utworzyć funkcję niestandardową w celu matematycznego przeliczenia wartości dolara na frank.

Przed napisaniem pierwszej funkcji niestandardowej musisz zmodyfikować zbiór danych, aby mógł wyświetlać prawidłowe dane wyjściowe. Aby wysłać prośbę o skonfigurowanie konta:

1. W Arkuszach kliknij prawym przyciskiem myszy kolumnę H.
2. W wyświetlonym menu kliknij Wstaw 1 z prawej.

3. W kolumnie I1 oznacz kolumnę &Worldwide_Gross (frank szwajcarski)&quot.

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

4. W polu customFunctions.gs zastąp kod 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;
}

To kod, który przelicza dolary amerykańskie na frank szwajcarski. Zapoznaj się z poniższymi instrukcjami 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 kolumny:

8. Przenieś kursor w prawy dolny róg komórki I2 i zaznacz małe niebieskie pole (wskazuje on , że wskazuje on niebieskie pole).
9. Przeciągnij niebieskie pole w dół, aby podświetlić zakres I3:I11.

Kolumna I zawiera listę konwersji szwajcarskich wyrażonych w dolarach amerykańskich w kolumnie H.

Gratulacje, udało Ci się utworzyć pierwszą funkcję niestandardową. Następna sekcja zawiera kod składający się z USDTOCHF().

Analiza kolumny USDTOCHF()

Pierwsze komentarze szczegółowo opisują przeznaczenie 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
 */

Takie bloki są często używane w programowaniu do wyjaśniania działania poszczególnych funkcji.

W tym komentarzu zidentyfikujesz dwie funkcje: opis funkcji (do przeliczania dolarów na franczyzy) i adnotacje, które opisują parametry i typ zwrotu funkcji.

Dzięki adnotacjom Apps Script wykorzystuje JSDoc do udokumentowania i utworzenia wskazówek autouzupełniania w kodzie. Poniżej znajdziesz informacje o tym, jak każda adnotacja z USDTOCHF() pomaga w tworzeniu skryptu Apps Script:

• @param: do każdego parametru przekazanego do funkcji możesz użyć adnotacji @param.
• @return: możesz użyć adnotacji @return, by opisać, co zwraca funkcja.
• @customfunction: do każdej funkcji niestandardowej należy dodać atrybut @customfunction w komentarzu do dokumentu. Adnotacje powiadamiają Arkusze, by automatycznie uzupełniały Twoją funkcję niestandardową tak samo, jak w przypadku autouzupełniania wbudowanych funkcji, gdy wpiszesz nazwę funkcji w komórce (jak pokazano poniżej):

Zauważ, że tekst widoczny w wyskakującym okienku autouzupełniania jest dokładnie taki sam jak tekst opisu w bloku komentarzy. Możesz ułatwić sobie korzystanie z funkcji niestandardowych, upewniając się, że Twoje opisy są dobrze napisane i kompletne.

Następnie przejdź do kodu funkcji USDTOCHF():

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

Jak wspomnieliśmy wcześniej, USDTOCHF() przyjmuje zmienną liczbową w dolarach amerykańskich, mnoży ją przez stałą kurs wymiany i zwraca wartość przekonwertowaną na frank szwajcarski w zmiennej liczbowej swissFrancs. Parametr wejściowy to wartość zawartych w komórce określonej podczas dodawania funkcji niestandardowej do komórki. W tym przykładzie kwota wejściowa pochodzi z kolumny H. Wartość wyjściowa swissFrancs jest umieszczona w komórce funkcji (kolumna I w tym przykładzie).

Funkcje niestandardowe mogą współdziałać z wartościami liczbowymi lub ciągami, jak zobaczysz w następnej sekcji.

Łączenie prefiksu ciągu

Załóżmy, że dane liczbowe w funkcji USDTOCHF() mają zawierać prefiks szwajcarskie franky CHF. Korzystając z Apps Script, możesz użyć operatora łączenia (+),, jak pokazano w tych instrukcjach:

1. W edytorze skryptu zaktualizuj adnotację @return, aby zwracała ciąg tekstowy, a nie liczbę.
2. Zmień return swissFrancs na return 'CHF' + swissFrancs.

Operator + dodaje ciąg CHF na początku wartości zawartej w zasadzie swissFrancs. Kod powinien 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 szwajcarskiego franku zawiera teraz wartości z kolumny I:

Funkcja niestandardowa nie tylko przelicza dolary amerykańskie na frank szwajcarski, ale też wyświetla walutę z prefiksem ciągu.

Zaawansowane: pobieranie danych zewnętrznych

To dobry początek w przypadku podstawowej funkcji niestandardowej, ale w tym przykładzie założono, że kurs wymiany dolara szwajcarskiego jest stały. Załóżmy, że chcesz zastosować bieżący kurs wymiany, więc za każdym razem, gdy arkusz jest ponownie ładowany, wartości są przeliczane, by odzwierciedlić aktualną konwersję. W tym celu potrzebny jest sposób sprawdzania aktualnego kursu wymiany. Te informacje nie są łatwo dostępne w Arkuszach Google, ale możesz je uzyskać za pomocą Apps Script.

Aby sprawdzić aktualny współczynnik konwersji we frankach szwajcarskich i dolarach amerykańskich, możesz użyć poniższego kodu:

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 przy użyciu zewnętrznego interfejsu API kursu wymiany. Odbywa się to za pomocą usług Apps Script, takich jak UrlFetchApp i CacheService. Te zaawansowane zagadnienia są poza zakresem tej konkretnej ćwiczenia z programowania, ale możesz zobaczyć, jak wszechstronny skrypt Apps Script automatyzuje złożone zadania w Arkuszach Google.

Wytyczne dotyczące funkcji niestandardowych

Gratulujemy ukończenia ćwiczeń dotyczących funkcji niestandardowych. Ponieważ używasz w swoich projektach funkcji niestandardowych, pamiętaj, że mają one pewne ograniczenia. Poniższa lista zawiera podsumowanie ograniczeń opisanych w przewodniku Funkcje niestandardowe w Arkuszach Google:

• Nie twórz funkcji niestandardowych, które wymagają autoryzacji użytkownika. Zamiast tego utwórz funkcje niestandardowe służące do wykonywania prostych zadań, takich jak obliczanie przykładowych danych, edytowanie tekstu itp. Przejdź do artykułu Używanie usług Apps Script.
• Nazwij funkcję niestandardową tak samo jak inna wbudowana funkcja lub nazwij ją znakiem podkreślenia. Zapoznaj się z wytycznymi dotyczącymi nazw.
• Nie przekazuj argumentów zmiennych do funkcji niestandardowych. Jako funkcje niestandardowe możesz przekazywać do funkcji niestandardowych tylko wartości deterministyczne (stałe). Przekazywanie argumentów zmiennej, np. wynik funkcji =RAND(), spowoduje nieprawidłowe działanie funkcji niestandardowej. Zobacz wytyczne dotyczące oświadczeń.
• Nie twórz funkcji, które trwają dłużej niż 30 sekund. Jeśli wystąpi dłuższy czas, błąd kodu funkcji powinien być prosty, a jego zakres powinien być ograniczony. Najlepiej, aby obliczenia wykonywane w funkcjach niestandardowych były jak najbardziej proste. Zobacz wytyczne dotyczące wartości zwrotów.

Teraz możesz ulepszać swoje arkusze kalkulacyjne, korzystając z edytora skryptów, który obsługuje makra, i tworzy niestandardowe funkcje. W następnej sekcji możesz sprawdzić, czego się nauczysz, i co możesz zrobić, by poprawić swoje umiejętności pisania.

6. Podsumowanie

Udało Ci się ukończyć pierwsze ćwiczenie z podstaw programowania w Arkuszach Google. Tworząc i edytując makra oraz funkcje niestandardowe Arkuszy, nauczysz się podstawowych pojęć związanych ze skryptami aplikacji. Poszerz swoją wiedzę na temat Apps Script podczas kolejnego ćwiczenia z programowania.

Czy te ćwiczenia są przydatne?

Tak Nie

Omawiane zagadnienia

• Podstawowe koncepcje Apps Script.
• Poruszanie się po edytorze skryptów.
• Tworzenie i aktualizowanie makr Arkuszy.
• Jak tworzyć niestandardowe funkcje Arkuszy Google.

Kluczowe terminy

• Odwołanie bezwzględne: ustawienie rejestrowania makr określające zarejestrowane działania makro jest stosowane do dokładnie tych samych komórek, które zostały użyte w nagraniu.
• Aktywny (stan): wskazuje, że arkusz kalkulacyjny, arkusz, zakres lub komórka są tym, który użytkownik aktualnie wyświetla lub podświetla.
• Apps Script: szybka platforma do tworzenia aplikacji oparta na języku JavaScript, która umożliwia szybkie i łatwe rozszerzanie aplikacji Google Workspace oraz automatyzację pracy.
• Autoryzacja: proces przyznający użytkownikom uprawnienia, które pozwalają skryptom Apps Script na dostęp do danych użytkownika w odpowiednich usługach Google lub na inne działania w jego imieniu.
• Skrypt powiązany z kontenerem: dowolny skrypt powiązany z dokumentem Google Workspace, np. Arkusz Google lub Dokument Google.
• Funkcje niestandardowe: formuły zdefiniowane przez użytkownika w języku Apps Script używane w arkuszach kalkulacyjnych do wykonywania prostych operacji.
• JSDoc: język znaczników, który służy do zapisywania uwag przy plikach źródłowych opartych na języku JavaScript.
• Makro: skrót „makroinstrukcja”. Zarejestrowana seria czynności w Arkuszach Google, które można powtórzyć przy użyciu elementu menu lub klawisza skrótu.
• Łańcuch metod: idiom programistyczny służący do wywoływania wielu wywołań metody na podstawie obiektu z uwzględnieniem faktu, że każde wywołanie metody zwraca obiekt.
• Zakres: grupa zawierająca co najmniej jedną sąsiednią komórkę arkusza.
• Względne odwołanie: ustawienie nagrywania makro określające, które działania są rejestrowane, są stosowane do komórek względem bieżącego wyboru komórek.
• Edytor skryptów: edytor kodu przeglądarki w języku Apps Script.
• Arkusz: pojedyncza strona arkusza kalkulacyjnego (podobnie jak skoroszyty programu Microsoft Excel). Czasami odnoszą się do pliku Arkuszy Google na Dysku Google.
• SpreadsheetApp: ta klasa służy jako klasa nadrzędna usługi w Arkuszach Google i stanowi punkt wyjścia do odczytywania i edytowania danych Arkuszy Google.
• Arkusz kalkulacyjny: plik Arkuszy Google, który znajduje się na Dysku Google.
• Usługa arkusza kalkulacyjnego: usługa Apps Script, która umożliwia skryptom tworzenie, modyfikowanie i modyfikowanie plików Arkuszy Google.

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

Co dalej

W następnym ćwiczeniu z programowania na tej playliście znajdziesz podstawowe klasy i terminologię usługi arkuszy kalkulacyjnych Apps Script. Ta usługa pozwala precyzyjnie kontrolować wartości i wyświetlanie danych w Arkuszach Google przy użyciu Apps Script.

Kolejne ćwiczenia z programowania znajdziesz w sekcji Arkusze kalkulacyjne, Arkusze i Zakresy.