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 | Default |
|---|---|---|---|
| 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); } }
- 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';