Pobieranie danych z Arkuszy Google do Excela

Arkusze Google, ze względu na możliwość jednoczesnej pracy kilku użytkowników, są bardzo popularnym narzędziem do onlineowego gromadzenia danych. Mimo wszystko funkcjonalności Arkuszy Google nie są tak rozbudowane jak Excela. Mnie osobiście bardzo drażni przestarzała wstążka, toporność w działaniu oraz brak większości funkcjonalności znanych z Excela. Natomiast na plus Arkuszy Google na pewno przemawia możliwość pracy w jednym arkuszu przez wiele osób i dzięki temu łatwa możliwość gromadzenia danych od wielu użytkowników. Często dane zebrane właśnie w ten sposób muszą być później importowane i analizowane już w samym Excelu stacjonarnym. Opiszę jak zautomatyzować pobieranie danych z Arkuszy Google do arkuszy Excela. W tym celu utworzymy dynamiczną excelową tabelę odświeżalną połączoną z Arkuszem w Googlach. Dzięki tej metodzie, odświeżając naszą tabelę w Excelu, na bieżąco będziemy pobierać aktualne dane z Arkusza Google. Wykorzystam do tego jedno z moich ulubionych narzędzi – Power Query dostępne w Excelu na karcie Dane.

Tabela w Arkuszach Google z danymi źródłowymi

Źródło danych Arkuszy Google znajduje się pod linkiem, którym możemy podzielić się z innymi osobami. Kod zawarty w linku jest na stałe połączony z danym Arkuszem Google więc nie będzie zmieniał się z upływem czasu i kolejnymi edycjami tabelki. Testowy skoroszyt zawiera dane dotyczące historycznej sprzedaży samochodów wybranych marek jest dostępny pod tym linkiem.
https://docs.google.com/spreadsheets/d/18nH53ZK04K5JxpoQOfb96V5dH93eP301vddsI6BDNDs/edit#gid=0

Pobranie danych z Arkuszy Google do Excela

Na karcie „Dane” w sekcji „Pobieranie i przekształcanie” wybieramy opcję „Nowe zapytanie”. Następnie wybieramy „Z innych źródeł” i „Z sieci Web”.

W okienku, w polu przeznaczonym na adres URL wklejamy nasz link skopiowany z paska przeglądarki i odnoszący się do naszego Arkusza Google. Teraz ważna rzecz – link musimy nieco zmodyfikować. Jego końcówka wygląda w ten sposób:
…BDNDs/edit#gid=0

Tą ostatnią część, za ostatnim ukosnikiem, zmieniamy na:
…BDNDs/export?format=xlsx

Zatem cały link powinien wyglądać w ten sposób:
https://docs.google.com/spreadsheets/d/18nH53ZK04K5JxpoQOfb96V5dH93eP301vddsI6BDNDs/export?format=xlsx

Co ciekawe, ten link już sam w sobie umożliwia pobranie danych do Excela stacjonarnego. Wystarczy wkleić go do przeglądarki www i zapisać na dysku plik .xlsx. Problem jednak polega na tym, że będą to dane statyczne, czyli takie które aktualnie są zapisane w Arkuszu Google w momencie ich pobierania. Nam zależy na w pełni dynamicznym rozwiązaniu, które ciągle będzie pobierać najświeższe, dopisywane dane.

Wybór lokalizacji danych wewnątrz Arkusza Google

Po zatwierdzeniu okienka z linkiem przechodzimy do nawigatora, w którym wybieramy dokładną lokalizację danych wewnątrz Arkusza Google. Tutaj możemy też podejrzeć układ skoroszytu w celu wyboru arkusza z interesującymi nas danymi. W naszym przypadku całość znajduje się w „Arkusz1”, którego zawartość dokładnie widać na podglądzie danych.

Power Query oferuje szeroką możliwość przekształcenia pobranych danych. W typ przypadku nie będziemy ich dodatkowo przetwarzać dlatego zatwierdzamy klikając w „Załaduj”. Gdybyśmy chcieli je dodatkowo przekształcać to należałoby wybrać przycisk „Przekształć dane”.
Jeśli chcesz dowiedzieć się więcej na temat samego przekształcania danych w Power Query odsyłam do innych moich wpisów z tej dziedziny. W szczególności polecam wpis, w którym opisywałem jak w PQ przekształcamy skonsolidowane dane. Tabelki w takiej formie, również mogą być przechowywane w Arkuszach Google. Tutaj przeczytasz więcej na ten temat – kliknij.

Po zatwierdzeniu poprzedniego okienka dane będą ładowane przez kilka sekund. Po zakończeniu tego procesu ujrzymy tabelę excelową zawierającą dane pobrane z Arkusza Google. Wynik będzie widoczny w formie tabeli odświeżalnej. Tak przygotowane dane możemy dalej analizować i prezentować na wykresach i dashboardach z wykorzystaniem nieograniczonych możliwości Excela stacjonarnego.

Sprawdzenie połączenia Excela z Arkuszem Google

Zbudowaliśmy odświeżalne połączenie, które tworzymy tylko raz. Potem tylko je odświeżamy. Pora zatem przetestować połączenie tabelki excelowej ze źródłem online. W Arkuszu Google dodajmy wiersze ze sprzedażą kolejnych dwóch modeli samochodów.

I odświeżmy tabelkę w Excelu.

Excel bardzo szybko zaczytuje nowe dane, które pojawiają się na końcu tabelki, analogicznie jak w arkuszu źródłowym.

Podsumowanie

Power Query to genialne narzędzie ETL, którego znajomość wydaje się być coraz bardziej niezbędna dla użytkowników Excela. Kliknij tutaj aby zapoznać się z moimi pozostałymi  wpisami na jego temat. Z pomocą Power Query m. in. dynamicznie pobierzmy i przekształcimy dane pochodzące z najróżniejszych źródeł zewnętrznych. Jednym z nich są Arkusze Google, które są szeroko stosowane do gromadzenia danych w jednym czasie i przez wielu użytkowników. Arkusze Google nie nadają się jednak do dalszej obróbki, analizy i prezentacji danych. Tutaj niezawodnym narzędziem pozostaje stacjonarna wersja arkuszy kalkulacyjnych. Istotne jest jednak, aby dane w stacjonarnym arkuszu odpowiadały bieżącej zawartości Arkuszy Google. Z tym świetnie poradzi sobie wspomniane Power Query umożliwiający pobieranie aktualnych danych z Arkuszy Google do Excela jednym kliknięciem.