# Google Calendar-Sheet Assistant — Full Edition (MCP + LLM)

A complete Google Calendar assistant. Not just "create a meeting" — every
common calendar task, exposed as an MCP tool, chosen automatically by an LLM
based on what you type.

```
        User
         │
         ▼
"Move my 3 PM meeting to 5 PM"
         │
         ▼
   OpenAI / GPT-OSS-120B
         │
(Decides which tool(s) to call — can chain more than one)
         │
         ▼
   MCP Client (Python)          ← client.py
         │
 Calls tool(s) through MCP protocol
         │
         ▼
Google Calendar MCP Server       ← server.py  (14 tools)
         │
 Executes Google Calendar API    ← calendar_utils.py
         │
         ▼
     Google Calendar
         │
         ▼
Success / Event Details
         │
         ▼
        User
```

## All supported tasks

| Task | Example prompt | Tool used |
|---|---|---|
| Create event | "Schedule a meeting tomorrow at 3 PM." | `schedule_event` |
| Update event | "Move my 3 PM meeting to 5 PM." | `search_events`/`list_events` → `update_event` |
| Delete event | "Cancel tomorrow's interview." | `search_events` → `cancel_event` |
| List events | "What are my meetings today?" | `daily_agenda` |
| Search events | "Find all AI meetings this month." | `search_events` |
| Get event details | "Show details of my client meeting." | `search_events` → `get_event` |
| Check free/busy | "Am I free between 2 PM and 4 PM?" | `check_freebusy` |
| Daily agenda | "What's on my schedule today?" | `daily_agenda` |
| Weekly agenda | "Show this week's calendar." | `weekly_agenda` |
| Monthly agenda | "Show my August meetings." | `monthly_agenda` |
| Recurring events | "Every Monday 10 AM team standup." | `schedule_event` (with `recurrence`) |
| Invite attendees | "Create meeting and invite abc@gmail.com." | `schedule_event` (with `attendees`) |
| Add Google Meet link | "Create an online meeting." | `schedule_event` (with `add_meet_link`) |
| Set reminders | "Remind me 30 minutes before." | `schedule_event`/`update_event` (with `reminder_minutes_before`) |
| Add location | "Meeting at Baner Office." | `schedule_event` (with `location`) |
| Add description | "Agenda: Sprint Planning." | `schedule_event` (with `description`) |
| List calendars | "Show all my calendars." | `list_calendars` |
| Move event | "Move this event to my Work calendar." | `move_event` |
| Import events | Bringing in an event from another system | `import_event` |
| Watch calendar changes | Trigger the agent on new events | `watch_calendar` (needs a public webhook URL — see note below) |

## Project structure

```
google-calendar-sheet-mcp-/
├── server.py            # MCP server — 15 Calendar tools + 8 Sheets tools
├── client.py            # MCP client — LLM picks tool(s), can chain multiple calls
├── calendar_utils.py     # All Google Calendar API logic + OAuth (token.json)
├── sheets_utils.py        # All Google Sheets API logic + OAuth (token_sheets.json)
├── requirements.txt
├── .env.example
└── README.md
```

## Setup

### 1. Google Cloud (one-time)
1. [Google Cloud Console](https://console.cloud.google.com/) → create/select a project.
2. **APIs & Services → Library** → enable **Google Calendar API** AND **Google Sheets API** (search + enable both, same project).
3. **OAuth consent screen** → External → add your email as a test user.
4. **Credentials → Create Credentials → OAuth client ID** → **Desktop app**.
5. Download JSON → rename to `credentials.json` → place next to `server.py`. (This one file is reused by both `calendar_utils.py` and `sheets_utils.py`.)

### 2. Install
```bash
cd AI-AGENT
python3 -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate
pip install -r requirements.txt
```

### 3. Configure
```bash
cp .env.example .env
# add GROQ_API_KEY (https://console.groq.com/keys)
```

### 4. Run
```bash
python3 client.py
```
First run opens a browser to authorize **Calendar** access (saves `token.json`).
The **first time you ask it to do anything with Sheets**, a *second*
browser prompt appears — authorizing Sheets access separately (saves
`token_sheets.json`). This is expected: Calendar and Sheets are
different permissions, so they get separate consent + separate token
files, even though both use the same `credentials.json` app identity.

## Google Sheets — setup notes & example prompts

Every Sheets tool needs a `spreadsheet_id` — the long string in a
sheet's URL, between `/d/` and `/edit`:
```
https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz/edit
                                        └──────── this part ────────┘
```

**Example prompts:**
- "Create a new spreadsheet called 'Q3 Leads'." → `create_spreadsheet`
- "In spreadsheet [id], what's in Sheet1 rows 1 to 10?" → `read_sheet`
- "Add a row to spreadsheet [id]: Priya, priya@email.com, Contacted" → `append_sheet_row`
- "Overwrite A1:B2 in [id] with these values..." → `write_sheet`
- "What tabs does spreadsheet [id] have?" → `list_sheet_tabs`
- "Add a new tab called 'August' to [id]." → `add_sheet_tab`
- "Clear rows 2 to 50 in Sheet1 of [id]." → `clear_sheet_range`
- "Give me the title and link for spreadsheet [id]." → `get_spreadsheet_info`

**Sharing note**: the Google account you authorized with (whichever
one created `token_sheets.json`) needs edit access to any spreadsheet
you ask it to read/write — either it owns the sheet, or someone shared
it with that account.

## How multi-step requests work

Some tasks need more than one tool call — e.g. "Move my 3 PM meeting to 5 PM"
requires first *finding* the event (no id was given), then updating it.
`client.py` handles this with a loop: it keeps letting the LLM call tools
back-to-back (find → then act) until the LLM has enough information to give
you a final plain-language answer. You'll see each intermediate tool call
printed, e.g.:
```
You: Move my 3 PM meeting to 5 PM
[client] LLM chose tool: search_events({'query': '3 PM'})
[client] LLM chose tool: update_event({'event_id': 'abc123', 'start_time': '...', 'end_time': '...'})

Assistant: Done — moved your meeting to 5:00–5:30 PM today.
```

## Recurring events — how RRULE works

`schedule_event`'s `recurrence` argument takes standard iCalendar RRULE
strings. The LLM constructs these automatically, but for reference:
- Every Monday: `RRULE:FREQ=WEEKLY;BYDAY=MO`
- Every weekday: `RRULE:FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR`
- Every day for 10 occurrences: `RRULE:FREQ=DAILY;COUNT=10`
- Every month on the 1st: `RRULE:FREQ=MONTHLY;BYMONTHDAY=1`

## Watch calendar changes — important note

`watch_calendar` sets up Google push notifications, but Google will only
send them to a **public HTTPS URL you control** — not `localhost`. For
local development:
1. Run a tiny webhook receiver (Flask/FastAPI) that logs/handles the POST
   Google sends on changes.
2. Expose it publicly with a tunnel tool (e.g. `ngrok http 8000`).
3. Call `watch_calendar` with that public `https://...ngrok.../webhook` URL.
4. The subscription **expires** (Google enforces a max TTL, typically up to
   ~7 days) — re-run `watch_calendar` periodically (e.g. a daily cron job)
   to keep it alive.

This part is the most "production infrastructure"-heavy feature here — the
other 13 tools work immediately with no extra hosting required.

## Testing the server alone (no LLM)

```bash
npx @modelcontextprotocol/inspector python3 server.py
```
Lets you call any of the 14 tools directly from a browser UI to confirm the
Calendar integration works before wiring up chat.

## Troubleshooting

| Problem | Fix |
|---|---|
| `FileNotFoundError: credentials.json not found` | Complete Google Cloud setup step 1–5 |
| `invalid_grant` / token errors | Delete `token.json`, re-run to re-authorize |
| LLM never calls a tool | Check `OPENAI_API_KEY` in `.env` |
| Update/cancel says "event not found" | The LLM needs the real `event_id` — make sure it searched/listed first |
| Wrong timezone on events | Set `CALENDAR_TIMEZONE` in `.env` |
| Recurring event didn't repeat as expected | Double check the RRULE the LLM generated — ask it to explain the rule if unsure |