MQTT a Google Sheets: envía datos de sensores con Python y la API de Sheets

Enviar MQTT a Google Sheets es una de las formas más rápidas de dar a compañeros sin perfil técnico una vista en vivo de los datos de sensores: ya conocen las hojas de cálculo y pueden añadir sus propios gráficos y fórmulas. En este tutorial construirás un suscriptor en Python que recoge lecturas de topics MQTT, las agrupa en lotes y las añade a una Google Sheet compartida mediante la API oficial de Sheets. Al final tendrás un pequeño pipeline de informes fiable que respeta las cuotas de la API y sobrevive a los cortes de red.

Cuándo tiene sentido Google Sheets para datos IoT

Google Sheets no es una base de datos de series temporales, y fingir lo contrario lleva a hojas de cálculo lentas e infladas. Aun así, encaja muy bien en un conjunto concreto de tareas:

Las hojas de cálculo también tienen un límite estricto de celdas totales por archivo, y se vuelven lentas mucho antes de alcanzarlo. Por eso el diseño que sigue agrupa las filas en lotes y te permite agregar antes de escribir, para que la hoja siga siendo pequeña y útil.

Arquitectura: un suscriptor en medio

Tus dispositivos no deberían hablar directamente con Google. Incrustar credenciales de Google en el firmware es un problema de seguridad, y cada dispositivo necesitaría su propio cliente HTTP y su propia lógica de reintentos. En su lugar, los dispositivos publican en un broker MQTT como siempre, y un único proceso Python se suscribe y reenvía a Sheets:

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

Este es el patrón honesto para cualquier integración con un broker MQTT: el broker enruta los mensajes, y pequeños servicios externos como este se encargan de entregarlos a otros sistemas. Si el puente se cae un momento, los dispositivos siguen publicando con normalidad.

Paso 1: crear una cuenta de servicio de Google

1. Abre la consola de Google Cloud y crea un proyecto (o reutiliza uno).
2. Habilita la Google Sheets API para ese proyecto.
3. Ve a IAM y administración, Cuentas de servicio, crea una cuenta de servicio y después crea una clave JSON y descárgala como service_account.json.
4. Crea una nueva Google Sheet, añade una pestaña llamada Readings y pon las cabeceras en la fila 1: received_at, device, metric, value, topic.
5. Haz clic en Compartir en la hoja y añade la dirección de correo de la cuenta de servicio (termina en iam.gserviceaccount.com) como Editor.
6. Copia la clave de la hoja de cálculo desde la URL: la cadena larga entre /d/ y /edit.

El paso 5 es el que la gente olvida. Sin él, todas las llamadas a la API fallan con un error de permisos aunque las credenciales sean válidas.

Paso 2: instalar las bibliotecas

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

gspread envuelve la API de Sheets y gestiona la autenticación a partir del archivo de la cuenta de servicio. La referencia completa de la API está en la documentación de gspread.

Paso 3: el puente de MQTT a Google Sheets

Guarda esto como sheets_bridge.py. Espera topics con la forma <prefix>/<device>/<metric> y un payload numérico o 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  # evitar filas duplicadas cada vez que reconectamos
    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 (cuota) o 5xx: conservar las filas y reintentar más tarde
            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()

Por qué importa agrupar en lotes

La API de Sheets impone cuotas de peticiones por minuto por proyecto y por usuario. Una llamada a la API por cada mensaje MQTT funciona en una demo y falla en cuanto unos pocos dispositivos informan cada segundo. El puente recoge las filas en una cola segura entre hilos y las envía con una única llamada append_rows cada 30 segundos, de modo que diez dispositivos que informan cada segundo se convierten en dos peticiones por minuto en lugar de seiscientas.

Cuando la API sí devuelve un error (cuota superada o un error temporal del servidor), el worker conserva el lote en pending y reintenta con backoff exponencial. Las filas solo se descartan si se llena el búfer en memoria, lo que te da mucho margen durante una breve caída de Google.

Por qué USER_ENTERED

Con value_input_option="USER_ENTERED", Sheets interpreta los valores como si los hubiera escrito una persona: la marca de tiempo se convierte en una fecha y hora real y los números en números, así que los gráficos y las fórmulas funcionan de inmediato. Con RAW, todo llega como texto.

Paso 4: ejecutar y probar

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

Desde otra terminal, publica algunas lecturas en el servidor MQTT público gratuito:

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 menos de 30 segundos ambas filas aparecen en la hoja. Recuerda que todo lo que hay en el broker público es legible por otros, así que usa solo valores inventados durante las pruebas.

Agrega antes de añadir filas

Para cualquier cosa que vaya más allá de un piloto, envía resúmenes en lugar de lecturas en bruto. Un enfoque sencillo es mantener en memoria una media móvil por dispositivo y enviar una fila por dispositivo cada cinco o quince minutos. Así la hoja sigue siendo legible para las personas y queda muy por debajo del límite de celdas. Si también necesitas el flujo en bruto, escríbelo en algo pensado para volumen, como el enfoque CSV de registrar mensajes MQTT en CSV, y reserva Sheets para el resumen.

Lista de comprobación para solucionar problemas

1. Errores SpreadsheetNotFound o 403. La hoja no está compartida con el correo de la cuenta de servicio, o la clave es incorrecta.
2. WorksheetNotFound. El nombre de la pestaña no coincide exactamente con WORKSHEET, incluidas mayúsculas y minúsculas.
3. Las filas llegan dos veces tras un reinicio. Se reenviaron retained messages. El puente ya los omite; comprueba que tus dispositivos no vuelvan a publicar el historial al arrancar.
4. Las filas se detienen y el log muestra 429. Has superado la cuota. Aumenta FLUSH_SECONDS o agrega antes de añadir filas.
5. El puente se desconecta en bucle. Otro cliente usa el mismo client ID. Mantén el ID aleatorio generado en el broker público.
6. Los números aparecen alineados a la izquierda como texto. Cambiaste a RAW, o la configuración regional de la hoja espera la coma como separador decimal. Cambia la configuración regional de la hoja en Archivo, Configuración.

Ejecutar el puente en producción

Para un despliegue real, conéctate a un broker privado con credenciales y TLS en lugar del área pública. En ConnectMQ creas un broker en el portal y recibes un usuario, una contraseña y un client ID; defínelos como MQTT_USERNAME, MQTT_PASSWORD y MQTT_CLIENT_ID y usa el puerto 8883. Como se trata de un broker MQTT gestionado, el puente es lo único que operas, y puedes ejecutarlo como servicio systemd, como contenedor o en una pequeña VM.

El mismo patrón de suscriptor impulsa el resto de esta serie: un bot de Telegram con MQTT para alertas por chat y una API REST con Flask delante de MQTT para otras aplicaciones. Para elegir una biblioteca cliente en otro lenguaje, consulta la panorámica de bibliotecas cliente MQTT.

Empieza con ConnectMQ

Crea un broker privado para tu puente de Google Sheets en pocos minutos. Regístrate en app.connectmq.com, elige el plan gratuito Serverless sin necesidad de tarjeta de crédito y copia las credenciales generadas en el puente. El cliente MQTT en el navegador y el explorador de topics en vivo del portal te permiten confirmar que los datos fluyen antes de que lleguen a la hoja, y ConnectMQ se encarga por ti de TLS y de la operación del broker.

Preguntas frecuentes

¿Puede un dispositivo publicar directamente en Google Sheets?

Puede llamar a la API de Sheets por HTTPS, pero eso coloca credenciales de Google en el dispositivo y obliga a cada dispositivo a gestionar cuotas y reintentos. Publicar por MQTT y usar un único puente es más sencillo y más seguro.

¿Con qué frecuencia debo añadir filas a Google Sheets?

Agrupa las escrituras cada 30 a 60 segundos para datos en bruto, o cada pocos minutos para resúmenes. Menos llamadas append_rows, pero más grandes, se mantienen holgadamente dentro de las cuotas de la API.

¿Funciona con Google Apps Script en lugar de Python?

Apps Script no puede mantener abierta una conexión MQTT, así que seguirías necesitando un suscriptor externo. El puente en Python es la pieza que habla MQTT; después, Apps Script puede dar formato a los datos o crear gráficos dentro de la hoja.

¿Qué pasa si Google no está disponible?

El puente conserva las filas en memoria y reintenta con backoff, así que las caídas breves provocan un retraso en lugar de pérdida de datos. Una caída larga acaba llenando el búfer, y a partir de ahí las filas nuevas se descartan y se registran en el log.