google-sheets-mcp

Instructions

Get comprehensive metadata for tables in Google Sheets.

This tool provides detailed information about table structure, columns, data types,
formatting, statistics, and other properties. If no table name is provided, returns 
metadata for all tables.

Args:
    spreadsheet_name: Name of the spreadsheet
    sheet_name: Name of the sheet containing the table
    table_name: Name of the table to get metadata for (optional)
    include_sample_data: Whether to include sample data rows
    include_formulas: Whether to include formula information
    include_formatting: Whether to include cell formatting details
    include_statistics: Whether to include data statistics
    max_sample_rows: Maximum number of sample rows to return
    specific_columns: List of column names to get metadata for (optional)
    exclude_metadata_types: List of metadata types to exclude

Returns:
    JSON string containing table metadata or list of all tables

Implementation Reference

• gsheet_mcp_server/server.py:330-380 (registration)

  Tool registration with @mcp.tool() decorator, input schema via Pydantic Fields, and wrapper calling the handler.

  @mcp.tool()
  def get_table_metadata_tool(
      spreadsheet_name: str = Field(..., description="The name of the Google Spreadsheet"),
      sheet_name: str = Field(..., description="The name of the sheet containing the table"),
      table_name: str = Field(default=None, description="Name of the table to get metadata for. If not provided, returns metadata for all tables in the sheet."),
      include_sample_data: bool = Field(default=False, description="Whether to include sample data rows"),
      include_formulas: bool = Field(default=False, description="Whether to include formula information"),
      include_formatting: bool = Field(default=False, description="Whether to include cell formatting details"),
      include_statistics: bool = Field(default=False, description="Whether to include data statistics"),
      max_sample_rows: int = Field(default=5, description="Maximum number of sample rows to return"),
      specific_columns: List[str] = Field(default=None, description="List of column names to get metadata for (optional)"),
      exclude_metadata_types: List[str] = Field(default=None, description="List of metadata types to exclude (e.g., ['sample_data', 'formatting', 'statistics', 'merges', 'conditional_formatting', 'filters'])")
  ) -> str:
      """
      Get comprehensive metadata for tables in Google Sheets.
      
      This tool provides detailed information about table structure, columns, data types,
      formatting, statistics, and other properties. If no table name is provided, returns 
      metadata for all tables.
      
      Args:
          spreadsheet_name: Name of the spreadsheet
          sheet_name: Name of the sheet containing the table
          table_name: Name of the table to get metadata for (optional)
          include_sample_data: Whether to include sample data rows
          include_formulas: Whether to include formula information
          include_formatting: Whether to include cell formatting details
          include_statistics: Whether to include data statistics
          max_sample_rows: Maximum number of sample rows to return
          specific_columns: List of column names to get metadata for (optional)
          exclude_metadata_types: List of metadata types to exclude
      
      Returns:
          JSON string containing table metadata or list of all tables
      """
      sheets_service, drive_service = _get_google_services()
      return get_table_metadata_handler(
          drive_service,
          sheets_service,
          spreadsheet_name,
          sheet_name,
          table_name,
          include_sample_data,
          include_formulas,
          include_formatting,
          include_statistics,
          max_sample_rows,
          specific_columns,
          exclude_metadata_types
      )

• gsheet_mcp_server/handler/tables/get_table_metadata_handler.py:17-298 (handler)

  Core handler function executing the tool logic: fetches spreadsheet/sheet/table info via Google APIs, computes metadata including dimensions, columns, sample data, formatting, statistics, merges, conditional formatting, filters; handles single table or all tables; includes input validation and error handling.

  def get_table_metadata_handler(
      drive_service,
      sheets_service,
      spreadsheet_name: str,
      sheet_name: str,
      table_name: str = None,
      include_sample_data: bool = False,
      include_formulas: bool = False,
      include_formatting: bool = False,
      include_statistics: bool = False,
      max_sample_rows: int = 5,
      specific_columns: List[str] = None,
      exclude_metadata_types: List[str] = None
  ) -> str:
      """
      Get comprehensive metadata for a specific table in Google Sheets.
      If table_name is not provided, returns a list of all tables in the sheet.
      
      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 containing the table
          table_name: Name of the table to get metadata for (optional)
          include_sample_data: Whether to include sample data rows
          include_formulas: Whether to include formula information
          include_formatting: Whether to include cell formatting details
          include_statistics: Whether to include data statistics
          max_sample_rows: Maximum number of sample rows to return
          specific_columns: List of column names to get metadata for (optional)
          exclude_metadata_types: List of metadata types to exclude (optional)
      
      Returns:
          str: JSON-formatted string containing table metadata or list of all tables
      """
      try:
          # Validate inputs
          if not spreadsheet_name or spreadsheet_name.strip() == "":
              return compact_json_response({
                  "success": False,
                  "message": "Spreadsheet name is required."
              })
          
          if not sheet_name or sheet_name.strip() == "":
              return compact_json_response({
                  "success": False,
                  "message": "Sheet name is required."
              })
          
          # Validate table name if provided
          if table_name:
              table_validation = validate_table_name(table_name)
              if not table_validation["valid"]:
                  return compact_json_response({
                      "success": False,
                      "message": table_validation["error"]
                  })
              validated_table_name = table_validation["cleaned_name"]
          else:
              validated_table_name = None
          
          # Get spreadsheet ID and metadata
          spreadsheet_id = get_spreadsheet_id_by_name(drive_service, spreadsheet_name)
          if not spreadsheet_id:
              return compact_json_response({
                  "success": False,
                  "message": f"Spreadsheet '{spreadsheet_name}' not found."
              })
  
          # Get spreadsheet metadata for modification info
          spreadsheet_metadata = drive_service.files().get(
              fileId=spreadsheet_id,
              fields="modifiedTime,lastModifyingUser"
          ).execute()
  
          # Get sheet ID and metadata
          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 compact_json_response({
                  "success": False,
                  "message": f"Sheet '{sheet_name}' not found in spreadsheet '{spreadsheet_name}'."
              })
          
          # Get sheet metadata
          sheet_metadata = sheets_service.spreadsheets().get(
              spreadsheetId=spreadsheet_id,
              ranges=[f"{sheet_name}"],
              includeGridData=include_formatting,
              fields="sheets(properties,merges,protectedRanges,basicFilter,conditionalFormats)"
          ).execute()
          
          sheet_props = sheet_metadata.get("sheets", [])[0].get("properties", {})
          
          # If table_name is not provided, get all tables in the sheet
          if not validated_table_name:
              return _get_all_tables_metadata(
                  sheets_service, 
                  spreadsheet_id, 
                  sheet_id, 
                  sheet_name, 
                  spreadsheet_name,
                  sheet_props,
                  spreadsheet_metadata,
                  include_sample_data,
                  include_formulas,
                  include_formatting,
                  include_statistics,
                  max_sample_rows,
                  specific_columns,
                  exclude_metadata_types
              )
          
          # Get specific table metadata
          # Get table ID and check for both displayName and name fields
          table_id = None
          result = sheets_service.spreadsheets().get(
              spreadsheetId=spreadsheet_id,
              fields="sheets.tables"
          ).execute()
          
          for sheet in result.get("sheets", []):
              tables = sheet.get("tables", [])
              for table in tables:
                  table_display_name = table.get("displayName")
                  table_name = table.get("name")
                  if validated_table_name in [table_display_name, table_name]:
                      table_id = table.get("tableId")
                      break
              if table_id:
                  break
          if not table_id:
              return compact_json_response({
                  "success": False,
                  "message": f"Table '{validated_table_name}' not found in sheet '{sheet_name}'."
              })
          
          try:
              # Get comprehensive table metadata
              table_metadata = get_table_info(sheets_service, spreadsheet_id, table_id)
              
              # Get table range for additional data
              start_col = table_metadata.get('start_col', 0)
              end_col = table_metadata.get('end_col', 0)
              start_row = table_metadata.get('start_row', 0)
              end_row = table_metadata.get('end_row', 0)
              table_range = f"{sheet_name}!{column_index_to_letter(start_col)}{start_row + 1}:{column_index_to_letter(end_col - 1)}{end_row}"
              
              # Get sample data if requested
              sample_data = None
              if include_sample_data:
                  data_response = sheets_service.spreadsheets().values().get(
                      spreadsheetId=spreadsheet_id,
                      range=table_range,
                      valueRenderOption="UNFORMATTED_VALUE" if not include_formulas else "FORMULA"
                  ).execute()
                  sample_data = data_response.get("values", [])[:max_sample_rows]
              
              # Get formatting if requested
              formatting_data = None
              if include_formatting:
                  format_response = sheets_service.spreadsheets().get(
                      spreadsheetId=spreadsheet_id,
                      ranges=[table_range],
                      fields="sheets(data(rowData(values(userEnteredFormat))))"
                  ).execute()
                  formatting_data = format_response.get("sheets", [])[0].get("data", [])[0].get("rowData", [])
              
              # Calculate statistics if requested
              statistics = None
              if include_statistics and sample_data:
                  statistics = _calculate_statistics(sample_data)
              
              # Filter columns if specific ones requested
              columns = table_metadata.get("columns", [])
              if specific_columns:
                  columns = [col for col in columns if col.get("name") in specific_columns]
              
              # Get frozen dimensions
              grid_properties = sheet_props.get("gridProperties", {})
              frozen_rows = grid_properties.get("frozenRowCount", 0)
              frozen_columns = grid_properties.get("frozenColumnCount", 0)
              
              # Get column dimensions
              column_metadata = sheet_props.get("columnMetadata", [])
              column_dimensions = []
              for i, col in enumerate(columns):
                  col_index = table_metadata.get("start_col", 0) + i
                  if col_index < len(column_metadata):
                      width = column_metadata[col_index].get("pixelSize")
                      column_dimensions.append({"index": i, "width": width})
              
              # Get merged ranges
              merges = sheet_metadata.get("sheets", [])[0].get("merges", [])
              table_merges = _filter_merges_for_table(merges, table_metadata)
              
              # Get conditional formatting
              conditional_formats = sheet_metadata.get("sheets", [])[0].get("conditionalFormats", [])
              table_conditional_formats = _filter_conditional_formats_for_table(conditional_formats, table_metadata)
              
              # Get filters
              basic_filter = sheet_metadata.get("sheets", [])[0].get("basicFilter", {})
              table_filters = _filter_basic_filter_for_table(basic_filter, table_metadata)
              
              # Format the response
              formatted_metadata = {
                  "table_name": validated_table_name,
                  "table_id": table_id,
                  "spreadsheet_name": spreadsheet_name,
                  "sheet_name": sheet_name,
                  "dimensions": {
                      "column_count": table_metadata.get("column_count"),
                      "row_count": table_metadata.get("row_count"),
                      "frozen_rows": frozen_rows,
                      "frozen_columns": frozen_columns
                  },
                  "range": {
                      "start_row": table_metadata.get("start_row"),
                      "end_row": table_metadata.get("end_row"),
                      "start_column": table_metadata.get("start_col"),
                      "end_column": table_metadata.get("end_col")
                  },
                  "range_notation": table_metadata.get("range_notation"),
                  "columns": columns,
                  "column_dimensions": column_dimensions,
                  "header_row": {
                      "index": table_metadata.get("start_row"),
                      "values": sample_data[0] if sample_data else None
                  },
                  "modification_info": {
                      "last_modified": spreadsheet_metadata.get("modifiedTime"),
                      "last_modified_by": spreadsheet_metadata.get("lastModifyingUser", {}).get("displayName")
                  }
              }
              
              # Add optional data based on parameters and exclusions
              if not exclude_metadata_types or "sample_data" not in exclude_metadata_types:
                  if include_sample_data:
                      formatted_metadata["sample_data"] = sample_data[1:] if sample_data else []
              
              if not exclude_metadata_types or "formatting" not in exclude_metadata_types:
                  if include_formatting:
                      formatted_metadata["formatting"] = formatting_data
              
              if not exclude_metadata_types or "statistics" not in exclude_metadata_types:
                  if include_statistics:
                      formatted_metadata["statistics"] = statistics
              
              if not exclude_metadata_types or "merges" not in exclude_metadata_types:
                  formatted_metadata["merges"] = table_merges
              
              if not exclude_metadata_types or "conditional_formatting" not in exclude_metadata_types:
                  formatted_metadata["conditional_formatting"] = table_conditional_formats
              
              if not exclude_metadata_types or "filters" not in exclude_metadata_types:
                  formatted_metadata["filters"] = table_filters
              
              response_data = {
                  "success": True,
                  "message": f"Successfully retrieved metadata for table '{validated_table_name}'",
                  "data": formatted_metadata
              }
              
              return compact_json_response(response_data)
              
          except RuntimeError as e:
              return compact_json_response({
                  "success": False,
                  "message": f"Could not retrieve metadata for table '{validated_table_name}': {str(e)}"
              })
          
      except HttpError as error:
          return compact_json_response({
              "success": False,
              "message": f"Google Sheets API error: {str(error)}"
          })
      except Exception as e:
          return compact_json_response({
              "success": False,
              "message": f"Error getting table metadata: {str(e)}"
          })

• gsheet_mcp_server/server.py:331-341 (schema)

  Pydantic-based input schema for tool parameters including descriptions and defaults.

  def get_table_metadata_tool(
      spreadsheet_name: str = Field(..., description="The name of the Google Spreadsheet"),
      sheet_name: str = Field(..., description="The name of the sheet containing the table"),
      table_name: str = Field(default=None, description="Name of the table to get metadata for. If not provided, returns metadata for all tables in the sheet."),
      include_sample_data: bool = Field(default=False, description="Whether to include sample data rows"),
      include_formulas: bool = Field(default=False, description="Whether to include formula information"),
      include_formatting: bool = Field(default=False, description="Whether to include cell formatting details"),
      include_statistics: bool = Field(default=False, description="Whether to include data statistics"),
      max_sample_rows: int = Field(default=5, description="Maximum number of sample rows to return"),
      specific_columns: List[str] = Field(default=None, description="List of column names to get metadata for (optional)"),
      exclude_metadata_types: List[str] = Field(default=None, description="List of metadata types to exclude (e.g., ['sample_data', 'formatting', 'statistics', 'merges', 'conditional_formatting', 'filters'])")