Google Sheets API × Gemini API: A Python Data Pipeline — No Apps Script Required

The Apps Script Wall

Most people start with Apps Script when they want to add AI to Google Sheets. It's built right in, easy to set up, and Google even provides official Gemini integration. For simple tasks, it's genuinely the right tool.

But the moment you need to reach beyond its sandbox — using third-party libraries, running jobs longer than 6 minutes, doing proper error recovery, or debugging locally — you hit a wall. Repeatedly.

This guide shows a different approach: Python only, no Apps Script, full control. Using the Google Sheets API with a service account and the Gemini API for AI processing, you get something you can run as a cron job, plug into a FastAPI server, or trigger from a webhook.

As a practical example, we'll build a customer feedback analysis pipeline: read free-text feedback from a spreadsheet, have Gemini classify it by category, sentiment, and urgency, then write the results back to the same sheet.

What You'll Need

Before we start, here's what the setup requires:

• A Google Cloud project (free tier is fine)
• A service account with a JSON key file
• Google Sheets API and Google Drive API enabled
• A Gemini API key from Google AI Studio
• Python 3.10 or later

The flow looks like this:

1. Create a service account and download its credentials
2. Share your target spreadsheet with the service account email
3. Read data from Sheets in Python
4. Process it with Gemini API (classification, summarization, extraction)
5. Write results back to the spreadsheet

Setting Up the Service Account

Go to Google Cloud Console, select your project, and navigate to APIs & Services → Library. Enable:

• Google Sheets API
• Google Drive API (needed to access shared files)

Then go to APIs & Services → Credentials → Create Credentials → Service Account. Give it any name. Under IAM & Admin, grant the Editor role — this covers both reading and writing Sheets data.

Once created, open the service account, go to the Keys tab, click Add Key → JSON, and download the key file. Add this file to your .gitignore immediately. Never commit it.

Sharing the Spreadsheet

Open the downloaded JSON key file and copy the client_email value — it looks like xxx@your-project.iam.gserviceaccount.com. Open your target Google Spreadsheet, click Share, paste that email address, and give it Editor access.

Setting Up Python

pip install google-auth google-auth-oauthlib google-api-python-client google-generativeai

Project layout:

sheets-gemini-pipeline/
├── credentials.json      # Service account key — never commit this
├── pipeline.py           # Main processing logic
└── .env                  # GEMINI_API_KEY

Reading Data from the Spreadsheet

import os
from google.oauth2 import service_account
from googleapiclient.discovery import build
 
SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.readonly",
]
 
def get_sheets_service():
    """Return an authenticated Google Sheets API service object."""
    credentials = service_account.Credentials.from_service_account_file(
        "credentials.json",
        scopes=SCOPES,
    )
    return build("sheets", "v4", credentials=credentials)
 
def read_sheet(spreadsheet_id: str, range_name: str) -> list[list[str]]:
    """
    Read data from a specified range in a spreadsheet.
 
    Args:
        spreadsheet_id: The ID from the spreadsheet URL (/d/{ID}/)
        range_name: e.g. "Sheet1\!A2:C100" (skip the header row)
 
    Returns:
        2D list of cell values. Empty list if no data.
    """
    service = get_sheets_service()
    result = (
        service.spreadsheets()
        .values()
        .get(spreadsheetId=spreadsheet_id, range=range_name)
        .execute()
    )
    return result.get("values", [])

The spreadsheet_id is the string between /d/ and /edit in the spreadsheet URL.

Processing with Gemini API

Here's the core of the pipeline: sending feedback text to Gemini and getting back structured data using response_schema.

import json
import google.generativeai as genai
import typing_extensions as typing
 
genai.configure(api_key=os.environ["GEMINI_API_KEY"])
model = genai.GenerativeModel("gemini-2.5-flash-preview")
 
class FeedbackAnalysis(typing.TypedDict):
    category: str      # bug_report / feature_request / question / praise / other
    sentiment: str     # positive / neutral / negative
    urgency: str       # high / medium / low
    summary: str       # Under 50 characters
    suggestion: str    # Improvement suggestion or "none"
 
def analyze_feedback(feedback_text: str) -> FeedbackAnalysis:
    """
    Analyze a customer feedback entry with Gemini.
 
    Args:
        feedback_text: The raw feedback string to analyze
 
    Returns:
        Structured FeedbackAnalysis dict
 
    Raises:
        ValueError: If the response cannot be parsed
    """
    prompt = f"""Analyze the following customer feedback and return structured data.
 
Feedback:
{feedback_text}
 
Classification rules:
- category: bug_report / feature_request / question / praise / other
- sentiment: positive / neutral / negative
- urgency: high (needs immediate attention) / medium / low
- summary: under 50 characters
- suggestion: a concrete improvement idea, or "none" if no action needed"""
 
    response = model.generate_content(
        prompt,
        generation_config=genai.GenerationConfig(
            response_mime_type="application/json",
            response_schema=FeedbackAnalysis,
        ),
    )
 
    return json.loads(response.text)

Using response_schema=FeedbackAnalysis forces Gemini to return JSON that matches the TypedDict structure. In practice, this eliminates nearly all JSON parsing errors — Gemini simply doesn't return malformed output when a schema is enforced. For more on how structured output works, see Gemini API JSON Mode and Structured Output.

Writing Results Back to the Sheet

def write_results(
    spreadsheet_id: str,
    results: list[FeedbackAnalysis],
    start_row: int = 2,
    start_col: str = "D",
) -> None:
    """
    Write analysis results to spreadsheet columns D through H.
 
    Args:
        spreadsheet_id: Target spreadsheet ID
        results: List of FeedbackAnalysis dicts to write
        start_row: First data row (default 2, skipping headers)
        start_col: First column to write (default D)
    """
    service = get_sheets_service()
 
    values = [
        [
            r["category"],
            r["sentiment"],
            r["urgency"],
            r["summary"],
            r["suggestion"],
        ]
        for r in results
    ]
 
    end_row = start_row + len(values) - 1
    end_col = chr(ord(start_col) + 4)  # D + 4 columns = H
    range_name = f"Sheet1\!{start_col}{start_row}:{end_col}{end_row}"
 
    service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id,
        range=range_name,
        valueInputOption="USER_ENTERED",
        body={"values": values},
    ).execute()
 
    print(f"✅ Wrote {len(values)} rows to {range_name}")

Putting It All Together

Here's the full pipeline with error handling for each row:

import time
 
SPREADSHEET_ID = "your-spreadsheet-id-here"
INPUT_RANGE = "Sheet1\!C2:C1000"  # Column C: feedback text
 
def run_pipeline() -> None:
    """Main entry point for the feedback analysis pipeline."""
 
    print("📊 Reading data from spreadsheet...")
    rows = read_sheet(SPREADSHEET_ID, INPUT_RANGE)
 
    if not rows:
        print("No data found. Exiting.")
        return
 
    feedback_texts = [row[0].strip() for row in rows if row and row[0].strip()]
    print(f"📝 Processing {len(feedback_texts)} feedback entries...")
 
    results = []
    for i, text in enumerate(feedback_texts, 1):
        print(f"  [{i}/{len(feedback_texts)}] {text[:40]}...")
        try:
            analysis = analyze_feedback(text)
            results.append(analysis)
        except Exception as e:
            # Don't let one failure stop the whole batch
            print(f"  ⚠️ Error on row {i + 1}: {e}")
            results.append({
                "category": "error",
                "sentiment": "unknown",
                "urgency": "low",
                "summary": "Processing failed",
                "suggestion": str(e)[:50],
            })
 
        # Rate limit guard: pause every 10 requests
        # Free tier allows 60 rpm — this keeps us safely under that
        if i % 10 == 0:
            time.sleep(1)
 
    print("📤 Writing results to spreadsheet...")
    write_results(SPREADSHEET_ID, results)
    print("🎉 Pipeline complete\!")
 
if __name__ == "__main__":
    run_pipeline()

Common Issues Worth Knowing About

"Request had invalid authentication credentials"

Nine times out of ten, this means the service account email hasn't been added to the spreadsheet's share settings. Double-check by opening the JSON key file, copying client_email, and verifying it appears in the spreadsheet's sharing list.

The other cause: missing Drive API scope. If the spreadsheet lives in a shared drive, you need both spreadsheets and drive.readonly in your SCOPES list.

Gemini occasionally returns unexpected output

Even with response_schema, there's a rare edge case where the response doesn't match the expected structure — usually with very long or ambiguous input text. Wrap your json.loads() call in a try/except and handle failures gracefully per row, rather than letting one bad response crash the whole batch.

try:
    result = json.loads(response.text)
except (json.JSONDecodeError, KeyError):
    result = {
        "category": "parse_error",
        "sentiment": "unknown",
        "urgency": "low",
        "summary": "Parse failed",
        "suggestion": "Review manually",
    }

429 Rate Limit Errors

The free tier is capped at 60 requests per minute. For larger batches, the time.sleep(1) after every 10 requests keeps you well under the limit. For production workloads with higher volume, consider upgrading to a paid tier or implementing exponential backoff. See Gemini API Rate Limiting and Quota Management for production-grade patterns.

Scheduling and Server Integration

Once you have a working pipeline, extending it is straightforward.

Cron job (runs daily at 9 AM local time):

# crontab -e
0 9 * * * cd /path/to/project && python pipeline.py >> logs/pipeline.log 2>&1

FastAPI endpoint (trigger on demand):

from fastapi import FastAPI, BackgroundTasks
 
app = FastAPI()
 
@app.post("/analyze")
async def trigger_analysis(background_tasks: BackgroundTasks):
    background_tasks.add_task(run_pipeline)
    return {"status": "started", "message": "Analysis running in background"}

This pattern works well for Slack bots, webhook receivers, or any system where you want to trigger the pipeline externally without waiting for it to complete.

The First Thing That Breaks as Row Counts Grow

This pipeline is comfortable on a few hundred rows. Past a few thousand, a different problem appears — and for me it was not the Gemini API rate limit but the Sheets API write quota.

An implementation that calls update once per row turns the row count directly into a request count. The processing itself succeeds, then a 429 comes back partway through, and re-running reprocesses the first several hundred rows a second time.

Three changes address it.

1. Batch writes through batchUpdate. Grouping in chunks of 100 rows cuts your request count by a factor of 100
2. Add a marker column for processed rows so a rerun can skip them. Once that exists, dying mid-run stops being dangerous
3. Use exponential backoff on the Gemini API calls. A flat sleep(1) will not get you out of a congested window

Of the three, the marker column mattered most in practice. Once the job is idempotent, failing partway through is no longer something to fear. As an indie developer without heavy monitoring, designing for "breaks safely" has consistently been easier than designing for "never breaks."

If you want to revisit the instruction design itself, "Gemini API System Instructions and Prompt Design" is a good companion piece.

When to Use This vs. Apps Script

Both have legitimate use cases — this isn't about which one is "better."

Apps Script is the right choice when the spreadsheet itself is the center of the workflow, humans are involved in the loop, and setup simplicity matters more than flexibility. Google's built-in Gemini integration handles straightforward tasks well.

Pure Python makes more sense when the AI processing is the main job and the spreadsheet is just a data store. If you need external libraries, long runtimes, retry logic, or want to test your code locally before deploying, the approach in this guide gives you considerably more room to work with.

Start with python pipeline.py and a small test sheet. Once that's working, you'll have a clear picture of whether it fits your needs before investing in the full scheduling and server integration.