How to Connect AI Models to Google Sheets

Connecting AI to Google Sheets is one of the fastest ways to add AI capabilities to a workflow that already lives in a spreadsheet. The connect ai api google sheets automation pattern lets you process every row, write AI-generated content back to specific cells, and do it on a schedule without any manual work. I use this for clients who want AI-written ad copy in a sheet, lead scoring on a CRM export, or email subject line generation from a campaign list.

The Google Sheets API is the cleanest way to read and write data programmatically. With a service account, there is no manual OAuth flow for automated scripts.

What You Need Before Starting

• A Google Cloud project (free at console.cloud.google.com)
• Google Sheets API enabled on that project
• A service account with JSON credentials
• gspread and google-auth Python packages
• An AI API key (OpenAI or Anthropic)
• A Google Sheet shared with your service account email

Step 1: Set Up a Google Service Account

1. Go to console.cloud.google.com
2. Create a new project or select an existing one
3. Navigate to "APIs and Services" > "Enable APIs"
4. Enable "Google Sheets API"
5. Go to "Credentials" > "Create Credentials" > "Service Account"
6. Name it (e.g., sheets-automation), click through, create
7. Click the service account, go to "Keys" tab, "Add Key" > "Create new key" > JSON
8. Download the JSON file. Store it at a safe path, not in version control.

Add to .env:

GOOGLE_SERVICE_ACCOUNT_PATH=/absolute/path/to/your-credentials.json

Step 2: Share Your Sheet with the Service Account

Open the JSON credentials file. Find the client_email field (looks like [email protected]).

Open your Google Sheet. Click "Share." Paste the service account email. Give "Editor" access.

Step 3: Connect to Google Sheets with gspread

pip install gspread google-auth openai anthropic python-dotenv

import os
import gspread
from google.oauth2.service_account import Credentials
from dotenv import load_dotenv

load_dotenv()

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.readonly"
]


def get_sheets_client() -> gspread.Client:
    """Initialize and return a gspread client using service account credentials."""
    creds = Credentials.from_service_account_file(
        os.getenv("GOOGLE_SERVICE_ACCOUNT_PATH"),
        scopes=SCOPES
    )
    return gspread.authorize(creds)


def open_sheet(spreadsheet_url: str, worksheet_name: str = None):
    """Open a specific worksheet by URL."""
    client = get_sheets_client()
    spreadsheet = client.open_by_url(spreadsheet_url)
    
    if worksheet_name:
        return spreadsheet.worksheet(worksheet_name)
    return spreadsheet.sheet1  # Default to first sheet


# Test the connection
SHEET_URL = "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID"
sheet = open_sheet(SHEET_URL)
print(f"Connected to: {sheet.title}")
print(f"Rows: {sheet.row_count}")

Step 4: Read Data from a Sheet

import pandas as pd


def read_sheet_as_dataframe(sheet_url: str, worksheet: str = None) -> pd.DataFrame:
    """Read a Google Sheet into a pandas DataFrame."""
    ws = open_sheet(sheet_url, worksheet)
    data = ws.get_all_records()  # Returns list of dicts using header row
    return pd.DataFrame(data)


def read_sheet_range(sheet_url: str, range_notation: str) -> list:
    """Read a specific range from a sheet. Range like 'A1:D50'"""
    client = get_sheets_client()
    spreadsheet = client.open_by_url(sheet_url)
    ws = spreadsheet.sheet1
    return ws.get(range_notation)


# Read all data
df = read_sheet_as_dataframe(SHEET_URL)
print(df.head())
print(f"Shape: {df.shape}")

Step 5: Process Rows with AI and Write Results Back

This is the core pattern: read a column, process each value with AI, write to an output column:

from openai import OpenAI
import time

openai_client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))


def generate_ad_copy(product_name: str, key_benefit: str) -> str:
    """Generate Facebook ad copy for a product."""
    response = openai_client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {
                "role": "system",
                "content": "Write Facebook ad copy. Format: Hook (1 sentence) + Body (2 sentences) + CTA (1 sentence). Total under 90 words."
            },
            {
                "role": "user",
                "content": f"Product: {product_name}\nKey benefit: {key_benefit}"
            }
        ],
        temperature=0.7
    )
    return response.choices[0].message.content


def process_sheet_with_ai(
    sheet_url: str,
    input_columns: list[str],
    output_column: str,
    processor_fn: callable,
    skip_if_filled: bool = True
) -> None:
    """
    Process rows in a sheet using an AI function, writing results to an output column.
    
    Args:
        sheet_url: Google Sheets URL
        input_columns: Column header names to read as input
        output_column: Column header name to write AI output to
        processor_fn: Function that takes *input_values and returns a string
        skip_if_filled: Skip rows that already have a value in output_column
    """
    ws = open_sheet(sheet_url)
    all_data = ws.get_all_records()
    headers = ws.row_values(1)
    
    # Find column indices (1-indexed for gspread)
    output_col_idx = headers.index(output_column) + 1
    
    processed = 0
    skipped = 0
    
    for row_idx, row in enumerate(all_data, start=2):  # Row 1 is header
        # Skip if output already filled
        if skip_if_filled and row.get(output_column):
            skipped += 1
            continue
        
        # Skip if required inputs are missing
        input_values = [str(row.get(col, "")).strip() for col in input_columns]
        if not all(input_values):
            continue
        
        # Generate AI output
        try:
            result = processor_fn(*input_values)
            
            # Write to output column
            ws.update_cell(row_idx, output_col_idx, result)
            processed += 1
            
            print(f"Row {row_idx}: Generated '{result[:50]}...'")
            time.sleep(0.5)  # Rate limit courtesy
            
        except Exception as e:
            print(f"Row {row_idx} error: {e}")
    
    print(f"\nDone. Processed: {processed}, Skipped (already filled): {skipped}")


# Example: Generate ad copy for each product in the sheet
# Sheet has columns: Product Name, Key Benefit, Ad Copy (output)
process_sheet_with_ai(
    sheet_url=SHEET_URL,
    input_columns=["Product Name", "Key Benefit"],
    output_column="Ad Copy",
    processor_fn=generate_ad_copy
)

Step 6: Write Multiple Values at Once (Batch Updates)

Writing one cell at a time is slow and uses many API calls. Batch updates are faster:

def batch_write_to_sheet(
    sheet_url: str,
    column_name: str,
    values: list[str],
    start_row: int = 2
) -> None:
    """
    Write a list of values to a column starting at a specific row.
    Much faster than writing cell by cell.
    """
    ws = open_sheet(sheet_url)
    headers = ws.row_values(1)
    col_idx = headers.index(column_name) + 1
    col_letter = gspread.utils.rowcol_to_a1(1, col_idx)[0]
    
    # Format for batch update: list of [value] lists
    cell_values = [[v] for v in values]
    
    range_notation = f"{col_letter}{start_row}:{col_letter}{start_row + len(values) - 1}"
    ws.update(range_notation, cell_values)
    
    print(f"Wrote {len(values)} values to column '{column_name}'")


# Process all rows first, then batch write
def process_all_rows(sheet_url: str) -> None:
    ws = open_sheet(sheet_url)
    df = pd.DataFrame(ws.get_all_records())
    
    results = []
    for _, row in df.iterrows():
        if row.get("Product Name") and row.get("Key Benefit"):
            copy = generate_ad_copy(row["Product Name"], row["Key Benefit"])
            results.append(copy)
            time.sleep(0.3)
        else:
            results.append("")  # Preserve empty rows
    
    batch_write_to_sheet(sheet_url, "Ad Copy", results)

What to Build Next

• Schedule this script to run nightly with cron or Windows Task Scheduler
• Add a "Status" column that records when each row was processed and by which model
• Build a two-way sync: changes in the sheet trigger re-processing of that specific row

Related Reading

• How to Set Up Perplexity API for Research Automation - Use Perplexity to fill research columns in a sheet
• How to Handle AI API Rate Limits Gracefully - Large sheets trigger rate limits fast
• How to Configure Claude for JSON Output Mode - Write structured multi-column data from a single AI call