Daten in Google Sheets mit Python analysieren: Die Schritt-für-Schritt-Anleitung

3. Eine Verbindung einrichten

Klicke im Workbook auf View > Databases und dann auf das +-Symbol. Dir wird eine Übersicht aller Datenbanktechnologien angezeigt, zu denen du mit DataLab verbinden kannst:

Wähle Google Sheets. Jetzt wirst du aufgefordert, DataCamp die Berechtigung zum Zugriff auf Dateien in deinem Google Drive zu erteilen.

Klicke auf „Berechtigung für Google Drive erteilen“. Anschließend erscheint ein Dateiauswahlfenster:

Wähle die Spreadsheet-Datei aus, aus der du Daten lesen möchtest. Das ist entweder die Kopie des bereitgestellten Beispiel-Sheets oder dein eigenes Google Sheet.

4. Die Google-Sheets-Datei abfragen

Jetzt kannst du die verbundene Google-Sheets-Datei abfragen! Das machst du mit einer SQL-Zelle – einem Baustein in DataLab, um Datenbanken mit SQL zu queryen. Wenn du ein Workbook aus der Vorlage erstellt hast, ist diese SQL-Zelle bereits enthalten. Wenn du bei null gestartet bist, fügst du sie über den Launcher hinzu und klickst auf „SQL“:

Klicke in der SQL-Zelle oben links auf das Source-Dropdown.

Wähle das Spreadsheet aus, das du analysieren möchtest (im Beispiel „Unicorn Companies“). Schreibe dann folgende SQL-Query:

SELECT * FROM unicorn_companies

Wenn du auf Run klickst, holt diese Abfrage alle Zeilen aus dem Sheet unicorn_companies innerhalb des Google Sheets „Unicorn Companies“ und speichert das Ergebnis in einem neuen pandas DataFrame df:

Ein pandas DataFrame ist eine tabellenähnliche Datenstruktur, mit der du in Python Daten speichern und bearbeiten kannst. Füge eine Python-Zelle hinzu und führe folgenden Code aus, der die Median-Finanzierung der Unicorn-Unternehmen im Datensatz je Land berechnet:

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

Oben haben wir eine einfache SQL-Query genutzt, um sämtliche Daten zu laden. Du kannst aber genauso gut eine präzisere Abfrage formulieren, zum Beispiel die zehn wertvollsten Unicorns in den Vereinigten Staaten:

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10

Das war’s schon! Weitere Profi-Tipps, wie du die SQL-Query feiner abstimmen kannst, etwa um nur einen bestimmten Bereich eines Sheets zu laden, findest du im entsprechenden DataLab-Dokumentationsartikel.

Wie eingangs erwähnt, lässt sich mit dieser nahtlosen Verbindung zu Google Sheets nur lesen. Wenn du Daten sowohl lesen als auch Ergebnisse zurück in Google Sheets schreiben möchtest, findest du im nächsten Abschnitt die Anleitung über die Google Sheets API – für maximale Flexibilität.

Mit der Google Sheets API

Wähle diese Option, wenn du Daten aus einem Google Sheet lesen und die Ergebnisse deiner Python-Berechnungen in dasselbe oder ein anderes Google Sheet zurückschreiben willst.

Wie in der Einleitung erwähnt, erkaufst du dir die Flexibilität mit etwas mehr Setup. Konkret gehen wir diese Schritte:

• Ein Google-Servicekonto konfigurieren
• Ein Google-Spreadsheet mit Daten anlegen
• Ein neues Workbook erstellen
• Servicekonto-Zugangsdaten in DataLab hinterlegen
• Google-Sheets-Daten lesen
• Daten in Python analysieren
• Ergebnisse in Google Sheets zurückschreiben

Schauen wir uns jeden Schritt im Detail an.

1. Ein Google-Servicekonto konfigurieren

Um programmgesteuert auf Daten in Google Sheets zuzugreifen, brauchst du ein Google-Servicekonto. Das ist ein spezieller Kontotyp, den Programme nutzen können, um auf Google-Ressourcen wie ein Spreadsheet zuzugreifen. Dieses Servicekonto verwendest du dann, um DataLab mit Google Sheets zu verbinden.

Du musst das Servicekonto pro Google-Konto nur einmal einrichten. Wenn du später wieder mit Spreadsheets im selben Konto arbeitest, kannst du diesen Schritt überspringen.

Folge den Schritten unten, um das Servicekonto zu erstellen und die nötigen Zugangsdaten zu generieren.

• Stelle sicher, dass du mit deinem Google-Konto angemeldet bist.
• Wechsle zur Google API Library
• Erstelle ein neues Projekt über das Dropdown in der Navigationsleiste.
• Suche nach der „Google Sheets API“ und aktiviere sie. Das kann bis zu 10 Sekunden dauern.
• Gehe in der linken Navigation unter „APIs und Dienste“ zum Tab „Anmeldedaten“.
• Klicke auf „+ ANMELDEDATEN ERSTELLEN“ und wähle „Servicekonto“.
• Gib im ersten Schritt (Servicedetails) einen Namen ein, z. B. „gsheet-operator“, und klicke auf „Erstellen und fortfahren“.
• Wähle im zweiten Schritt die Rolle „Owner“ und klicke auf „Weiter“.
• Im dritten Schritt nichts ändern und auf „Fertig“ klicken.
• Zurück auf der Seite „Anmeldedaten“ klickst du auf das eben erstellte Servicekonto.
• Wechsle zum Tab „Schlüssel“, klicke „Schlüssel hinzufügen > Neuen Schlüssel erstellen“.
• Wähle „JSON“ und klicke „Erstellen“. Die JSON-Datei mit deinen Servicekonto-Zugangsdaten wird automatisch heruntergeladen.

Jetzt hast du ein Servicekonto und eine JSON-Credentials-Datei! Öffne den Download-Ordner (oder den Speicherort der Datei), öffne die JSON-Datei und sieh sie dir an. Sie sollte in etwa so aussehen:

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "gsheets-operator@steam-verve-386214.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}

Darin findest du das Feld „client_email“: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Kopiere diese E-Mail in die Zwischenablage – du brauchst sie im nächsten Schritt.

2. Ein Google-Spreadsheet mit Daten anlegen

Bevor du Daten in Spreadsheets analysieren kannst, brauchst du ein Spreadsheet mit Daten. Falls du kein Dataset parat hast, kannst du mit einem Google Sheet starten, das wir für dieses Tutorial vorbereitet haben: Öffne das Beispiel-Spreadsheet und klicke in Google Sheets auf „Datei > Kopie erstellen“, gib einen Namen ein und klicke „Kopie erstellen“. Wenn du bereits ein Spreadsheet mit passenden Daten hast, öffne es einfach.

Egal ob du mit einer Kopie des Beispiel-Spreadsheets oder deinem eigenen arbeitest: Du musst dem im ersten Schritt erstellten Google-Servicekonto Zugriff auf das Spreadsheet geben:

• Klicke auf „Freigeben“.
• Füge die E-Mail des Servicekontos aus der Zwischenablage als Bearbeiter zum Spreadsheet hinzu (z. B. gsheets-operator@<google-project-name>.iam.gserviceaccount.com).
• Klicke auf „Senden“.

Servicekonto: check. Google-Spreadsheet mit den richtigen Rechten: check. Zeit für etwas Python-Code!

3. Ein neues Workbook erstellen

Als Nächstes erstellst du in DataLab ein neues Datenprojekt, also ein Workbook. Du kannst entweder ein leeres Workbook von Grund auf anlegen oder ein Workbook mit komplettem Code erstellen.

4. Servicekonto-Zugangsdaten in DataLab speichern

Damit du die JSON-Datei mit den Servicekonto-Zugangsdaten in deinem neu erstellten Workbook nutzen kannst, musst du sie in DataLab hinterlegen.

Du könntest den JSON-Inhalt in eine Codezelle deines Workbooks kopieren und einfügen:

Das ist jedoch unsicher und nicht zu empfehlen. Wenn du dein Notebook teilst, sind die Zugangsdaten im Klartext sichtbar. Andere könnten dich damit imitieren und auf Ressourcen zugreifen, für die das Servicekonto Rechte hat.

Außerdem müsstest du diesen Code in jedem Workbook wiederholen, das auf Spreadsheet-Daten zugreifen soll. Wenn du die Zugangsdaten rotieren willst, müsstest du alle Workbooks einzeln anpassen.

Sicherer und skalierbarer ist es, die JSON in einer sogenannten Umgebungsvariable zu speichern. Wenn du diese Umgebungsvariable verbindest, steht sie in deiner Python-Session zur Verfügung. In deinem Workbook:

• Klicke links auf den Tab „Environment“.
• Klicke auf das Plus-Symbol neben „Environment Variables“.
• Im Modal „Add Environment Variables“:
  • Setze „Name“ auf GOOGLE_JSON.
  • Setze „Value“ auf den vollständigen Inhalt der heruntergeladenen Servicekonto-JSON. Öffne die Datei, markiere alles, kopiere es in die Zwischenablage und füge es im Value-Feld ein.
  • Setze „Environment Variables Set Name“ auf „Google Service Account“ (der Name ist frei wählbar).

Nach dem Ausfüllen klicke auf „Create“, „Next“ und schließlich „Connect“. Deine DataLab-Session startet neu, und GOOGLE_JSON steht dir nun als Umgebungsvariable im Workbook zur Verfügung. Du kannst das mit einer Python-Zelle prüfen:

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)

Wenn du dieselben Zugangsdaten in einem anderen Workbook wiederverwenden willst, musst du die Umgebungsvariable nicht erneut einrichten: Du kannst sie in anderen Workbooks erneut verbinden.

5. Google-Sheets-Daten lesen

Jetzt kommt der spannende Teil: Python-Code schreiben, um auf die Google-Sheets-Daten zuzugreifen.

Die richtigen Pakete installieren und importieren

Installiere und importiere zunächst alle Pakete, die du für die Verbindung zu deinem Google-API-Konto brauchst. Wir verwenden google-auth für die Authentifizierung gegen die Google API, gspread für die einfache Anbindung an Google Spreadsheets und pandas zur Verarbeitung tabellarischer Daten in Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os

Einen Client mit Scoped Credentials einrichten

Lade die Umgebungsvariable GOOGLE_JSON und speichere sie in einer Variable:

import os
google_json = os.environ["GOOGLE_JSON"]

Erzeuge aus der Servicekonto-JSON ein Credentials-Objekt:

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

Weise den Credentials den richtigen „API Scope“ zu, damit die passenden Berechtigungen für das Google-Spreadsheet greifen:

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)

Erstelle einen neuen Client mit diesen Scoped Credentials:

client = gspread.authorize(creds_with_scope)

Spreadsheet-Daten in ein DataFrame einlesen

Mit dem fertigen Client können wir die Daten aus dem zuvor erstellten Spreadsheet lesen.

Erzeuge zuerst eine neue Sheet-Instanz aus einer Spreadsheet-URL:

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')

Fokussiere dann das gewünschte Worksheet (den Tab im Google-Spreadsheet). Wir holen uns das erste Sheet über den Index 0:

worksheet = spreadsheet.get_worksheet(0)

Lies zunächst alle Zeilen als JSON ein:

records_data = worksheet.get_all_records()

Wandle die JSON-Records anschließend in ein pandas DataFrame um:

records_df = pd.DataFrame.from_dict(records_data)
records_df

Geschafft!

6. Spreadsheet-Daten in Python analysieren

Jetzt kannst du die aus dem Google-Spreadsheet eingelesenen Daten mit Python analysieren. Der folgende Minimalcode geht vom Beispiel-Spreadsheet aus. Falls du mit einem anderen Datensatz arbeitest, funktionieren diese Schritte eventuell nicht direkt. Wenn du tiefer einsteigen willst, sieh dir den Kurs Data Manipulation with pandas auf DataCamp an!

Berechne den Median der Finanzierung pro Land:

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

7. Ergebnisse in Google Sheets zurückschreiben

Wenn die Spreadsheet-Nutzer, mit denen du zusammenarbeitest, die Ergebnisse direkt im Spreadsheet sehen sollen, kannst du die Resultate deiner Python-Analyse in ein neues Google-Worksheet (also einen Tab) im ursprünglichen Spreadsheet zurückschreiben.

Im Beispiel erstellen wir ein neues Worksheet median_country, falls es noch nicht existiert, und fügen dann den Inhalt des DataFrames median_funding_df inklusive Spaltenüberschriften ein. Der Code wurde mit Hilfe des AI Assistant generiert!

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

Wenn du das Spreadsheet wieder öffnest, siehst du ein neues Worksheet mit den Daten aus dem DataFrame! Dieses Zurückschreiben ist nur möglich, wenn du den API-basierten Weg über die Google Sheets API nutzt – nicht mit dem integrierten Google-Sheets-Connector aus dem ersten Teil des Tutorials.

Fazit

Je nach Bedarf ist die Analyse von Google-Sheets-Daten mit Python entweder nahtlos oder erfordert etwas Vorarbeit. Wenn du nur Daten aus einem Sheet lesen willst, nimm den integrierten Connector – in weniger als fünf Klicks bist du startklar. Wenn du Daten sowohl lesen als auch zurückschreiben möchtest, lohnt sich die Aktivierung der Google Sheets API und das Einrichten eines Servicekontos.

In beiden Fällen gilt: Ist alles einmal korrekt konfiguriert, ist der Zugriff auf Google-Sheets-Daten fast so einfach wie das Abfragen einer CSV-Datei. Der große Vorteil: Du triffst deine Stakeholder dort, wo sie arbeiten – viele Kolleginnen und Kollegen schwören auf Spreadsheets.

Schluss mit zeitraubenden CSV-Exports – her mit leistungsfähigen, automatischen Workflows, um mit Google-Sheets-Daten zu arbeiten, ohne DataLab zu verlassen.