sheets_batch_update_values

Update multiple ranges in a Google Sheets spreadsheet with new values in one request. Specify spreadsheet ID, range-value pairs, and input interpretation (RAW or USER_ENTERED).

Instructions

Update values in multiple ranges of a Google Sheets spreadsheet

Input Schema

TableJSON Schema

Name	Required	Description
`data`	Yes	Array of range-value pairs to update
`spreadsheetId`	Yes	The ID of the spreadsheet (found in the URL after /d/)
`valueInputOption`	No	How the input data should be interpreted (default: USER_ENTERED)

Implementation Reference

src/tools/batch-update-values.ts:48-75 (handler)

The main handler function that validates the input, calls the Google Sheets API to batch update values across multiple ranges, computes the total updated cells, and returns a formatted response.

export async function handleBatchUpdateValues(input: any) {
  try {
    const validatedInput = validateBatchUpdateValuesInput(input);
    const sheets = await getAuthenticatedClient();

    const response = await sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: validatedInput.spreadsheetId,
      requestBody: {
        valueInputOption: validatedInput.valueInputOption,
        data: validatedInput.data.map((item) => ({
          range: item.range,
          values: item.values,
        })),
      },
    });

    const totalUpdatedCells = response.data.responses
      ? response.data.responses.reduce(
          (sum: number, resp: any) => sum + (resp.updatedCells || 0),
          0
        )
      : 0;

    return formatUpdateResponse(totalUpdatedCells);
  } catch (error) {
    return handleError(error);
  }
}

src/tools/batch-update-values.ts:7-46 (schema)

The Tool object defining the name, description, and input schema for validating parameters like spreadsheetId, data (array of range-value pairs), and optional valueInputOption.

export const batchUpdateValuesTool: Tool = {
  name: 'sheets_batch_update_values',
  description: 'Update values in multiple ranges of a Google Sheets spreadsheet',
  inputSchema: {
    type: 'object',
    properties: {
      spreadsheetId: {
        type: 'string',
        description: 'The ID of the spreadsheet (found in the URL after /d/)',
      },
      data: {
        type: 'array',
        items: {
          type: 'object',
          properties: {
            range: {
              type: 'string',
              description: 'The A1 notation range to update',
            },
            values: {
              type: 'array',
              items: {
                type: 'array',
              },
              description: 'A 2D array of values for this range',
            },
          },
          required: ['range', 'values'],
        },
        description: 'Array of range-value pairs to update',
      },
      valueInputOption: {
        type: 'string',
        enum: ['RAW', 'USER_ENTERED'],
        description: 'How the input data should be interpreted (default: USER_ENTERED)',
      },
    },
    required: ['spreadsheetId', 'data'],
  },
};

src/index.ts:32-64 (registration)

Registration of tool handlers in a Map, mapping 'sheets_batch_update_values' to its handleBatchUpdateValues function.

const toolHandlers = new Map<string, (input: any) => Promise<any>>([
  ['sheets_check_access', tools.handleCheckAccess],
  ['sheets_get_values', tools.handleGetValues],
  ['sheets_batch_get_values', tools.handleBatchGetValues],
  ['sheets_get_metadata', tools.handleGetMetadata],
  ['sheets_update_values', tools.handleUpdateValues],
  ['sheets_batch_update_values', tools.handleBatchUpdateValues],
  ['sheets_append_values', tools.handleAppendValues],
  ['sheets_clear_values', tools.handleClearValues],
  ['sheets_create_spreadsheet', tools.handleCreateSpreadsheet],
  ['sheets_insert_sheet', tools.handleInsertSheet],
  ['sheets_delete_sheet', tools.handleDeleteSheet],
  ['sheets_duplicate_sheet', tools.handleDuplicateSheet],
  ['sheets_copy_to', tools.handleCopyTo],
  ['sheets_update_sheet_properties', tools.handleUpdateSheetProperties],
  ['sheets_format_cells', tools.formatCellsHandler],
  ['sheets_update_borders', tools.updateBordersHandler],
  ['sheets_merge_cells', tools.mergeCellsHandler],
  ['sheets_unmerge_cells', tools.unmergeCellsHandler],
  ['sheets_add_conditional_formatting', tools.addConditionalFormattingHandler],
  // Batch operations
  ['sheets_batch_delete_sheets', tools.handleBatchDeleteSheets],
  ['sheets_batch_format_cells', tools.handleBatchFormatCells],
  // Chart operations
  ['sheets_create_chart', tools.handleCreateChart],
  ['sheets_update_chart', tools.handleUpdateChart],
  ['sheets_delete_chart', tools.handleDeleteChart],
  // Link and date operations
  ['sheets_insert_link', tools.handleInsertLink],
  ['sheets_insert_date', tools.handleInsertDate],
  // Row operations
  ['sheets_insert_rows', tools.handleInsertRows],
]);

src/index.ts:67-99 (registration)

Registration of all tool objects including batchUpdateValuesTool for the listTools request handler.

const allTools = [
  tools.checkAccessTool,
  tools.getValuesTool,
  tools.batchGetValuesTool,
  tools.getMetadataTool,
  tools.updateValuesTool,
  tools.batchUpdateValuesTool,
  tools.appendValuesTool,
  tools.clearValuesTool,
  tools.createSpreadsheetTool,
  tools.insertSheetTool,
  tools.deleteSheetTool,
  tools.duplicateSheetTool,
  tools.copyToTool,
  tools.updateSheetPropertiesTool,
  tools.formatCellsTool,
  tools.updateBordersTool,
  tools.mergeCellsTool,
  tools.unmergeCellsTool,
  tools.addConditionalFormattingTool,
  // Batch operations
  tools.batchDeleteSheetsTool,
  tools.batchFormatCellsTool,
  // Chart operations
  tools.createChartTool,
  tools.updateChartTool,
  tools.deleteChartTool,
  // Link and date operations
  tools.insertLinkTool,
  tools.insertDateTool,
  // Row operations
  tools.insertRowsTool,
];

src/tools/index.ts:7-7 (registration)
Re-export of the batch-update-values tool and handler from its module.
```
export * from './batch-update-values.js';
```

mcp-gsheets