import { getGoogleAPIs } from '../auth/google-auth.js';
import {
getLogger,
validateInput,
SheetsCreateSchema,
SheetsGetValuesSchema,
SheetsUpdateValuesSchema,
SheetsAppendValuesSchema,
SheetsBatchUpdateSchema,
SheetsAddSheetSchema,
SheetsDeleteSheetSchema,
SheetsFormatCellsSchema,
isOperationAllowed,
OperationNotAllowedError,
GoogleAPIError,
withErrorHandling,
type SheetsCreate,
type SheetsGetValues,
type SheetsUpdateValues,
type SheetsAppendValues,
type SheetsBatchUpdate,
type SheetsAddSheet,
type SheetsDeleteSheet,
type SheetsFormatCells,
} from '@company-mcp/core';
const logger = getLogger();
// Types
export interface SpreadsheetInfo {
spreadsheetId: string;
url: string;
}
export interface ValuesResult {
values: unknown[][];
}
export interface UpdateResult {
updatedCells: number;
}
export interface AppendResult {
updates: {
updatedRows: number;
};
}
export interface BatchUpdateResult {
repliesCount: number;
}
export interface AddSheetResult {
sheetId: number;
title: string;
index: number;
}
export interface DeleteSheetResult {
success: boolean;
}
export interface FormatCellsResult {
success: boolean;
}
// Create spreadsheet
export async function sheetsCreateSpreadsheet(
input: unknown
): Promise<SpreadsheetInfo> {
return withErrorHandling('sheets_create_spreadsheet', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsCreateSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsCreate;
const startTime = Date.now();
const { sheets, drive } = getGoogleAPIs();
// Create spreadsheet
const response = await sheets.spreadsheets.create({
requestBody: {
properties: {
title: params.title,
},
},
});
const spreadsheetId = response.data.spreadsheetId!;
// Move to parent folder if specified
if (params.parentFolderId) {
await drive.files.update({
fileId: spreadsheetId,
addParents: params.parentFolderId,
removeParents: 'root',
fields: 'id, parents',
});
}
logger.audit('sheets_create_spreadsheet', 'create', {
args: { title: params.title, parentFolderId: params.parentFolderId },
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
spreadsheetId,
url: `https://docs.google.com/spreadsheets/d/${spreadsheetId}`,
};
});
}
// Get values
export async function sheetsGetValues(
input: unknown
): Promise<ValuesResult> {
return withErrorHandling('sheets_get_values', async () => {
const validation = validateInput(SheetsGetValuesSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsGetValues;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
const response = await sheets.spreadsheets.values.get({
spreadsheetId: params.spreadsheetId,
range: params.range,
});
logger.audit('sheets_get_values', 'get', {
args: { spreadsheetId: params.spreadsheetId, range: params.range },
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
values: response.data.values || [],
};
});
}
// Update values
export async function sheetsUpdateValues(
input: unknown
): Promise<UpdateResult> {
return withErrorHandling('sheets_update_values', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsUpdateValuesSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsUpdateValues;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
const response = await sheets.spreadsheets.values.update({
spreadsheetId: params.spreadsheetId,
range: params.range,
valueInputOption: params.valueInputOption,
requestBody: {
values: params.values,
},
});
logger.audit('sheets_update_values', 'update', {
args: {
spreadsheetId: params.spreadsheetId,
range: params.range,
rowCount: params.values.length,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
updatedCells: response.data.updatedCells || 0,
};
});
}
// Append values
export async function sheetsAppendValues(
input: unknown
): Promise<AppendResult> {
return withErrorHandling('sheets_append_values', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsAppendValuesSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsAppendValues;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
const response = await sheets.spreadsheets.values.append({
spreadsheetId: params.spreadsheetId,
range: params.range,
valueInputOption: params.valueInputOption,
insertDataOption: 'INSERT_ROWS',
requestBody: {
values: params.values,
},
});
logger.audit('sheets_append_values', 'append', {
args: {
spreadsheetId: params.spreadsheetId,
range: params.range,
rowCount: params.values.length,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
updates: {
updatedRows: response.data.updates?.updatedRows || 0,
},
};
});
}
// Batch update
export async function sheetsBatchUpdate(
input: unknown
): Promise<BatchUpdateResult> {
return withErrorHandling('sheets_batch_update', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsBatchUpdateSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsBatchUpdate;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
const response = await sheets.spreadsheets.batchUpdate({
spreadsheetId: params.spreadsheetId,
requestBody: {
requests: params.requests,
},
});
logger.audit('sheets_batch_update', 'batchUpdate', {
args: {
spreadsheetId: params.spreadsheetId,
requestCount: params.requests.length,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
repliesCount: response.data.replies?.length || 0,
};
});
}
// Add sheet
export async function sheetsAddSheet(
input: unknown
): Promise<AddSheetResult> {
return withErrorHandling('sheets_add_sheet', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsAddSheetSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsAddSheet;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
const response = await sheets.spreadsheets.batchUpdate({
spreadsheetId: params.spreadsheetId,
requestBody: {
requests: [
{
addSheet: {
properties: {
title: params.title,
...(params.index !== undefined && { index: params.index }),
},
},
},
],
},
});
const addedSheet = response.data.replies?.[0]?.addSheet?.properties;
logger.audit('sheets_add_sheet', 'addSheet', {
args: {
spreadsheetId: params.spreadsheetId,
title: params.title,
index: params.index,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
sheetId: addedSheet?.sheetId || 0,
title: addedSheet?.title || params.title,
index: addedSheet?.index || 0,
};
});
}
// Delete sheet
export async function sheetsDeleteSheet(
input: unknown
): Promise<DeleteSheetResult> {
return withErrorHandling('sheets_delete_sheet', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsDeleteSheetSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsDeleteSheet;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
await sheets.spreadsheets.batchUpdate({
spreadsheetId: params.spreadsheetId,
requestBody: {
requests: [
{
deleteSheet: {
sheetId: params.sheetId,
},
},
],
},
});
logger.audit('sheets_delete_sheet', 'deleteSheet', {
args: {
spreadsheetId: params.spreadsheetId,
sheetId: params.sheetId,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
success: true,
};
});
}
// Helper function to parse A1 notation range and get sheetId
async function parseRangeForSheetId(
sheets: ReturnType<typeof getGoogleAPIs>['sheets'],
spreadsheetId: string,
range: string
): Promise<{ sheetId: number; gridRange: object }> {
// Extract sheet name from range (e.g., "Sheet1!A1:B10" -> "Sheet1")
const sheetNameMatch = range.match(/^([^!]+)!/);
const sheetName = sheetNameMatch ? sheetNameMatch[1] : range.split('!')[0] || 'Sheet1';
// Get spreadsheet metadata to find sheetId
const metadata = await sheets.spreadsheets.get({
spreadsheetId,
fields: 'sheets.properties',
});
const sheet = metadata.data.sheets?.find(
(s) => s.properties?.title === sheetName
);
if (!sheet?.properties?.sheetId && sheet?.properties?.sheetId !== 0) {
throw new GoogleAPIError(`Sheet "${sheetName}" not found`, 404);
}
const sheetId = sheet.properties.sheetId;
// Parse cell range (e.g., "A1:B10" -> { startRowIndex, endRowIndex, startColumnIndex, endColumnIndex })
const cellRange = range.includes('!') ? range.split('!')[1] : range;
const gridRange = parseA1Range(cellRange, sheetId);
return { sheetId, gridRange };
}
// Helper to convert A1 notation to grid range
function parseA1Range(range: string, sheetId: number): object {
const colToIndex = (col: string): number => {
let index = 0;
for (let i = 0; i < col.length; i++) {
index = index * 26 + (col.charCodeAt(i) - 64);
}
return index - 1;
};
const parseCell = (cell: string): { col: number; row: number } | null => {
const match = cell.match(/^([A-Z]+)(\d+)$/);
if (!match) return null;
return {
col: colToIndex(match[1]),
row: parseInt(match[2], 10) - 1,
};
};
const parts = range.split(':');
const start = parseCell(parts[0]);
const end = parts[1] ? parseCell(parts[1]) : start;
if (!start) {
return { sheetId };
}
const gridRange: Record<string, number> = {
sheetId,
startRowIndex: start.row,
startColumnIndex: start.col,
};
if (end) {
gridRange.endRowIndex = end.row + 1;
gridRange.endColumnIndex = end.col + 1;
} else {
gridRange.endRowIndex = start.row + 1;
gridRange.endColumnIndex = start.col + 1;
}
return gridRange;
}
// Format cells
export async function sheetsFormatCells(
input: unknown
): Promise<FormatCellsResult> {
return withErrorHandling('sheets_format_cells', async () => {
// Check if write is allowed
if (!isOperationAllowed('sheets_write')) {
throw new OperationNotAllowedError('sheets_write');
}
const validation = validateInput(SheetsFormatCellsSchema, input);
if (!validation.success) {
throw new GoogleAPIError(validation.errors.join(', '), 400);
}
const params = validation.data as SheetsFormatCells;
const startTime = Date.now();
const { sheets } = getGoogleAPIs();
// Parse range to get sheetId and grid range
const { gridRange } = await parseRangeForSheetId(
sheets,
params.spreadsheetId,
params.range
);
// Build cell format
const cellFormat: Record<string, unknown> = {};
const fields: string[] = [];
if (params.format.backgroundColor) {
cellFormat.backgroundColor = params.format.backgroundColor;
fields.push('userEnteredFormat.backgroundColor');
}
if (params.format.textFormat) {
cellFormat.textFormat = params.format.textFormat;
fields.push('userEnteredFormat.textFormat');
}
if (params.format.horizontalAlignment) {
cellFormat.horizontalAlignment = params.format.horizontalAlignment;
fields.push('userEnteredFormat.horizontalAlignment');
}
if (params.format.verticalAlignment) {
cellFormat.verticalAlignment = params.format.verticalAlignment;
fields.push('userEnteredFormat.verticalAlignment');
}
await sheets.spreadsheets.batchUpdate({
spreadsheetId: params.spreadsheetId,
requestBody: {
requests: [
{
repeatCell: {
range: gridRange,
cell: {
userEnteredFormat: cellFormat,
},
fields: fields.join(','),
},
},
],
},
});
logger.audit('sheets_format_cells', 'formatCells', {
args: {
spreadsheetId: params.spreadsheetId,
range: params.range,
formatFields: fields,
},
result: 'success',
duration_ms: Date.now() - startTime,
});
return {
success: true,
};
});
}
// Tool definitions for MCP
export const sheetsTools = [
{
name: 'sheets_create_spreadsheet',
description:
'Create a new Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
title: {
type: 'string',
description: 'Title for the new spreadsheet',
},
parentFolderId: {
type: 'string',
description: 'ID of the Drive folder to create in (optional)',
},
},
required: ['title'],
},
},
{
name: 'sheets_get_values',
description: 'Read values from a range in a Google Spreadsheet.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
range: {
type: 'string',
description: 'A1 notation range (e.g., "Sheet1!A1:B10")',
},
},
required: ['spreadsheetId', 'range'],
},
},
{
name: 'sheets_update_values',
description:
'Update values in a range of a Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
range: {
type: 'string',
description: 'A1 notation range (e.g., "Sheet1!A1:B10")',
},
values: {
type: 'array',
items: {
type: 'array',
items: {},
},
description: '2D array of values to write',
},
valueInputOption: {
type: 'string',
enum: ['RAW', 'USER_ENTERED'],
description:
'How to interpret input values (RAW: literal values, USER_ENTERED: parse as if typed)',
default: 'USER_ENTERED',
},
},
required: ['spreadsheetId', 'range', 'values'],
},
},
{
name: 'sheets_append_values',
description:
'Append values to the end of a range in a Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
range: {
type: 'string',
description:
'A1 notation range to find the table (e.g., "Sheet1!A:B")',
},
values: {
type: 'array',
items: {
type: 'array',
items: {},
},
description: '2D array of values to append',
},
valueInputOption: {
type: 'string',
enum: ['RAW', 'USER_ENTERED'],
description:
'How to interpret input values (RAW: literal values, USER_ENTERED: parse as if typed)',
default: 'USER_ENTERED',
},
},
required: ['spreadsheetId', 'range', 'values'],
},
},
{
name: 'sheets_batch_update',
description:
'Apply multiple updates to a spreadsheet in one request. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
requests: {
type: 'array',
items: {
type: 'object',
},
description: 'Array of update requests (see Sheets API documentation)',
},
},
required: ['spreadsheetId', 'requests'],
},
},
{
name: 'sheets_add_sheet',
description:
'Add a new sheet to a Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
title: {
type: 'string',
description: 'Title for the new sheet',
},
index: {
type: 'number',
description: 'The index at which to insert the sheet (optional, 0-based)',
},
},
required: ['spreadsheetId', 'title'],
},
},
{
name: 'sheets_delete_sheet',
description:
'Delete a sheet from a Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
sheetId: {
type: 'number',
description: 'The ID of the sheet to delete (not the sheet name)',
},
},
required: ['spreadsheetId', 'sheetId'],
},
},
{
name: 'sheets_format_cells',
description:
'Apply formatting to cells in a Google Spreadsheet. Requires SHEETS_WRITE_ENABLED=true.',
inputSchema: {
type: 'object',
properties: {
spreadsheetId: {
type: 'string',
description: 'ID of the spreadsheet',
},
range: {
type: 'string',
description: 'A1 notation range (e.g., "Sheet1!A1:B10")',
},
format: {
type: 'object',
description: 'Formatting options to apply',
properties: {
backgroundColor: {
type: 'object',
description: 'Background color (RGB values 0-1)',
properties: {
red: { type: 'number' },
green: { type: 'number' },
blue: { type: 'number' },
},
},
textFormat: {
type: 'object',
description: 'Text formatting options',
properties: {
bold: { type: 'boolean' },
italic: { type: 'boolean' },
fontSize: { type: 'number' },
foregroundColor: {
type: 'object',
properties: {
red: { type: 'number' },
green: { type: 'number' },
blue: { type: 'number' },
},
},
},
},
horizontalAlignment: {
type: 'string',
enum: ['LEFT', 'CENTER', 'RIGHT'],
description: 'Horizontal text alignment',
},
verticalAlignment: {
type: 'string',
enum: ['TOP', 'MIDDLE', 'BOTTOM'],
description: 'Vertical text alignment',
},
},
},
},
required: ['spreadsheetId', 'range', 'format'],
},
},
];
// Tool handlers
export const sheetsHandlers: Record<
string,
(input: unknown) => Promise<unknown>
> = {
sheets_create_spreadsheet: sheetsCreateSpreadsheet,
sheets_get_values: sheetsGetValues,
sheets_update_values: sheetsUpdateValues,
sheets_append_values: sheetsAppendValues,
sheets_batch_update: sheetsBatchUpdate,
sheets_add_sheet: sheetsAddSheet,
sheets_delete_sheet: sheetsDeleteSheet,
sheets_format_cells: sheetsFormatCells,
};
