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)
- Open console.cloud.google.com. Create a project (or pick existing).
- APIs & Services → Library → search "Google Sheets API" → Enable.
- Same sidebar → Library → "Google Drive API" → Enable. (Needed for gspread to list spreadsheets.)
- IAM & Admin → Service Accounts → + Create service account. Name it
telegram-bot-sheets. Skip the "Grant access" step. - Click the new service account → Keys tab → Add Key → Create new key → JSON → downloads a file like
my-project-abc123.json. - Open the file, find the
client_emailfield. Looks liketelegram-bot-sheets@my-project.iam.gserviceaccount.com. Copy that.
Step 2 — Create and share the sheet
- Open sheets.google.com → + Blank. Name it, say,
Orders Log. - Row 1 headers:
created_at | order_id | user_id | username | total | name | phone | address | note | items | status. - Top right → Share → paste the service account email → Editor → Send (or skip notification).
- From the URL —
https://docs.google.com/spreadsheets/d/<ID>/edit— copy the<ID>. That's yourSHEETS_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
- 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 theclient_email→ Editor. - JSON credentials have literal newlines in the private key. Pasting into a
.envfile breaks the key. Use base64 form always for env-based deploys. - 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
- Telegram bot admin notifications in Python — the other half of the confirm handler.
- aiogram 3 FSM checkout example — the flow that produces the
orderobject you log here. - Deploying to Railway — 3 traps — where the env var for the JSON credentials actually lives in production.
Want the Sheets logger file + full template?
Catalog + cart + checkout + admin alerts + Sheets, Railway-deployable. $9 (48h flash: $5 with LAUNCH5).