MQTT vers Google Sheets : envoyer des données de capteurs avec Python et l'API Sheets

Envoyer MQTT vers Google Sheets est l'un des moyens les plus rapides d'offrir à des collègues non techniques une vue en direct des données de capteurs : ils connaissent déjà les feuilles de calcul et peuvent ajouter leurs propres graphiques et formules. Dans ce tutoriel, vous allez construire un subscriber Python qui collecte les relevés de topics MQTT, les regroupe et les ajoute à une Google Sheet partagée via l'API Sheets officielle. À la fin, vous disposerez d'un petit pipeline de reporting fiable qui respecte les quotas de l'API et survit aux coupures réseau.

Quand Google Sheets a du sens pour les données IoT

Google Sheets n'est pas une base de données de séries temporelles, et prétendre le contraire mène à des feuilles lentes et surchargées. Il convient en revanche très bien à un ensemble précis d'usages :

Les feuilles de calcul ont aussi une limite stricte du nombre total de cellules par fichier, et elles ralentissent bien avant de l'atteindre. La conception ci-dessous regroupe donc les lignes et vous permet d'agréger avant d'écrire, pour que la feuille reste compacte et utile.

Architecture : un subscriber au milieu

Vos appareils ne doivent pas communiquer directement avec Google. Intégrer des identifiants Google dans un firmware pose un problème de sécurité, et chaque appareil aurait besoin de son propre client HTTP et de sa propre logique de nouvelle tentative. Les appareils publient plutôt vers un broker MQTT comme d'habitude, et un processus Python unique s'abonne et transfère vers Sheets :

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

C'est le modèle honnête pour toute intégration avec un broker MQTT : le broker route les messages, et de petits services externes comme celui-ci assurent la livraison vers d'autres systèmes. Si le pont est momentanément arrêté, les appareils continuent de publier normalement.

Étape 1 : créer un compte de service Google

1. Ouvrez la console Google Cloud et créez un projet (ou réutilisez-en un).
2. Activez l'API Google Sheets pour ce projet.
3. Allez dans IAM et administration, Comptes de service, créez un compte de service, puis créez une clé JSON et téléchargez-la sous le nom service_account.json.
4. Créez une nouvelle Google Sheet, ajoutez un onglet nommé Readings et placez les en-têtes en ligne 1 : received_at, device, metric, value, topic.
5. Cliquez sur Partager dans la feuille et ajoutez l'adresse e-mail du compte de service (elle se termine par iam.gserviceaccount.com) en tant qu'Éditeur.
6. Copiez la clé de la feuille de calcul depuis l'URL : la longue chaîne entre /d/ et /edit.

L'étape 5 est celle que l'on oublie. Sans elle, chaque appel à l'API échoue avec une erreur d'autorisation, même si les identifiants sont valides.

Étape 2 : installer les bibliothèques

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

gspread encapsule l'API Sheets et gère l'authentification à partir du fichier du compte de service. La référence complète de l'API se trouve dans la documentation de gspread.

Étape 3 : le pont MQTT vers Google Sheets

Enregistrez ceci sous sheets_bridge.py. Il attend des topics de la forme <prefix>/<device>/<metric> avec un payload numérique ou JSON.

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  # éviter des lignes en double à chaque reconnexion
    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 (quota) ou 5xx : conserver les lignes et réessayer plus tard
            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()

Pourquoi le regroupement compte

L'API Sheets impose des quotas de requêtes par minute, par projet et par utilisateur. Un appel d'API par message MQTT fonctionne pour une démo, puis échoue dès que quelques appareils remontent des données chaque seconde. Le pont collecte les lignes dans une file thread-safe et les envoie en un seul appel append_rows toutes les 30 secondes : dix appareils qui remontent chaque seconde deviennent ainsi deux requêtes par minute au lieu de six cents.

Lorsque l'API renvoie une erreur (quota dépassé ou erreur serveur temporaire), le worker conserve le lot dans pending et réessaie avec un backoff exponentiel. Les lignes ne sont abandonnées que si le tampon en mémoire se remplit, ce qui laisse une large marge pendant une courte panne de Google.

Pourquoi USER_ENTERED

Avec value_input_option="USER_ENTERED", Sheets interprète les valeurs comme si une personne les avait saisies : l'horodatage devient une vraie date-heure et les nombres deviennent des nombres, de sorte que graphiques et formules fonctionnent immédiatement. Avec RAW, tout arrive sous forme de texte.

Étape 4 : exécuter et tester

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

Depuis un autre terminal, publiez quelques relevés sur le serveur MQTT public gratuit, réservé aux tests :

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

En moins de 30 secondes, les deux lignes apparaissent dans la feuille. N'oubliez pas que tout ce qui se trouve sur le serveur MQTT public gratuit est lisible par d'autres : n'utilisez que des valeurs fictives pendant les tests.

Agréger avant d'ajouter

Au-delà d'un pilote, envoyez des synthèses plutôt que des relevés bruts. Une approche simple consiste à maintenir en mémoire une moyenne glissante par appareil et à pousser une ligne par appareil toutes les cinq ou quinze minutes. La feuille reste ainsi lisible pour des humains et bien en deçà de la limite de cellules. Si vous avez aussi besoin du flux brut, écrivez-le dans un stockage conçu pour le volume, comme l'approche CSV de enregistrer les messages MQTT en CSV, et réservez Sheets à la synthèse.

Liste de dépannage

1. Erreurs SpreadsheetNotFound ou 403. La feuille n'est pas partagée avec l'e-mail du compte de service, ou la clé est erronée.
2. WorksheetNotFound. Le nom de l'onglet ne correspond pas exactement à WORKSHEET, casse comprise.
3. Les lignes arrivent en double après un redémarrage. Des retained messages ont été rejoués. Le pont les ignore déjà ; vérifiez que vos appareils ne republient pas leur historique au démarrage.
4. Les lignes s'arrêtent et le log affiche 429. Vous dépassez le quota. Augmentez FLUSH_SECONDS ou agrégez avant d'ajouter.
5. Le pont se déconnecte en boucle. Un autre client utilise le même client ID. Conservez l'ID aléatoire généré sur le serveur MQTT public gratuit.
6. Les nombres apparaissent alignés à gauche comme du texte. Vous êtes passé à RAW, ou les paramètres régionaux de la feuille attendent une virgule comme séparateur décimal, ce qui est le cas en français. Modifiez les paramètres régionaux dans Fichier, Paramètres.

Exécuter le pont en production

Pour un vrai déploiement, connectez-vous à un broker privé avec identifiants et TLS plutôt qu'au serveur MQTT public gratuit. Sur ConnectMQ, vous créez un broker dans le portail et recevez un username, un mot de passe et un client ID ; définissez-les dans MQTT_USERNAME, MQTT_PASSWORD et MQTT_CLIENT_ID, et utilisez le port 8883. Comme le broker est un broker MQTT managé, le pont est la seule chose que vous exploitez, et vous pouvez l'exécuter comme service systemd, conteneur ou petite VM.

Le même modèle de subscriber alimente le reste de cette série : un bot Telegram MQTT pour les alertes par messagerie et une API REST Flask devant MQTT pour d'autres applications. Pour choisir une bibliothèque cliente dans un autre langage, consultez la présentation des bibliothèques clientes MQTT.

Démarrer avec ConnectMQ

Créez un broker privé pour votre pont Google Sheets en quelques minutes. Inscrivez-vous sur app.connectmq.com, choisissez le plan Serverless gratuit sans carte bancaire et copiez les identifiants générés dans le pont. Le client MQTT intégré au navigateur et l'explorateur de topics en direct du portail vous permettent de confirmer que les données circulent avant qu'elles n'atteignent la feuille, et ConnectMQ gère pour vous le TLS et l'exploitation du broker.

Questions fréquentes

Un appareil peut-il publier directement dans Google Sheets ?

Il peut appeler l'API Sheets en HTTPS, mais cela place des identifiants Google sur l'appareil et oblige chaque appareil à gérer les quotas et les nouvelles tentatives. Publier en MQTT et utiliser un pont unique est plus simple et plus sûr.

À quelle fréquence ajouter des lignes à Google Sheets ?

Regroupez les écritures toutes les 30 à 60 secondes pour les données brutes, ou toutes les quelques minutes pour les synthèses. Des appels append_rows moins nombreux et plus volumineux restent largement dans les quotas de l'API.

Cela fonctionne-t-il avec Google Apps Script au lieu de Python ?

Apps Script ne peut pas maintenir une connexion MQTT ouverte : vous auriez donc toujours besoin d'un subscriber externe. Le pont Python est l'élément qui parle MQTT ; Apps Script peut ensuite mettre en forme ou représenter graphiquement les données dans la feuille.

Que se passe-t-il si Google est indisponible ?

Le pont conserve les lignes en mémoire et réessaie avec un backoff : les courtes pannes provoquent donc un retard plutôt qu'une perte de données. Une longue panne finit par remplir le tampon, après quoi les nouvelles lignes sont abandonnées et journalisées.