Google Workspace MCP Server - Control Gmail, Calendar, Docs, Sheets, Slides, Chat, Forms & Drive

Instructions

Modifies values in a specific range of a Google Sheet - can write, update, or clear values.

Args:
    user_google_email (str): The user's Google email address. Required.
    spreadsheet_id (str): The ID of the spreadsheet. Required.
    range_name (str): The range to modify (e.g., "Sheet1!A1:D10", "A1:D10"). Required.
    values (Optional[List[List[str]]]): 2D array of values to write/update. Required unless clear_values=True.
    value_input_option (str): How to interpret input values ("RAW" or "USER_ENTERED"). Defaults to "USER_ENTERED".
    clear_values (bool): If True, clears the range instead of writing values. Defaults to False.

Returns:
    str: Confirmation message of the successful modification operation.

Implementation Reference

• gsheets/sheets_tools.py:173-258 (handler)

  The core handler function for the 'modify_sheet_values' tool. It handles writing, updating, or clearing values in a Google Sheet range using the Sheets API. Includes JSON parsing for MCP parameters, API calls for update or clear, and logging.

  @server.tool()
  @handle_http_errors("modify_sheet_values", service_type="sheets")
  @require_google_service("sheets", "sheets_write")
  async def modify_sheet_values(
      service,
      user_google_email: str,
      spreadsheet_id: str,
      range_name: str,
      values: Optional[Union[str, List[List[str]]]] = None,
      value_input_option: str = "USER_ENTERED",
      clear_values: bool = False,
  ) -> str:
      """
      Modifies values in a specific range of a Google Sheet - can write, update, or clear values.
  
      Args:
          user_google_email (str): The user's Google email address. Required.
          spreadsheet_id (str): The ID of the spreadsheet. Required.
          range_name (str): The range to modify (e.g., "Sheet1!A1:D10", "A1:D10"). Required.
          values (Optional[Union[str, List[List[str]]]]): 2D array of values to write/update. Can be a JSON string or Python list. Required unless clear_values=True.
          value_input_option (str): How to interpret input values ("RAW" or "USER_ENTERED"). Defaults to "USER_ENTERED".
          clear_values (bool): If True, clears the range instead of writing values. Defaults to False.
  
      Returns:
          str: Confirmation message of the successful modification operation.
      """
      operation = "clear" if clear_values else "write"
      logger.info(f"[modify_sheet_values] Invoked. Operation: {operation}, Email: '{user_google_email}', Spreadsheet: {spreadsheet_id}, Range: {range_name}")
  
      # Parse values if it's a JSON string (MCP passes parameters as JSON strings)
      if values is not None and isinstance(values, str):
          try:
              parsed_values = json.loads(values)
              if not isinstance(parsed_values, list):
                  raise ValueError(f"Values must be a list, got {type(parsed_values).__name__}")
              # Validate it's a list of lists
              for i, row in enumerate(parsed_values):
                  if not isinstance(row, list):
                      raise ValueError(f"Row {i} must be a list, got {type(row).__name__}")
              values = parsed_values
              logger.info(f"[modify_sheet_values] Parsed JSON string to Python list with {len(values)} rows")
          except json.JSONDecodeError as e:
              raise Exception(f"Invalid JSON format for values: {e}")
          except ValueError as e:
              raise Exception(f"Invalid values structure: {e}")
  
      if not clear_values and not values:
          raise Exception("Either 'values' must be provided or 'clear_values' must be True.")
  
      if clear_values:
          result = await asyncio.to_thread(
              service.spreadsheets()
              .values()
              .clear(spreadsheetId=spreadsheet_id, range=range_name)
              .execute
          )
  
          cleared_range = result.get("clearedRange", range_name)
          text_output = f"Successfully cleared range '{cleared_range}' in spreadsheet {spreadsheet_id} for {user_google_email}."
          logger.info(f"Successfully cleared range '{cleared_range}' for {user_google_email}.")
      else:
          body = {"values": values}
  
          result = await asyncio.to_thread(
              service.spreadsheets()
              .values()
              .update(
                  spreadsheetId=spreadsheet_id,
                  range=range_name,
                  valueInputOption=value_input_option,
                  body=body,
              )
              .execute
          )
  
          updated_cells = result.get("updatedCells", 0)
          updated_rows = result.get("updatedRows", 0)
          updated_columns = result.get("updatedColumns", 0)
  
          text_output = (
              f"Successfully updated range '{range_name}' in spreadsheet {spreadsheet_id} for {user_google_email}. "
              f"Updated: {updated_cells} cells, {updated_rows} rows, {updated_columns} columns."
          )
          logger.info(f"Successfully updated {updated_cells} cells for {user_google_email}.")
  
      return text_output

• gsheets/__init__.py:7-23 (registration)

  Module __init__.py imports and lists 'modify_sheet_values' in __all__, registering it as part of the gsheets module tools.

  from .sheets_tools import (
      list_spreadsheets,
      get_spreadsheet_info,
      read_sheet_values,
      modify_sheet_values,
      create_spreadsheet,
      create_sheet,
  )
  
  __all__ = [
      "list_spreadsheets",
      "get_spreadsheet_info", 
      "read_sheet_values",
      "modify_sheet_values",
      "create_spreadsheet",
      "create_sheet",
  ]