Spaces:
Sleeping
Sleeping
File size: 5,723 Bytes
7dc28be | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | import type { FastMCP } from 'fastmcp';
import { UserError } from 'fastmcp';
import { z } from 'zod';
import { getSheetsClient } from '../../clients.js';
import { rowColToA1 } from '../../googleSheetsApiHelpers.js';
/**
* Converts a Google Sheets RGBA color object (0-1 range) to a hex string.
* Returns null if the color is undefined or has no meaningful channels.
*/
function rgbaToHex(
color: { red?: number | null; green?: number | null; blue?: number | null } | null | undefined
): string | null {
if (!color) return null;
const r = Math.round((color.red ?? 0) * 255);
const g = Math.round((color.green ?? 0) * 255);
const b = Math.round((color.blue ?? 0) * 255);
return `#${r.toString(16).padStart(2, '0').toUpperCase()}${g.toString(16).padStart(2, '0').toUpperCase()}${b.toString(16).padStart(2, '0').toUpperCase()}`;
}
/**
* Extracts a simplified formatting summary from a Google Sheets CellFormat object.
* Only includes properties that are explicitly set (non-default).
*/
function simplifyFormat(fmt: any): Record<string, any> | null {
if (!fmt) return null;
const result: Record<string, any> = {};
// Text formatting
if (fmt.textFormat) {
const tf: Record<string, any> = {};
if (fmt.textFormat.bold) tf.bold = true;
if (fmt.textFormat.italic) tf.italic = true;
if (fmt.textFormat.strikethrough) tf.strikethrough = true;
if (fmt.textFormat.underline) tf.underline = true;
if (fmt.textFormat.fontSize != null) tf.fontSize = fmt.textFormat.fontSize;
if (fmt.textFormat.fontFamily) tf.fontFamily = fmt.textFormat.fontFamily;
if (fmt.textFormat.foregroundColorStyle?.rgbColor) {
tf.foregroundColor = rgbaToHex(fmt.textFormat.foregroundColorStyle.rgbColor);
} else if (fmt.textFormat.foregroundColor) {
tf.foregroundColor = rgbaToHex(fmt.textFormat.foregroundColor);
}
if (Object.keys(tf).length > 0) result.textFormat = tf;
}
// Background color
if (fmt.backgroundColorStyle?.rgbColor) {
result.backgroundColor = rgbaToHex(fmt.backgroundColorStyle.rgbColor);
} else if (fmt.backgroundColor) {
result.backgroundColor = rgbaToHex(fmt.backgroundColor);
}
// Alignment
if (fmt.horizontalAlignment) result.horizontalAlignment = fmt.horizontalAlignment;
if (fmt.verticalAlignment) result.verticalAlignment = fmt.verticalAlignment;
// Number format
if (fmt.numberFormat) {
result.numberFormat = {
type: fmt.numberFormat.type,
pattern: fmt.numberFormat.pattern,
};
}
// Borders
if (fmt.borders) {
const borders: Record<string, any> = {};
for (const side of ['top', 'bottom', 'left', 'right'] as const) {
if (fmt.borders[side]) {
borders[side] = {
style: fmt.borders[side].style,
...(fmt.borders[side].colorStyle?.rgbColor
? { color: rgbaToHex(fmt.borders[side].colorStyle.rgbColor) }
: fmt.borders[side].color
? { color: rgbaToHex(fmt.borders[side].color) }
: {}),
};
}
}
if (Object.keys(borders).length > 0) result.borders = borders;
}
// Wrap strategy
if (fmt.wrapStrategy) result.wrapStrategy = fmt.wrapStrategy;
return Object.keys(result).length > 0 ? result : null;
}
export function register(server: FastMCP) {
server.addTool({
name: 'readCellFormat',
description:
'Reads the formatting/style of cells in a given range. Returns formatting details like bold, italic, fontSize, fontFamily, colors, alignment, borders, and number format per cell.',
parameters: z.strictObject({
spreadsheetId: z
.string()
.describe(
'The spreadsheet ID — the long string between /d/ and /edit in a Google Sheets URL.'
),
range: z
.string()
.describe('A1 notation range to read formatting from (e.g., "Sheet1!A1:D5" or "A1:B2").'),
}),
execute: async (args, { log }) => {
const sheets = await getSheetsClient();
log.info(
`Reading cell format for range "${args.range}" in spreadsheet ${args.spreadsheetId}`
);
try {
const response = await sheets.spreadsheets.get({
spreadsheetId: args.spreadsheetId,
ranges: [args.range],
includeGridData: true,
fields:
'sheets.data.rowData.values.userEnteredFormat,sheets.data.startRow,sheets.data.startColumn',
});
const sheetData = response.data.sheets?.[0]?.data?.[0];
if (!sheetData?.rowData) {
return JSON.stringify({ range: args.range, cells: [] }, null, 2);
}
const startRow = sheetData.startRow ?? 0;
const startCol = sheetData.startColumn ?? 0;
const cells: Array<{ cell: string; format: Record<string, any> }> = [];
for (let rowIdx = 0; rowIdx < sheetData.rowData.length; rowIdx++) {
const row = sheetData.rowData[rowIdx];
if (!row.values) continue;
for (let colIdx = 0; colIdx < row.values.length; colIdx++) {
const cellData = row.values[colIdx];
const fmt = simplifyFormat(cellData?.userEnteredFormat);
if (fmt) {
const cellRef = rowColToA1(startRow + rowIdx, startCol + colIdx);
cells.push({ cell: cellRef, format: fmt });
}
}
}
return JSON.stringify({ range: args.range, cells }, null, 2);
} catch (error: any) {
log.error(
`Error reading cell format for spreadsheet ${args.spreadsheetId}: ${error.message || error}`
);
if (error instanceof UserError) throw error;
throw new UserError(`Failed to read cell format: ${error.message || 'Unknown error'}`);
}
},
});
}
|