domains / data / excel-automation / excel-automation

excel-automation

Skill card

combined

The daymade skill is a local, code-first procedure using openpyxl, stdlib zipfile/xml parsing, and macOS AppleScript. The two Composio skills are identical MCP-based procedures that drive Microsoft Excel and Google Sheets through the Rube MCP server. They overlap in the goal of automating spreadsheets but use entirely different toolchains, prerequisites, and workflows. Crowning one would hide a real alternative: local file manipulation versus cloud/MCP-driven spreadsheet management.

Repositories

Other copies

Combined skill

# Excel Automation

Automate spreadsheet operations using either local Python tooling or MCP-based cloud integration.

## Choose Your Approach

- **Local file manipulation** (openpyxl, zipfile, AppleScript): Use when working with local .xlsx/.xlsm files, complex financial models, or macOS Excel window control.
- **MCP cloud integration** (Rube MCP, Google Sheets, OneDrive): Use when working with cloud-hosted spreadsheets, CRM syncs, or when local file access is unavailable.

## Local Approach (openpyxl + stdlib)

### Quick Start

Before controlling Excel windows, check user activity and agent occupancy. Use only foreground operations covered by the current task's authorization.

```bash
# Create a formatted Excel report
uv run --with openpyxl scripts/create_formatted_excel.py output.xlsx

# Parse a complex xlsm that openpyxl can't handle
uv run scripts/parse_complex_excel.py model.xlsm              # List sheets
uv run scripts/parse_complex_excel.py model.xlsm "DCF"        # Extract a sheet
uv run scripts/parse_complex_excel.py model.xlsm --fix        # Fix corrupted names

# Control Excel via AppleScript (with timeout to prevent hangs)
timeout 5 osascript -e 'tell application "Microsoft Excel" to activate'
```

### Tool Selection

- Simple file (no VBA, <1MB): openpyxl or pandas
- .xlsm, investment bank file, or >1MB: zipfile + xml.etree.ElementTree
- True .xls (BIFF): xlrd
- Always run `file <path>` first; extensions lie.

### Professional Color Convention

| Color | RGB | Meaning |
|-------|-----|---------|
| Blue | 0000FF | User input / assumption |
| Black | 000000 | Calculated value |
| Green | 008000 | Cross-sheet reference |
| White on dark blue | FFFFFF on 4472C4 | Section headers |

### Number Formats

- Currency: `'$#,##0'`
- Percentage: `'0.0%'`
- Multiplier: `'0.0x'`

### Conditional Formatting (Sensitivity Tables)

Use ColorScaleRule with red-to-green gradient for sensitivity analysis.

## MCP Cloud Approach (Rube MCP)

### Setup

Requires the Rube MCP server connected at `https://rube.app/mcp`. Ensure an active connection for the `excel` and/or `googlesheets` toolkit before executing tools. If no connection is active, initiate one via `RUBE_MANAGE_CONNECTIONS`.

### Core Workflows

1. **Create workbook**: `EXCEL_CREATE_WORKBOOK` generates a .xlsx file and uploads to OneDrive.
2. **Write data**: `GOOGLESHEETS_BATCH_UPDATE` with `spreadsheet_id`, `sheet_name`, `values` (2D array), optional `first_cell_location` (A1 notation), and `valueInputOption` (`USER_ENTERED` or `RAW`).
3. **Upsert rows**: `GOOGLESHEETS_UPSERT_ROWS` with `keyColumn` for matching, `headers`, and `rows`. Use `strictMode: true` to error on mismatched columns.
4. **Format cells**: `GOOGLESHEETS_FORMAT_CELL` with `range` (A1 notation), `bold`, `italic`, `fontSize`, and background color as 0.0-1.0 float components.
5. **Add worksheet**: `GOOGLESHEETS_ADD_SHEET` with `title` and optional `forceUnique`.
6. **Read data**: `GOOGLESHEETS_BATCH_GET` to retrieve and validate cell ranges.

### Recommended Execution Plan

1. Create or locate the spreadsheet
2. Confirm the destination tab; create if missing
3. Read existing headers to align columns
4. Write or upsert data
5. Apply formatting
6. Verify results
7. Fallback: If Google Sheets creation returns HTTP 403, use `EXCEL_CREATE_WORKBOOK` for local .xlsx output

### Known Pitfalls

- HTTP 403 on sheet creation: fall back to local .xlsx output
- Color values in `GOOGLESHEETS_FORMAT_CELL` are 0.0-1.0 floats, not 0-255 integers
- `valueInputOption: RAW` stores formulas as text; use `USER_ENTERED` to parse them