Samouczek Google Apps Script do opanowania makr

Skuteczni menedżerowie wiedzą, że czas jest czynnikiem ograniczającym… Chyba nic innego nie wyróżnia skutecznych menedżerów tak bardzo, jak ich czuła troska o czas.

Piotr Drucker

Czas to nasz najcenniejszy zasób. Chcemy je wydać na najbardziej efektywne i najbardziej wartościowe działania, jakie tylko możemy, nie tylko dlatego, że zwykle niosą one największą wartość pieniężną, ale także na ciągłe stawianie sobie wyzwań i maksymalizację naszej satysfakcji z pracy.

Istnieje wiele sposobów na poprawę wydajności i produktywności w celu lepszego wykorzystania czasu. W poprzednim artykule o Arkuszach Google omówiłem, w jaki sposób siła współpracy online jest jednym z takich kluczy do zwiększenia produktywności.

W innym artykule pokazałem, w jaki sposób język programowania Python może być potężnym narzędziem do analizy i automatyzacji zadań dla specjalistów finansowych.

Czerpiąc z tego inspirację, chcę teraz przedstawić samouczek dotyczący Google Apps Script. Google Apps Script umożliwia pisanie skryptów i programów w języku JavaScript w celu automatyzacji, łączenia i rozszerzania produktów w G Suite Google, w tym Arkuszy, Dokumentów, Prezentacji, Gmaila, Dysku i kilku innych. Nauka tego wymaga zainwestowania czasu, podobnie jak pisanie skryptów, ale zwiększa się produktywność, a dodatkowe możliwości, które otwiera, sprawiają, że jest to warte.

Jako pierwszy krok zacznijmy od przyjrzenia się znanej koncepcji: makra.

Nagrywanie i używanie makr w Arkuszach Google

Jeśli spędziłeś dużo czasu pracując z Excelem, na pewno w pewnym momencie zetknąłeś się z interfejsem makr VBA (Visual Basic for Applications) Excela. Albo przez nagranie lub napisanie ich samodzielnie, albo zrzucenie tych stworzonych przez innych.

Makra to świetny sposób na zautomatyzowanie powtarzających się i żmudnych przepływów pracy. VBA może nie jest językiem, któremu poświęciłeś dużo czasu na naukę, ale jego piękno polegało na tym, że tak naprawdę nie było to konieczne, aby stać się produktywnym i tworzyć własne makra. Możesz po prostu zarejestrować przepływ pracy, który chcesz zautomatyzować, a następnie przejść do kodu i wprowadzić wszelkie drobne zmiany, które były potrzebne, aby makro stało się bardziej ogólne.

Pod pewnymi względami VBA to świetna i zapomniana lekcja, jak wprowadzić w kodowanie osoby nietechniczne . Sposób, w jaki można rejestrować działania, a następnie wypełniać kod do późniejszego przejrzenia, jest rzeczywiście znacznie bardziej pragmatycznym sposobem uczenia się niż czytanie podręczników i bierne oglądanie samouczków.

Ta sama funkcja nagrywania VBA jest dostępna w Arkuszach Google. Oto prosty przykład, jak z niego korzystać:

Zacznijmy od przykładowych danych, używając zapytania IMPORTHTML do importowania tabeli. W tym przykładzie pobrałem z Wikipedii listę 15 największych funduszy hedgingowych na świecie. To oczywiste, ale jest to arbitralny przykład; Twoim zamiarem jest skupienie się bardziej na aplikacji, na temacie.

Proces nagrywania makr jest inicjowany za pomocą następującej ścieżki menu: Narzędzia > Makra > Nagraj makro.

Następnie przechodzimy przez czynności (format PC), które chcemy nagrać:

1. Wybierz pierwszy wiersz
2. Naciśnij Shift + Ctrl + strzałka w dół, aby zaznaczyć wszystko
3. Ctrl + C, aby skopiować
4. Shift + F11, aby utworzyć nowy arkusz
5. Nadaj arkuszowi nową nazwę
6. Naciśnij Shift + Control + V, aby wkleić wartości

Po zakończeniu naciśnij przycisk Zapisz w oknie makra na dole, nadaj mu nazwę i opcjonalny skrót klawiaturowy.

W przypadku prostszych działań, które można odtworzyć dokładnie w tych samych krokach, proces zakończy się tutaj i można od razu zacząć używać makra. W tym przypadku jednak musimy wprowadzić pewne zmiany, zanim kod będzie użyteczny. Na przykład arkusz, do którego kopiujemy, będzie musiał mieć za każdym razem inną nazwę. Zobaczmy, jak to zrobić.

Ręczne pisanie skryptu Google Apps

Teraz po raz pierwszy zobaczymy kości Google Apps Script; platforma programistyczna działająca na serwerach Google. To zasila nasze makra i pozwala tworzyć bardzo złożone przepływy pracy, a nawet dodatki dla samych aplikacji. Może być używany do automatyzacji nie tylko pracy z arkuszami kalkulacyjnymi, ale właściwie prawie wszystkiego, co jest połączone w ramach G Suite Google.

Językiem programowania Apps Script jest JavaScript , jeden z najpopularniejszych języków programowania, co oznacza, że ​​istnieje wiele zasobów dla każdego, kto chce się intensywnie uczyć. Ale, tak jak w przypadku VBA, tak naprawdę nie musisz: możesz użyć tej samej funkcji nagrywania i po prostu wykonać kroki, które chcesz, aby móc automatycznie powtarzać. Dane wyjściowe z nagrania mogą wyglądać na surowe i najprawdopodobniej nie będą idealnie pasować do tego, co chcesz zrobić, ale zapewni wystarczająco solidny punkt wyjścia. Zróbmy to teraz dla skryptu, który właśnie nagraliśmy.

Podczas nagrywania warto uważać, aby przypadkowo nie nagrać żadnych dodatkowych kroków, których nie chcesz uchwycić w końcowym nagraniu, ale czasami trudno tego uniknąć: coś tak prostego, jak wybranie innej komórki przed naciśnięciem przycisku Stop Przycisk nagrywania zostanie przechwycony, a następnie powtórzony za każdym razem, gdy uruchomisz skrypt. Pierwszym krokiem podczas edycji naszego skryptu byłoby wyczyszczenie go i usunięcie wszelkich takich kroków. Zanurzmy się, przechodząc do Narzędzia > Edytor skryptów w menu Plik.

Jeśli znasz JavaScript, rozpoznasz to natychmiast, a także możesz być zaskoczony, widząc słowo kluczowe „var” zamiast „let” lub „const”, jak w nowoczesnym JavaScript. Odzwierciedla to fakt, że wersja JavaScript w Apps Script jest dość stara i nie obsługuje wielu nowszych funkcji tego języka. Pod koniec przedstawię obejście dla tych, którzy chcieliby korzystać z najnowszych funkcji językowych.

Kiedy uruchamiasz skrypt po raz pierwszy, poprosi on o autoryzację, co ma sens, ponieważ skrypty mogą modyfikować (i potencjalnie usuwać) wszystkie Twoje dane. Najprawdopodobniej rozpoznasz proces autoryzacji z innych produktów Google.

Teraz możemy zacząć modyfikować kod. Zmiany, które musimy wprowadzić, są niewielkie, ale jeśli zrobisz to po raz pierwszy, może to wymagać szybkiego przeszukania dokumentacji skryptu aplikacji Arkuszy i/lub szybkiego wyszukania koncepcji JavaScript, na przykład pracy z datami. Tutaj przydaje się fakt, że JavaScript jest tak rozpowszechnionym językiem: rozwiązanie każdego problemu, z którym się zmagasz, lub funkcji, która przychodzi Ci na myśl, można zwykle szybko znaleźć, jeśli wpiszesz wyszukiwane hasło w prosty sposób.

Zmiany wprowadzone w tej wersji skryptu w stosunku do oryginalnie zarejestrowanej wersji polegają na tym, że zamiast zakodowanej na sztywno nazwy tworzonego przez nas nowego arkusza, zamiast tego nazywamy go dzisiejszą datą. Ponadto zmieniamy również ścieżkę kopiowania na końcu, aby odnosić się do tego nowego arkusza. Ostatnie cztery wiersze pokazują również, jak wykonać niektóre operacje formatowania, takie jak zmiana wartości komórki, zmiana rozmiaru kolumn i ukrywanie linii siatki.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

Uruchomienie skryptu teraz pokaże, że nowy arkusz rzeczywiście ma nazwę z dzisiejszą datą i zawiera informacje skopiowane jako wartości (nie formuły) z głównego arkusza.

Wizualizacje wykresów można teraz dodawać przy użyciu tego samego procesu rejestrowania. Użyłem tego do stworzenia trzech prostych wykresów.

Kod porządkowy dla każdego będzie wyglądał mniej więcej tak:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Ponownie, nie martw się, jeśli niektóre opcje wyglądają na mylące: to wszystko jest generowane automatycznie, wystarczy zrozumieć wystarczająco dużo, aby usunąć niepotrzebne kroki i być może później wprowadzić drobne poprawki.

Przykłady zaawansowanych skryptów Google Apps: łączenie arkuszy z Dyskiem i Prezentacjami Google

Wszystko zaczyna nabierać kształtu, ale co, jeśli rzeczywisty wynik, którego oczekujemy, nie jest arkuszem kalkulacyjnym, ale prezentacją? Jeśli tak jest, to większość pracy z tego miejsca może nadal być ręczna i nie zaoszczędziliśmy dużo czasu, jeśli musimy to robić w sposób powtarzalny.

Przyjrzyjmy się teraz, jak może wyglądać automatyzacja tworzenia prezentacji, korzystając z przykładowych danych z naszego arkusza kalkulacyjnego.

To ćwiczenie staje się teraz bardziej zaawansowane z dwóch powodów:

1. Oprócz Arkuszy będziemy musieli zapoznać się z pracą z Prezentacjami Google (i Dyskiem Google).
2. W Prezentacjach lub ogólnie podczas pracy między aplikacjami G Suite nie jest dostępna funkcja „Rejestruj makro”. Oznacza to, że aby pisać skrypty od podstaw, musisz wiedzieć wystarczająco dużo o Apps Script (i swobodnie poruszać się po dokumentacji poszczególnych produktów G Suite).

Ten następny przykład ma na celu dostarczenie podstawowych elementów konstrukcyjnych, które pomogą Ci zacząć i zapoznać się z nimi.

Na początek stwórzmy szablon, który później będziemy wypełniać treścią za pomocą naszego skryptu. Oto dwa proste slajdy prezentacji, które złożyłem:

Następnie będziesz musiał uzyskać identyfikator tego szablonu, ponieważ będziesz musiał odwołać się do niego w swoim skrypcie. Podświadomie widziałeś ten identyfikator wiele razy, ponieważ w rzeczywistości jest to losowo wyglądająca sekwencja znaków i cyfr, którą widzisz w adresie URL przeglądarki:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Teraz musimy dodać następujące wiersze do naszego oryginalnego skryptu. To ponownie poprosi o autoryzację, tym razem o dostęp do Twojego Dysku Google.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Nie zobaczysz żadnej natychmiastowej wizualnej informacji zwrotnej, jeśli uruchomisz ten fragment kodu, ale jeśli zajrzysz do folderu na Dysku Google, w którym zapisano szablon, zobaczysz, że jego kopia rzeczywiście została utworzona i ma dzisiejszą data w nazwie pliku. Na dobry początek!

Użyjmy teraz więcej bloków konstrukcyjnych, aby zacząć wypełniać go treścią, programowo zamiast ręcznie. Dodaj następujące wiersze do tej samej funkcji:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Teraz robi się trochę ciekawiej, ponieważ zmieniliśmy pierwszą stronę, aby zawierała dzisiejszą datę. W Prezentacjach, podobnie jak w Arkuszach, pracujesz z obiektami (reprezentowanymi przez klasy), z których każdy ma właściwości i metody (tj. dołączoną funkcjonalność). Są one zorganizowane w hierarchię, a obiektami najwyższego poziomu są SpreadsheetsApp, DriveApp lub SlidesApp. W powyższym fragmencie kodu musimy przejść przez tę hierarchię krok po kroku, aby dotrzeć do elementu, który chcemy edytować, w tym przypadku: Tekst w polu tekstowym. W praktyce oznacza to przechodzenie przez obiekty Presentation, Slide, PageElement i Shape, aż w końcu dotrzemy do obiektu TextRange, który chcemy edytować.

Śledzenie typu obiektu, z którym masz do czynienia, może być mylące, a błędy wynikające z próby zastosowania operacji do niewłaściwego obiektu mogą być trudne do rozwiązania. Niestety, funkcja pomocy i komunikaty o błędach w samym edytorze skryptów nie zawsze dostarczają tu wielu wskazówek, ponieważ taka uwaga przynajmniej poprawi twoje praktyki kontroli jakości.

Po utworzeniu prezentacji i zaktualizowaniu tytułu nadszedł czas, aby wstawić do niej jeden z naszych nowych wykresów. Mając na uwadze hierarchię obiektów, poniższy kod powinien mieć teraz sens:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Jeśli uruchomisz pełny skrypt, prezentacja wyjściowa powinna wyglądać mniej więcej tak:

Mamy nadzieję, że ten przykład ilustruje zasady i dostarcza inspiracji do rozpoczęcia własnych eksperymentów. Jeśli się nad tym zastanowisz, na pewno znajdziesz przynajmniej kilka przykładów ręcznej pracy wykonywanej dzisiaj w Twojej firmie, która naprawdę powinna zostać w ten sposób zautomatyzowana. Służą uwolnieniu czasu na myślenie, analizowanie i stosowanie osądów, zamiast na mechaniczne przenoszenie danych z jednego formatu i/lub miejsca do drugiego. Ulepszanie środowiska programistycznego Jak wspomniano wcześniej, wersja JavaScript obsługiwana w Google Apps Script jest stara, a funkcjonalność internetowego edytora skryptów jest bardzo ograniczona. Jeśli po prostu nagrywasz makro lub piszesz kilkadziesiąt linijek, tak naprawdę nie zauważysz. Jeśli jednak masz ambitne plany zautomatyzowania wszystkich aspektów tygodniowego lub miesięcznego raportowania lub chcesz zbudować wtyczki, z przyjemnością dowiesz się, że istnieje narzędzie wiersza poleceń, które pozwala programować w Twoim ulubionym środowisku programistycznym .

Jeśli jesteś na takim poziomie zaawansowania, prawdopodobnie będziesz chciał również skorzystać z najnowszych funkcji JavaScript, a potencjalnie nawet więcej, ponieważ za pomocą narzędzia wiersza poleceń możesz również programować w TypeScript.

Używanie Pythona do programowania Arkuszy Google

Jeśli okaże się, że praca z Apps Script nie jest twoją filiżanką herbaty, istnieją inne opcje, w zależności od przypadku użycia. Jeśli chcesz bardziej zaawansowane przetwarzanie liczb, łączyć się z API lub bazami danych lub po prostu preferować język programowania Python od JavaScript, to Colaboratory Google jest bezcennym produktem. Daje Ci notatnik Jupyter działający na serwerach Google, który umożliwia pisanie skryptów Pythona, które bezproblemowo integrują się z plikami na Dysku Google, a dzięki bibliotece „gspread” ułatwia pracę z danymi arkusza kalkulacyjnego.

Opisałem wiele zalet Pythona w artykule o tym, jak używać go w funkcjach finansowych, który służy również jako delikatne wprowadzenie do pracy z notatnikami Pythona i Jupyter w kontekście biznesowym i finansowym. Jedną z bardzo ważnych korzyści dla mnie jest to, że w przeciwieństwie do Apps Script, notatnik Pythona w Colaboratory jest interaktywny, więc widzisz wyniki (lub komunikat o błędzie) po wykonaniu każdego wiersza lub małego bloku kodu.

Automatyzacja uzależnia

Ten samouczek Google Apps Script pokazał, co jest możliwe dzięki językowi kodowania Google. Możliwości są praktycznie nieograniczone. Jeśli jednak nie masz wiedzy technicznej, przykłady kodu mogą wydawać się zniechęcające i możesz pomyśleć, że wzrost produktywności uzyskany dzięki nauce Google Apps Script może nie wystarczyć, aby zrównoważyć znaczną inwestycję pod względem potrzebnego czasu nauczyć się tego.

Zależy to oczywiście od wielu czynników, w tym od tego, jaką rolę pełnisz lub jaką masz pełnić w przyszłości. Ale nawet jeśli nie spodziewasz się, że zrobisz coś podobnego do przedstawionych tutaj przykładów, zrozumienie tego, co jest możliwe i mniej więcej ile pracy zajęłoby wdrożenie, może wywołać myśli i pomysły dotyczące tego, jak poprawić produktywność w Twojej firmie, na przykład swoich klientów lub siebie osobiście.

Osobiście mogę potwierdzić satysfakcję z siedzenia i naciśnięcia przycisku, który kończy godzinną żmudną ręczną pracę w niecałą minutę. Po zrobieniu tego po raz pięćdziesiąty będziesz wdzięczny za kilka godzin spędzonych na sklejaniu wszystkiego razem, co ostatecznie pozwoliło ci uwolnić czas na bardziej wartościowe zajęcia. Po pewnym czasie te korzyści skalowalności stają się uzależniające.