// Google Sheets MCP tools
import { z } from 'zod';
import type { SheetsService } from '../services/sheets.js';
// ─────────────────────────────────────────────────────────────────────────────
// SCHEMAS
// ─────────────────────────────────────────────────────────────────────────────
export const CreateGoogleSheetSchema = z.object({
name: z.string().describe('Spreadsheet name'),
data: z.array(z.array(z.string())).describe('Initial data as array of rows'),
parentFolder: z.string().optional().describe('Parent folder ID or path'),
});
export const UpdateGoogleSheetSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to update (e.g., "Sheet1!A1:C10")'),
data: z.array(z.array(z.string())).describe('Data as array of rows'),
});
export const GetGoogleSheetContentSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to read (e.g., "Sheet1!A1:C10")'),
});
const ColorSchema = z.object({
red: z.number().min(0).max(1).optional(),
green: z.number().min(0).max(1).optional(),
blue: z.number().min(0).max(1).optional(),
});
export const FormatGoogleSheetCellsSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to format (e.g., "A1:C10")'),
backgroundColor: ColorSchema.optional().describe('Background color (RGB 0-1)'),
horizontalAlignment: z.enum(['LEFT', 'CENTER', 'RIGHT']).optional(),
verticalAlignment: z.enum(['TOP', 'MIDDLE', 'BOTTOM']).optional(),
wrapStrategy: z.enum(['OVERFLOW_CELL', 'CLIP', 'WRAP']).optional(),
});
export const FormatGoogleSheetTextSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to format'),
bold: z.boolean().optional(),
italic: z.boolean().optional(),
strikethrough: z.boolean().optional(),
underline: z.boolean().optional(),
fontSize: z.number().optional(),
fontFamily: z.string().optional(),
foregroundColor: ColorSchema.optional(),
});
export const FormatGoogleSheetNumbersSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to format'),
pattern: z.string().describe('Number format pattern (e.g., "#,##0.00", "$#,##0.00", "0.00%")'),
});
export const SetGoogleSheetBordersSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to add borders'),
style: z.enum(['SOLID', 'DASHED', 'DOTTED', 'DOUBLE']),
color: ColorSchema.optional(),
});
export const MergeGoogleSheetCellsSchema = z.object({
spreadsheetId: z.string().describe('Spreadsheet ID'),
range: z.string().describe('Range to merge'),
mergeType: z.enum(['MERGE_ALL', 'MERGE_COLUMNS', 'MERGE_ROWS']).optional(),
});
// ─────────────────────────────────────────────────────────────────────────────
// TOOL DEFINITIONS
// ─────────────────────────────────────────────────────────────────────────────
export const sheetsTools = [
{
name: 'createGoogleSheet',
description: 'Create a new Google Sheet with initial data',
inputSchema: {
type: 'object' as const,
properties: {
name: { type: 'string', description: 'Spreadsheet name' },
data: { type: 'array', description: 'Initial data as array of rows', items: { type: 'array', items: { type: 'string' } } },
parentFolder: { type: 'string', description: 'Parent folder ID or path' },
},
required: ['name', 'data'],
},
},
{
name: 'updateGoogleSheet',
description: 'Update cells in a Google Sheet',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to update (e.g., "Sheet1!A1:C10")' },
data: { type: 'array', description: 'Data as array of rows', items: { type: 'array', items: { type: 'string' } } },
},
required: ['spreadsheetId', 'range', 'data'],
},
},
{
name: 'getGoogleSheetContent',
description: 'Get content from a Google Sheet',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to read (e.g., "Sheet1!A1:C10")' },
},
required: ['spreadsheetId', 'range'],
},
},
{
name: 'formatGoogleSheetCells',
description: 'Format cells (background color, alignment, text wrap)',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to format' },
backgroundColor: { type: 'object', description: 'Background color (RGB 0-1)', properties: { red: { type: 'number' }, green: { type: 'number' }, blue: { type: 'number' } } },
horizontalAlignment: { type: 'string', enum: ['LEFT', 'CENTER', 'RIGHT'] },
verticalAlignment: { type: 'string', enum: ['TOP', 'MIDDLE', 'BOTTOM'] },
wrapStrategy: { type: 'string', enum: ['OVERFLOW_CELL', 'CLIP', 'WRAP'] },
},
required: ['spreadsheetId', 'range'],
},
},
{
name: 'formatGoogleSheetText',
description: 'Format text in cells (bold, italic, font size, color)',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to format' },
bold: { type: 'boolean' },
italic: { type: 'boolean' },
strikethrough: { type: 'boolean' },
underline: { type: 'boolean' },
fontSize: { type: 'number' },
fontFamily: { type: 'string' },
foregroundColor: { type: 'object', properties: { red: { type: 'number' }, green: { type: 'number' }, blue: { type: 'number' } } },
},
required: ['spreadsheetId', 'range'],
},
},
{
name: 'formatGoogleSheetNumbers',
description: 'Apply number formatting (currency, percentage, date, etc)',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to format' },
pattern: { type: 'string', description: 'Number format pattern (e.g., "#,##0.00", "$#,##0.00", "0.00%")' },
},
required: ['spreadsheetId', 'range', 'pattern'],
},
},
{
name: 'setGoogleSheetBorders',
description: 'Set borders on cells',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to add borders' },
style: { type: 'string', enum: ['SOLID', 'DASHED', 'DOTTED', 'DOUBLE'] },
color: { type: 'object', properties: { red: { type: 'number' }, green: { type: 'number' }, blue: { type: 'number' } } },
},
required: ['spreadsheetId', 'range', 'style'],
},
},
{
name: 'mergeGoogleSheetCells',
description: 'Merge cells in a range',
inputSchema: {
type: 'object' as const,
properties: {
spreadsheetId: { type: 'string', description: 'Spreadsheet ID' },
range: { type: 'string', description: 'Range to merge' },
mergeType: { type: 'string', enum: ['MERGE_ALL', 'MERGE_COLUMNS', 'MERGE_ROWS'] },
},
required: ['spreadsheetId', 'range'],
},
},
];
// ─────────────────────────────────────────────────────────────────────────────
// HANDLERS
// ─────────────────────────────────────────────────────────────────────────────
export function createSheetsHandlers(sheetsService: SheetsService) {
return {
createGoogleSheet: async (args: z.infer<typeof CreateGoogleSheetSchema>) => {
const params = CreateGoogleSheetSchema.parse(args);
return sheetsService.createSheet(params.name, params.data, params.parentFolder);
},
updateGoogleSheet: async (args: z.infer<typeof UpdateGoogleSheetSchema>) => {
const params = UpdateGoogleSheetSchema.parse(args);
return sheetsService.updateSheet(params.spreadsheetId, params.range, params.data);
},
getGoogleSheetContent: async (args: z.infer<typeof GetGoogleSheetContentSchema>) => {
const params = GetGoogleSheetContentSchema.parse(args);
return sheetsService.getSheetContent(params.spreadsheetId, params.range);
},
formatGoogleSheetCells: async (args: z.infer<typeof FormatGoogleSheetCellsSchema>) => {
const params = FormatGoogleSheetCellsSchema.parse(args);
await sheetsService.formatCells(params.spreadsheetId, params.range, {
backgroundColor: params.backgroundColor,
horizontalAlignment: params.horizontalAlignment,
verticalAlignment: params.verticalAlignment,
wrapStrategy: params.wrapStrategy,
});
return { success: true };
},
formatGoogleSheetText: async (args: z.infer<typeof FormatGoogleSheetTextSchema>) => {
const params = FormatGoogleSheetTextSchema.parse(args);
await sheetsService.formatText(params.spreadsheetId, params.range, {
bold: params.bold,
italic: params.italic,
strikethrough: params.strikethrough,
underline: params.underline,
fontSize: params.fontSize,
fontFamily: params.fontFamily,
foregroundColor: params.foregroundColor,
});
return { success: true };
},
formatGoogleSheetNumbers: async (args: z.infer<typeof FormatGoogleSheetNumbersSchema>) => {
const params = FormatGoogleSheetNumbersSchema.parse(args);
await sheetsService.formatNumbers(params.spreadsheetId, params.range, params.pattern);
return { success: true };
},
setGoogleSheetBorders: async (args: z.infer<typeof SetGoogleSheetBordersSchema>) => {
const params = SetGoogleSheetBordersSchema.parse(args);
await sheetsService.setBorders(params.spreadsheetId, params.range, params.style, params.color);
return { success: true };
},
mergeGoogleSheetCells: async (args: z.infer<typeof MergeGoogleSheetCellsSchema>) => {
const params = MergeGoogleSheetCellsSchema.parse(args);
await sheetsService.mergeCells(params.spreadsheetId, params.range, params.mergeType);
return { success: true };
},
};
}
