Googlesheets Automation

1---
2name: googlesheets-automation
3description: "Automate Google Sheets operations (read, write, format, filter, manage spreadsheets) via Rube MCP (Composio). Read/write data, manage tabs, apply formatting, and search rows programmatically."
4requires:
5  mcp: [rube]
6---
7 
8# Google Sheets Automation via Rube MCP
9 
10Automate Google Sheets workflows including reading/writing data, managing spreadsheets and tabs, formatting cells, filtering rows, and upserting records through Composio's Google Sheets toolkit.
11 
12## Prerequisites
13 
14- Rube MCP must be connected (RUBE_SEARCH_TOOLS available)
15- Active Google Sheets connection via `RUBE_MANAGE_CONNECTIONS` with toolkit `googlesheets`
16- Always call `RUBE_SEARCH_TOOLS` first to get current tool schemas
17 
18## Setup
19 
20**Get Rube MCP**: Add `https://rube.app/mcp` as an MCP server in your client configuration. No API keys needed — just add the endpoint and it works.
21 
22 
231. Verify Rube MCP is available by confirming `RUBE_SEARCH_TOOLS` responds
242. Call `RUBE_MANAGE_CONNECTIONS` with toolkit `googlesheets`
253. If connection is not ACTIVE, follow the returned auth link to complete Google OAuth
264. Confirm connection status shows ACTIVE before running any workflows
27 
28## Core Workflows
29 
30### 1. Read and Write Data
31 
32**When to use**: User wants to read data from or write data to a Google Sheet
33 
34**Tool sequence**:
351. `GOOGLESHEETS_SEARCH_SPREADSHEETS` - Find spreadsheet by name if ID unknown [Prerequisite]
362. `GOOGLESHEETS_GET_SHEET_NAMES` - Enumerate tab names to target the right sheet [Prerequisite]
373. `GOOGLESHEETS_BATCH_GET` - Read data from one or more ranges [Required]
384. `GOOGLESHEETS_BATCH_UPDATE` - Write data to a range or append rows [Required]
395. `GOOGLESHEETS_VALUES_UPDATE` - Update a single specific range [Alternative]
406. `GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND` - Append rows to end of table [Alternative]
41 
42**Key parameters**:
43- `spreadsheet_id`: Alphanumeric ID from the spreadsheet URL (between '/d/' and '/edit')
44- `ranges`: A1 notation array (e.g., 'Sheet1!A1:Z1000'); always use bounded ranges
45- `sheet_name`: Tab name (case-insensitive matching supported)
46- `values`: 2D array where each inner array is a row
47- `first_cell_location`: Starting cell in A1 notation (omit to append)
48- `valueInputOption`: 'USER_ENTERED' (parsed) or 'RAW' (literal)
49 
50**Pitfalls**:
51- Mis-cased or non-existent tab names error "Sheet 'X' not found"
52- Empty ranges may omit `valueRanges[i].values`; treat missing as empty array
53- `GOOGLESHEETS_BATCH_UPDATE` values must be a 2D array (list of lists), even for a single row
54- Unbounded ranges like 'A:Z' on sheets with >10,000 rows may cause timeouts; always bound with row limits
55- Append follows the detected `tableRange`; use returned `updatedRange` to verify placement
56 
57### 2. Create and Manage Spreadsheets
58 
59**When to use**: User wants to create a new spreadsheet or manage tabs within one
60 
61**Tool sequence**:
621. `GOOGLESHEETS_CREATE_GOOGLE_SHEET1` - Create a new spreadsheet [Required]
632. `GOOGLESHEETS_ADD_SHEET` - Add a new tab/worksheet [Required]
643. `GOOGLESHEETS_UPDATE_SHEET_PROPERTIES` - Rename, hide, reorder, or color tabs [Optional]
654. `GOOGLESHEETS_GET_SPREADSHEET_INFO` - Get full spreadsheet metadata [Optional]
665. `GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE` - Check if a specific tab exists [Optional]
67 
68**Key parameters**:
69- `title`: Spreadsheet or sheet tab name
70- `spreadsheetId`: Target spreadsheet ID
71- `forceUnique`: Auto-append suffix if tab name exists (default true)
72- `properties.gridProperties`: Set row/column counts, frozen rows
73 
74**Pitfalls**:
75- Sheet names must be unique within a spreadsheet
76- Default sheet names are locale-dependent ('Sheet1' in English, 'Hoja 1' in Spanish)
77- Don't use `index` when creating multiple sheets in parallel (causes 'index too high' errors)
78- `GOOGLESHEETS_GET_SPREADSHEET_INFO` can return 403 if account lacks access
79 
80### 3. Search and Filter Rows
81 
82**When to use**: User wants to find specific rows or apply filters to sheet data
83 
84**Tool sequence**:
851. `GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW` - Find first row matching exact cell value [Required]
862. `GOOGLESHEETS_SET_BASIC_FILTER` - Apply filter/sort to a range [Alternative]
873. `GOOGLESHEETS_CLEAR_BASIC_FILTER` - Remove existing filter [Optional]
884. `GOOGLESHEETS_BATCH_GET` - Read filtered results [Optional]
89 
90**Key parameters**:
91- `query`: Exact text value to match (matches entire cell content)
92- `range`: A1 notation range to search within
93- `case_sensitive`: Boolean for case-sensitive matching (default false)
94- `filter.range`: Grid range with sheet_id for basic filter
95- `filter.criteria`: Column-based filter conditions
96- `filter.sortSpecs`: Sort specifications
97 
98**Pitfalls**:
99- `GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW` matches entire cell content, not substrings
100- Sheet names with spaces must be single-quoted in ranges (e.g., "'My Sheet'!A:Z")
101- Bare sheet names without ranges are not supported for lookup; always specify a range
102 
103### 4. Upsert Rows by Key
104 
105**When to use**: User wants to update existing rows or insert new ones based on a unique key column
106 
107**Tool sequence**:
1081. `GOOGLESHEETS_UPSERT_ROWS` - Update matching rows or append new ones [Required]
109 
110**Key parameters**:
111- `spreadsheetId`: Target spreadsheet ID
112- `sheetName`: Tab name
113- `keyColumn`: Column header name used as unique identifier (e.g., 'Email', 'SKU')
114- `headers`: List of column names for the data
115- `rows`: 2D array of data rows
116- `strictMode`: Error on mismatched column counts (default true)
117 
118**Pitfalls**:
119- `keyColumn` must be an actual header name, NOT a column letter (e.g., 'Email' not 'A')
120- If `headers` is NOT provided, first row of `rows` is treated as headers
121- With `strictMode=true`, rows with more values than headers cause an error
122- Auto-adds missing columns to the sheet
123 
124### 5. Format Cells
125 
126**When to use**: User wants to apply formatting (bold, colors, font size) to cells
127 
128**Tool sequence**:
1291. `GOOGLESHEETS_GET_SPREADSHEET_INFO` - Get numeric sheetId for target tab [Prerequisite]
1302. `GOOGLESHEETS_FORMAT_CELL` - Apply formatting to a range [Required]
1313. `GOOGLESHEETS_UPDATE_SHEET_PROPERTIES` - Change frozen rows, column widths [Optional]
132 
133**Key parameters**:
134- `spreadsheet_id`: Spreadsheet ID
135- `worksheet_id`: Numeric sheetId (NOT tab name); get from GET_SPREADSHEET_INFO
136- `range`: A1 notation (e.g., 'A1:F1') - preferred over index fields
137- `bold`, `italic`, `underline`, `strikethrough`: Boolean formatting options
138- `red`, `green`, `blue`: Background color as 0.0-1.0 floats (NOT 0-255 ints)
139- `fontSize`: Font size in points
140 
141**Pitfalls**:
142- Requires numeric `worksheet_id`, not tab title; get from spreadsheet metadata
143- Color channels are 0-1 floats (e.g., 1.0 for full red), NOT 0-255 integers
144- Responses may return empty reply objects ([{}]); verify formatting via readback
145- Format one range per call; batch formatting requires separate calls
146 
147## Common Patterns
148 
149### ID Resolution
150- **Spreadsheet name -> ID**: `GOOGLESHEETS_SEARCH_SPREADSHEETS` with `query`
151- **Tab name -> sheetId**: `GOOGLESHEETS_GET_SPREADSHEET_INFO`, extract from sheets metadata
152- **Tab existence check**: `GOOGLESHEETS_FIND_WORKSHEET_BY_TITLE`
153 
154### Rate Limits
155Google Sheets enforces strict rate limits:
156- Max 60 reads/minute and 60 writes/minute
157- Exceeding limits causes errors; batch operations where possible
158- Use `GOOGLESHEETS_BATCH_GET` and `GOOGLESHEETS_BATCH_UPDATE` for efficiency
159 
160### Data Patterns
161- Always read before writing to understand existing layout
162- Use `GOOGLESHEETS_UPSERT_ROWS` for CRM syncs, inventory updates, and dedup scenarios
163- Append mode (omit `first_cell_location`) is safest for adding new records
164- Use `GOOGLESHEETS_CLEAR_VALUES` to clear content while preserving formatting
165 
166## Known Pitfalls
167 
168- **Tab names**: Locale-dependent defaults; 'Sheet1' may not exist in non-English accounts
169- **Range notation**: Sheet names with spaces need single quotes in A1 notation
170- **Unbounded ranges**: Can timeout on large sheets; always specify row bounds (e.g., 'A1:Z10000')
171- **2D arrays**: All value parameters must be list-of-lists, even for single rows
172- **Color values**: Floats 0.0-1.0, not integers 0-255
173- **Formatting IDs**: `FORMAT_CELL` needs numeric sheetId, not tab title
174- **Rate limits**: 60 reads/min and 60 writes/min; batch to stay within limits
175- **Delete dimension**: `GOOGLESHEETS_DELETE_DIMENSION` is irreversible; double-check bounds
176 
177## Quick Reference
178 
179| Task | Tool Slug | Key Params |
180|------|-----------|------------|
181| Search spreadsheets | `GOOGLESHEETS_SEARCH_SPREADSHEETS` | `query`, `search_type` |
182| Create spreadsheet | `GOOGLESHEETS_CREATE_GOOGLE_SHEET1` | `title` |
183| List tabs | `GOOGLESHEETS_GET_SHEET_NAMES` | `spreadsheet_id` |
184| Add tab | `GOOGLESHEETS_ADD_SHEET` | `spreadsheetId`, `title` |
185| Read data | `GOOGLESHEETS_BATCH_GET` | `spreadsheet_id`, `ranges` |
186| Read single range | `GOOGLESHEETS_VALUES_GET` | `spreadsheet_id`, `range` |
187| Write data | `GOOGLESHEETS_BATCH_UPDATE` | `spreadsheet_id`, `sheet_name`, `values` |
188| Update range | `GOOGLESHEETS_VALUES_UPDATE` | `spreadsheet_id`, `range`, `values` |
189| Append rows | `GOOGLESHEETS_SPREADSHEETS_VALUES_APPEND` | `spreadsheetId`, `range`, `values` |
190| Upsert rows | `GOOGLESHEETS_UPSERT_ROWS` | `spreadsheetId`, `sheetName`, `keyColumn`, `rows` |
191| Lookup row | `GOOGLESHEETS_LOOKUP_SPREADSHEET_ROW` | `spreadsheet_id`, `query` |
192| Format cells | `GOOGLESHEETS_FORMAT_CELL` | `spreadsheet_id`, `worksheet_id`, `range` |
193| Set filter | `GOOGLESHEETS_SET_BASIC_FILTER` | `spreadsheetId`, `filter` |
194| Clear values | `GOOGLESHEETS_CLEAR_VALUES` | `spreadsheet_id`, range |
195| Delete rows/cols | `GOOGLESHEETS_DELETE_DIMENSION` | `spreadsheet_id`, `sheet_name`, dimension |
196| Spreadsheet info | `GOOGLESHEETS_GET_SPREADSHEET_INFO` | `spreadsheet_id` |
197| Update tab props | `GOOGLESHEETS_UPDATE_SHEET_PROPERTIES` | `spreadsheetId`, properties |
198