MQTT-Daten in Google Sheets: Sensordaten mit Python und der Sheets API senden

MQTT-Daten in Google Sheets zu senden ist einer der schnellsten Wege, nicht technischen Kollegen eine Live-Ansicht von Sensordaten zu geben: Tabellen kennen sie bereits, und eigene Diagramme und Formeln können sie selbst ergänzen. Im Mittelstand ist das oft der pragmatische erste Schritt, bevor ein MES oder ein Historian eingeführt wird: Schichtleitung, Instandhaltung und Controlling sehen Energieverbrauch oder Stückzahlen einer Linie, ohne ein neues Werkzeug zu lernen. In diesem Tutorial bauen Sie einen Python-Subscriber, der Messwerte von MQTT-Topics sammelt, bündelt und über die offizielle Sheets API an ein geteiltes Google Sheet anhängt. Am Ende haben Sie eine kleine, zuverlässige Reporting-Pipeline, die API-Kontingente respektiert und Netzwerkausfälle übersteht.

Wann Google Sheets für IoT-Daten sinnvoll ist

Google Sheets ist keine Zeitreihendatenbank, und wer so tut, als wäre es eine, erhält langsame, aufgeblähte Tabellen. Für bestimmte Aufgaben passt es aber sehr gut:

Tabellen haben außerdem eine feste Obergrenze für die Gesamtzahl an Zellen pro Datei und werden lange vorher träge. Das Design unten bündelt daher Zeilen und erlaubt Aggregation vor dem Schreiben, damit die Tabelle klein und nützlich bleibt.

Architektur: ein Subscriber in der Mitte

Ihre Geräte sollten nicht direkt mit Google sprechen. Google-Zugangsdaten in Firmware einzubetten ist ein Sicherheitsproblem, und jedes Gerät bräuchte einen eigenen HTTP-Client samt Wiederholungslogik. Stattdessen publizieren Geräte wie gewohnt an einen MQTT-Broker, und ein Python-Prozess abonniert und leitet an Sheets weiter:

devices --MQTT--> broker.connectmq.com --MQTT--> sheets_bridge.py --HTTPS--> Google Sheets API

Das ist das ehrliche Muster für jede Integration mit einem MQTT-Broker: Der Broker leitet Nachrichten weiter, und kleine externe Dienste wie dieser übernehmen die Zustellung an andere Systeme. Ist die Bridge kurz nicht verfügbar, publizieren Geräte ganz normal weiter.

Schritt 1: Ein Google-Dienstkonto anlegen

1. Öffnen Sie die Google Cloud Console und erstellen Sie ein Projekt (oder verwenden Sie ein bestehendes).
2. Aktivieren Sie für dieses Projekt die Google Sheets API.
3. Gehen Sie zu IAM und Verwaltung, Dienstkonten, erstellen Sie ein Dienstkonto, legen Sie dann einen JSON-Schlüssel an und laden Sie ihn als service_account.json herunter.
4. Erstellen Sie ein neues Google Sheet, fügen Sie ein Tabellenblatt namens Readings hinzu und tragen Sie in Zeile 1 die Überschriften ein: received_at, device, metric, value, topic.
5. Klicken Sie in der Tabelle auf Freigeben und fügen Sie die E-Mail-Adresse des Dienstkontos (sie endet auf iam.gserviceaccount.com) als Bearbeiter hinzu.
6. Kopieren Sie den Tabellenschlüssel aus der URL: die lange Zeichenfolge zwischen /d/ und /edit.

Schritt 5 ist der, den viele vergessen. Ohne ihn scheitert jeder API-Aufruf mit einem Berechtigungsfehler, obwohl die Zugangsdaten gültig sind.

Schritt 2: Die Bibliotheken installieren

pip install "paho-mqtt>=2.0" "gspread>=6.0"

gspread kapselt die Sheets API und übernimmt die Authentifizierung über die Dienstkontodatei. Die vollständige API-Referenz finden Sie in der gspread-Dokumentation.

Schritt 3: Die Bridge für MQTT-Daten in Google Sheets

Speichern Sie dies als sheets_bridge.py. Das Skript erwartet Topics der Form <prefix>/<device>/<metric> mit einem numerischen oder JSON-Payload.

import json
import os
import queue
import threading
import time
import uuid
from datetime import datetime, timezone

import gspread
import paho.mqtt.client as mqtt

BROKER = os.environ.get("MQTT_HOST", "broker.connectmq.com")
PORT = int(os.environ.get("MQTT_PORT", "1883"))
USERNAME = os.environ.get("MQTT_USERNAME")
PASSWORD = os.environ.get("MQTT_PASSWORD")
CLIENT_ID = os.environ.get("MQTT_CLIENT_ID", f"sheets-bridge-{uuid.uuid4().hex[:8]}")
PREFIX = os.environ.get("TOPIC_PREFIX", "connectmq/demo/gsheet-5307")
SHEET_KEY = os.environ["SHEET_KEY"]
WORKSHEET = os.environ.get("WORKSHEET", "Readings")
FLUSH_SECONDS = int(os.environ.get("FLUSH_SECONDS", "30"))
MAX_BATCH = int(os.environ.get("MAX_BATCH", "500"))

rows: "queue.Queue[list]" = queue.Queue(maxsize=50_000)
stop = threading.Event()


def parse_value(text: str):
    try:
        return float(text)
    except ValueError:
        pass
    try:
        data = json.loads(text)
        if isinstance(data, dict) and isinstance(data.get("value"), (int, float)):
            return data["value"]
    except json.JSONDecodeError:
        pass
    return None


def on_connect(client, userdata, flags, reason_code, properties):
    if reason_code.is_failure:
        print(f"MQTT connect failed: {reason_code}")
        return
    client.subscribe(f"{PREFIX}/+/+", qos=1)
    print(f"Subscribed to {PREFIX}/+/+")


def on_message(client, userdata, msg):
    if msg.retain:
        return  # doppelte Zeilen bei jedem Reconnect vermeiden
    parts = msg.topic.split("/")
    device, metric = parts[-2], parts[-1]
    value = parse_value(msg.payload.decode("utf-8", errors="replace"))
    if value is None:
        return
    row = [datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S"), device, metric, value, msg.topic]
    try:
        rows.put_nowait(row)
    except queue.Full:
        print("Buffer full, dropping row")


def flush_worker(ws: gspread.Worksheet):
    backoff = 5
    pending: list = []
    while not stop.is_set() or pending or not rows.empty():
        stop.wait(FLUSH_SECONDS)
        while len(pending) < MAX_BATCH:
            try:
                pending.append(rows.get_nowait())
            except queue.Empty:
                break
        if not pending:
            continue
        try:
            ws.append_rows(pending, value_input_option="USER_ENTERED")
            print(f"Appended {len(pending)} rows")
            pending = []
            backoff = 5
        except gspread.exceptions.APIError as exc:
            # 429 (Kontingent) oder 5xx: Zeilen behalten und später erneut versuchen
            print(f"Sheets API error, retrying in {backoff}s: {exc}")
            time.sleep(backoff)
            backoff = min(backoff * 2, 300)


def main():
    gc = gspread.service_account(filename="service_account.json")
    ws = gc.open_by_key(SHEET_KEY).worksheet(WORKSHEET)

    worker = threading.Thread(target=flush_worker, args=(ws,), daemon=True)
    worker.start()

    client = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2, client_id=CLIENT_ID)
    if USERNAME:
        client.username_pw_set(USERNAME, PASSWORD)
    if PORT == 8883:
        client.tls_set()
    client.on_connect = on_connect
    client.on_message = on_message
    client.connect(BROKER, PORT, keepalive=60)
    client.loop_start()

    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        print("Stopping, flushing remaining rows...")
        client.loop_stop()
        client.disconnect()
        stop.set()
        worker.join(timeout=FLUSH_SECONDS + 30)


if __name__ == "__main__":
    main()

Warum das Bündeln wichtig ist

Die Sheets API erzwingt Anfragekontingente pro Minute, pro Projekt und pro Nutzer. Ein API-Aufruf pro MQTT-Nachricht funktioniert für eine Demo und scheitert dann, sobald einige Geräte jede Sekunde melden. Die Bridge sammelt Zeilen in einer threadsicheren Queue und sendet sie alle 30 Sekunden mit einem einzigen append_rows-Aufruf. So werden aus zehn Geräten, die jede Sekunde melden, zwei Anfragen pro Minute statt sechshundert.

Liefert die API doch einen Fehler (Kontingent überschritten oder vorübergehender Serverfehler), behält der Worker den Batch in pending und versucht es mit exponentiellem Backoff erneut. Zeilen werden nur verworfen, wenn sich der Puffer im Arbeitsspeicher füllt, was Ihnen bei einem kurzen Google-Ausfall reichlich Spielraum lässt.

Warum USER_ENTERED

Mit value_input_option="USER_ENTERED" interpretiert Sheets Werte so, als hätte eine Person sie eingetippt: Der Zeitstempel wird zu einem echten Datum mit Uhrzeit und Zahlen werden zu Zahlen, sodass Diagramme und Formeln sofort funktionieren. Mit RAW landet alles als Text.

Schritt 4: Starten und testen

export SHEET_KEY=your-spreadsheet-key
python sheets_bridge.py

Publizieren Sie aus einem anderen Terminal einige Messwerte an den kostenlosen öffentlichen MQTT-Broker:

mosquitto_pub -h broker.connectmq.com -p 1883 -i gsheet-pub-5307 \
  -t connectmq/demo/gsheet-5307/boiler1/temperature -m '{"value": 64.2}'
mosquitto_pub -h broker.connectmq.com -p 1883 -i gsheet-pub-5307 \
  -t connectmq/demo/gsheet-5307/boiler1/pressure -m 2.4

Innerhalb von 30 Sekunden erscheinen beide Zeilen in der Tabelle. Denken Sie daran, dass alles auf dem öffentlichen MQTT-Broker für andere lesbar ist. Er ist für Tests und Prototypen gedacht, nicht für Produktionsdaten; verwenden Sie beim Testen also nur erfundene Werte.

Vor dem Anhängen aggregieren

Für alles, was über ein Pilotprojekt hinausgeht, senden Sie Zusammenfassungen statt Rohwerten. Ein einfacher Ansatz ist, pro Gerät einen laufenden Mittelwert im Arbeitsspeicher zu halten und alle fünf oder fünfzehn Minuten eine Zeile pro Gerät zu schreiben. Das hält die Tabelle für Menschen lesbar und weit unter dem Zellenlimit. Brauchen Sie zusätzlich den Rohdatenstrom, schreiben Sie ihn an einen Ort, der für Volumen gebaut ist, etwa mit dem Ansatz aus MQTT-Nachrichten als CSV protokollieren, und nutzen Sie Sheets für die Zusammenfassung.

Checkliste zur Fehlerbehebung

1. SpreadsheetNotFound oder 403-Fehler. Die Tabelle ist nicht für die E-Mail-Adresse des Dienstkontos freigegeben, oder der Schlüssel ist falsch.
2. WorksheetNotFound. Der Name des Tabellenblatts stimmt nicht exakt mit WORKSHEET überein, einschließlich Groß- und Kleinschreibung.
3. Zeilen kommen nach einem Neustart doppelt an. Retained Messages wurden erneut ausgespielt. Die Bridge überspringt sie bereits; prüfen Sie, dass Ihre Geräte beim Booten keine Historie erneut publizieren.
4. Zeilen bleiben aus und das Log zeigt 429. Sie haben das Kontingent überschritten. Erhöhen Sie FLUSH_SECONDS oder aggregieren Sie vor dem Anhängen.
5. Die Bridge trennt sich in einer Schleife. Ein anderer Client nutzt dieselbe Client ID. Behalten Sie auf dem öffentlichen MQTT-Broker die generierte zufällige ID bei.
6. Zahlen erscheinen linksbündig als Text. Sie sind zu RAW gewechselt, oder das Gebietsschema der Tabelle erwartet ein Komma als Dezimaltrennzeichen, wie bei deutschen Einstellungen üblich. Ändern Sie das Gebietsschema unter Datei, Einstellungen.

Die Bridge in der Produktion betreiben

Für einen echten Einsatz verbinden Sie sich statt mit dem öffentlichen MQTT-Broker mit einem privaten Broker mit Zugangsdaten und TLS. Bei ConnectMQ erstellen Sie im Portal einen Broker und erhalten Benutzername, Passwort und Client ID; setzen Sie diese als MQTT_USERNAME, MQTT_PASSWORD und MQTT_CLIENT_ID und verwenden Sie Port 8883. Da es sich um einen verwalteten MQTT-Broker handelt, betreiben Sie nur die Bridge, etwa als systemd-Dienst, als Container oder auf einer kleinen VM.

Dasselbe Subscriber-Muster steckt hinter dem Rest dieser Reihe: ein MQTT-Telegram-Bot für Chat-Alarme und eine Flask-REST-API vor MQTT für andere Anwendungen. Zur Auswahl einer Client-Bibliothek in einer anderen Sprache lesen Sie die Übersicht der MQTT-Client-Bibliotheken.

Starten Sie mit ConnectMQ

Erstellen Sie in wenigen Minuten einen privaten Broker für Ihre Google-Sheets-Bridge. Registrieren Sie sich unter app.connectmq.com, wählen Sie den kostenlosen Serverless-Plan ohne Kreditkarte und kopieren Sie die generierten Zugangsdaten in die Bridge. Mit dem MQTT-Client im Browser und dem Live-Topic-Explorer des Portals prüfen Sie, ob Daten fließen, bevor sie die Tabelle erreichen, und ConnectMQ kümmert sich für Sie um TLS und den Broker-Betrieb.

Häufig gestellte Fragen

Kann ein Gerät direkt an Google Sheets publizieren?

Es kann die Sheets API über HTTPS aufrufen, doch dann liegen Google-Zugangsdaten auf dem Gerät, und jedes Gerät muss Kontingente und Wiederholungen selbst handhaben. Über MQTT zu publizieren und eine einzige Bridge zu nutzen, ist einfacher und sicherer.

Wie oft sollte ich Zeilen an Google Sheets anhängen?

Bündeln Sie Schreibvorgänge bei Rohdaten alle 30 bis 60 Sekunden, bei Zusammenfassungen alle paar Minuten. Wenige, größere append_rows-Aufrufe bleiben deutlich innerhalb der API-Kontingente.

Funktioniert das auch mit Google Apps Script statt Python?

Apps Script kann keine MQTT-Verbindung offen halten, Sie bräuchten also trotzdem einen externen Subscriber. Die Python-Bridge ist der Teil, der MQTT spricht; Apps Script kann die Daten anschließend innerhalb der Tabelle formatieren oder visualisieren.

Was passiert, wenn Google nicht verfügbar ist?

Die Bridge hält Zeilen im Arbeitsspeicher und versucht es mit Backoff erneut, sodass kurze Ausfälle eine Verzögerung statt Datenverlust bedeuten. Ein langer Ausfall füllt irgendwann den Puffer, danach werden neue Zeilen verworfen und protokolliert.