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”.
Pobieranie danych z Arkuszy Google do Excela
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

Pobieranie danych z Arkuszy Google do Excela
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.
Pobieranie danych z Arkuszy Google do Excela
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.

Pobieranie danych z Arkuszy Google do Excela

I odświeżmy tabelkę w Excelu.

Pobieranie danych z Arkuszy Google do Excela

 

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

Pobieranie danych z Arkuszy Google do Excela

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.



1 gwiazdka2 gwiazdki3 gwiazdki4 gwiazdki5 gwiazdek (4 głosów, średnia: 3,75 z 5)
Loading...


Powiązane

  1. Dzięki za sposób na zaciaganie excela do docs. Do excela nie zaciągają się jednak grafiki umieszczone w komórkach za pomocą =image(link) lub poprostu wklejone w komórce docs. Jest na to jakaś metoda?

    1. Nigdy nie pobierałem tym sposobem zdjęć, ale Power Query raczej sobie z tym nie poradzi. PQ pobiera wartości wpisane w komórki, w przypadku zdjęć prawdopodobnie zaczyta puste pola.

  2. Norbert

    MI nie chce pobrać, w momencie w którym jest podglad tabeli pokazuje jakąś randomową, ale jak sprawdzę podgląd strony pokazuje sie strona do logowania do arkuszy google, jak to ominąć?

    1. Jeśli nie chcesz podawać poświadczeń do logowania to prawdopodobnie potrzebna będzie zmiana ustawień Twojego arkusza google na „Każdy użytkownik internetu mający ten link może wyświetlać”. Wtedy połaczenie powinno zostać nawiązane bez logowania do konta google.

  3. Tomek

    Świetny materiał, choć nie do końca tego szukałem. Brakuje mi podobnej funkcjonalności, której chyba próżno szukać w arkuszach google – importu danych ze strony web do arkusza google. Oglądnąłem kilka filmików instruktażowych w temacie BigQuery, ale to chyba jeszcze nie jest to. Możesz to skomentować?

    1. Akurat z BigQuery nie miałem niestety większej styczności.

Komentarze