// src/sheets-client.ts
import { GoogleSpreadsheet, GoogleSpreadsheetWorksheet } from 'google-spreadsheet';
import { JWT } from 'google-auth-library';
import { google, sheets_v4 } from 'googleapis';
export interface SheetsCredentials {
client_email: string;
private_key: string;
}
export interface ConditionalFormatRule {
index: number;
formula?: string;
ranges: string[];
backgroundColor?: { red: number; green: number; blue: number };
textColor?: { red: number; green: number; blue: number };
bold?: boolean;
}
export class SheetsClient {
private jwt: JWT | null = null;
private sheetsApi: sheets_v4.Sheets | null = null;
private credentials: SheetsCredentials | null = null;
private docCache: Map<string, GoogleSpreadsheet> = new Map();
async initialize(credentials: SheetsCredentials): Promise<void> {
this.credentials = credentials;
this.jwt = new JWT({
email: credentials.client_email,
key: credentials.private_key.replace(/\\n/g, '\n'),
scopes: [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive.file',
],
});
// Inicializar API directa para operaciones avanzadas
this.sheetsApi = google.sheets({ version: 'v4', auth: this.jwt });
}
// Verifica si el cliente esta inicializado (MCP 2025-11-25)
isInitialized(): boolean {
return this.jwt !== null && this.sheetsApi !== null;
}
async getDocument(spreadsheetId: string): Promise<GoogleSpreadsheet> {
if (this.docCache.has(spreadsheetId)) {
return this.docCache.get(spreadsheetId)!;
}
if (!this.jwt) throw new Error('Cliente no inicializado');
const doc = new GoogleSpreadsheet(spreadsheetId, this.jwt);
await doc.loadInfo();
this.docCache.set(spreadsheetId, doc);
return doc;
}
async getSheet(spreadsheetId: string, sheetName: string): Promise<GoogleSpreadsheetWorksheet> {
const doc = await this.getDocument(spreadsheetId);
const sheet = doc.sheetsByTitle[sheetName];
if (!sheet) {
throw new Error(`Hoja "${sheetName}" no encontrada`);
}
return sheet;
}
// ═══════════════════════════════════════════════════════════════
// DATOS
// ═══════════════════════════════════════════════════════════════
async readRange(spreadsheetId: string, sheetName: string, range: string): Promise<any[][]> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.loadCells(range);
// Parsear rango (ej: "A1:C10")
const match = range.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/);
if (!match) throw new Error('Rango inválido');
const startCol = this.colLetterToIndex(match[1]);
const startRow = parseInt(match[2]) - 1;
const endCol = this.colLetterToIndex(match[3]);
const endRow = parseInt(match[4]) - 1;
const result: any[][] = [];
for (let row = startRow; row <= endRow; row++) {
const rowData: any[] = [];
for (let col = startCol; col <= endCol; col++) {
const cell = sheet.getCell(row, col);
rowData.push(cell.value);
}
result.push(rowData);
}
return result;
}
async writeRange(spreadsheetId: string, sheetName: string, range: string, values: any[][]): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.loadCells(range);
const match = range.match(/([A-Z]+)(\d+)/);
if (!match) throw new Error('Rango inválido');
const startCol = this.colLetterToIndex(match[1]);
const startRow = parseInt(match[2]) - 1;
for (let i = 0; i < values.length; i++) {
for (let j = 0; j < values[i].length; j++) {
const cell = sheet.getCell(startRow + i, startCol + j);
cell.value = values[i][j];
}
}
await sheet.saveUpdatedCells();
}
async appendRows(spreadsheetId: string, sheetName: string, rows: Record<string, any>[]): Promise<number> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.addRows(rows);
return rows.length;
}
async deleteRows(spreadsheetId: string, sheetName: string, startIndex: number, count: number): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.loadCells();
// Eliminar de abajo hacia arriba
for (let i = startIndex + count - 1; i >= startIndex; i--) {
const rows = await sheet.getRows();
if (rows[i - 1]) { // -1 porque getRows no incluye header
await rows[i - 1].delete();
}
}
}
async getRows(spreadsheetId: string, sheetName: string, limit?: number): Promise<Record<string, any>[]> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
const rows = await sheet.getRows({ limit });
return rows.map(row => row.toObject());
}
// ═══════════════════════════════════════════════════════════════
// FORMATO
// ═══════════════════════════════════════════════════════════════
async formatRange(
spreadsheetId: string,
sheetName: string,
range: string,
format: {
backgroundColor?: string;
textColor?: string;
bold?: boolean;
italic?: boolean;
fontSize?: number;
horizontalAlignment?: 'LEFT' | 'CENTER' | 'RIGHT';
}
): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.loadCells(range);
const match = range.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/);
if (!match) throw new Error('Rango inválido');
const startCol = this.colLetterToIndex(match[1]);
const startRow = parseInt(match[2]) - 1;
const endCol = this.colLetterToIndex(match[3]);
const endRow = parseInt(match[4]) - 1;
for (let row = startRow; row <= endRow; row++) {
for (let col = startCol; col <= endCol; col++) {
const cell = sheet.getCell(row, col);
if (format.backgroundColor) {
cell.backgroundColor = this.hexToRgb(format.backgroundColor);
}
if (format.textColor || format.bold !== undefined || format.italic !== undefined || format.fontSize) {
cell.textFormat = {
...cell.textFormat,
foregroundColor: format.textColor ? this.hexToRgb(format.textColor) : undefined,
bold: format.bold,
italic: format.italic,
fontSize: format.fontSize,
};
}
if (format.horizontalAlignment) {
cell.horizontalAlignment = format.horizontalAlignment;
}
}
}
await sheet.saveUpdatedCells();
}
async clearFormat(spreadsheetId: string, sheetName: string, range: string): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.loadCells(range);
const match = range.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/);
if (!match) throw new Error('Rango inválido');
const startCol = this.colLetterToIndex(match[1]);
const startRow = parseInt(match[2]) - 1;
const endCol = this.colLetterToIndex(match[3]);
const endRow = parseInt(match[4]) - 1;
for (let row = startRow; row <= endRow; row++) {
for (let col = startCol; col <= endCol; col++) {
const cell = sheet.getCell(row, col);
cell.backgroundColor = { red: 1, green: 1, blue: 1, alpha: 1 };
cell.textFormat = {};
}
}
await sheet.saveUpdatedCells();
}
// ═══════════════════════════════════════════════════════════════
// FORMATO CONDICIONAL
// ═══════════════════════════════════════════════════════════════
async getConditionalRules(spreadsheetId: string, sheetName: string): Promise<ConditionalFormatRule[]> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const sheetId = sheet.sheetId;
const response = await this.sheetsApi.spreadsheets.get({
spreadsheetId,
fields: 'sheets(properties,conditionalFormats)',
});
const sheetData = response.data.sheets?.find(s => s.properties?.sheetId === sheetId);
if (!sheetData?.conditionalFormats) return [];
return sheetData.conditionalFormats.map((rule, index) => ({
index,
formula: rule.booleanRule?.condition?.values?.[0]?.userEnteredValue || undefined,
ranges: rule.ranges?.map(r => this.rangeToA1(r, sheetName)) || [],
backgroundColor: rule.booleanRule?.format?.backgroundColor as any,
textColor: rule.booleanRule?.format?.textFormat?.foregroundColor as any,
bold: rule.booleanRule?.format?.textFormat?.bold || undefined,
}));
}
async addConditionalRule(
spreadsheetId: string,
sheetName: string,
formula: string,
format: {
backgroundColor?: string;
textColor?: string;
bold?: boolean;
},
priority: number = 0
): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const sheetId = sheet.sheetId;
const request: sheets_v4.Schema$Request = {
addConditionalFormatRule: {
rule: {
ranges: [{
sheetId,
startRowIndex: 1,
endRowIndex: sheet.rowCount,
}],
booleanRule: {
condition: {
type: 'CUSTOM_FORMULA',
values: [{ userEnteredValue: formula }],
},
format: {
backgroundColor: format.backgroundColor ? this.hexToRgb(format.backgroundColor) : undefined,
textFormat: {
foregroundColor: format.textColor ? this.hexToRgb(format.textColor) : undefined,
bold: format.bold,
},
},
},
},
index: priority,
},
};
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: { requests: [request] },
});
}
async deleteConditionalRule(spreadsheetId: string, sheetName: string, index: number): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const sheetId = sheet.sheetId;
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
deleteConditionalFormatRule: {
sheetId,
index,
},
}],
},
});
}
async clearConditionalRules(spreadsheetId: string, sheetName: string): Promise<number> {
const rules = await this.getConditionalRules(spreadsheetId, sheetName);
// Eliminar de mayor a menor índice
for (let i = rules.length - 1; i >= 0; i--) {
await this.deleteConditionalRule(spreadsheetId, sheetName, i);
}
return rules.length;
}
// ═══════════════════════════════════════════════════════════════
// HOJAS
// ═══════════════════════════════════════════════════════════════
async listSheets(spreadsheetId: string): Promise<{ name: string; index: number; rowCount: number; colCount: number }[]> {
const doc = await this.getDocument(spreadsheetId);
return doc.sheetsByIndex.map((sheet, index) => ({
name: sheet.title,
index,
rowCount: sheet.rowCount,
colCount: sheet.columnCount,
}));
}
async createSheet(spreadsheetId: string, title: string, headers?: string[]): Promise<void> {
const doc = await this.getDocument(spreadsheetId);
await doc.addSheet({
title,
headerValues: headers,
});
}
async deleteSheet(spreadsheetId: string, sheetName: string): Promise<void> {
const doc = await this.getDocument(spreadsheetId);
const sheet = doc.sheetsByTitle[sheetName];
if (!sheet) throw new Error(`Hoja "${sheetName}" no encontrada`);
await sheet.delete();
}
async renameSheet(spreadsheetId: string, oldName: string, newName: string): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, oldName);
await sheet.updateProperties({ title: newName });
}
async duplicateSheet(spreadsheetId: string, sheetName: string, newName: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
await this.sheetsApi.spreadsheets.sheets.copyTo({
spreadsheetId,
sheetId: sheet.sheetId,
requestBody: { destinationSpreadsheetId: spreadsheetId },
});
// Renombrar la copia
const doc = await this.getDocument(spreadsheetId);
await doc.loadInfo(); // Refrescar
const copySheet = doc.sheetsByTitle[`Copia de ${sheetName}`] || doc.sheetsByTitle[`Copy of ${sheetName}`];
if (copySheet) {
await copySheet.updateProperties({ title: newName });
}
}
async hideSheet(spreadsheetId: string, sheetName: string, hidden: boolean = true): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.updateProperties({ hidden });
}
// ═══════════════════════════════════════════════════════════════
// CELDAS
// ═══════════════════════════════════════════════════════════════
async mergeCells(spreadsheetId: string, sheetName: string, range: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
mergeCells: {
range: gridRange,
mergeType: 'MERGE_ALL',
},
}],
},
});
}
async unmergeCells(spreadsheetId: string, sheetName: string, range: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
unmergeCells: { range: gridRange },
}],
},
});
}
async freezeRows(spreadsheetId: string, sheetName: string, count: number): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.updateProperties({
gridProperties: { frozenRowCount: count } as any,
});
}
async freezeColumns(spreadsheetId: string, sheetName: string, count: number): Promise<void> {
const sheet = await this.getSheet(spreadsheetId, sheetName);
await sheet.updateProperties({
gridProperties: { frozenColumnCount: count } as any,
});
}
async setColumnWidth(spreadsheetId: string, sheetName: string, column: string, width: number): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const colIndex = this.colLetterToIndex(column);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
updateDimensionProperties: {
range: {
sheetId: sheet.sheetId,
dimension: 'COLUMNS',
startIndex: colIndex,
endIndex: colIndex + 1,
},
properties: { pixelSize: width },
fields: 'pixelSize',
},
}],
},
});
}
async setRowHeight(spreadsheetId: string, sheetName: string, row: number, height: number): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
updateDimensionProperties: {
range: {
sheetId: sheet.sheetId,
dimension: 'ROWS',
startIndex: row - 1,
endIndex: row,
},
properties: { pixelSize: height },
fields: 'pixelSize',
},
}],
},
});
}
// ═══════════════════════════════════════════════════════════════
// VALIDACIÓN
// ═══════════════════════════════════════════════════════════════
async addDropdown(
spreadsheetId: string,
sheetName: string,
range: string,
options: string[]
): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
setDataValidation: {
range: gridRange,
rule: {
condition: {
type: 'ONE_OF_LIST',
values: options.map(opt => ({ userEnteredValue: opt })),
},
showCustomUi: true,
strict: true,
},
},
}],
},
});
}
async removeValidation(spreadsheetId: string, sheetName: string, range: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
setDataValidation: {
range: gridRange,
rule: null as any,
},
}],
},
});
}
// ═══════════════════════════════════════════════════════════════
// FILTROS
// ═══════════════════════════════════════════════════════════════
async setFilter(spreadsheetId: string, sheetName: string, range: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
setBasicFilter: {
filter: { range: gridRange },
},
}],
},
});
}
async clearFilter(spreadsheetId: string, sheetName: string): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
clearBasicFilter: { sheetId: sheet.sheetId },
}],
},
});
}
// ═══════════════════════════════════════════════════════════════
// PROTECCIÓN
// ═══════════════════════════════════════════════════════════════
async protectRange(
spreadsheetId: string,
sheetName: string,
range: string,
description: string
): Promise<void> {
if (!this.sheetsApi) throw new Error('API no inicializada');
const sheet = await this.getSheet(spreadsheetId, sheetName);
const gridRange = this.a1ToGridRange(range, sheet.sheetId);
await this.sheetsApi.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
addProtectedRange: {
protectedRange: {
range: gridRange,
description,
warningOnly: false,
},
},
}],
},
});
}
// ═══════════════════════════════════════════════════════════════
// INFO
// ═══════════════════════════════════════════════════════════════
async getSpreadsheetInfo(spreadsheetId: string): Promise<{
title: string;
locale: string;
timeZone: string;
sheets: { name: string; rowCount: number; colCount: number }[];
}> {
const doc = await this.getDocument(spreadsheetId);
return {
title: doc.title,
locale: doc.locale,
timeZone: doc.timeZone,
sheets: doc.sheetsByIndex.map(s => ({
name: s.title,
rowCount: s.rowCount,
colCount: s.columnCount,
})),
};
}
// ═══════════════════════════════════════════════════════════════
// UTILIDADES
// ═══════════════════════════════════════════════════════════════
private colLetterToIndex(letter: string): number {
let result = 0;
for (let i = 0; i < letter.length; i++) {
result = result * 26 + (letter.charCodeAt(i) - 64);
}
return result - 1;
}
private indexToColLetter(index: number): string {
let result = '';
index++;
while (index > 0) {
const remainder = (index - 1) % 26;
result = String.fromCharCode(65 + remainder) + result;
index = Math.floor((index - 1) / 26);
}
return result;
}
private hexToRgb(hex: string): { red: number; green: number; blue: number; alpha: number } {
const result = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex);
if (!result) {
return { red: 1, green: 1, blue: 1, alpha: 1 };
}
return {
red: parseInt(result[1], 16) / 255,
green: parseInt(result[2], 16) / 255,
blue: parseInt(result[3], 16) / 255,
alpha: 1,
};
}
private a1ToGridRange(range: string, sheetId: number): sheets_v4.Schema$GridRange {
const match = range.match(/([A-Z]+)(\d+):([A-Z]+)(\d+)/);
if (!match) throw new Error('Rango inválido');
return {
sheetId,
startColumnIndex: this.colLetterToIndex(match[1]),
startRowIndex: parseInt(match[2]) - 1,
endColumnIndex: this.colLetterToIndex(match[3]) + 1,
endRowIndex: parseInt(match[4]),
};
}
private rangeToA1(range: sheets_v4.Schema$GridRange, sheetName: string): string {
const startCol = this.indexToColLetter(range.startColumnIndex || 0);
const endCol = this.indexToColLetter((range.endColumnIndex || 1) - 1);
const startRow = (range.startRowIndex || 0) + 1;
const endRow = range.endRowIndex || 1;
return `${sheetName}!${startCol}${startRow}:${endCol}${endRow}`;
}
}
export const sheetsClient = new SheetsClient();
