Excel Automation

# Excel Automation

Automate spreadsheet operations including creating workbooks, writing data, formatting cells, upserting rows, and managing worksheets. Works with Microsoft Excel (OneDrive) and Google Sheets.

**Toolkit docs:** [composio.dev/toolkits/excel](https://composio.dev/toolkits/excel)

---

## Setup

This skill requires the **Rube MCP server** connected at `https://rube.app/mcp`.

Before executing any tools, ensure an active connection exists for the `excel` (and optionally `googlesheets`) toolkit. If no connection is active, initiate one via `RUBE_MANAGE_CONNECTIONS`.

---

## Core Workflows

### 1. Create a New Excel Workbook

Use `EXCEL_CREATE_WORKBOOK` to generate a new `.xlsx` file and upload it to OneDrive.

**Tool:** `EXCEL_CREATE_WORKBOOK`

**Steps:**
1. Call `EXCEL_CREATE_WORKBOOK` with worksheet names and data
2. The tool creates a `.xlsx` file and uploads it to OneDrive
3. Use the returned file path/URL for subsequent operations

---

### 2. Write Data to a Spreadsheet

Use `GOOGLESHEETS_BATCH_UPDATE` to write values to a specific range or append rows.

**Tool:** `GOOGLESHEETS_BATCH_UPDATE`

**Key Parameters:**
- `spreadsheet_id` (required) -- The spreadsheet ID from the URL (44-char alphanumeric string)
- `sheet_name` (required) -- Tab name, e.g., `"Sheet1"`, `"Sales Data"`
- `values` (required) -- 2D array of cell values, e.g., `[["Name","Amount"],["Alice",100]]`
- `first_cell_location` -- Starting cell in A1 notation (e.g., `"A1"`, `"D3"`). Omit to append rows
- `valueInputOption` -- `"USER_ENTERED"` (default, parses formulas) or `"RAW"` (stores as-is)

**Example:**
```
Tool: GOOGLESHEETS_BATCH_UPDATE
Arguments:
  spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  sheet_name: "Sheet1"
  values: [["Item","Cost","Stocked"],["Wheel",20.50,true],["Screw",0.50,true]]
  first_cell_location: "A1"
```

---

### 3. Upsert Rows by Key Column

Use `GOOGLESHEETS_UPSERT_ROWS` to update existing rows by matching a key column, or append new rows if no match is found. Ideal for CRM syncs, inventory updates, and deduplication.

**Tool:** `GOOGLESHEETS_UPSERT_ROWS`

**Key Parameters:**
- `spreadsheetId` (required) -- The spreadsheet ID
- `sheetName` (required) -- Tab name
- `rows` (required) -- 2D array of data rows (min 1 row). If `headers` is omitted, the first row is treated as headers
- `headers` -- Column names for the data, e.g., `["Email","Phone","Status"]`
- `keyColumn` -- Column header to match on, e.g., `"Email"`, `"SKU"`, `"Lead ID"`
- `strictMode` -- `true` (default) errors on mismatched columns; `false` truncates silently

**Example:**
```
Tool: GOOGLESHEETS_UPSERT_ROWS
Arguments:
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  sheetName: "Contacts"
  keyColumn: "Email"
  headers: ["Email","Phone","Status"]
  rows: [["john@example.com","555-0101","Active"],["jane@example.com","555-0102","Pending"]]
```

---

### 4. Format Cells

Use `GOOGLESHEETS_FORMAT_CELL` to apply bold, italic, font size, and background colors to ranges.

**Tool:** `GOOGLESHEETS_FORMAT_CELL`

**Key Parameters:**
- `spreadsheet_id` (required) -- The spreadsheet ID
- `range` -- Cell range in A1 notation, e.g., `"A1:D1"`, `"B2:B10"` (recommended over index-based)
- `sheet_name` -- Worksheet name, e.g., `"Sheet1"`
- `bold` -- `true`/`false`
- `italic` -- `true`/`false`
- `fontSize` -- Font size in points, e.g., `12`
- `red`, `green`, `blue` -- Background color components (0.0--1.0 float scale, NOT 0--255)

**Example (bold header row with blue background):**
```
Tool: GOOGLESHEETS_FORMAT_CELL
Arguments:
  spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  range: "A1:D1"
  sheet_name: "Sheet1"
  bold: true
  fontSize: 12
  red: 0.2
  green: 0.4
  blue: 0.9
```

---

### 5. Add New Worksheet Tabs

Use `GOOGLESHEETS_ADD_SHEET` to create new tabs within an existing spreadsheet.

**Tool:** `GOOGLESHEETS_ADD_SHEET`

**Key Parameters:**
- `spreadsheetId` (required) -- The spreadsheet ID
- `title` -- Name for the new tab, e.g., `"Q4 Report"`
- `forceUnique` -- `true` (default) auto-appends suffix if name exists

**Example:**
```
Tool: GOOGLESHEETS_ADD_SHEET
Arguments:
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
  title: "Q4 Report"
  forceUnique: true
```

---

### 6. Read Data and Verify Content

Use `GOOGLESHEETS_BATCH_GET` to retrieve data from specified cell ranges for validation or further processing.

**Tool:** `GOOGLESHEETS_BATCH_GET`

**Steps:**
1. Call `GOOGLESHEETS_BATCH_GET` with the spreadsheet ID and target ranges
2. Validate headers and data alignment
3. Use results to inform subsequent write or update operations

**Supporting Tools:**
- `GOOGLESHEETS_GET_SHEET_NAMES` -- List all tab names in a spreadsheet
- `GOOGLESHEETS_GET_SPREADSHEET_INFO` -- Get metadata (sheet IDs, properties)
- `GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE` -- Check if a specific tab exists

---

## Recommended Execution Plan

1. **Create or locate the spreadsheet** using `GOOGLESHEETS_CREATE_GOOGLE_SHEET1` or reuse an existing `spreadsheetId`
2. **Confirm the destination tab** using `GOOGLESHEETS_GET_SHEET_NAMES` or `GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE`; create it with `GOOGLESHEETS_ADD_SHEET` if missing
3. **Read existing headers** (optional) using `GOOGLESHEETS_BATCH_GET` to align columns
4. **Write or upsert data** using `GOOGLESHEETS_BATCH_UPDATE` or `GOOGLESHEETS_UPSERT_ROWS`
5. **Apply formatting** (optional) using `GOOGLESHEETS_FORMAT_CELL`
6. **Verify results** (optional) using `GOOGLESHEETS_BATCH_GET`
7. **Fallback:** If Google Sheets creation is blocked (HTTP 403), use `EXCEL_CREATE_WORKBOOK` for local `.xlsx` output

---

## Known Pitfalls

| Pitfall | Detail |
|---------|--------|
| **HTTP 403 on sheet creation** | `GOOGLESHEETS_CREATE_GOOGLE_SHEET1` fails when Drive create scope is missing. Reuse an existing `spreadsheetId` or fall back to `EXCEL_CREATE_WORKBOOK`. |
| **Cell limit and rate throttling** | Google Sheets has a ~5,000,000 cell limit per spreadsheet. Excessive write frequency triggers HTTP 429. Batch changes and chunk large writes (~500 rows/call). |
| **Format range off-by-one** | `GOOGLESHEETS_FORMAT_CELL` uses 0-based, endIndex-exclusive ranges when using index mode. Background color uses 0--1 float RGB, NOT 0--255 integer RGB. |
| **Sheet title uniqueness** | Sheet titles are not guaranteed unique across API responses. Prefer operating by numeric `sheetId` and verify the resolved tab before writing. |
| **Upsert payload shape** | `GOOGLESHEETS_UPSERT_ROWS` requires headers + 2D rows array. Sending list-of-dicts or empty `rows` causes validation errors. Ensure at least 1 data row. |

---

## Quick Reference

| Tool Slug | Description |
|-----------|-------------|
| `EXCEL_CREATE_WORKBOOK` | Create a new `.xlsx` workbook and upload to OneDrive |
| `GOOGLESHEETS_BATCH_UPDATE` | Write values to a range or append new rows |
| `GOOGLESHEETS_UPSERT_ROWS` | Update existing rows by key or append new ones |
| `GOOGLESHEETS_FORMAT_CELL` | Apply text/background formatting to cell ranges |
| `GOOGLESHEETS_ADD_SHEET` | Add a new worksheet tab to a spreadsheet |
| `GOOGLESHEETS_CREATE_GOOGLE_SHEET1` | Create a new Google Spreadsheet in Drive |
| `GOOGLESHEETS_GET_SHEET_NAMES` | List all worksheet names in a spreadsheet |
| `GOOGLESHEETS_GET_SPREADSHEET_INFO` | Retrieve spreadsheet metadata |
| `GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE` | Check if a worksheet exists by title |
| `GOOGLESHEETS_BATCH_GET` | Read data from specified cell ranges |

---

*Powered by [Composio](https://composio.dev)*