import { google, sheets_v4 } from 'googleapis';
import { getGoogleAuth } from './auth.js';
export interface SheetMetadata {
spreadsheetId: string;
title: string;
sheets: {
sheetId: number;
title: string;
rowCount: number;
columnCount: number;
}[];
}
export interface SheetStructure {
sheetName: string;
headers: string[];
dataTypes: { [column: string]: string };
sampleData: string[][];
totalRows: number;
}
export interface SearchResult {
sheetName: string;
row: number;
column: string;
value: string;
}
export interface RgbColor {
red: number; // 0-1
green: number; // 0-1
blue: number; // 0-1
}
export interface HeaderStyle {
backgroundColor?: string; // Hex color like "#4285f4"
textColor?: string; // Hex color like "#ffffff"
bold?: boolean;
fontSize?: number;
freeze?: boolean; // Freeze header row
}
export interface ColumnConfig {
column: string; // Column letter (A, B, etc.)
width?: number; // Width in pixels
validation?: DataValidation;
}
export interface DataValidation {
type: 'dropdown' | 'number' | 'date' | 'checkbox' | 'custom';
values?: string[]; // For dropdown
min?: number; // For number
max?: number; // For number
formula?: string; // For custom
allowInvalid?: boolean; // Show warning instead of reject
helpText?: string; // Input message
}
export interface FormatSheetOptions {
headerStyle?: HeaderStyle;
columns?: ColumnConfig[];
alternateRowColors?: {
firstColor: string; // Hex color
secondColor: string; // Hex color
};
}
export interface CellError {
sheetName: string;
cell: string; // e.g., "A1", "B5"
row: number;
column: string;
errorType: string; // REF, ERROR, VALUE, NAME, NUM, N_A, DIVIDE_BY_ZERO, NULL_VALUE, LOADING
errorDisplay: string; // #REF!, #ERROR!, etc.
errorMessage: string; // Detailed error message
formula?: string; // The formula that caused the error
}
/**
* Google Sheets API service wrapper
*/
export class SheetsService {
private sheets: sheets_v4.Sheets | null = null;
private drive: ReturnType<typeof google.drive> | null = null;
/**
* Get authenticated Sheets API client
*/
private async getSheetsClient(): Promise<sheets_v4.Sheets> {
if (this.sheets) {
return this.sheets;
}
const auth = getGoogleAuth();
const client = await auth.getAuthenticatedClient();
this.sheets = google.sheets({ version: 'v4', auth: client });
return this.sheets;
}
/**
* Get authenticated Drive API client (for creating spreadsheets)
*/
private async getDriveClient() {
if (this.drive) {
return this.drive;
}
const auth = getGoogleAuth();
const client = await auth.getAuthenticatedClient();
this.drive = google.drive({ version: 'v3', auth: client });
return this.drive;
}
/**
* Get spreadsheet metadata
*/
async getMetadata(spreadsheetId: string, sheetName?: string): Promise<SheetMetadata> {
const sheets = await this.getSheetsClient();
const response = await sheets.spreadsheets.get({
spreadsheetId,
includeGridData: false,
});
const spreadsheet = response.data;
let sheetList = spreadsheet.sheets || [];
// Filter by sheet name if specified
if (sheetName) {
sheetList = sheetList.filter(
(s) => s.properties?.title?.toLowerCase() === sheetName.toLowerCase()
);
}
return {
spreadsheetId,
title: spreadsheet.properties?.title || '',
sheets: sheetList.map((sheet) => ({
sheetId: sheet.properties?.sheetId || 0,
title: sheet.properties?.title || '',
rowCount: sheet.properties?.gridProperties?.rowCount || 0,
columnCount: sheet.properties?.gridProperties?.columnCount || 0,
})),
};
}
/**
* Get data from a specific range
*/
async getData(spreadsheetId: string, range: string): Promise<string[][]> {
const sheets = await this.getSheetsClient();
const response = await sheets.spreadsheets.values.get({
spreadsheetId,
range,
valueRenderOption: 'FORMATTED_VALUE',
});
return (response.data.values as string[][]) || [];
}
/**
* Get all values from a specific column
*/
async getColumnValues(
spreadsheetId: string,
sheetName: string,
column: string
): Promise<string[]> {
const range = `${sheetName}!${column}:${column}`;
const data = await this.getData(spreadsheetId, range);
return data.map((row) => row[0] || '');
}
/**
* Analyze sheet structure
*/
async getSheetStructure(
spreadsheetId: string,
sheetName?: string
): Promise<SheetStructure[]> {
const metadata = await this.getMetadata(spreadsheetId, sheetName);
const results: SheetStructure[] = [];
for (const sheet of metadata.sheets) {
// Get header row and sample data (first 10 rows)
const range = `${sheet.title}!A1:Z11`;
const data = await this.getData(spreadsheetId, range);
if (data.length === 0) {
results.push({
sheetName: sheet.title,
headers: [],
dataTypes: {},
sampleData: [],
totalRows: 0,
});
continue;
}
const headers = data[0] || [];
const sampleData = data.slice(1, 6); // First 5 data rows
// Infer data types from sample data
const dataTypes: { [column: string]: string } = {};
headers.forEach((header, index) => {
if (!header) return;
const columnLetter = this.columnIndexToLetter(index);
const values = data.slice(1).map((row) => row[index]).filter((v) => v !== undefined && v !== '');
dataTypes[header] = this.inferDataType(values);
});
results.push({
sheetName: sheet.title,
headers,
dataTypes,
sampleData,
totalRows: sheet.rowCount - 1, // Exclude header row
});
}
return results;
}
/**
* Check for errors in a sheet
*/
async checkSheetErrors(
spreadsheetId: string,
sheetName?: string
): Promise<CellError[]> {
const sheets = await this.getSheetsClient();
const metadata = await this.getMetadata(spreadsheetId, sheetName);
const errors: CellError[] = [];
// Error type to display string mapping
const errorDisplayMap: { [key: string]: string } = {
'ERROR': '#ERROR!',
'NULL_VALUE': '#NULL!',
'DIVIDE_BY_ZERO': '#DIV/0!',
'VALUE': '#VALUE!',
'REF': '#REF!',
'NAME': '#NAME?',
'NUM': '#NUM!',
'N_A': '#N/A',
'LOADING': '#LOADING...',
};
for (const sheet of metadata.sheets) {
// Get sheet data with grid data to access error information
const response = await sheets.spreadsheets.get({
spreadsheetId,
ranges: [`${sheet.title}!A1:Z${Math.min(sheet.rowCount, 500)}`],
includeGridData: true,
});
const sheetData = response.data.sheets?.[0]?.data?.[0];
if (!sheetData?.rowData) continue;
sheetData.rowData.forEach((row, rowIndex) => {
if (!row.values) return;
row.values.forEach((cell, colIndex) => {
// Check for error in effectiveValue
if (cell.effectiveValue?.errorValue) {
const errorValue = cell.effectiveValue.errorValue;
const errorType = errorValue.type || 'ERROR';
errors.push({
sheetName: sheet.title,
cell: `${this.columnIndexToLetter(colIndex)}${rowIndex + 1}`,
row: rowIndex + 1,
column: this.columnIndexToLetter(colIndex),
errorType,
errorDisplay: errorDisplayMap[errorType] || `#${errorType}`,
errorMessage: errorValue.message || 'Unknown error',
formula: cell.userEnteredValue?.formulaValue || undefined,
});
}
});
});
}
return errors;
}
/**
* Search for a string in the sheet
*/
async searchInSheet(
spreadsheetId: string,
query: string,
sheetName?: string
): Promise<SearchResult[]> {
const metadata = await this.getMetadata(spreadsheetId, sheetName);
const results: SearchResult[] = [];
const queryLower = query.toLowerCase();
for (const sheet of metadata.sheets) {
// Get all data from the sheet
const range = `${sheet.title}!A1:Z${Math.min(sheet.rowCount, 1000)}`;
const data = await this.getData(spreadsheetId, range);
data.forEach((row, rowIndex) => {
row.forEach((cell, colIndex) => {
if (cell && cell.toLowerCase().includes(queryLower)) {
results.push({
sheetName: sheet.title,
row: rowIndex + 1,
column: this.columnIndexToLetter(colIndex),
value: cell,
});
}
});
});
}
return results;
}
/**
* Create a new spreadsheet
*/
async createSpreadsheet(
title: string,
sheetNames?: string[]
): Promise<{ spreadsheetId: string; url: string }> {
const sheets = await this.getSheetsClient();
const sheetProperties = sheetNames?.map((name, index) => ({
properties: {
sheetId: index,
title: name,
},
})) || [{ properties: { sheetId: 0, title: 'Sheet1' } }];
const response = await sheets.spreadsheets.create({
requestBody: {
properties: {
title,
},
sheets: sheetProperties,
},
});
const spreadsheetId = response.data.spreadsheetId!;
const url = response.data.spreadsheetUrl!;
return { spreadsheetId, url };
}
/**
* Add a new sheet to an existing spreadsheet
*/
async createSheet(
spreadsheetId: string,
sheetName: string
): Promise<{ sheetId: number }> {
const sheets = await this.getSheetsClient();
const response = await sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [
{
addSheet: {
properties: {
title: sheetName,
},
},
},
],
},
});
const sheetId = response.data.replies?.[0]?.addSheet?.properties?.sheetId || 0;
return { sheetId };
}
/**
* Set header row for a sheet
*/
async setHeaders(
spreadsheetId: string,
sheetName: string,
headers: string[],
dryRun: boolean = false
): Promise<{ success: boolean; message: string }> {
if (dryRun) {
return {
success: true,
message: `[DRY RUN] Would set headers in ${sheetName}:\n${headers.join(' | ')}`,
};
}
const sheets = await this.getSheetsClient();
const range = `${sheetName}!A1:${this.columnIndexToLetter(headers.length - 1)}1`;
await sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: 'RAW',
requestBody: {
values: [headers],
},
});
return {
success: true,
message: `Headers set successfully in ${sheetName}`,
};
}
/**
* Append rows to a sheet
*/
async appendRows(
spreadsheetId: string,
sheetName: string,
rows: string[][],
dryRun: boolean = false
): Promise<{ success: boolean; message: string; rowsAdded: number }> {
if (dryRun) {
return {
success: true,
message: `[DRY RUN] Would append ${rows.length} rows to ${sheetName}:\n${rows.slice(0, 3).map((r) => r.join(' | ')).join('\n')}${rows.length > 3 ? `\n... and ${rows.length - 3} more rows` : ''}`,
rowsAdded: 0,
};
}
const sheets = await this.getSheetsClient();
const range = `${sheetName}!A:A`;
const response = await sheets.spreadsheets.values.append({
spreadsheetId,
range,
valueInputOption: 'RAW',
insertDataOption: 'INSERT_ROWS',
requestBody: {
values: rows,
},
});
const updatedRows = response.data.updates?.updatedRows || rows.length;
return {
success: true,
message: `Successfully appended ${updatedRows} rows to ${sheetName}`,
rowsAdded: updatedRows,
};
}
/**
* Format a sheet with styling options
*/
async formatSheet(
spreadsheetId: string,
sheetName: string,
options: FormatSheetOptions,
dryRun: boolean = false
): Promise<{ success: boolean; message: string }> {
const metadata = await this.getMetadata(spreadsheetId, sheetName);
const sheet = metadata.sheets[0];
if (!sheet) {
throw new Error(`Sheet "${sheetName}" not found`);
}
const requests: sheets_v4.Schema$Request[] = [];
// Header style
if (options.headerStyle) {
const style = options.headerStyle;
// Format header row
requests.push({
repeatCell: {
range: {
sheetId: sheet.sheetId,
startRowIndex: 0,
endRowIndex: 1,
},
cell: {
userEnteredFormat: {
backgroundColor: style.backgroundColor
? this.hexToRgb(style.backgroundColor)
: undefined,
textFormat: {
foregroundColor: style.textColor
? this.hexToRgb(style.textColor)
: undefined,
bold: style.bold,
fontSize: style.fontSize,
},
horizontalAlignment: 'CENTER',
verticalAlignment: 'MIDDLE',
},
},
fields: 'userEnteredFormat(backgroundColor,textFormat,horizontalAlignment,verticalAlignment)',
},
});
// Freeze header row
if (style.freeze) {
requests.push({
updateSheetProperties: {
properties: {
sheetId: sheet.sheetId,
gridProperties: {
frozenRowCount: 1,
},
},
fields: 'gridProperties.frozenRowCount',
},
});
}
}
// Column widths and validation
if (options.columns) {
for (const col of options.columns) {
const colIndex = this.columnLetterToIndex(col.column);
// Set column width
if (col.width) {
requests.push({
updateDimensionProperties: {
range: {
sheetId: sheet.sheetId,
dimension: 'COLUMNS',
startIndex: colIndex,
endIndex: colIndex + 1,
},
properties: {
pixelSize: col.width,
},
fields: 'pixelSize',
},
});
}
// Set data validation
if (col.validation) {
const validationRequest = this.createValidationRequest(
sheet.sheetId,
colIndex,
col.validation
);
if (validationRequest) {
requests.push(validationRequest);
}
}
}
}
// Alternate row colors
if (options.alternateRowColors) {
requests.push({
addBanding: {
bandedRange: {
range: {
sheetId: sheet.sheetId,
startRowIndex: 1, // Skip header
},
rowProperties: {
headerColor: this.hexToRgb(options.alternateRowColors.firstColor),
firstBandColor: this.hexToRgb(options.alternateRowColors.firstColor),
secondBandColor: this.hexToRgb(options.alternateRowColors.secondColor),
},
},
},
});
}
if (dryRun) {
return {
success: true,
message: `[DRY RUN] Would apply ${requests.length} formatting operations to "${sheetName}":\n` +
(options.headerStyle ? `- Header style: ${JSON.stringify(options.headerStyle)}\n` : '') +
(options.columns ? `- Column configs: ${options.columns.length} columns\n` : '') +
(options.alternateRowColors ? `- Alternate row colors enabled\n` : ''),
};
}
if (requests.length === 0) {
return {
success: true,
message: 'No formatting options specified',
};
}
const sheets = await this.getSheetsClient();
await sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: { requests },
});
return {
success: true,
message: `Successfully applied ${requests.length} formatting operations to "${sheetName}"`,
};
}
/**
* Set data validation for a range
*/
async setDataValidation(
spreadsheetId: string,
sheetName: string,
column: string,
validation: DataValidation,
startRow: number = 2, // Start from row 2 (after header)
dryRun: boolean = false
): Promise<{ success: boolean; message: string }> {
const metadata = await this.getMetadata(spreadsheetId, sheetName);
const sheet = metadata.sheets[0];
if (!sheet) {
throw new Error(`Sheet "${sheetName}" not found`);
}
const colIndex = this.columnLetterToIndex(column);
if (dryRun) {
return {
success: true,
message: `[DRY RUN] Would set ${validation.type} validation on column ${column}:\n` +
(validation.values ? ` Values: ${validation.values.join(', ')}\n` : '') +
(validation.min !== undefined ? ` Min: ${validation.min}\n` : '') +
(validation.max !== undefined ? ` Max: ${validation.max}\n` : '') +
(validation.helpText ? ` Help: ${validation.helpText}\n` : ''),
};
}
const request = this.createValidationRequest(
sheet.sheetId,
colIndex,
validation,
startRow - 1 // Convert to 0-indexed
);
if (!request) {
return {
success: false,
message: `Unsupported validation type: ${validation.type}`,
};
}
const sheets = await this.getSheetsClient();
await sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: { requests: [request] },
});
return {
success: true,
message: `Successfully set ${validation.type} validation on column ${column}`,
};
}
/**
* Create a validation request
*/
private createValidationRequest(
sheetId: number,
colIndex: number,
validation: DataValidation,
startRowIndex: number = 1
): sheets_v4.Schema$Request | null {
let condition: sheets_v4.Schema$BooleanCondition | undefined;
switch (validation.type) {
case 'dropdown':
condition = {
type: 'ONE_OF_LIST',
values: validation.values?.map((v) => ({ userEnteredValue: v })),
};
break;
case 'number':
if (validation.min !== undefined && validation.max !== undefined) {
condition = {
type: 'NUMBER_BETWEEN',
values: [
{ userEnteredValue: String(validation.min) },
{ userEnteredValue: String(validation.max) },
],
};
} else if (validation.min !== undefined) {
condition = {
type: 'NUMBER_GREATER_THAN_EQ',
values: [{ userEnteredValue: String(validation.min) }],
};
} else if (validation.max !== undefined) {
condition = {
type: 'NUMBER_LESS_THAN_EQ',
values: [{ userEnteredValue: String(validation.max) }],
};
}
break;
case 'date':
condition = {
type: 'DATE_IS_VALID',
};
break;
case 'checkbox':
condition = {
type: 'BOOLEAN',
};
break;
case 'custom':
if (validation.formula) {
condition = {
type: 'CUSTOM_FORMULA',
values: [{ userEnteredValue: validation.formula }],
};
}
break;
}
if (!condition) {
return null;
}
return {
setDataValidation: {
range: {
sheetId,
startColumnIndex: colIndex,
endColumnIndex: colIndex + 1,
startRowIndex,
},
rule: {
condition,
strict: !validation.allowInvalid,
showCustomUi: validation.type === 'dropdown',
inputMessage: validation.helpText,
},
},
};
}
/**
* Convert hex color to RGB (0-1 range)
*/
private hexToRgb(hex: string): RgbColor {
const result = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex);
if (!result) {
return { red: 0, green: 0, blue: 0 };
}
return {
red: parseInt(result[1], 16) / 255,
green: parseInt(result[2], 16) / 255,
blue: parseInt(result[3], 16) / 255,
};
}
/**
* Convert column letter to index (A -> 0, B -> 1, etc.)
*/
private columnLetterToIndex(column: string): number {
let index = 0;
for (let i = 0; i < column.length; i++) {
index = index * 26 + (column.charCodeAt(i) - 64);
}
return index - 1;
}
/**
* Convert column index to letter (0 -> A, 1 -> B, etc.)
*/
private columnIndexToLetter(index: number): string {
let letter = '';
while (index >= 0) {
letter = String.fromCharCode((index % 26) + 65) + letter;
index = Math.floor(index / 26) - 1;
}
return letter;
}
/**
* Infer data type from a list of values
*/
private inferDataType(values: string[]): string {
if (values.length === 0) return 'unknown';
const types = values.map((v) => {
if (v === '' || v === null || v === undefined) return 'empty';
if (/^\d+$/.test(v)) return 'integer';
if (/^\d+\.\d+$/.test(v)) return 'float';
if (/^\d{4}[-/]\d{2}[-/]\d{2}/.test(v)) return 'date';
if (/^(true|false)$/i.test(v)) return 'boolean';
return 'string';
});
// Find most common non-empty type
const typeCounts = types.reduce((acc, type) => {
if (type !== 'empty') {
acc[type] = (acc[type] || 0) + 1;
}
return acc;
}, {} as { [key: string]: number });
const sortedTypes = Object.entries(typeCounts).sort((a, b) => b[1] - a[1]);
return sortedTypes.length > 0 ? sortedTypes[0][0] : 'string';
}
}
// Singleton instance
let sheetsService: SheetsService | null = null;
export function getSheetsService(): SheetsService {
if (!sheetsService) {
sheetsService = new SheetsService();
}
return sheetsService;
}
