MQTT para Google Sheets: Envie Dados de Sensores com Python e a API do Sheets

Enviar dados de MQTT para Google Sheets é uma das formas mais rápidas de dar a colegas sem perfil técnico uma visão ao vivo dos dados dos sensores: eles já conhecem planilhas e podem adicionar seus próprios gráficos e fórmulas. Neste tutorial você vai construir um subscriber em Python que coleta leituras de topics MQTT, agrupa essas leituras em lotes e as anexa a uma Planilha Google compartilhada pela API oficial do Sheets. No final, você terá um pipeline de relatórios pequeno e confiável, que respeita as cotas da API e sobrevive a quedas de rede.

Quando o Google Sheets Faz Sentido para Dados de IoT

O Google Sheets não é um banco de dados de séries temporais, e fingir que é leva a planilhas lentas e inchadas. Mas ele se encaixa muito bem em um conjunto específico de tarefas:

As planilhas também têm um limite rígido de células por arquivo e ficam lentas bem antes de chegar a ele. Por isso, o design abaixo agrupa as linhas em lotes e permite agregar antes de gravar, para que a planilha continue pequena e útil.

Arquitetura: Um Subscriber no Meio

Seus dispositivos não devem falar diretamente com o Google. Embutir credenciais do Google no firmware é um problema de segurança, e cada dispositivo precisaria de seu próprio cliente HTTP e de sua própria lógica de nova tentativa. Em vez disso, os dispositivos publicam em um broker MQTT normalmente, e um único processo Python assina e encaminha para o Sheets:

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

Esse é o padrão honesto para qualquer integração com um broker MQTT: o broker roteia as mensagens, e pequenos serviços externos como este cuidam da entrega para outros sistemas. Se a ponte ficar fora do ar por um momento, os dispositivos continuam publicando normalmente.

Passo 1: Crie uma Conta de Serviço do Google

1. Abra o console do Google Cloud e crie um projeto (ou reutilize um).
2. Ative a Google Sheets API para esse projeto.
3. Vá em IAM e administrador, Contas de serviço, crie uma conta de serviço, depois crie uma chave JSON e baixe-a como service_account.json.
4. Crie uma nova Planilha Google, adicione uma aba chamada Readings e coloque os cabeçalhos na linha 1: received_at, device, metric, value, topic.
5. Clique em Compartilhar na planilha e adicione o e-mail da conta de serviço (ele termina em iam.gserviceaccount.com) como Editor.
6. Copie a chave da planilha a partir da URL: a string longa entre /d/ e /edit.

O passo 5 é o que as pessoas esquecem. Sem ele, toda chamada à API falha com erro de permissão, mesmo com credenciais válidas.

Passo 2: Instale as Bibliotecas

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

O gspread encapsula a API do Sheets e cuida da autenticação a partir do arquivo da conta de serviço. A referência completa da API está na documentação do gspread.

Passo 3: A Ponte de MQTT para Google Sheets

Salve como sheets_bridge.py. Ele espera topics no formato <prefix>/<device>/<metric> com payload numérico 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  # evita linhas duplicadas a cada reconexão
    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 (cota) ou 5xx: mantém as linhas e tenta de novo mais 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 que os Lotes Importam

A API do Sheets impõe cotas de requisições por minuto, por projeto e por usuário. Uma chamada à API por mensagem MQTT funciona em uma demonstração e depois falha assim que alguns dispositivos passam a reportar a cada segundo. A ponte coleta as linhas em uma fila thread-safe e as envia com uma única chamada append_rows a cada 30 segundos, de modo que dez dispositivos reportando a cada segundo viram duas requisições por minuto, em vez de seiscentas.

Quando a API retorna um erro (cota excedida ou erro temporário do servidor), o worker mantém o lote em pending e tenta novamente com backoff exponencial. As linhas só são descartadas se o buffer em memória encher, o que dá bastante margem durante uma instabilidade curta do Google.

Por que USER_ENTERED

Com value_input_option="USER_ENTERED", o Sheets interpreta os valores como se uma pessoa os tivesse digitado: o timestamp vira uma data e hora de verdade e os números viram números, então gráficos e fórmulas funcionam imediatamente. Com RAW, tudo chega como texto.

Passo 4: Execute e Teste

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

Em outro terminal, publique algumas leituras no 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

Em até 30 segundos as duas linhas aparecem na planilha. Lembre-se de que qualquer coisa no servidor MQTT público pode ser lida por outras pessoas, então use apenas valores fictícios durante os testes.

Agregue Antes de Anexar

Para qualquer coisa além de um piloto, envie resumos em vez de leituras brutas. Uma abordagem simples é manter em memória uma média móvel por dispositivo e enviar uma linha por dispositivo a cada cinco ou quinze minutos. Isso mantém a planilha legível para pessoas e muito abaixo do limite de células. Se você também precisar do fluxo bruto, grave-o em algo feito para volume, como a abordagem com CSV de salvar mensagens MQTT em CSV, e deixe o Sheets para o resumo.

Checklist de Solução de Problemas

1. Erros SpreadsheetNotFound ou 403. A planilha não foi compartilhada com o e-mail da conta de serviço, ou a chave está errada.
2. WorksheetNotFound. O nome da aba não corresponde exatamente a WORKSHEET, incluindo maiúsculas e minúsculas.
3. Linhas chegam duplicadas após um reinício. Retained messages foram reenviadas. A ponte já as ignora; verifique se seus dispositivos não republicam o histórico ao iniciar.
4. As linhas param e o log mostra 429. Você ultrapassou a cota. Aumente FLUSH_SECONDS ou agregue antes de anexar.
5. A ponte desconecta em loop. Outro cliente está usando o mesmo client ID. Mantenha o ID aleatório gerado no servidor MQTT público.
6. Os números aparecem alinhados à esquerda, como texto. Você mudou para RAW, ou a localidade da planilha espera vírgula como separador decimal (como em pt-BR). Altere a localidade da planilha em Arquivo, Configurações.

Executando a Ponte em Produção

Em uma implantação real, conecte-se a um broker privado com credenciais e TLS em vez da área pública. No ConnectMQ você cria um broker no portal e recebe um nome de usuário, uma senha e um client ID; defina-os como MQTT_USERNAME, MQTT_PASSWORD e MQTT_CLIENT_ID e use a porta 8883. Como o ConnectMQ é um broker MQTT gerenciado, a ponte é a única coisa que você opera, e pode executá-la como um serviço systemd, um contêiner ou uma pequena VM.

O mesmo padrão de subscriber move o restante desta série: um bot do Telegram com MQTT para alertas no chat e uma API REST em Flask na frente do MQTT para outras aplicações. Para escolher uma biblioteca cliente em outra linguagem, veja a visão geral de bibliotecas cliente MQTT.

Comece a Usar o ConnectMQ

Crie um broker privado para sua ponte com o Google Sheets em poucos minutos. Cadastre-se em app.connectmq.com, escolha o plano Serverless gratuito, sem cartão de crédito, e copie as credenciais geradas para a ponte. O cliente MQTT no navegador e o explorador de topics ao vivo do portal permitem confirmar que os dados estão fluindo antes de chegarem à planilha, e o ConnectMQ cuida do TLS e da operação do broker para você.

Perguntas frequentes

Um dispositivo pode publicar diretamente no Google Sheets?

Ele pode chamar a API do Sheets via HTTPS, mas isso coloca credenciais do Google no dispositivo e obriga cada dispositivo a lidar com cotas e novas tentativas. Publicar via MQTT e usar uma única ponte é mais simples e mais seguro.

Com que frequência devo anexar linhas ao Google Sheets?

Agrupe as gravações a cada 30 a 60 segundos para dados brutos, ou a cada poucos minutos para resumos. Menos chamadas append_rows, e maiores, ficam bem dentro das cotas da API.

Isso funciona com Google Apps Script em vez de Python?

O Apps Script não consegue manter uma conexão MQTT aberta, então você ainda precisaria de um subscriber externo. A ponte em Python é a peça que fala MQTT; o Apps Script pode depois formatar os dados ou criar gráficos dentro da planilha.

O que acontece se o Google ficar indisponível?

A ponte mantém as linhas em memória e tenta novamente com backoff, então instabilidades curtas causam atraso, e não perda de dados. Uma indisponibilidade longa acaba enchendo o buffer, e a partir daí as novas linhas são descartadas e registradas no log.