Google Sheets is one of the most popular tools for working with data. Python is one of the most popular programming languages for data analysis, automation, and backend development.
But what if you could bring both worlds together seamlessly? That's where pydantic-gsheets
comes in.
This package allows you to use Google Sheets like a simple database in Python, powered by Pydantic for schema validation. It’s still experimental, but it already opens up many possibilities for building real-world applications quickly and cleanly.
Note: This repository is experimental and not yet production-ready. However, it is a great tool for quick starts and prototyping.
Why Use pydantic-gsheets
?
Imagine you:
- Maintain customer records in Google Sheets.
- Have a dataset that needs validation (e.g., emails must be valid, phone numbers must be strings, prices must be floats).
- Want to read/write this data in Python without worrying about messy conversions.
pydantic-gsheets
solves this by:
- Reading directly from Google Sheets into Pydantic models.
- Validating the data so you don't have to manually check rows.
- Writing back to Google Sheets, keeping everything in sync.
Installation
You can install the package directly from PyPI:
pip install pydantic-gsheets
A First Example
Let's say you have a Google Sheet with customer data:
Name | Age | |
---|---|---|
Alice | alice@example.com | 30 |
Bob | bob@example.com | 25 |
You can define a Pydantic model in Python:
from pydantic import EmailStr
from pydantic_gsheets import SheetRow, get_sheets_service, AuthConfig, AuthMethod, GoogleWorkSheet,GSRequired,GSReadonly
from typing import Annotated
class Customer(SheetRow):
name: Annotated[str,GSRequired(),GSReadonly()]
email: Annotated[EmailStr,GSRequired()]
age: Annotated[int,GSRequired()]
svc = get_sheets_service(AuthConfig( #Use your personal auth, this is a helper for oauth
method=AuthMethod.USER_OAUTH,
client_secrets_file="client_secret.json",
token_cache_file=".tokens/google.json",
))
# Connect to a Google Sheet worksheet
ws = GoogleWorkSheet.create_sheet(
Customer,
svc,
"{YOUR_SHEET_ID}",
"{YOUR_SHEET_NAME}",
skip_if_exists=True, #skip if sheet exists, defaults to True
)
row1 = Customer(name="Alice", email="alice@example.com", age=30,)
ws.saveRow(row1)
# Load data into Python
customers = list(ws.rows()) # Dequeue as rows() returns a generator
print(customers)
When you run the example code, you should see the following output in your terminal:
[Customer(name='Alice', email='alice@example.com', age=30)]
This will:
- Create a worksheet if it doesn't exist in spreadsheet.
- Add a new row for Alice.
- Fetch all rows and convert them into a list of
Customer
objects.
Now Alice shows up in your Google Sheet instantly!
New Feature: Smartchips
A recent addition is Smartchips support. Smartchips are the little rich elements in Google Sheets (like people, files, or links that display extra info). With this feature, pydantic-gsheets
can now:
- Read smartchips from Sheets into structured Python data.
- Write smartchips back, keeping Google's rich formatting intact.
This opens the door for more advanced integrations, like mapping Google users directly into your Python application.
Note: If you have a link chip other than Google Drive, it will be considered automatically as read-only. See Writing Smart Chips. This is due to Google Drive API limitations.
Example Code
from pydantic import EmailStr
from pydantic_gsheets import SheetRow, get_sheets_service, AuthConfig, AuthMethod, GoogleWorkSheet,GSRequired,get_drive_service
from pydantic_gsheets.types import GS_SMARTCHIP,smartChips,peopleSmartChip,fileSmartChip
from typing import Annotated
class User(SheetRow):
user: Annotated[smartChips,GS_SMARTCHIP("@",smartchips=[peopleSmartChip]), GSRequired()]
mapping: Annotated[smartChips,GS_SMARTCHIP("@ maps to @",smartchips=[fileSmartChip,fileSmartChip]), GSRequired()]
svc = get_sheets_service(AuthConfig(
method=AuthMethod.USER_OAUTH,
client_secrets_file="client_secret.json",
token_cache_file=".tokens/google.json",
))
ds = get_drive_service(AuthConfig(
method=AuthMethod.USER_OAUTH,
client_secrets_file="client_secret.json",
token_cache_file=".tokens/google.json",
))
ws = GoogleWorkSheet.create_sheet(
User,
svc,
"{YOUR_SHEET_ID}",
"{YOUR_SHEET_NAME}",
skip_if_exists=True, #skip if sheet exists, defaults to True
drive_service=ds # Pass the Drive service
)
row1 = User(
user=smartChips(chipRuns=[peopleSmartChip(email="example@gmail.com")]),
mapping=smartChips(chipRuns=[fileSmartChip(uri="{GOOGLE_DRIVE_LINK_1}"),
fileSmartChip(uri="{GOOGLE_DRIVE_LINK_2}")
])
)
ws.saveRow(row1)
# Load data into Python
users = list(ws.rows()) # Dequeue as rows() returns a generator
print(users)
Documentation
For more detailed information on how to use pydantic-gsheets
, please refer to the official documentation: pydantic-gsheets Documentation
You can also check out my blog post: Exploring pydantic-gsheets
Contributing – The Project Needs You 🚀
⚠️ pydantic-gsheets
is still experimental. This means things might change quickly, and not every feature is production-ready. The maintainer is actively looking for contributors.
You can help by:
- Testing the package in your projects.
- Reporting bugs.
- Suggesting improvements.
- Submitting pull requests.
👉 Check out the repo here: pydantic-gsheets GitHub
Final Thoughts
pydantic-gsheets
is a beginner-friendly yet powerful way to bridge Google Sheets and Python. By combining the simplicity of spreadsheets with the robustness of Pydantic validation, it enables you to:
- Move faster in building prototypes.
- Let non-technical teammates contribute via Sheets.
- Ensure data integrity at all times.
If you’ve been looking for a simple way to treat Google Sheets like a database in Python, give pydantic-gsheets
a try today!
Top comments (0)