Integration · Nov 19, 2026

Logging Telegram bot orders to Google Sheets — the full 2026 walkthrough (aiogram 3)

The clients I built for want to open Google Sheets and see every order. Not a database. A spreadsheet. Here's the service-account setup, the append pattern, and the three failure modes that will eventually bite you.

Google Sheets is the poor-dev's analytics database. One row per order, automatic timestamp, open in a browser, pivot it, share with the accountant. For shops doing fewer than 500 orders/day, there's no better sink.

The hard parts aren't aiogram — they're Google Cloud. Service account auth, scopes, sharing the sheet with the right email, keeping the secret file (or JSON blob) out of your repo. Walkthrough below, from zero to one row per order landing in your sheet.

Why service account, not OAuth

OAuth needs a browser flow — user clicks Allow, you store a refresh token. For a server-side bot with no human in the loop, service accounts are the pattern: a robot identity that owns no files but you can share sheets with. Sheet owner = you (your Gmail). Bot writes = service account email you grant Editor access.

Step 1 — Create the service account (5 min)

  1. Open console.cloud.google.com. Create a project (or pick existing).
  2. APIs & Services → Library → search "Google Sheets API" → Enable.
  3. Same sidebar → Library → "Google Drive API" → Enable. (Needed for gspread to list spreadsheets.)
  4. IAM & Admin → Service Accounts → + Create service account. Name it telegram-bot-sheets. Skip the "Grant access" step.
  5. Click the new service account → Keys tab → Add Key → Create new key → JSON → downloads a file like my-project-abc123.json.
  6. Open the file, find the client_email field. Looks like telegram-bot-sheets@my-project.iam.gserviceaccount.com. Copy that.

Step 2 — Create and share the sheet

  1. Open sheets.google.com → + Blank. Name it, say, Orders Log.
  2. Row 1 headers: created_at | order_id | user_id | username | total | name | phone | address | note | items | status.
  3. Top right → Share → paste the service account email → Editor → Send (or skip notification).
  4. From the URL — https://docs.google.com/spreadsheets/d/<ID>/edit — copy the <ID>. That's your SHEETS_SPREADSHEET_ID.

Step 3 — Install and config

pip install gspread google-auth

.env:

SHEETS_ENABLED=true
SHEETS_SPREADSHEET_ID=1AbcDef_your_long_id_from_the_url
SHEETS_WORKSHEET=Orders
# Either the full JSON pasted on one line, or base64 of it.
SHEETS_CREDENTIALS_JSON={"type":"service_account","project_id":"..."}

Base64 is easier on hosts that strip quotes from env vars (looking at you, some PaaS). base64 -w0 my-project-abc123.json on Linux, [Convert]::ToBase64String([IO.File]::ReadAllBytes("my-project-abc123.json")) on PowerShell.

Step 4 — The logger module

# sheets.py
from __future__ import annotations
import base64
import json
import logging
from dataclasses import dataclass
from typing import Optional

logger = logging.getLogger(__name__)

HEADERS = [
    "created_at", "order_id", "user_id", "username", "total",
    "name", "phone", "address", "note", "items", "status",
]

@dataclass
class SheetsLogger:
    enabled: bool
    _ws: object = None

    def log_order(self, order: dict) -> None:
        if not self.enabled or not self._ws:
            return
        row = [
            order.get("created_at", ""),
            order["id"],
            order["user_id"],
            order.get("username", ""),
            f"{order['total']:.2f}",
            order["name"],
            order["phone"],
            order["address"],
            order.get("note", ""),
            "; ".join(f"{i['name']} x{i['qty']}" for i in order.get("items", [])),
            order.get("status", "pending"),
        ]
        try:
            self._ws.append_row(row, value_input_option="RAW")
        except Exception as e:
            logger.warning("sheets append failed: %s", e)

def build_sheets_logger(settings) -> Optional[SheetsLogger]:
    if not settings.sheets_enabled:
        return SheetsLogger(enabled=False)
    try:
        import gspread
        from google.oauth2.service_account import Credentials

        raw = settings.sheets_credentials_json.strip()
        # Accept either raw JSON or base64-encoded JSON.
        if not raw.startswith("{"):
            raw = base64.b64decode(raw).decode("utf-8")
        info = json.loads(raw)

        scopes = [
            "https://www.googleapis.com/auth/spreadsheets",
            "https://www.googleapis.com/auth/drive",
        ]
        creds = Credentials.from_service_account_info(info, scopes=scopes)
        gc = gspread.authorize(creds)
        sh = gc.open_by_key(settings.sheets_spreadsheet_id)
        try:
            ws = sh.worksheet(settings.sheets_worksheet)
        except gspread.WorksheetNotFound:
            ws = sh.add_worksheet(title=settings.sheets_worksheet, rows=1000, cols=len(HEADERS))
            ws.append_row(HEADERS)
        return SheetsLogger(enabled=True, _ws=ws)
    except Exception as e:
        logger.warning("sheets init failed, continuing without: %s", e)
        return SheetsLogger(enabled=False)

The function returns a logger even on init failure — just a disabled one that no-ops. The bot stays alive if Google Cloud changes a scope or the JSON blob is malformed.

Step 5 — Call it from the checkout confirm handler

# handlers/checkout.py
if sheets is not None:
    try:
        sheets.log_order(order_to_dict(order))
    except Exception as e:
        logger.warning("sheets log failed: %s", e)

Wrapped a second time because log_order itself swallows errors, but better one exception path than two surprise routes. A failed Sheets write never fails the user's checkout.

Three failure modes you will hit

  1. You forgot to share the sheet with the service account email. gspread raises PermissionError: The caller does not have permission. Fix: open the sheet → Share → paste the client_email → Editor.
  2. JSON credentials have literal newlines in the private key. Pasting into a .env file breaks the key. Use base64 form always for env-based deploys.
  3. Rate limit at scale. Sheets API free tier is 300 requests/minute per project. Fine for a shop. For a 100-order/minute bot, batch with worksheet.append_rows([...]) every 5 seconds instead of per-order.

Why not just use a database

You should for anything that touches financial reconciliation at scale. Sheets starts creaking at ~10k rows per worksheet (scroll is laggy, charts slow). For a Fiverr client's coffee shop, it will never matter. For your SaaS doing 5k orders/day, graduate to Postgres.

The pattern above makes the swap trivial: SheetsLogger is behind a log_order(order) interface, so a PostgresLogger with the same signature drops in without touching any handler.

A working reference

The code above is simplified from sheets.py in the OrderBot full template. The lite version on GitHub doesn't ship with Sheets (keeps the install light); the full version has the exact file ready-to-use plus the env config documented in .env.example.

Related

Want the Sheets logger file + full template?

Catalog + cart + checkout + admin alerts + Sheets, Railway-deployable. $9 (48h flash: $5 with LAUNCH5).

← Back to blog