13. Lecke – Google Sheet összekötése Excellel

1. SpreadSheet riport importálása Excelben

Kezdjünk egyből egy példával: hozzunk létre egy lekérdezést, amiben a múlt hét hétfőtől vasárnapig szeretnénk látni a felhasználók megoszlását eszköz szerint. Ezt kell látnunk, persze más dátumokkal:

Futtassuk le a lekérdezést. Menjünk a menüben a Add-ons-ra, Google Analytics, majd Run reports. Felugrik egy popup, lefut a lekérdezés és egy új munkalapon ott látjuk a kész riportunkat.

A munkalapunk neve ugye az lett, amit a lekérdezés nevének megadtunk. Eddig remélem tiszta minden, most jönnek a trükkös részek.

1. Osszuk meg a SpreadSheet-ünket
Ahhoz, hogy az Excel később ki tudjon olvasni a Google Sheet táblázatunkból adatokat, hozzá kell tudnia férni. Éppen ezért a megosztási beállításnál, állítsuk be, hogy a “link birtokában bárki olvasni tudja a táblázatunkat. Erre több útvonal is van, talán a legegyszerűbb, ha SpreadSheet-ünk jobb felső sarkában a Megosztás / Share gombra kattintunk. Ott a felugró ablakban rámegyünk a Advanced / Haladó beállításokra, majd Change és beállítjuk, hogy a link birtokában bárki olvasni tudja.

2. Buheráljuk meg kicsit a riportunk URL-jét
Excelbe importálni fogjuk weblapként a riportot tartalmazó SpreadSheet munkalapunkat. Azonban mivel a böngésző ablakunkon belül a Google táblázatunknálnem csak maga a táblázat van, hanem egyéb, az importálás számára irreleváns információ (menüsor, ikonok, google account bejelentkezés, stb.), így ezektől meg kell szabadulnunk.

Menjünk a már lefuttatott munkalapunkra, és másoljuk ki az URL-t a böngészősávból. Ekkor valami ilyesmit kapunk:
https://docs.google.com/spreadsheets/d/1B_nRsTLaRxIFVnTELiWFQj8ffUkcAcpYkGOYwGty_es/edit#gid=1574993426

Az URL-ben van egy ilyen rész: edit#

Ezt kell kicserélnünk erre: gviz/tq?tqx=out:html&

Ha ez megvan, akkor a fenti példa URL-ből ez lesz:

https://docs.google.com/spreadsheets/d/1B_nRsTLaRxIFVnTELiWFQj8ffUkcAcpYkGOYwGty_es/gviz/tq?tqx=out:html&gid=1574993426

Egy szép kis html táblázat.

3. Nyissunk meg egy üres Excelt

A menüben menjünk az Adatok-ra, ott pedig kattintsunk a Weblap-ról gomba.

Ekkor felugrik egy ablak, ahol meg kell adnod a már előszerkesztett URL-ed. Innentől eléggé attól függ, hogy pontosan melyik évi Exceled is van.

2019-es verziónál így fog kinézni:

Ha minden rendben, akkor kattints a Betöltés vagy az Importálás gombra!

Ezennel már kijelenthetjünk, hogy Google Analytics adatok vannak az Exceledben. De ez még kevés nekünk, mert hát hol van itt az automatizálás?

Rendszeres riport létrehozása SpreadSheetben

4. Térjünk vissza a SpreadSheetünkbe

A bővítmény menüjében a riport futtatása pont alatt van a “Schedule riports” pont, amivel időzíteni tudjuk a lekérdezéseket. Például beállíthatjuk, hogy minden héten hétfőn reggel 7-8 között futtassa le a lekérdezésünket.

Ha ezt a beállítást elmentjük, akkor innentől kezdve minden héten hétfő reggel automatikusan lefut a lekérdezésünk, és a riportunk munkalapján új számok lesznek. Persze ez csak akkor, ha a beállítások munkalapon a kezdő és a vég dátumot dinamikusan adtuk meg. Azaz például a =MA() függvény ugye minden nap más értéket fog visszaadni, míg ha azt írom be a cellába, hogy 2018.01.01, akkor az minden nap 2018.01.01 lesz.

Ha jól felépítettünk egy lekérdezést, és megfelelően beállítottuk az időzített futtatását, akkor innentől kezdve gyakorlatilag nem kell foglalkozunk a SpreadSheet-tel.

5. Kell egy gomb Excelben, amit hetente nyomkodhatunk!

Térjünk is vissza Excelbe. A célunk most az, hogy hozzunk lére egy gombot, amit megnyomva frissíti a beimportált riportunkat. Mivel SpreadSheetben automatikusan minden hétfőn reggel 7-8 között frissül a riport, így az Excelünkben minden hétfő reggel 8 órától nyomhatnánk meg a gombunkat egészen a következő hét hétfő 7-ig, hogy az elmúlt hét Analyticses adataihoz jussunk.

Szóval a már ismert módon hozzunk létre egy új munkalapra egy gombot, amihez rendeljünk hozzá egy makrót. A makrónk egy végtelenül egyszerű kódból fog állni, minden egyes futtatáskor frissít mindent az adott Munkafüzetünkben (diagramot, pivot táblát, lekérdezést, stb.). Ebben pedig benne van az előző munkalapon már beimportált Weblapos Adatforrásunk. (Ami ugye a Google Sheet riportja).

Tehát a kódunk:

Sub Gomb1_Kattintás()
   ActiveWorkbook.RefreshAll
End Sub

Ezennel létrehoztunk egy Excelt, amiben gombnyomásra megjelennek az elmúlt hét Analyticses adatai.

Gyakorlati feladat

Kössünk össze a ma tanult módon az Excelt egy Google Sheet-es Analytics riporttal.

Összefoglalás

– Megtanultuk, hogyan tudunk rendszeres Analytics riportot lefuttatni a Google SpreadSheetben.
– Megnéztük, hogyan tudjuk Excelbe importálni ezt a lefuttatott SpreadSheet-es riportot
– Összességében létre tudunk már hozni egy olyan makrós Excelt, amiben ha hetente megnyomunk egy gombot, friss Analytics számok lesznek.

Mennyire találtad hasznosnak ezt cikket?
[Összes szavazat: 0 Átlag értékelés: 0]