Excel: Google Drive-on lévő táblázat cellatartalmának kiolvasása

Képzelje el a helyzetet: van egy projektje, ahol az adatok egy része Google Drive-on, pontosabban egy Google Sheets (korábbi nevén Google Táblázatok) fájlban tárolódik. Ezt az adatot azonban egy Excel munkafüzetben szeretné feldolgozni, elemezni, vagy éppen egy dinamikus jelentésbe beilleszteni. A manuális másolás-beillesztés nemcsak időigényes, de hibalehetőségeket is rejt, ráadásul nem nyújt megoldást a folyamatosan frissülő adatok kezelésére. A jó hír az, hogy az Excel kiváló eszközöket kínál a Google Drive-on lévő táblázatok cellatartalmának kiolvasásához és automatikus frissítéséhez. Ebben az átfogó cikkben bemutatjuk a leggyakoribb és leghatékonyabb módszereket, hogy Ön is profi módon kezelhesse a külső adatforrásokat.

Miért érdemes az Excelt és a Google Sheets-et összekapcsolni?

Az Excel továbbra is a piacvezető táblázatkezelő program az összetett adatelemzésekhez, vizualizációkhoz és makró alapú automatizáláshoz. A Google Sheets viszont kiválóan alkalmas kollaboratív munkára, online hozzáférésre és egyszerűbb adatrögzítésre. Amikor ez a két világ találkozik, egy rendkívül erőteljes adatelemző környezet jön létre. Képes lesz valós idejű adatokra épülő dashboardokat létrehozni Excelben, anélkül, hogy manuálisan kellene szinkronizálnia a forrásfájlt. Ez nemcsak időt takarít meg, de jelentősen növeli az adatok pontosságát és aktualitását.

1. módszer: Adatok közzététele a weben (CSV/TSV) – A legegyszerűbb út

Ez a módszer a leggyorsabb és legegyszerűbb, ha statikus adatokra van szüksége, vagy ha a Google Sheets táblázatot csak korlátozottan, például havonta egyszer frissíti. Lényegében nyilvános webes linkké alakítja a táblázatot, amelyet az Excel be tud olvasni.

Lépések Google Sheets-ben:

1. Nyissa meg a Google Sheets táblázatot, amelynek tartalmát Excelbe szeretné importálni.
2. Kattintson a „Fájl” menüre, majd válassza a „Megosztás” almenüből a „Közzététel a weben” opciót.
3. Megjelenik egy párbeszédpanel. Itt kiválaszthatja, hogy az egész dokumentumot vagy csak egy adott lapot szeretné-e közzétenni.
4. A „Közzététel mint” legördülő menüből válassza ki a „Vesszővel elválasztott értékek (.csv)” vagy „Tabulátorral elválasztott értékek (.tsv)” opciót. A CSV a legelterjedtebb.
5. Kattintson a „Közzététel” gombra. A Google Sheets ekkor megad egy URL-t, amelyet másoljon ki. Ez az URL lesz az adatforrás az Excel számára.

Lépések Excelben:

1. Nyissa meg az Excel munkafüzetet, ahová az adatokat importálni szeretné.
2. Lépjen az „Adatok” fülre a menüszalagon.
3. A „Adatok lekérése és átalakítása” csoportban kattintson az „Adatok lekérése” menüre, majd válassza a „Webről” opciót. (Régebbi Excel verziókban ez „Webes lekérdezés” vagy „Adatok webhelyről” néven futhat).
4. A megjelenő ablakba illessze be a Google Sheets-ből kimásolt URL-t.
5. Kattintson az „OK” gombra.
6. Megjelenik egy „Navigátor” ablak. Itt az Excel megpróbálja értelmezni a webes tartalmat. Mivel CSV-t adtunk meg, általában egyetlen táblázatot fog ajánlani. Válassza ki a megfelelő táblázatot (általában „Document” vagy hasonló néven jelenik meg).
7. Kattintson a „Betöltés” gombra az adatok közvetlen importálásához, vagy a „Adatok átalakítása” gombra, ha a Power Query szerkesztőben további tisztítást vagy átalakítást szeretne végezni (pl. oszlopok átnevezése, adattípusok beállítása).

Előnyök és hátrányok:

• Előnyök: Rendkívül egyszerű és gyors beállítás. Nem igényel különösebb technikai tudást.
• Hátrányok: Az adatok nem „élőek”. Ha a Google Sheets fájl frissül, az Excelben csak manuális frissítéssel (vagy beállított automatikus frissítéssel, de az is a közzétett állapotot tükrözi) tudja lekérni az új adatokat. A közzététel miatt az adatok elvileg bárki számára elérhetővé válnak, aki ismeri a linket, ami biztonsági kockázatot jelenthet.

2. módszer: Power Query – A modern és rugalmas megoldás

A Power Query (más néven Get & Transform Data) az Excel beépített, rendkívül erős adatimportáló és adatátalakító eszköze. Ez a módszer sokkal rugalmasabb, és bár a Google Sheets-nek ugyanúgy nyilvánosan elérhetőnek kell lennie, a Power Query képességei messze felülmúlják az egyszerű webes lekérdezést.

Előkészületek Google Sheets-ben:

Ahhoz, hogy a Power Query be tudja olvasni a Google Sheets táblázatot, azt „megosztás linkkel” opcióval elérhetővé kell tenni. Győződjön meg róla, hogy a megosztási beállítások a „Link birtokában bárki” opcióra vannak állítva, legalábbis „Megtekintő” joggal. (Figyelem: ez azt jelenti, hogy bárki, akinek megvan a link, láthatja az adatokat. Ne használja érzékeny adatokhoz!)

1. Nyissa meg a Google Sheets táblázatot.
2. Kattintson a jobb felső sarokban a „Megosztás” gombra.
3. A „Link lekérése” részben válassza a „Link birtokában bárki” opciót. Győződjön meg róla, hogy a jog „Megtekintő”.
4. Másolja ki a linket. Ez a link fogja tartalmazni a Google Sheets fájl egyedi azonosítóját (ID).

Lépések Excelben Power Queryvel:

A Power Query nem tudja közvetlenül beolvasni a normál Google Sheets megtekintési linket. Egy speciális formátumú linkre van szükségünk, amely letölthető CSV formátumban. A kimásolt linkből (ami így néz ki: https://docs.google.com/spreadsheets/d/[AZ_ON_GOOGLE_SHEETS_ID]/edit?usp=sharing) ki kell szednünk az egyedi azonosítót (a d/ és /edit? közötti rész), majd a következő formátumba kell illesztenünk:

https://docs.google.com/spreadsheets/d/[AZ_ON_GOOGLE_SHEETS_ID]/export?format=csv&gid=[LAP_AZONOSÍTÓJA]

• A [AZ_ON_GOOGLE_SHEETS_ID] helyére illessze be a kimásolt azonosítót.
• A [LAP_AZONOSÍTÓJA] opcionális, ha csak egy lapról van szó, vagy az első lapról. Ha több lapja van, és nem az elsőt szeretné, akkor meg kell találnia a lap azonosítóját (gid). Ezt a Google Sheets URL-jében találja meg a böngésző címsorában, amikor az adott lapra kattint: .../edit#gid=123456789. Ezt a számot illessze be.

Tehát, ha a Google Sheets fájl ID-je 1A2B3C4D5E6F7G8H9I0J és az első lapról van szó, a linkje így fog kinézni:

https://docs.google.com/spreadsheets/d/1A2B3C4D5E6F7G8H9I0J/export?format=csv&gid=0 (a gid=0 az első lapra utal).

A Power Query használata Excelben:

1. Nyissa meg az Excel munkafüzetet.
2. Lépjen az „Adatok” fülre a menüszalagon.
3. Kattintson az „Adatok lekérése” menüre, majd válassza a „Webről” opciót.
4. A megjelenő ablakba illessze be az előzőleg összeállított, speciális CSV export linket.
5. Kattintson az „OK” gombra.
6. A következő ablakban, a hitelesítési mód kiválasztásakor, ha az adatok publikusan elérhetőek, válassza az „Névtelen” opciót.
7. Kattintson az „Csatlakozás” gombra.
8. Megjelenik a „Navigátor” ablak. Itt az Excel megpróbálja táblázatként értelmezni a CSV-t. Válassza ki a megfelelő táblázatot.
9. Kattintson az „Adatok átalakítása” gombra. Ez megnyitja a Power Query szerkesztőt.
10. A Power Query szerkesztőben számos műveletet végezhet az adatokon:
    • Fejlécek előléptetése: Győződjön meg róla, hogy az első sor az oszlopfejléceket tartalmazza. Ha nem, használja a „Kezdőlap” fülön a „Első sor használata fejlécként” opciót.
    • Adattípusok beállítása: A Power Query megpróbálja automatikusan felismerni az adattípusokat (szám, szöveg, dátum stb.), de érdemes manuálisan is ellenőrizni és korrigálni ezeket.
    • Oszlopok eltávolítása/átrendezése: Csak a szükséges oszlopokat tartsa meg.
    • Szűrés, rendezés, oszlopok felosztása: Szinte bármilyen adatkezelési műveletet elvégezhet.
11. Amikor elégedett az adatokkal, kattintson a „Bezárás és betöltés” gombra (vagy „Bezárás és betöltés ide…”) a „Kezdőlap” fülön. Az adatok ekkor betöltődnek egy új Excel munkalapra.

Adatok frissítése:

A Power Queryvel létrehozott adatkapcsolat rendkívül dinamikus. Ha a Google Sheets-ben lévő forrásadatok megváltoznak, egyszerűen kattintson az Excelben az importált táblázaton belül a jobb egérgombbal, és válassza a „Frissítés” opciót, vagy az „Adatok” fülön a „Mind frissítése” gombot. Az Excel újra lekérdezi az adatokat a Google Sheets-ből, és alkalmazza az összes Power Queryben beállított átalakítást.

Előnyök és hátrányok:

• Előnyök: Rendkívül rugalmas és automatizálható (Power Queryen belüli lépések). Az adatok frissítése egyszerű. Lehetőséget ad az adatok előzetes tisztítására és átalakítására az Excelbe való betöltés előtt.
• Hátrányok: A Google Sheets fájlnak továbbra is nyilvánosan elérhetőnek kell lennie a link birtokában, ami biztonsági kockázatokat rejt. A speciális link felépítése eleinte bonyolultnak tűnhet.

3. módszer: Haladó megoldások – Google Sheets API és VBA

Ez a módszer a legösszetettebb, és programozási ismereteket igényel (VBA – Visual Basic for Applications az Excelben, valamint a Google Sheets API ismerete). Ez a megoldás lehetővé teszi a legfinomabb vezérlést az adatimportálás felett, például hitelesítést igénylő táblázatok elérését, specifikus cellatartományok lekérdezését, vagy akár adatok visszaírását is a Google Sheets-be (bár ez utóbbi már túlmutat a cikk témáján).

A lényeg, hogy a Google Sheets API (alkalmazásprogramozási felület) használatával az Excel VBA kódon keresztül közvetlenül kommunikálhat a Google szervereivel. Ez megkerüli a „nyilvános közzététel” szükségességét, és sokkal biztonságosabb, hitelesített hozzáférést biztosít. Ez a módszer már egy komplexebb fejlesztési feladat, de a legdinamikusabb és legbiztonságosabb adatkapcsolatot kínálja.

Fontos szempontok és tippek

• Biztonság: Soha ne tegyen közzé a weben vagy ne osszon meg „Link birtokában bárki” opcióval érzékeny, személyes, vagy üzleti titkot tartalmazó adatokat! Ha az adatok bizalmasak, fontolja meg a harmadik módszert, vagy használjon erre a célra fejlesztett, biztonságos adatkapcsolati megoldásokat.
• Adatstruktúra: Mind a Power Query, mind a webes lekérdezés a táblázatos adatokat szereti. Győződjön meg róla, hogy a Google Sheets táblázata tiszta, rendezett, nincsenek üres sorok vagy oszlopok a fejléc és az adatok között, és minden oszlopban azonos típusú adatok vannak.
• Frissítési gyakoriság: Az Excelben beállíthatja, hogy az adatok automatikusan frissüljenek bizonyos időközönként (pl. 5 percenként, munkafüzet megnyitásakor). Ezt az „Adatok” fülön a „Lekérdezések és kapcsolatok” panelen a kapcsolat tulajdonságai között teheti meg.
• Hibaelhárítás: Ha az adatimportálás nem sikerül, ellenőrizze újra a Google Sheets megosztási beállításait, a link pontosságát, és győződjön meg róla, hogy nincs-e tűzfal vagy proxy beállítás, ami blokkolja az Excel hozzáférését a külső webes forráshoz.

Összegzés

Az Excel és a Google Sheets közötti adatimportálás képessége rendkívül hasznos a modern adatelemzésben. A legegyszerűbb, gyors, de statikus megoldástól (webes közzététel) a rendkívül rugalmas és dinamikus Power Query-n át egészen a testreszabható, de komplex API alapú megközelítésig számos opció áll rendelkezésére. A legtöbb felhasználó számára a Power Query kínálja a legjobb egyensúlyt az egyszerűség, a rugalmasság és az adatfrissítés lehetősége között. Válassza ki az igényeinek és adatainak érzékenységének leginkább megfelelő módszert, és tegye hatékonyabbá az adatelemzési munkafolyamatait!