🛠️ Skill: Placement Spreadsheet Filler (MCP Tool)

Description

# 🛠️ 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": "[email protected]", "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 ```

Security Status