# 🛠️ Skill: Placement Spreadsheet Filler (MCP Tool)

---

## Skill Name
`placement-sheet-filler`

## Description
An MCP tool that automatically fills student placement details into Google Sheets or Excel files. The tool reads the student's profile from a local JSON file, fetches the headers from the target spreadsheet, smart-maps profile fields to columns, and appends a new row.

---

## Exposed MCP Tools

### 1. `fill_sheet`
> Primary tool. Fills student data into a given spreadsheet.

**Parameters:**
| Parameter | Type | Required | Description |
|---|---|---|---|
| `sheet_url` | string | ✅ | Google Sheets URL or Excel file path/URL |
| `sheet_name` | string | ❌ | Specific tab/sheet name (defaults to first sheet) |
| `profile_path` | string | ✅ | Path to local student profile JSON file |
| `dry_run` | boolean | ❌ | If true, preview mapping without writing (default: false) |

**Returns:**
```json
{
  "status": "success",
  "row_appended": 42,
  "mapped_fields": {
    "Name": "John Doe",
    "Roll Number": "21CS001",
    "Branch": "CSE",
    "CGPA": "8.9"
  },
  "unmapped_columns": ["Internship Duration"]
}
```

---

### 2. `preview_mapping`
> Shows how profile fields will map to spreadsheet columns without writing anything.

**Parameters:**
| Parameter | Type | Required | Description |
|---|---|---|---|
| `sheet_url` | string | ✅ | Google Sheets URL or Excel file path |
| `profile_path` | string | ✅ | Path to local student profile JSON file |

**Returns:** Column → Profile field mapping as a table.

---

### 3. `update_profile`
> Updates one or more fields in the local student profile.

**Parameters:**
| Parameter | Type | Required | Description |
|---|---|---|---|
| `profile_path` | string | ✅ | Path to profile JSON |
| `updates` | object | ✅ | Key-value pairs to update (e.g. `{"cgpa": "9.1"}`) |

---

### 4. `check_profile`
> Reads and displays the current student profile.

**Parameters:**
| Parameter | Type | Required | Description |
|---|---|---|---|
| `profile_path` | string | ✅ | Path to profile JSON |

---

## Student Profile Format (JSON)

```json
{
  "name": "John Doe",
  "roll_no": "21CS001",
  "branch": "Computer Science & Engineering",
  "cgpa": "8.9",
  "email": "john@college.edu",
  "phone": "+91 9876543210",
  "resume_link": "https://drive.google.com/..."
}
```

> 💡 **Extensible**: New fields can be added to this file at any time. The mapping engine will automatically discover them.

---

## Smart Column Mapping Algorithm

The tool uses a **multi-stage fuzzy + semantic mapping** strategy:

```
Stage 1: Exact match
  e.g., column "email" → profile field "email"

Stage 2: Fuzzy string match (Levenshtein distance)
  e.g., column "Roll Number" → profile field "roll_no"

Stage 3: Synonym/alias table
  e.g., column "CGPA / GPA" → profile field "cgpa"

Stage 4: LLM-assisted mapping (fallback)
  e.g., column "Academic Performance" → profile field "cgpa"

Stage 5: Skip + report
  Columns with no confident match are listed as "unmapped_columns"
```

**Confidence threshold**: Matches below 60% confidence are skipped and reported.

---

## Authentication

### Google Sheets
- Uses **OAuth 2.0** with the student's own Google account.
- On first use, the user opens a one-time auth URL in their browser.
- Token is cached locally for future use.
- Scope: `https://www.googleapis.com/auth/spreadsheets`

### Excel
- For local files: direct `openpyxl` read/write.
- For cloud-hosted Excel (OneDrive/SharePoint): URL download + re-upload via Microsoft Graph API *(future scope)*.

---

## Error Handling

| Scenario | Behaviour |
|---|---|
| Profile field not in sheet | Silently skipped, reported in response |
| Sheet column not in profile | Listed under `unmapped_columns` |
| Auth failure | Returns auth URL for re-authentication |
| Network error | Retry 3x, then return error message |
| Sheet not found / wrong tab name | Returns list of available tab names |
| Duplicate row detection (future) | Warn user, confirm before appending |

---

## Hosting Notes

- MCP server runs as a **stateless HTTP service**.
- Each request carries the `profile_path` (student-side) and `sheet_url`.
- No student data persists on the server — all profile data stays local with the student.
- Deployable on: **Railway, Render, VPS, Docker**.

---

## Usage Example (MCP Client)

```
User: Fill my details in this Google Sheet: https://docs.google.com/spreadsheets/d/...

Tool Call: fill_sheet(
  sheet_url = "https://docs.google.com/...",
  profile_path = "/home/student/profile.json"
)

Response: ✅ Row appended successfully!
  Mapped: Name, Roll No, Branch, CGPA, Email, Phone
  Unmapped columns: ["Preferred Location", "Internship Duration"]
```

---

## File Structure (Project)

```
placement-sheet-filler/
├── server.py               # MCP server entry point
├── tools/
│   ├── fill_sheet.py       # fill_sheet tool logic
│   ├── preview_mapping.py  # preview_mapping tool logic
│   ├── update_profile.py   # update_profile tool logic
│   └── check_profile.py    # check_profile tool logic
├── core/
│   ├── mapper.py           # Smart column mapping engine
│   ├── sheets_client.py    # Google Sheets API wrapper
│   └── excel_client.py     # openpyxl Excel wrapper
├── auth/
│   └── google_auth.py      # OAuth 2.0 flow handler
├── profile.json            # Student profile (local, not committed)
├── requirements.txt
└── README.md
```