Google Apps Script - wprowadzenie
Kontynuacja serii o obróbce danych. Jak poradzić sobie w sytuacji, kiedy żadna wtyczka czy program nie posiada niezbędnych dla nas funkcjonalności? Google Apps Script pozwala na pisanie własnych rozwiązań dla usług od Google.
Wstęp
Poprzednio zaprezentowałem działanie wtyczki Power Tools do Google SpreadSheet na konkretnym przykładzie: Google SpreadSheets i Power Tools - Jak wydzielić produkty główne z wariantów?
Pojawiła się tam pewna kontrowersja, mianowicie, wtyczka wymagała dość wielu uprawnień (w tym pozwolenie na przetwarzanie danych na osobnym serwerze), oraz po okresie próbnym należy wykupić subskrypcję (co nie jest tanie). Zrozumiałym jest więc, że dla niektórych osób są to czynniki dyskwalifikujące taką wtyczkę, z drugiej zaś strony oferuje ona na prawdę potężne możliwości. Czy trzeba jednak godzić się na takie warunki by ułatwić sobie pracę z arkuszami kalkulacyjnymi?
Google Apps Script
Rozwiązaniem jest mechanizm pisania własnych skryptów dla dokumentów na platformie Google Sheets. Jeżeli znacie Excelowy VBA - język w którym wiele osób próbowało napisać coś pożytecznego, a następnie poddawało się w połowie - to powiem wam, jest to jego lepsza i prostsza wersja.
Google Apps Script oferuje nam prosty edytor skryptów, które piszemy w języku opartym o JavaScript, wykonując je i przechowując w chmurze. Możliwości są ogromne - jak proste formatowanie tekstu, automatyzacja czynności, ale też budowanie sieci zależności z innymi aplikacjami google, korzystanie z bibliotek JS, implementacje dla zaawansowanej obróbki danych i jeszcze więcej.
Jeżeli chcesz w ogóle zacząć pisać skrypty w GAS, musisz przynajmniej znać podstawy JavaScript. Nie trzeba być mistrzem, co pokazuje przykład mojej osoby, więc nie ma się czego bać.
Google Apps Script udostępnia możliwość pisania samodzielnych skryptów, które działać mogą na wielu usługach Google (np. autoresponder dla Gmaila z funkcją dodawania załączników do Google Drive, zbieranie informacji o aktywności z kalendarza do arkusza Google Sheet, itd.), oraz takie które działają (zawierają się) w obrębie konkretnego pliku (np. formatują tekst pliku Google Docs, obrabiają dane w arkuszu, dodają nowe pozycje i funkcje w menu).
Warto pamiętać, że udostępniając dokumenty z zawartym skryptem, jego kod źródłowy będzie dostępny również dla osoby przeglądającej owy dokument - należy więc uważać, jeżeli chcemy udostępnić jedynie treść dokumentu (bez skryptu).
Skrypty samodzielne tworzymy przechodząc na stronę script.google.com, jednak im poświęcę czas innym razem. Teraz skupię się na tym drugim typie, konkretnie przedstawię jak napisać prosty skrypt dla arkusza kalkulacyjnego. Oczywiście analogicznie możemy pisać skrypty dla Google Docs czy innych aplikacji z pakietu Google Sheets.
Pierwszy skrypt (Google SpreadSheets)
Zaczynamy od otworzenia pliku Google SpreadSheets i stworzenia dla niego skryptu.
Przejdź do Tools > Script Editor - powinien otworzyć się edytor. To tutaj będziemy pisać nasz kod. Przykładowo, najprostsza funkcja wyświetlająca alert z napisem “Cześć”.
function func() {
Browser.msgBox("Cześć");
}Ok, mamy funkcję, tylko jak ją teraz wywołać? Jest kilka sposobów - my skorzystamy z możliwości dodawania elementów do menu, aby można było wywołać ją z poziomu interfejsu Google SpreadSheets.
// jest to funkcja która uruchamia się wraz z otwarciem pliku
// należy do tzw. triggerów, które reagują na pewne działania
// czyli wykonują się w konkretnej sytuacji
function onOpen() {
// Odwołujemy się do naszego arkusza i jego metody
// dla pobrania całego UI
SpreadsheetApp.getUi()
.createMenu('Nasz dodatek') // Tworzymy nową pozycję w głównym menu
.addItem('Nasza funkcja', 'func') // Dodajemy opcję która uruchomi wskazaną w drugim parametrze funkcję
.addToUi();
}
function func() {
Browser.msgBox("Cześć");
}W tym momencie za każdym razem gdy otworzymy nasz dokument, zostanie dodane do menu element Nasz dodatek który posiadać będzie opcję Nasza funkcja po wybraniu której uruchomi się funkcja func() naszego skryptu. Pamiętajmy o tym, że trigger nastawiony jest na otwarcie pliku, więc jeżeli nie odświeżymy / otworzymy ponownie naszego dokumentu, to nie będziemy widzieć nowej pozycji w menu.
Listę triggerów można znaleźć w dokumentacji: Simple Triggers - Google Apps Script
Możemy również skorzystać z możliwości uruchamiania funkcji z edytora, wystarczy że w menu wybierzemy funkcję onOpen() i klikniemy strzałkę.
Dzięki opcji uruchamiania pojedynczych funkcji możemy sprawdzać działanie poszczególnych z nich. Wróćmy do naszego arkusza, powinna teraz pojawić się nowa pozycja w menu, którą możemy przetestować.
Istotna uwaga! (Bezpieczeństwo)
Google pilnuje nas, abyśmy nie uruchamiali skryptów, które nie zostały zweryfikowane. Nasz skrypt też nie został zweryfikowany, co za tym idzie, wyświetli się nam ostrzeżenie - które musimy ominąć jeżeli wiemy co robimy i co uruchamiamy (jeżeli to nasz skrypt i wiemy co tam jest, to wyrażamy zgodę na odpalenie).
Praktyczne przykłady
Wiem, że przykład wyżej nie urywa dolnych części ciała, dlatego postaram się zrehabilitować i zaprezentować kilka skryptów, które są praktyczne i rzeczywiście mogą do czegoś się przydać
Komentarz z datą edycji komórki
Pracując na jednym arkuszu z wieloma osobami, warto czasami wiedzieć czy przypadkiem coś się nie zmieniło (np. ktoś zaktualizował ceny produktów). Kod na Github
Wydzielanie wierszy z unikalnym wystąpieniem wartości w danym zakresie
Skrypt tworzy nowy arkusz i kopiuje do niego nagłówek (pierwszą linię arkusza na którym działamy). Następnie wyszukuje w zaznaczonym zakresie (zaznaczamy tylko jedną kolumnę!) pierwsze wystąpienia wartości (to znaczy, że duplikaty nie zostaną skopiowane) i kopiuje je do tego nowo stworzonego arkusza. Jest to moje rozwiązanie problemu opisanego w poscie o wydzielaniu produktów głównych. Kod na Github
Szukanie duplikatów (procentowo)
W zasadzie ten przykład to taki Proof of Concept. Nie nadaje się on zbytnio do realnej pracy z danymi, chociaż przy bardzo małych arkuszach i niewielkiej długości tekstach można się nim pobawić (problemy z optymalizacją). Najistotniejszym elementem w tym przykładzie jest fakt, że możemy korzystać z zewnętrznych bibliotek napisanych w JS!
Skrypt ma za zadanie odnajdowanie duplikatów, a raczej podobnych do siebie w określonym stopniu (wyrażonym procentowo) tekstów.
Wystarczy wkleić zawartość biblioteki difflib-browser do projektu skryptu jako plik difflib.gs a właściwy skrypt w drugim pliku np. find_uniques.gs. Kod na Github
Podsumowanie
Po więcej informacji zapraszam do oficjalnej dokumentacji: Dokumentacja Google Apps Script
Zapraszam wszystkich do komentowania, szczególnie zachęcam do opisywania swoich problemów - postaram się opisać ich rozwiązania w następnych wpisach.
