Smartsheet MCP Server

Overview

Key Benefits

• **Intelligent Integration**: Seamlessly connects AI systems with Smartsheet's collaboration platform

• **Data Integrity**: Enforces validation rules and maintains referential integrity across operations

• **Formula Management**: Preserves and updates formula references automatically

• **Flexible Configuration**: Supports various column types and complex data structures

• **Error Resilience**: Implements comprehensive error handling and validation at multiple layers

• **Healthcare Analytics**: Specialized analysis capabilities for clinical and research data

• **Batch Processing**: Efficient handling of large healthcare datasets

• **Custom Scoring**: Flexible scoring systems for healthcare initiatives and research

Use Cases

1. **Clinical Research Analytics** - Protocol compliance scoring - Patient data analysis - Research impact assessment - Clinical trial data processing - Automated research note summarization

2. **Hospital Operations** - Resource utilization analysis - Patient satisfaction scoring - Department efficiency metrics - Staff performance analytics - Quality metrics tracking

3. **Healthcare Innovation** - Pediatric alignment scoring - Innovation impact assessment - Research prioritization - Implementation feasibility analysis - Clinical value assessment

4. **Automated Document Management** - Programmatic sheet structure modifications - Dynamic column creation and management - Automated data validation and formatting

5. **Data Operations** - Bulk data updates with integrity checks - Intelligent duplicate detection - Formula-aware modifications

6. **System Integration** - AI-driven sheet customization - Automated reporting workflows - Cross-system data synchronization

Integration Points

• Smartsheet API for data operations

• MCP protocol for standardized communication

• Local development tools via stdio interface

• Monitoring systems through structured logging

Architecture

1. **TypeScript MCP Layer** (`src/index.ts`) - Handles MCP protocol communication - Registers and manages available tools - Routes requests to Python implementation - Manages configuration and error handling

2. **Python CLI Layer** (`smartsheet_ops/cli.py`) - Provides command-line interface for operations - Handles argument parsing and validation - Implements duplicate detection - Manages JSON data formatting

3. **Core Operations Layer** (`smartsheet_ops/__init__.py`) - Implements Smartsheet API interactions - Handles complex column type management - Provides data normalization and validation - Manages system columns and formula parsing

Column Management Flow

Error Handling Flow

Features

Tools

1. `get_column_map` (Read) - Retrieves column mapping and sample data from a Smartsheet - Provides detailed column metadata including: - Column types (system columns, formulas, picklists) - Validation rules - Format specifications - Auto-number configurations - Returns sample data for context - Includes usage examples for writing data

2. `smartsheet_write` (Create) - Writes new rows to Smartsheet with intelligent handling of: - System-managed columns - Multi-select picklist values - Formula-based columns - Implements automatic duplicate detection - Returns detailed operation results including row IDs

3. `smartsheet_update` (Update) - Updates existing rows in a Smartsheet - Supports partial updates (modify specific fields) - Maintains data integrity with validation - Handles multi-select fields consistently - Returns success/failure details per row

4. `smartsheet_delete` (Delete) - Deletes rows from a Smartsheet - Supports batch deletion of multiple rows - Validates row existence and permissions - Returns detailed operation results

5. `smartsheet_add_column` (Column Management) - Adds new columns to a Smartsheet - Supports all column types: - TEXT_NUMBER - DATE - CHECKBOX - PICKLIST - CONTACT_LIST - Configurable options: - Position index - Validation rules - Formula definitions - Picklist options - Enforces column limit (400) with validation - Returns detailed column information

6. `smartsheet_delete_column` (Column Management) - Safely deletes columns with dependency checking - Validates formula references before deletion - Prevents deletion of columns used in formulas - Returns detailed dependency information - Supports force deletion option

7. `smartsheet_rename_column` (Column Management) - Renames columns while preserving relationships - Updates formula references automatically - Maintains data integrity - Validates name uniqueness - Returns detailed update information

8. `smartsheet_bulk_update` (Conditional Updates) - Performs conditional bulk updates based on rules - Supports complex condition evaluation: - Multiple operators (equals, contains, greaterThan, etc.) - Type-specific comparisons (text, dates, numbers) - Empty/non-empty checks - Batch processing with configurable size - Comprehensive error handling and rollback - Detailed operation results tracking

9. `start_batch_analysis` (Healthcare Analytics) - Processes entire sheets or selected rows with AI analysis - Supports multiple analysis types: - Summarization of clinical notes - Sentiment analysis of patient feedback - Custom scoring for healthcare initiatives - Research impact assessment - Features: - Automatic batch processing (50 rows per batch) - Progress tracking and status monitoring - Error handling with detailed reporting - Customizable analysis goals - Support for multiple source columns

10. `get_job_status` (Analysis Monitoring) - Tracks batch analysis progress - Provides detailed job statistics: - Total rows to process - Processed row count - Failed row count - Processing timestamps - Real-time status updates - Comprehensive error reporting

11. `cancel_batch_analysis` (Job Control) - Cancels running batch analysis jobs - Graceful process termination - Maintains data consistency - Returns final job status

Key Capabilities

• **Column Type Management** - Handles system column types (AUTO_NUMBER, CREATED_DATE, etc.) - Supports formula parsing and dependency tracking - Manages picklist options and multi-select values - Comprehensive column operations (add, delete, rename) - Formula reference preservation and updates

• **Data Validation** - Automatic duplicate detection - Column type validation - Data format verification - Column dependency analysis - Name uniqueness validation

• **Metadata Handling** - Extracts and processes column metadata - Handles validation rules - Manages format specifications - Tracks formula dependencies - Maintains column relationships

• **Healthcare Analytics** - Clinical note summarization - Patient feedback sentiment analysis - Protocol compliance scoring - Research impact assessment - Resource utilization analysis

• **Batch Processing** - Automatic row batching (50 rows per batch) - Progress tracking and monitoring - Error handling and recovery - Customizable processing goals - Multi-column analysis support

• **Job Management** - Real-time status monitoring - Detailed progress tracking - Error reporting and logging - Job cancellation support - Batch operation controls

Setup

Prerequisites

• Node.js and npm

• Conda (for environment management)

• Smartsheet API access token

Environment Setup

1. Create a dedicated conda environment:

1. Install Node.js dependencies:

1. Install Python package:

1. Build the TypeScript server:

Configuration

1. Get Your Smartsheet API Key

1. Log in to [Smartsheet](https://app.smartsheet.com)

2. Go to Account Personal Settings API Access

3. Generate a new access token

2. Configure for Cline

3. Configure for Claude Desktop (Optional)

Starting the Server

Verifying Installation

1. The server should output "Smartsheet MCP server running on stdio" when started

2. Test the connection using any MCP tool (e.g., get_column_map)

3. Check the Python environment has the smartsheet package installed: ```bash conda activate cline_mcp_env pip show smartsheet-python-sdk ```

Usage Examples

Getting Column Information (Read)

Writing Data (Create)

Updating Data (Update)

Deleting Data (Delete)

Healthcare Analytics Examples

Managing Columns

Type-Specific Comparisons

Complex Multi-Condition Examples

1. Operator Support: - `equals`: Exact value matching - `contains`: Substring matching - `greaterThan`: Numeric/date comparison - `lessThan`: Numeric/date comparison - `isEmpty`: Null/empty check - `isNotEmpty`: Present value check

2. Type-Specific Features: - TEXT_NUMBER: String/numeric comparisons - DATE: ISO date parsing and comparison - PICKLIST: Option validation - CHECKBOX: Boolean handling

3. Processing Options: - `batchSize`: Control update batch size (default 500) - `lenientMode`: Continue on errors - Multiple rules per request - Multiple updates per rule

4. Result Tracking: - Total rows attempted - Success/failure counts - Detailed error information - Per-row failure details

Debugging

• Error logging to stderr

• Detailed error messages in MCP responses

• Type validation at multiple levels

• Comprehensive operation result reporting

• Dependency analysis for column operations

• Formula reference tracking

Error Handling

1. MCP Layer - Validates tool parameters - Handles protocol-level errors - Provides formatted error responses - Manages timeouts and retries

2. CLI Layer - Validates command arguments - Handles execution errors - Formats error messages as JSON - Validates column operations

3. Operations Layer - Handles Smartsheet API errors - Validates data types and formats - Provides detailed error context - Manages column dependencies - Validates formula references - Ensures data integrity

Contributing

1. TypeScript/Python code follows existing style

2. New features include appropriate error handling

3. Changes maintain backward compatibility

4. Updates include appropriate documentation

5. Column operations maintain data integrity

6. Formula references are properly handled