Exploring pydantic-gsheets

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:

Bash

pip install pydantic-gsheets

A First Example

Let's say you have a Google Sheet with customer data:

Name	Email	Age
Alice	alice@example.com	30
Bob	bob@example.com	25

You can define a Pydantic model in Python:

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:

Plaintext

[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

Python

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

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!