MCP + Google Sheets: AI That Reads and Writes Your Bookings Spreadsheet

Why Keeping Google Sheets Is Actually Fine

• Zero cost: Google Sheets is free forever, unlike Calendly ($20+/month), Vagaro ($30+/month), or Fresha (commission on bookings)
• No lock-in: Your data is always yours, in a format every tool can import
• Familiar: Your staff already knows how to use it — no training required
• Flexible: You built it your way; every business has unique booking logic
• Shareable: Send a link and anyone can view (or edit, with permission)

How MCP Bridges AI and Google Sheets

• Read cell ranges, entire sheets, and named ranges
• Write new rows, update existing cells
• Search for values (find a client by name)
• Format cells programmatically

Two Approaches: Choose What Fits Your Technical Level

Approach A: MCP Server for Claude Desktop (No Code Required)

• Claude Desktop installed
• Google account
• Node.js installed (node --version to check)

npm install -g @modelcontextprotocol/server-google-sheets

{
  "mcpServers": {
    "google-sheets": {
      "command": "npx",
      "args": ["@modelcontextprotocol/server-google-sheets"],
      "env": {
        "GOOGLE_SHEETS_CREDENTIALS_PATH": "path/to/your-credentials.json",
        "GOOGLE_SHEETS_SPREADSHEET_ID": "YOUR_SPREADSHEET_ID"
      }
    }
  }
}

Approach B: Python + gspread (For Custom Automation Scripts)

pip install gspread google-auth anthropic

Setting Up Google Sheets API Access

1. Go to Google Cloud Console and create a new project (name it something like sheets-ai-agent)
2. Go to APIs & Services → Enable APIs and enable Google Sheets API
3. Go to APIs & Services → Credentials and click Create Credentials → Service Account
4. Name the service account ai-agent and click through the rest
5. On the service account page, go to Keys → Add Key → Create new key → JSON
6. Download the JSON file and save it as credentials.json in a safe location (NOT inside a public repo)

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "YOUR_PRIVATE_KEY_ID",
  "private_key": "-----BEGIN RSA PRIVATE KEY-----\nYOUR_PRIVATE_KEY_HERE\n-----END RSA PRIVATE KEY-----\n",
  "client_email": "ai-agent@your-project-id.iam.gserviceaccount.com",
  "client_id": "YOUR_CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token"
}

Recommended Spreadsheet Structure

5 Automation Workflows With Code

Workflow 1: Auto-Log New Bookings from WhatsApp or Email

import gspread
from google.oauth2.service_account import Credentials
from datetime import datetime

def log_booking(client_name: str, phone: str, service: str,
                date: str, time: str, price: float, staff: str = "TBD"):
    """Append a new booking row to the Appointments sheet."""
    creds = Credentials.from_service_account_file(
        "path/to/your-credentials.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    spreadsheet = gc.open_by_key("YOUR_SPREADSHEET_ID")
    sheet = spreadsheet.worksheet("Appointments")

    new_row = [
        date,          # Date
        time,          # Time
        client_name,   # Client Name
        phone,         # Phone
        service,       # Service
        staff,         # Staff
        "",            # Duration (fill in later)
        price,         # Price
        "Confirmed",   # Status
        ""             # Notes
    ]

    sheet.append_row(new_row, value_input_option="USER_ENTERED")
    return f"Booked: {client_name} for {service} on {date} at {time}"

Workflow 2: Morning Briefing — Today's Schedule via Slack

import gspread
import anthropic
import requests
from google.oauth2.service_account import Credentials
from datetime import date

def send_morning_briefing():
    creds = Credentials.from_service_account_file(
        "path/to/your-credentials.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    sheet = gc.open_by_key("YOUR_SPREADSHEET_ID").worksheet("Appointments")

    today = date.today().strftime("%Y-%m-%d")
    all_rows = sheet.get_all_records()
    today_rows = [r for r in all_rows if r.get("Date") == today]

    if not today_rows:
        schedule_text = "No appointments today."
    else:
        lines = []
        for appt in sorted(today_rows, key=lambda x: x["Time"]):
            lines.append(
                f"  {appt['Time']} — {appt['Client Name']} ({appt['Service']}, "
                f"${appt['Price']}, Staff: {appt['Staff']})"
            )
        schedule_text = "\n".join(lines)

    client = anthropic.Anthropic(api_key="YOUR_ANTHROPIC_API_KEY")
    summary = client.messages.create(
        model="claude-opus-4-5",
        max_tokens=300,
        messages=[{
            "role": "user",
            "content": f"Write a brief, friendly morning briefing for a salon owner. Today's schedule:\n{schedule_text}\nInclude total expected revenue and any notes to watch for."
        }]
    )

    # Send to Slack
    requests.post("YOUR_SLACK_WEBHOOK_URL", json={"text": summary.content[0].text})

send_morning_briefing()

Workflow 3: No-Show Tracker

def mark_no_shows():
    creds = Credentials.from_service_account_file(
        "path/to/your-credentials.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    sheet = gc.open_by_key("YOUR_SPREADSHEET_ID").worksheet("Appointments")

    today = date.today().strftime("%Y-%m-%d")
    all_records = sheet.get_all_records()

    # Find confirmed appointments from today that weren't completed
    no_shows = []
    for i, row in enumerate(all_records, start=2):  # row 1 is header
        if row["Date"] == today and row["Status"] == "Confirmed":
            sheet.update_cell(i, 9, "No-Show")  # Column 9 = Status
            no_shows.append(row["Client Name"])

    return f"Marked {len(no_shows)} no-shows: {', '.join(no_shows)}"

Workflow 4: Weekly Revenue Report

def weekly_revenue_report():
    creds = Credentials.from_service_account_file(
        "path/to/your-credentials.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    sheet = gc.open_by_key("YOUR_SPREADSHEET_ID").worksheet("Appointments")
    revenue_sheet = gc.open_by_key("YOUR_SPREADSHEET_ID").worksheet("Revenue")

    from datetime import timedelta
    last_monday = date.today() - timedelta(days=date.today().weekday() + 7)
    last_sunday = last_monday + timedelta(days=6)

    all_records = sheet.get_all_records()
    week_appts = [
        r for r in all_records
        if last_monday.strftime("%Y-%m-%d") <= r["Date"] <= last_sunday.strftime("%Y-%m-%d")
    ]

    completed = [r for r in week_appts if r["Status"] == "Completed"]
    no_shows = [r for r in week_appts if r["Status"] == "No-Show"]
    total_revenue = sum(float(r["Price"] or 0) for r in completed)
    avg_ticket = total_revenue / len(completed) if completed else 0
    no_show_rate = len(no_shows) / len(week_appts) * 100 if week_appts else 0

    new_row = [
        last_monday.strftime("%Y-%m-%d"),
        len(completed),
        round(total_revenue, 2),
        round(avg_ticket, 2),
        len(no_shows),
        round(no_show_rate, 1)
    ]
    revenue_sheet.append_row(new_row)

    return f"Week of {last_monday}: {len(completed)} appointments, ${total_revenue:.2f} revenue, {no_show_rate:.1f}% no-show rate"

Workflow 5: Availability Checker (Full Example)

import gspread
import anthropic
from google.oauth2.service_account import Credentials
from datetime import date, timedelta

def check_availability(query: str) -> str:
    """
    Given a natural language availability query, check Google Sheets
    and return a plain-English answer.
    """
    creds = Credentials.from_service_account_file(
        "path/to/your-credentials.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    sheet = gc.open_by_key("YOUR_SPREADSHEET_ID").worksheet("Appointments")

    # Get all upcoming appointments
    all_records = sheet.get_all_records()
    today = date.today()
    upcoming = [
        r for r in all_records
        if r.get("Date", "") >= today.strftime("%Y-%m-%d")
        and r.get("Status") in ("Confirmed", "Pending")
    ]

    # Build a booked-slots summary for the next 7 days
    booked_summary = []
    for r in upcoming:
        booked_summary.append(f"{r['Date']} at {r['Time']} ({r['Service']}, {r['Duration']} min)")

    booked_text = "\n".join(booked_summary) if booked_summary else "No upcoming bookings"

    client = anthropic.Anthropic(api_key="YOUR_ANTHROPIC_API_KEY")

    response = client.messages.create(
        model="claude-opus-4-5",
        max_tokens=200,
        system=f"""You are a booking assistant for a hair salon.
Business hours: Mon-Sat 9am-6pm, closed Sunday.
Each slot is 60 minutes minimum.
Today is {today.strftime('%A, %B %d, %Y')}.

Currently booked slots (next 7 days):
{booked_text}

Answer availability questions based on this data. Be specific: give a yes/no answer and suggest alternatives if the requested slot is taken.""",
        messages=[{"role": "user", "content": query}]
    )

    return response.content[0].text


# Example
print(check_availability("Is 3pm this Tuesday free for a haircut?"))
# → "Tuesday 3pm is taken (we have Maria Chen booked 3pm-4:30pm).
#    I have Tuesday at 10am, 1pm, or Wednesday at 3pm available. Which works for you?"

Comparison: This Setup vs Booking Platforms

FAQ

Related Articles

• What Is MCP? The Model Context Protocol Explained for Local Business Owners
• Claude + MCP + Google Calendar: Automate Your Booking Confirmations
• MCP + Supabase: Give Your AI Agent a Persistent Database
• MCP + Notion: Build a Client CRM for Your Local Business
• Best MCP Servers for Local Business: The Complete Setup Guide