import {
getSheetMetadata,
getSheetData,
getColumnValues,
getSheetStructure,
searchInSheet,
checkSheetErrors,
} from './read.js';
import {
createSpreadsheet,
createSheet,
setHeaders,
appendRows,
} from './write.js';
import {
formatSheet,
setDataValidation,
} from './format.js';
/**
* Tool definitions for MCP
*/
export const toolDefinitions = [
// Phase 1: Read tools
{
name: 'get_sheet_metadata',
description: 'Get metadata about a spreadsheet including sheet list, row counts, and column counts. Use this to understand the structure of a spreadsheet.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet (found in the URL)',
},
sheet_name: {
type: 'string',
description: 'Optional: Filter to a specific sheet by name',
},
},
required: ['spreadsheet_id'],
},
},
{
name: 'get_sheet_data',
description: 'Get cell data from a specific range in a spreadsheet. Returns a 2D array of values.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
range: {
type: 'string',
description: 'The A1 notation range to retrieve (e.g., "Sheet1!A1:D10" or "A1:D10")',
},
},
required: ['spreadsheet_id', 'range'],
},
},
{
name: 'get_column_values',
description: 'Get all values from a specific column in a sheet. Useful for understanding value patterns and distributions.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name of the sheet',
},
column: {
type: 'string',
description: 'The column letter (e.g., "A", "B", "AA")',
},
},
required: ['spreadsheet_id', 'sheet_name', 'column'],
},
},
{
name: 'get_sheet_structure',
description: 'Analyze the structure of a sheet including headers, inferred data types, and sample data. Great for understanding sheet format.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'Optional: Analyze only a specific sheet',
},
},
required: ['spreadsheet_id'],
},
},
{
name: 'search_in_sheet',
description: 'Search for a text string within a spreadsheet. Returns matching cells with their locations.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
query: {
type: 'string',
description: 'The text to search for (case-insensitive)',
},
sheet_name: {
type: 'string',
description: 'Optional: Search only in a specific sheet',
},
},
required: ['spreadsheet_id', 'query'],
},
},
{
name: 'check_sheet_errors',
description: 'Check for cell errors (#REF!, #ERROR!, #VALUE!, #N/A, #DIV/0!, etc.) in a spreadsheet. Useful for debugging formulas and finding broken references.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'Optional: Check only a specific sheet',
},
},
required: ['spreadsheet_id'],
},
},
// Phase 2: Write tools
{
name: 'create_spreadsheet',
description: 'Create a new Google Spreadsheet with optional sheet names.',
inputSchema: {
type: 'object',
properties: {
title: {
type: 'string',
description: 'The title for the new spreadsheet',
},
sheets: {
type: 'array',
items: { type: 'string' },
description: 'Optional: Names for the sheets to create (defaults to "Sheet1")',
},
},
required: ['title'],
},
},
{
name: 'create_sheet',
description: 'Add a new sheet to an existing spreadsheet.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name for the new sheet',
},
},
required: ['spreadsheet_id', 'sheet_name'],
},
},
{
name: 'set_headers',
description: 'Set the header row (first row) of a sheet. Use dry_run=true to preview changes before applying.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name of the sheet',
},
headers: {
type: 'array',
items: { type: 'string' },
description: 'Array of header names',
},
dry_run: {
type: 'boolean',
description: 'If true, only preview the changes without applying them',
},
},
required: ['spreadsheet_id', 'sheet_name', 'headers'],
},
},
{
name: 'append_rows',
description: 'Append rows of data to a sheet. Use dry_run=true to preview changes before applying.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name of the sheet',
},
rows: {
type: 'array',
items: {
type: 'array',
items: { type: 'string' },
},
description: 'Array of rows, where each row is an array of cell values',
},
dry_run: {
type: 'boolean',
description: 'If true, only preview the changes without applying them',
},
},
required: ['spreadsheet_id', 'sheet_name', 'rows'],
},
},
// Phase 3: Format tools
{
name: 'format_sheet',
description: 'Apply formatting to a sheet including header styling, column widths, and alternate row colors. Use this to make sheets more readable and user-friendly.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name of the sheet to format',
},
header_style: {
type: 'object',
properties: {
background_color: {
type: 'string',
description: 'Hex color for header background (e.g., "#4285f4")',
},
text_color: {
type: 'string',
description: 'Hex color for header text (e.g., "#ffffff")',
},
bold: {
type: 'boolean',
description: 'Make header text bold',
},
font_size: {
type: 'number',
description: 'Font size in points',
},
freeze: {
type: 'boolean',
description: 'Freeze the header row',
},
},
description: 'Styling options for the header row',
},
columns: {
type: 'array',
items: {
type: 'object',
properties: {
column: {
type: 'string',
description: 'Column letter (e.g., "A", "B")',
},
width: {
type: 'number',
description: 'Column width in pixels',
},
},
required: ['column'],
},
description: 'Column-specific settings',
},
alternate_row_colors: {
type: 'object',
properties: {
first_color: {
type: 'string',
description: 'Hex color for odd rows (e.g., "#ffffff")',
},
second_color: {
type: 'string',
description: 'Hex color for even rows (e.g., "#f3f3f3")',
},
},
required: ['first_color', 'second_color'],
description: 'Enable alternating row colors for better readability',
},
dry_run: {
type: 'boolean',
description: 'If true, only preview the changes without applying them',
},
},
required: ['spreadsheet_id', 'sheet_name'],
},
},
{
name: 'set_data_validation',
description: 'Set data validation rules for a column (dropdown lists, number ranges, dates, checkboxes). This helps users enter correct data.',
inputSchema: {
type: 'object',
properties: {
spreadsheet_id: {
type: 'string',
description: 'The ID of the Google Spreadsheet',
},
sheet_name: {
type: 'string',
description: 'The name of the sheet',
},
column: {
type: 'string',
description: 'Column letter to apply validation (e.g., "A", "B")',
},
validation_type: {
type: 'string',
enum: ['dropdown', 'number', 'date', 'checkbox', 'custom'],
description: 'Type of validation to apply',
},
values: {
type: 'array',
items: { type: 'string' },
description: 'For dropdown: list of allowed values',
},
min: {
type: 'number',
description: 'For number: minimum allowed value',
},
max: {
type: 'number',
description: 'For number: maximum allowed value',
},
formula: {
type: 'string',
description: 'For custom: validation formula',
},
allow_invalid: {
type: 'boolean',
description: 'If true, show warning instead of rejecting invalid input',
},
help_text: {
type: 'string',
description: 'Help message shown when user selects the cell',
},
start_row: {
type: 'number',
description: 'Row number to start validation from (default: 2, after header)',
},
dry_run: {
type: 'boolean',
description: 'If true, only preview the changes without applying them',
},
},
required: ['spreadsheet_id', 'sheet_name', 'column', 'validation_type'],
},
},
];
/**
* Handle tool calls
*/
export async function handleToolCall(
name: string,
args: Record<string, unknown>
): Promise<object> {
switch (name) {
// Phase 1: Read tools
case 'get_sheet_metadata':
return getSheetMetadata(args as { spreadsheet_id: string; sheet_name?: string });
case 'get_sheet_data':
return getSheetData(args as { spreadsheet_id: string; range: string });
case 'get_column_values':
return getColumnValues(args as {
spreadsheet_id: string;
sheet_name: string;
column: string;
});
case 'get_sheet_structure':
return getSheetStructure(args as { spreadsheet_id: string; sheet_name?: string });
case 'search_in_sheet':
return searchInSheet(args as {
spreadsheet_id: string;
query: string;
sheet_name?: string;
});
case 'check_sheet_errors':
return checkSheetErrors(args as {
spreadsheet_id: string;
sheet_name?: string;
});
// Phase 2: Write tools
case 'create_spreadsheet':
return createSpreadsheet(args as { title: string; sheets?: string[] });
case 'create_sheet':
return createSheet(args as { spreadsheet_id: string; sheet_name: string });
case 'set_headers':
return setHeaders(args as {
spreadsheet_id: string;
sheet_name: string;
headers: string[];
dry_run?: boolean;
});
case 'append_rows':
return appendRows(args as {
spreadsheet_id: string;
sheet_name: string;
rows: string[][];
dry_run?: boolean;
});
// Phase 3: Format tools
case 'format_sheet':
return formatSheet(args as {
spreadsheet_id: string;
sheet_name: string;
header_style?: {
background_color?: string;
text_color?: string;
bold?: boolean;
font_size?: number;
freeze?: boolean;
};
columns?: Array<{
column: string;
width?: number;
}>;
alternate_row_colors?: {
first_color: string;
second_color: string;
};
dry_run?: boolean;
});
case 'set_data_validation':
return setDataValidation(args as {
spreadsheet_id: string;
sheet_name: string;
column: string;
validation_type: 'dropdown' | 'number' | 'date' | 'checkbox' | 'custom';
values?: string[];
min?: number;
max?: number;
formula?: string;
allow_invalid?: boolean;
help_text?: string;
start_row?: number;
dry_run?: boolean;
});
default:
throw new Error(`Unknown tool: ${name}`);
}
}
