google-sheets-mcp

Instructions

Implementation Reference

• gsheet_mcp_server/handler/sheets/analyze_sheet_structure_handler.py:439-477 (handler)

  Core handler function that orchestrates the analysis: resolves spreadsheet ID, calls analysis helper, formats and returns JSON response.
  def analyze_sheet_structure_handler( drive_service, sheets_service, spreadsheet_name: str, sheet_name: str ) -> str: """ Handler for analyzing sheet structure - simplified overview with separated data detection. """ try: # Get spreadsheet ID spreadsheet_id = get_spreadsheet_id_by_name(drive_service, spreadsheet_name) # Perform simple analysis analysis = analyze_sheet_structure_simple( sheets_service=sheets_service, spreadsheet_id=spreadsheet_id, sheet_name=sheet_name ) result = { "success": True, "spreadsheet_name": spreadsheet_name, "sheet_name": sheet_name, "analysis": analysis, "message": f"Successfully analyzed sheet structure '{sheet_name}' in '{spreadsheet_name}'" } return compact_json_response(result) except Exception as e: error_result = { "success": False, "spreadsheet_name": spreadsheet_name, "sheet_name": sheet_name, "message": f"Error analyzing sheet structure: {str(e)}" } return compact_json_response(error_result)
• gsheet_mcp_server/server.py:218-244 (registration)

  Registers the tool 'analyze_sheet_structure_tool' with @mcp.tool() decorator, defines input schema using Pydantic Field, and delegates to the handler.
  @mcp.tool() def analyze_sheet_structure_tool( spreadsheet_name: str = Field(..., description="The name of the Google Spreadsheet"), sheet_name: str = Field(..., description="Name of the specific sheet to analyze") ) -> str: """ Analyze a specific sheet's structure - quick overview. This tool provides a simple overview of what's in the sheet: - Sheet basic info (name, size, hidden status) - Tables (count, names, ranges, sizes) - Charts (count, IDs, positions) - Slicers (count, IDs, positions) - Drawings (count, IDs, positions) - Developer metadata (count, keys, values) - Summary (total elements, sheet type, frozen panes) Args: spreadsheet_name: The name of the Google Spreadsheet sheet_name: Name of the specific sheet to analyze Returns: JSON string with simplified structure overview """ sheets_service, drive_service = _get_google_services() return analyze_sheet_structure_handler(drive_service, sheets_service, spreadsheet_name, sheet_name)
• gsheet_mcp_server/handler/sheets/analyze_sheet_structure_handler.py:7-51 (helper)

  Helper function that fetches sheet data from Google Sheets API using spreadsheets.get with specific fields and locates the target sheet.
  def analyze_sheet_structure_simple( sheets_service, spreadsheet_id: str, sheet_name: str ) -> Dict[str, Any]: """ Simple analysis of a sheet structure - quick overview of elements and data. Args: sheets_service: Google Sheets API service spreadsheet_id: ID of the spreadsheet sheet_name: Name of the sheet to analyze Returns: Dictionary with simple sheet structure and data overview """ try: # Get comprehensive spreadsheet data including values result = sheets_service.spreadsheets().get( spreadsheetId=spreadsheet_id, fields="sheets.properties,sheets.charts,sheets.tables,sheets.slicers,sheets.developerMetadata,sheets.drawings,sheets.data" ).execute() sheets = result.get('sheets', []) # Find the specific sheet target_sheet = None for sheet in sheets: props = sheet.get('properties', {}) if props.get('title') == sheet_name: target_sheet = sheet break if not target_sheet: raise RuntimeError(f"Sheet '{sheet_name}' not found in spreadsheet") return process_simple_sheet_analysis(target_sheet, sheets_service, spreadsheet_id, sheet_name) except HttpError as error: error_details = error.error_details[0] if hasattr(error, 'error_details') and error.error_details else {} error_message = error_details.get('message', str(error)) raise RuntimeError(f"Google Sheets API error: {error_message}") except Exception as error: raise RuntimeError(f"Unexpected error analyzing sheet structure: {str(error)}")
• gsheet_mcp_server/handler/sheets/analyze_sheet_structure_handler.py:53-250 (helper)

  Helper function that processes the raw sheet data: extracts basic info, structured elements (tables, charts, etc.), analyzes data areas, generates summary.
  def process_simple_sheet_analysis(sheet: Dict[str, Any], sheets_service, spreadsheet_id: str, sheet_name: str) -> Dict[str, Any]: """ Process simple analysis for a single sheet - overview of structure and data. Args: sheet: Raw sheet data from API sheets_service: Google Sheets API service spreadsheet_id: ID of the spreadsheet sheet_name: Name of the sheet Returns: Simple sheet structure and data overview """ props = sheet.get('properties', {}) grid_props = props.get('gridProperties', {}) # Basic sheet info sheet_info = { "name": props.get('title'), "hidden": props.get('hidden', False), "grid_size": f"{grid_props.get('rowCount', 0)} rows × {grid_props.get('columnCount', 0)} columns" } # Get structured elements first tables = sheet.get('tables', []) charts = sheet.get('charts', []) slicers = sheet.get('slicers', []) drawings = sheet.get('drawings', []) dev_metadata = sheet.get('developerMetadata', []) # Create structured element ranges for exclusion structured_ranges = [] # Add table ranges for table in tables: table_range = table.get('range', {}) if table_range: start_row = table_range.get('startRowIndex', 0) + 1 end_row = table_range.get('endRowIndex', 0) start_col = table_range.get('startColumnIndex', 0) + 1 end_col = table_range.get('endColumnIndex', 0) structured_ranges.append({ "type": "table", "name": table.get('displayName', table.get('name', 'Unknown')), "start_row": start_row, "end_row": end_row, "start_col": start_col, "end_col": end_col, "range": f"{chr(64 + start_col)}{start_row}:{chr(64 + end_col)}{end_row}" }) # Add chart ranges (approximate based on position) for chart in charts: position = chart.get('position', {}) if position: structured_ranges.append({ "type": "chart", "id": chart.get('chartId', 'Unknown'), "position": position }) # Add slicer ranges for slicer in slicers: position = slicer.get('position', {}) if position: structured_ranges.append({ "type": "slicer", "id": slicer.get('slicerId', 'Unknown'), "position": position }) # Add drawing ranges for drawing in drawings: position = drawing.get('position', {}) if position: structured_ranges.append({ "type": "drawing", "id": drawing.get('drawingId', 'Unknown'), "position": position }) # Get sheet data to analyze content try: data_response = sheets_service.spreadsheets().values().get( spreadsheetId=spreadsheet_id, range=f"'{sheet_name}'!A1:Z1000" # Get first 1000 rows and 26 columns ).execute() values = data_response.get('values', []) data_analysis = analyze_sheet_data_separated(values, structured_ranges) except Exception as e: data_analysis = { "structured_data": [], "unstructured_data": [], "data_summary": { "total_structured_rows": 0, "total_unstructured_rows": 0, "total_columns_with_data": 0, "structured_areas": [], "unstructured_areas": [] } } # Tables overview tables_overview = { "count": len(tables), "items": [] } for table in tables: table_info = { "name": table.get('displayName', table.get('name', 'Unnamed')), "range": table.get('range', 'Unknown'), "size": f"{len(table.get('columns', []))} columns × {len(table.get('rows', []))} rows" } tables_overview["items"].append(table_info) # Charts overview charts_overview = { "count": len(charts), "items": [] } for chart in charts: chart_info = { "id": chart.get('chartId', 'Unknown'), "position": chart.get('position', {}).get('overlayPosition', {}).get('anchorCell', {}).get('sheetId', 'Unknown') } charts_overview["items"].append(chart_info) # Slicers overview slicers_overview = { "count": len(slicers), "items": [] } for slicer in slicers: slicer_info = { "id": slicer.get('slicerId', 'Unknown'), "position": slicer.get('position', {}).get('overlayPosition', {}).get('anchorCell', {}).get('sheetId', 'Unknown') } slicers_overview["items"].append(slicer_info) # Drawings overview drawings_overview = { "count": len(drawings), "items": [] } for drawing in drawings: drawing_info = { "id": drawing.get('drawingId', 'Unknown'), "position": drawing.get('position', {}).get('overlayPosition', {}).get('anchorCell', {}).get('sheetId', 'Unknown') } drawings_overview["items"].append(drawing_info) # Developer metadata overview metadata_overview = { "count": len(dev_metadata), "items": [] } for metadata in dev_metadata: metadata_info = { "key": metadata.get('metadataKey', 'Unknown'), "value": metadata.get('metadataValue', 'Unknown') } metadata_overview["items"].append(metadata_info) # Calculate simple summary total_structured_elements = len(tables) + len(charts) + len(slicers) + len(drawings) + len(dev_metadata) total_unstructured_data = len(data_analysis["unstructured_data"]) sheet_type = "EMPTY" if total_structured_elements == 0 and total_unstructured_data == 0 else "DATA_TABLE" if len(tables) > 0 else "DATA_SHEET" if total_unstructured_data > 0 else "VISUAL" summary = { "total_structured_elements": total_structured_elements, "total_unstructured_data_areas": total_unstructured_data, "sheet_type": sheet_type, "has_frozen_panes": grid_props.get('frozenRowCount', 0) > 0 or grid_props.get('frozenColumnCount', 0) > 0, "element_breakdown": { "tables": len(tables), "charts": len(charts), "slicers": len(slicers), "drawings": len(drawings), "metadata": len(dev_metadata) }, "data_content": data_analysis["data_summary"] } return { "sheet_info": sheet_info, "structured_elements": { "tables": tables_overview, "charts": charts_overview, "slicers": slicers_overview, "drawings": drawings_overview, "developer_metadata": metadata_overview }, "unstructured_data": data_analysis["unstructured_data"], "summary": summary }