google-sheets-mcp

Instructions

Implementation Reference

• gsheet_mcp_server/handler/tables/create_table_handler.py:359-478 (handler)

  Core handler function implementing the table creation logic: validates inputs, calls create_table_api for initial table creation with addTable request, then updates properties including column types and dropdown validations using batchUpdate.
  def create_table_handler( drive_service, sheets_service, spreadsheet_name: str, sheet_name: str, table_name: str, start_cell: str, column_names: List[str], column_types: List[str], dropdown_columns: List[str] = [], dropdown_values: List[str] = [] ) -> str: """ Create a new table in Google Sheets using the official addTable operation. Available column types: - TEXT: Plain text data - DOUBLE: Numeric data with decimals - CURRENCY: Monetary values ($#,##0.00) - PERCENT: Percentage values (0.00%) - DATE: Date values (yyyy-mm-dd) - TIME: Time values (hh:mm:ss) - DATE_TIME: Date and time values - BOOLEAN: True/false values - DROPDOWN: Selection from predefined options - COLUMN_TYPE_UNSPECIFIED: Defaults to TEXT Args: drive_service: Google Drive service instance sheets_service: Google Sheets service instance spreadsheet_name: Name of the spreadsheet sheet_name: Name of the sheet to create table in table_name: Name for the table start_cell: Starting cell for the table (e.g., "A1") column_names: List of column names column_types: List of column types corresponding to column_names dropdown_columns: List of column names that should have dropdown validation dropdown_values: List of comma-separated dropdown options for each dropdown column Returns: str: Success message with table details """ try: # Create table with column properties in a single API call create_result = create_table_api( drive_service=drive_service, sheets_service=sheets_service, spreadsheet_name=spreadsheet_name, sheet_name=sheet_name, table_name=table_name, column_names=column_names, column_types=column_types, dropdown_columns=dropdown_columns, dropdown_values=dropdown_values, start_position=start_cell ) # Check if table creation was successful if not create_result["success"]: return compact_json_response(create_result) # Step 2: Update table properties using update_table_properties function # Create column updates based on the provided column names and types column_updates = [] for i, (col_name, col_type) in enumerate(zip(column_names, column_types)): update = { "column_index": i, "column_name": col_name, "column_type": col_type } # Add dropdown options if this column is in dropdown_columns if col_name in dropdown_columns: col_index = dropdown_columns.index(col_name) if col_index < len(dropdown_values): options_str = dropdown_values[col_index] options = [opt.strip() for opt in options_str.split(",") if opt.strip()] if options: update["dropdown_options"] = options column_updates.append(update) # Call update_table_properties to set the column properties update_result = update_table_properties( drive_service=drive_service, sheets_service=sheets_service, spreadsheet_name=spreadsheet_name, sheet_name=sheet_name, table_name=table_name, column_updates=column_updates ) # Check if update was successful if not update_result["success"]: return compact_json_response(update_result) # Combine results final_result = { "success": True, "spreadsheet_name": spreadsheet_name, "sheet_name": sheet_name, "table_name": table_name, "table_id": create_result.get("table_id"), "start_cell": start_cell, "column_count": len(column_names), "columns": column_names, "column_types": column_types, "range": create_result.get("range"), "dropdown_columns": dropdown_columns, "updates_applied": update_result.get("updates_applied", []), "message": f"Successfully created and updated table '{table_name}' with {len(column_names)} columns in '{sheet_name}'" } return compact_json_response(final_result) except Exception as e: return compact_json_response({ "success": False, "message": f"Error creating table: {str(e)}" })
• gsheet_mcp_server/server.py:246-278 (registration)

  MCP tool registration with @mcp.tool(), input schema validation via Pydantic Field descriptions, and thin wrapper that initializes Google services and delegates to create_table_handler.
  @mcp.tool() def create_table_tool( spreadsheet_name: str = Field(..., description="The name of the Google Spreadsheet"), sheet_name: str = Field(..., description="The name of the sheet to create table in"), table_name: str = Field(..., description="A descriptive name for the table (e.g., 'Project Tracker', 'Customer Data')"), start_cell: str = Field(..., description="Starting cell for the table (e.g., 'A1')"), column_names: List[str] = Field(..., description="List of column names (e.g., ['Employee Name', 'Age', 'Department', 'Salary'])"), column_types: List[str] = Field(..., description="List of column types: DOUBLE, CURRENCY, PERCENT, DATE, TIME, DATE_TIME, TEXT, BOOLEAN, DROPDOWN"), dropdown_columns: List[str] = Field(default=[], description="List of column names that should have dropdown validation"), dropdown_values: List[str] = Field(default=[], description="Comma-separated dropdown options for each dropdown column") ) -> str: """ Create a new table in Google Sheets. This tool creates a structured table with specified columns and data types. Tables provide better data organization, validation, and formatting capabilities. Args: spreadsheet_name: Name of the spreadsheet sheet_name: Name of the sheet to create table in table_name: Name for the table start_cell: Starting cell for the table (e.g., "A1") column_names: List of column names column_types: List of column types corresponding to column_names dropdown_columns: List of column names that should have dropdown validation dropdown_values: List of comma-separated dropdown options for each dropdown column Returns: JSON string with success status and table details """ sheets_service, drive_service = _get_google_services() return create_table_handler(drive_service, sheets_service, spreadsheet_name, sheet_name, table_name, start_cell, column_names, column_types, dropdown_columns, dropdown_values)
• gsheet_mcp_server/server.py:247-278 (schema)

  Pydantic-based input schema definition for the create_table_tool, specifying required fields, descriptions, and validation rules.
  def create_table_tool( spreadsheet_name: str = Field(..., description="The name of the Google Spreadsheet"), sheet_name: str = Field(..., description="The name of the sheet to create table in"), table_name: str = Field(..., description="A descriptive name for the table (e.g., 'Project Tracker', 'Customer Data')"), start_cell: str = Field(..., description="Starting cell for the table (e.g., 'A1')"), column_names: List[str] = Field(..., description="List of column names (e.g., ['Employee Name', 'Age', 'Department', 'Salary'])"), column_types: List[str] = Field(..., description="List of column types: DOUBLE, CURRENCY, PERCENT, DATE, TIME, DATE_TIME, TEXT, BOOLEAN, DROPDOWN"), dropdown_columns: List[str] = Field(default=[], description="List of column names that should have dropdown validation"), dropdown_values: List[str] = Field(default=[], description="Comma-separated dropdown options for each dropdown column") ) -> str: """ Create a new table in Google Sheets. This tool creates a structured table with specified columns and data types. Tables provide better data organization, validation, and formatting capabilities. Args: spreadsheet_name: Name of the spreadsheet sheet_name: Name of the sheet to create table in table_name: Name for the table start_cell: Starting cell for the table (e.g., "A1") column_names: List of column names column_types: List of column types corresponding to column_names dropdown_columns: List of column names that should have dropdown validation dropdown_values: List of comma-separated dropdown options for each dropdown column Returns: JSON string with success status and table details """ sheets_service, drive_service = _get_google_services() return create_table_handler(drive_service, sheets_service, spreadsheet_name, sheet_name, table_name, start_cell, column_names, column_types, dropdown_columns, dropdown_values)
• gsheet_mcp_server/handler/tables/create_table_handler.py:21-179 (helper)

  Supporting API function that performs the initial table creation via Google Sheets addTable batchUpdate request, including column properties and data validation setup.
  def create_table_api( drive_service, sheets_service, spreadsheet_name: str, sheet_name: str, table_name: str, column_names: List[str], column_types: List[str], dropdown_columns: List[str], dropdown_values: List[str], start_position: str ) -> Dict[str, Any]: """ API function for creating tables with column properties in a single call. Args: drive_service: Google Drive service instance sheets_service: Google Sheets service instance spreadsheet_name: Name of the spreadsheet sheet_name: Name of the sheet to create table in table_name: Name for the table column_names: List of column names column_types: List of column types dropdown_columns: List of column names that should have dropdown validation dropdown_values: List of comma-separated dropdown options for each dropdown column start_position: Starting position for the table (e.g., "A1", "B3") Returns: Dict: API response with success status and table details """ try: # Validate table name table_validation = validate_table_name(table_name) if not table_validation["valid"]: return { "success": False, "message": table_validation["error"] } validated_table_name = table_validation["cleaned_name"] # Get spreadsheet ID spreadsheet_id = get_spreadsheet_id_by_name(drive_service, spreadsheet_name) if not spreadsheet_id: return { "success": False, "message": f"Spreadsheet '{spreadsheet_name}' not found." } # Get sheet ID sheet_ids = get_sheet_ids_by_names(sheets_service, spreadsheet_id, [sheet_name]) sheet_id = sheet_ids.get(sheet_name) if sheet_id is None: return { "success": False, "message": f"Sheet '{sheet_name}' not found in spreadsheet '{spreadsheet_name}'." } # Parse start position and calculate table range try: start_row, start_col = parse_cell_reference(start_position) except ValueError as e: return { "success": False, "message": f"Invalid start position '{start_position}': {str(e)}" } end_row = start_row + 1 # Header row end_col = start_col + len(column_names) # Create column properties column_properties = [] for i, (col_name, col_type) in enumerate(zip(column_names, column_types)): column_property = { "columnIndex": i, "columnName": col_name, "columnType": map_column_type(col_type) } # Add data validation for dropdown columns if col_name in dropdown_columns: col_index = dropdown_columns.index(col_name) if col_index < len(dropdown_values): options_str = dropdown_values[col_index] options = [opt.strip() for opt in options_str.split(",") if opt.strip()] if options: column_property["dataValidationRule"] = { "condition": { "type": "ONE_OF_LIST", "values": [{"userEnteredValue": opt} for opt in options] } } column_properties.append(column_property) # Create unique table ID import uuid unique_id = str(uuid.uuid4())[:8] table_id = f"table_{validated_table_name.lower().replace(' ', '_')}_{unique_id}" # Create addTable request with column properties add_table_request = { "addTable": { "table": { "name": validated_table_name, "tableId": table_id, "range": { "sheetId": sheet_id, "startColumnIndex": start_col, "endColumnIndex": end_col, "startRowIndex": start_row, "endRowIndex": end_row } } } } # Execute the API request response = sheets_service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body={"requests": [add_table_request]} ).execute() # Extract response information replies = response.get("replies", []) if replies and "addTable" in replies[0]: new_table = replies[0]["addTable"] table_id = new_table.get("tableId") return { "success": True, "spreadsheet_name": spreadsheet_name, "sheet_name": sheet_name, "table_name": validated_table_name, "table_id": table_id, "start_position": start_position, "column_count": len(column_names), "columns": column_names, "column_types": column_types, "range": f"{start_position}:{chr(ord('A') + end_col - 1)}{end_row}", "message": f"Successfully created table '{validated_table_name}' with {len(column_names)} columns in '{sheet_name}'" } else: return { "success": False, "message": "Failed to create table - no response data from API" } except HttpError as error: return { "success": False, "message": f"Google Sheets API error: {str(error)}" } except Exception as e: return { "success": False, "message": f"Error creating table: {str(e)}" }