Przewodnik dla początkujących ze skryptem Arkuszy Google

Jak

PrzezRyan Dube

Ostatnia aktualizacja6 sierpnia 2020 r

Jeśli korzystasz z Arkuszy Google, możesz być zainteresowany, aby wiedzieć, że Skrypty Google mogą znacznie ulepszyć Twoje arkusze kalkulacyjne.

Pomyśl o skryptach Google, takich jak VBA w programie Microsoft Excel. Umożliwia dodawanie kodu do arkusza kalkulacyjnego w celu zautomatyzowania wprowadzania danych. Możesz również użyć go do przeniesienia danych zewnętrznych do arkuszy. A co najlepsze, możesz wykonywać obliczenia okresowo, nawet jeśli nie masz otwartego arkusza kalkulacyjnego.

Z tego artykułu dowiesz się, jak włączyć Skrypty Google oraz jak napisać i uruchomić prosty skrypt.

Jak korzystać ze skryptów Google

Jest kilka rzeczy w Arkuszach Google, które musisz włączyć, zanim będziesz mógł zacząć pisać tam Google Scripts.

Wybierz menu Narzędzia i wybierz Edytor skryptów z menu.

Spowoduje to otwarcie edytora Google Scripts w oknie projektu bez tytułu.

Ten skrypt jest faktycznie zawarty na Twoim koncie Google w narzędziu Google Scripts. Ten konkretny projekt jest połączony z arkuszem kalkulacyjnym, nad którym obecnie pracujesz.

Możesz napisać skrypt, ale nie możesz go uruchomić, dopóki skrypty nie zostaną faktycznie włączone w arkuszu. Musisz to zrobić przy pierwszym uruchomieniu (jak zobaczysz poniżej).

Aby zobaczyć, jak to działa, zróbmy prosty skrypt.

Utwórz swój pierwszy skrypt Google

W tym przykładzie masz zamiar napisać skrypt, który zsumuje wszystkie wyniki quizu i egzaminu końcowego w kolumnie B (wyniki Chrisa).

To jest przykładowy arkusz kalkulacyjny.

Wróć do okna Google Scripts, wklej następujący kod wewnątrz funkcji myFunction () w nawiasach klamrowych, na przykład:


function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheets () [0] var range = sheet.getRange (2,2,5);
var values ​​= range.getValues ​​();
var sum = 0
for (var row in values) {
for (var col in values ​​[wiersz]) {
suma = wartości [wiersz] [kolumna] + suma;
}
}
sheet.getRange (7,2) .setValue (suma);
}


Ten kod może wyglądać na skomplikowany, ale wyjaśnijmy dokładnie, co robi. To prostsze niż się wydaje.

Podzielmy ten kod po jednym wierszu na raz.

Definiowanie zmiennych

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

Ta linia pobiera aktualnie aktywny arkusz kalkulacyjny. Ponieważ uruchamiasz ten skrypt tylko wtedy, gdy arkusz kalkulacyjny, na którym chcesz przeprowadzić obliczenia, jest aktywny, zawsze zostanie wyświetlony prawidłowy arkusz kalkulacyjny. Arkusz zostanie zapisany jako zmienna „obiektu” o nazwie „ss”.

2. var sheet = ss.getSheets () [0]

Spowoduje to pobranie pierwszego arkusza i zapisanie go jako zmiennej obiektu o nazwie arkusz. Istnieje alternatywna funkcja, której możesz użyć, aby pobrać arkusz według nazwy arkusza.

To byłoby var sheet = ss.getSheetByName („Sheet1”);

Możesz użyć dowolnej funkcji.

3. var range = sheet.getRange (2,2,5);

Ta funkcja pobiera zakres wartości według wiersza, kolumny i liczby wierszy. W tym przypadku pobieramy wszystkie wartości, zaczynając od drugiego wiersza, drugiej kolumny, i pobieramy wartości pięć wierszy w dół.

Zmienna „zakres” staje się tak zwaną „tablicą”. Tablica to zmienna zawierająca wiele wartości. W Google Script odwołujesz się do dowolnej z tych liczb, używając specjalnego schematu adresowania elementów z nawiasami, jak zobaczysz poniżej.

4. var values ​​= range.getValues ​​();

Funkcja getValues ​​() w rzeczywistości pobiera wartości z obiektu zakresu, który zdefiniowałeś powyżej.

5. var sum = 0

To tylko pojedyncza zmienna liczbowa, którą inicjujemy liczbą 0. To będzie początek podsumowania wszystkich liczb w kolumnie.

Tworzenie pętli For

6. for (var row in values) {

Nazywa się to pętlą „For”. Ten rodzaj pętli przechodzi przez określoną liczbę elementów na liście. W tym przypadku przejdzie przez tablicę „wartości”, którą omówiliśmy powyżej. Ta pierwsza pętla przejdzie przez wszystkie wiersze tablicy. W tym przypadku będzie tylko jeden.

Uwaga: wiersze tablicy to nie to samo co wiersze arkusza kalkulacyjnego. Kiedy tablica ładowała wartości z kolumny w arkuszu kalkulacyjnym, umieszczała je w jednym wierszu (lub wierszu) danych, na przykład: „89, 92, 78, 98, 89”. Tak więc w tym przypadku tablica ma jedną linię (wiersz) i pięć liczb w poprzek (kolumny).

7. for (var col in values ​​[wiersz]) {

Ta sekunda pętli for przejdzie przez wszystkie „wiersze” w tablicy. W tym przypadku przejdzie przez wszystkie 5 wartości.

8. suma = wartości [wiersz] [kolumna] + suma;

Za każdym razem, gdy pętla zostanie uruchomiona, instrukcja ta będzie sumować do siebie sumę nowej wartości. Zanim dojdzie do wszystkich 5 pętli, wszystkie 5 liczb powinno zostać zsumowanych w zmienną „suma”.

9 sheet.getRange (7,2) .setValue (suma);

To jest instrukcja dwufunkcyjna. Funkcja getRange wskazuje komórkę w siódmym wierszu i drugiej kolumnie. Następnie funkcja setValue umieszcza wartość zmiennej „sum” w tej komórce.

Teraz, gdy już wiesz, jak działa każdy wiersz kodu, czas zobaczyć, jak to wszystko działa.

Zapisywanie skryptu Google

Po wpisaniu skryptu Google czas zapisać go i przejść przez kod. Najpierw wpisz „Projekt bez nazwy”, aby nadać projektowi nazwę. Następnie wybierz ikonę dyskietki w menu, aby zapisać skrypt.

Podczas próby zapisania może pojawić się błąd. Nie panikuj. W rzeczywistości jest to część procesu debugowania. Zwróć szczególną uwagę na numer wiersza, w którym jest napisane, że wystąpił błąd. Przyjrzyj się uważnie kodowi i rozwiąż wszelkie literówki lub błędy składniowe, które możesz mieć.

W tym przykładzie w mojej pętli „for” w linii 8 brakuje otwierającego symbolu „(”. Po naprawieniu spróbuj ponownie zapisać. Gdy możesz zapisać kod, Google uzna jego składnię za prawidłową.

Nie oznacza to, że Twój kod będzie działał zgodnie z przeznaczeniem, oznacza to po prostu, że można go „skompilować” bez problemów ze składnią.

Uruchamianie skryptu Google

Teraz wybierz ikonę odtwarzania u góry, aby przejść przez kod. Ponieważ jest to pierwsze uruchomienie nowego skryptu, musisz przyznać aplikacji Google Script uprawnienia dostępu do arkusza kalkulacyjnego (danych) na swoim koncie Google. Wybierz Przejrzyj uprawnienia w wyskakującym okienku.

Musisz zalogować się na swoje konto Google. A ponieważ jest to aplikacja niestandardowa, a nie zarejestrowana w Google, zobaczysz ostrzeżenie, że aplikacja nie jest zweryfikowana. Wybierz zaawansowane łącze, a następnie wybierz Przejdź do przykładowego skryptu Google (niebezpieczne) kontynuować.

Na następnym ekranie musisz wybrać Dopuszczać aby dać Google Script dostęp do swojego konta Google. Gdy to zrobisz, skrypt zostanie uruchomiony.

Jeśli nie widzisz żadnych błędów, przełącz się z powrotem do arkusza kalkulacyjnego. Wyniki skryptu powinny pojawić się na dole pierwszej kolumny.

Pisanie zaawansowanych skryptów Google

To był bardzo prosty skrypt Google, który po prostu zsumował komórki w jednej kolumnie. Jeśli chcesz dodać zmienne do dwóch pętli For w kodzie, możesz również przejrzeć wszystkie kolumny i podać podsumowanie w wierszu 7 dla nich wszystkich.

Google Scripts oferuje przeróżne możliwości, na przykład wysyłanie wiadomości e-mail z arkusza kalkulacyjnego. Możesz nawet skorzystać z specjalne interfejsy API Google które pozwalają zintegrować inne aplikacje Google, takie jak Gmail, Kalendarz, a nawet Google Analytics. Gdy zaczniesz uczyć się pisania skryptów Google, możliwości są nieograniczone.