MQTT to Google Sheets: Send Sensor Data with Python and the Sheets API

Sending MQTT to Google Sheets is one of the quickest ways to give non-technical colleagues a live view of sensor data: they already know spreadsheets, and they can add their own charts and formulas. In this tutorial you will build a Python subscriber that collects readings from MQTT topics, batches them, and appends them to a shared Google Sheet through the official Sheets API. By the end you will have a small, reliable reporting pipeline that respects API quotas and survives network drops.

When Google Sheets Makes Sense for IoT Data

Google Sheets is not a time-series database, and pretending otherwise leads to slow, bloated spreadsheets. It does fit a specific set of jobs very well:

Spreadsheets also have a hard cap on total cells per file, and they get sluggish well before reaching it. The design below therefore batches rows and lets you aggregate before writing, so the sheet stays small and useful.

Architecture: A Subscriber in the Middle

Your devices should not talk to Google directly. Embedding Google credentials in firmware is a security problem, and each device would need its own HTTP client and retry logic. Instead, devices publish to an MQTT broker as usual, and one Python process subscribes and forwards to Sheets:

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

This is the honest pattern for any integration with an MQTT broker: the broker routes messages, and small external services like this one handle delivery to other systems. If the bridge is down for a moment, devices keep publishing normally.

Step 1: Create a Google Service Account

1. Open the Google Cloud console and create a project (or reuse one).
2. Enable the Google Sheets API for that project.
3. Go to IAM and Admin, Service Accounts, create a service account, then create a JSON key and download it as service_account.json.
4. Create a new Google Sheet, add a tab called Readings, and put headers in row 1: received_at, device, metric, value, topic.
5. Click Share on the sheet and add the service account's email address (it ends in iam.gserviceaccount.com) as an Editor.
6. Copy the spreadsheet key from the URL: the long string between /d/ and /edit.

Step 5 is the one people forget. Without it, every API call fails with a permission error even though the credentials are valid.

Step 2: Install the Libraries

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

gspread wraps the Sheets API and handles authentication from the service account file. The full API reference is in the gspread documentation.

Step 3: The MQTT to Google Sheets Bridge

Save this as sheets_bridge.py. It expects topics shaped like <prefix>/<device>/<metric> with a numeric or 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  # avoid duplicate rows every time we reconnect
    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) or 5xx: keep the rows and retry later
            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()

Why the Batching Matters

The Sheets API enforces per-minute request quotas per project and per user. One API call per MQTT message works for a demo and then fails as soon as a few devices report every second. The bridge collects rows in a thread-safe queue and sends them with a single append_rows call every 30 seconds, so ten devices reporting every second become two requests per minute instead of six hundred.

When the API does return an error (quota exceeded or a temporary server error), the worker keeps the batch in pending and retries with exponential backoff. Rows are only dropped if the in-memory buffer fills up, which gives you plenty of margin during a short Google outage.

Why USER_ENTERED

With value_input_option="USER_ENTERED", Sheets parses values as if a person typed them: the timestamp becomes a real date-time and numbers become numbers, so charts and formulas work immediately. With RAW, everything lands as text.

Step 4: Run and Test

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

From another terminal, publish some readings to the free public 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

Within 30 seconds both rows appear in the sheet. Remember that anything on the public broker is readable by others, so only use made-up values while testing.

Aggregate Before You Append

For anything beyond a pilot, send summaries instead of raw readings. A simple approach is to keep a per-device running average in memory and push one row per device every five or fifteen minutes. That keeps the sheet readable for humans and far below the cell limit. If you need the raw stream as well, write it somewhere built for volume, such as the CSV approach from log MQTT messages to CSV, and keep Sheets for the summary.

Troubleshooting Checklist

1. SpreadsheetNotFound or 403 errors. The sheet is not shared with the service account email, or the key is wrong.
2. WorksheetNotFound. The tab name does not match WORKSHEET exactly, including case.
3. Rows arrive twice after a restart. Retained messages were replayed. The bridge already skips them; check that your devices do not republish history on boot.
4. Rows stop and the log shows 429. You are over quota. Increase FLUSH_SECONDS or aggregate before appending.
5. The bridge disconnects in a loop. Another client is using the same client ID. Keep the generated random ID on the public broker.
6. Numbers appear left-aligned as text. You switched to RAW, or the spreadsheet locale expects a comma as the decimal separator. Change the sheet locale under File, Settings.

Running the Bridge in Production

For a real deployment, connect to a private broker with credentials and TLS instead of the public area. On ConnectMQ you create a broker in the portal and receive a username, password and client ID; set them as MQTT_USERNAME, MQTT_PASSWORD and MQTT_CLIENT_ID, and use port 8883. Because the broker is a fully managed MQTT service, the bridge is the only thing you operate, and you can run it as a systemd service, a container or a small VM.

The same subscriber pattern powers the rest of this series: an MQTT Telegram bot for chat alerts and a Flask REST API in front of MQTT for other applications. For choosing a client library in a different language, see the overview of MQTT client libraries.

Get Started with ConnectMQ

Create a private broker for your Google Sheets bridge in a few minutes. Sign up at app.connectmq.com, pick the free Serverless plan with no credit card required, and copy the generated credentials into the bridge. The portal's in-browser MQTT client and live topic explorer let you confirm data is flowing before it reaches the sheet, and ConnectMQ handles TLS and broker operations for you.

Frequently Asked Questions

Can a device publish directly to Google Sheets?

It can call the Sheets API over HTTPS, but that puts Google credentials on the device and forces every device to handle quotas and retries. Publishing over MQTT and using one bridge is simpler and safer.

How often should I append rows to Google Sheets?

Batch writes every 30 to 60 seconds for raw data, or every few minutes for summaries. Fewer, larger append_rows calls stay well within API quotas.

Does this work with Google Apps Script instead of Python?

Apps Script cannot hold an MQTT connection open, so you would still need an external subscriber. The Python bridge is the piece that speaks MQTT; Apps Script can then format or chart the data inside the sheet.

What happens if Google is unavailable?

The bridge keeps rows in memory and retries with backoff, so short outages cause a delay rather than data loss. A long outage eventually fills the buffer, after which new rows are dropped and logged.