google-sheets-mcp

Instructions

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'])")