delete_table_records_tool
Remove specific records from a table while preserving its structure. Specify record numbers to delete rows in descending order to avoid index shifting.
Instructions
Delete specific records (rows) from a table.
This tool removes specific records from a table while preserving the table structure.
Record numbers are 1-based and exclude the header row. Records are deleted in descending order
(bigger numbers first) to avoid index shifting issues.
Args:
spreadsheet_name: Name of the spreadsheet
sheet_name: Name of the sheet containing the table
table_name: Name of the table to delete records from
record_numbers: List of record numbers to delete (1-based, excluding header)
Returns:
JSON string with success status and deletion detailsInput Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| spreadsheet_name | Yes | The name of the Google Spreadsheet | |
| sheet_name | Yes | The name of the sheet containing the table | |
| table_name | Yes | Name of the table to delete records from | |
| record_numbers | Yes | List of record numbers to delete (1-based, excluding header) |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| result | Yes |
Implementation Reference
- gsheet_mcp_server/server.py:445-469 (registration)The @mcp.tool() decorator registers delete_table_records_tool as an MCP tool. It defines the schema (parameters with Field descriptions) and delegates to delete_table_records_handler.
@mcp.tool() def delete_table_records_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(..., description="Name of the table to delete records from"), record_numbers: List[int] = Field(..., description="List of record numbers to delete (1-based, excluding header)") ) -> str: """ Delete specific records (rows) from a table. This tool removes specific records from a table while preserving the table structure. Record numbers are 1-based and exclude the header row. Records are deleted in descending order (bigger numbers first) to avoid index shifting issues. Args: spreadsheet_name: Name of the spreadsheet sheet_name: Name of the sheet containing the table table_name: Name of the table to delete records from record_numbers: List of record numbers to delete (1-based, excluding header) Returns: JSON string with success status and deletion details """ sheets_service, drive_service = _get_google_services() return delete_table_records_handler(drive_service, sheets_service, spreadsheet_name, sheet_name, table_name, record_numbers) - The core handler function that implements record deletion logic. Validates inputs, resolves spreadsheet/sheet/table IDs, builds DeleteRangeRequest batch updates sorted in descending order to avoid index shifting, and executes them via the Google Sheets API.
def delete_table_records_handler( drive_service, sheets_service, spreadsheet_name: str, sheet_name: str, table_name: str, record_numbers: List[int] ) -> str: """ Delete specific records (rows) from a table in Google Sheets using DeleteRangeRequest and UpdateTableRequest. According to the official Google Sheets API documentation, to delete records from a table: 1. Use DeleteRangeRequest to remove specific rows from the sheet 2. Use UpdateTableRequest to shrink the table's range to exclude deleted rows 3. Delete records in descending order (bigger numbers first) to avoid index shifting 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 delete records from record_numbers: List of record numbers to delete (1-based, excluding header) Returns: str: Success message with deletion details or error message """ try: # Validate inputs if not table_name or table_name.strip() == "": return compact_json_response({ "success": False, "message": "Table name is required." }) if not record_numbers or not isinstance(record_numbers, list) or len(record_numbers) == 0: return compact_json_response({ "success": False, "message": "Record numbers are required. Please provide at least one record number to delete." }) # Validate record numbers validated_numbers = [] invalid_numbers = [] for i, record_number in enumerate(record_numbers): if not isinstance(record_number, int): invalid_numbers.append({"index": i, "value": record_number, "error": "Record number must be an integer"}) continue if record_number < 1: invalid_numbers.append({"index": i, "value": record_number, "error": "Record number must be 1 or greater"}) continue validated_numbers.append(record_number) if invalid_numbers: error_messages = [f"Record {item['index']+1} ({item['value']}): {item['error']}" for item in invalid_numbers] return compact_json_response({ "success": False, "message": f"Invalid record numbers: {'; '.join(error_messages)}", "invalid_numbers": invalid_numbers }) if not validated_numbers: return compact_json_response({ "success": False, "message": "No valid record numbers provided after validation." }) # Remove duplicates and sort in descending order (to avoid index shifting) unique_numbers = list(set(validated_numbers)) unique_numbers.sort(reverse=True) # Delete bigger numbers first # Get spreadsheet ID 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 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 compact_json_response({ "success": False, "message": f"Sheet '{sheet_name}' not found in spreadsheet '{spreadsheet_name}'." }) # Get table ID table_ids = get_table_ids_by_names(sheets_service, spreadsheet_id, sheet_name, [table_name]) table_id = table_ids.get(table_name) if not table_id: return compact_json_response({ "success": False, "message": f"Table '{table_name}' not found in sheet '{sheet_name}'." }) # Get table information try: table_info = get_table_info(sheets_service, spreadsheet_id, table_id) table_range = table_info.get("range", {}) except Exception as e: return compact_json_response({ "success": False, "message": f"Could not retrieve information for table '{table_name}': {str(e)}" }) # Get table boundaries table_start_row = table_range.get("startRowIndex", 0) table_end_row = table_range.get("endRowIndex", 0) table_start_col = table_range.get("startColumnIndex", 0) table_end_col = table_range.get("endColumnIndex", 0) # Validate row indices are within table range invalid_range_indices = [] valid_delete_requests = [] # Convert 1-based user index to 0-based API index (accounting for header) # and then to 0-based API index for deletion (accounting for header) for row_index in unique_numbers: api_row_index = table_start_row + row_index if api_row_index < table_start_row or api_row_index >= table_end_row: invalid_range_indices.append({ "row_index": row_index, "error": f"Row {row_index} is outside table range (1 to {table_end_row - table_start_row - 1})" }) else: # Create DeleteRangeRequest for this row delete_range_request = { "deleteRange": { "range": { "sheetId": sheet_id, "startRowIndex": api_row_index, "endRowIndex": api_row_index + 1, "startColumnIndex": table_start_col, "endColumnIndex": table_end_col }, "shiftDimension": "ROWS" } } valid_delete_requests.append(delete_range_request) if invalid_range_indices: error_messages = [f"Row {item['row_index']}: {item['error']}" for item in invalid_range_indices] return compact_json_response({ "success": False, "message": f"Invalid row indices: {'; '.join(error_messages)}", "invalid_range_indices": invalid_range_indices }) if not valid_delete_requests: return compact_json_response({ "success": False, "message": "No valid rows to delete after range validation." }) # Use only delete range requests all_requests = valid_delete_requests # Execute the batch update requests response = sheets_service.spreadsheets().batchUpdate( spreadsheetId=spreadsheet_id, body={"requests": all_requests} ).execute() # Extract response information replies = response.get("replies", []) delete_count = len(valid_delete_requests) response_data = { "success": True, "spreadsheet_name": spreadsheet_name, "sheet_name": sheet_name, "table_name": table_name, "rows_deleted": delete_count, "deleted_row_indices": unique_numbers, "message": f"Successfully deleted {delete_count} row(s) from table '{table_name}' in '{sheet_name}'" } return compact_json_response(response_data) 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 deleting table rows: {str(e)}" }) - get_table_info helper used by the handler to retrieve table metadata (range boundaries like startRowIndex, endRowIndex, startColumnIndex, endColumnIndex) from the Google Sheets API. These boundaries are essential for constructing the DeleteRangeRequest.
def get_table_info( sheets_service, spreadsheet_id: str, table_id: str ) -> Dict[str, Any]: """ Get comprehensive information about a specific table. Args: sheets_service: Google Sheets API service spreadsheet_id: ID of the spreadsheet table_id: ID of the table Returns: Dict containing comprehensive table information Raises: RuntimeError: If table is not found or API error occurs """ if not sheets_service: raise RuntimeError("Google Sheets service not initialized") if not spreadsheet_id: raise RuntimeError("Spreadsheet ID is required") if not table_id: raise RuntimeError("Table ID is required") try: # Get spreadsheet to find table information result = sheets_service.spreadsheets().get( spreadsheetId=spreadsheet_id, fields="sheets.properties,sheets.tables,sheets.tables.columnProperties" ).execute() # Search for the table across all sheets for sheet in result.get("sheets", []): tables = sheet.get("tables", []) for table in tables: if table.get("tableId") == table_id: table_range = table.get("range", {}) start_row = table_range.get("startRowIndex", 0) end_row = table_range.get("endRowIndex", 0) start_col = table_range.get("startColumnIndex", 0) end_col = table_range.get("endColumnIndex", 0) # Calculate actual row and column counts from range actual_row_count = end_row - start_row actual_column_count = end_col - start_col # Get column properties if available column_properties = table.get("columnProperties", []) columns = [] for i, col_prop in enumerate(column_properties): column_name = col_prop.get("columnName", f"Column {i+1}") column_type = col_prop.get("columnType", "TEXT") # Check for data validation rules to identify dropdown columns data_validation = col_prop.get("dataValidationRule", {}) if data_validation: validation_condition = data_validation.get("condition", {}) if validation_condition.get("type") == "ONE_OF_LIST": column_type = "DROPDOWN" column_info = { "name": column_name, "type": column_type, "index": i } # Preserve dataValidationRule if it exists if data_validation: column_info["dataValidationRule"] = data_validation columns.append(column_info) # Calculate range notation range_notation = f"{column_index_to_letter(start_col)}{start_row + 1}:{column_index_to_letter(end_col - 1)}{end_row}" return { "table_id": table_id, "table_name": table.get("displayName") or table.get("name") or f"Table{table_id}", "range": table_range, "column_count": actual_column_count, "row_count": actual_row_count, "start_row": start_row, "end_row": end_row, "start_col": start_col, "end_col": end_col, "range_notation": range_notation, "columns": columns } raise RuntimeError(f"Table with ID '{table_id}' not found") except HttpError as error: raise RuntimeError(f"Google Sheets API error getting table info: {error}") except Exception as error: raise RuntimeError(f"Unexpected error getting table info: {str(error)}") - get_table_ids_by_names helper used to resolve a human-readable table name to a Google Sheets table ID, which is required for the API calls.
def get_table_ids_by_names( sheets_service, spreadsheet_id: str, sheet_name: str, table_names: List[str] ) -> Dict[str, Optional[str]]: """ Get table IDs from spreadsheet ID, sheet name, and table names. Args: sheets_service: Google Sheets API service instance spreadsheet_id: ID of the spreadsheet sheet_name: Name of the sheet table_names: List of table names to find Returns: Dictionary mapping table names to their IDs (None if not found) Raises: RuntimeError: If Google Sheets service not initialized """ if not sheets_service: raise RuntimeError("Google Sheets service not initialized. Set Google credentials environment variables.") if not spreadsheet_id: raise RuntimeError("Spreadsheet ID is required") if not sheet_name: raise RuntimeError("Sheet name is required") if not table_names: return {} try: # Get spreadsheet metadata to find tables result = sheets_service.spreadsheets().get( spreadsheetId=spreadsheet_id, fields="sheets.properties,sheets.tables" ).execute() # Find the specific sheet target_sheet = None for sheet in result.get("sheets", []): props = sheet.get("properties", {}) if props.get("title") == sheet_name: target_sheet = sheet break if not target_sheet: return {name: None for name in table_names} # Create lookup dictionary for all tables in the sheet table_lookup = {} tables = target_sheet.get("tables", []) for table in tables: table_id = table.get("tableId") # Try different possible name fields for table names table_name = table.get("displayName") or table.get("name") or f"Table{table_id}" if table_id else "Unknown" table_lookup[table_name] = table_id # Return results for requested table names results = {} for table_name in table_names: results[table_name] = table_lookup.get(table_name) return results except HttpError as error: print(f"Error getting table IDs for spreadsheet '{spreadsheet_id}': {error}") return {name: None for name in table_names} except Exception as error: print(f"Unexpected error while getting table IDs: {error}") return {name: None for name in table_names} - compact_json_response helper used to format the handler's return value as a compact JSON string with minimal whitespace.
def compact_json_response(data: Dict[str, Any]) -> str: """ Convert a Python dictionary to a compact JSON string with no newlines or extra spaces. Args: data: Python dictionary to serialize Returns: Compact JSON string with minimal formatting """ return json.dumps(data, separators=(',', ':'))