mcp-gsheets

Instructions

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';