import { getSheetsService, CellError } from '../google/sheets.js';
/**
* Get spreadsheet metadata (sheet list, column names, row counts)
*/
export async function getSheetMetadata(args: {
spreadsheet_id: string;
sheet_name?: string;
}): Promise<object> {
const service = getSheetsService();
const metadata = await service.getMetadata(args.spreadsheet_id, args.sheet_name);
return {
spreadsheet_id: metadata.spreadsheetId,
title: metadata.title,
sheets: metadata.sheets.map((sheet) => ({
name: sheet.title,
sheet_id: sheet.sheetId,
row_count: sheet.rowCount,
column_count: sheet.columnCount,
})),
};
}
/**
* Get data from a specific range
*/
export async function getSheetData(args: {
spreadsheet_id: string;
range: string;
}): Promise<object> {
const service = getSheetsService();
const data = await service.getData(args.spreadsheet_id, args.range);
return {
range: args.range,
row_count: data.length,
data: data,
};
}
/**
* Get all values from a specific column
*/
export async function getColumnValues(args: {
spreadsheet_id: string;
sheet_name: string;
column: string;
}): Promise<object> {
const service = getSheetsService();
const values = await service.getColumnValues(
args.spreadsheet_id,
args.sheet_name,
args.column
);
// Get unique values and their counts
const valueCounts: { [key: string]: number } = {};
values.forEach((v) => {
if (v) {
valueCounts[v] = (valueCounts[v] || 0) + 1;
}
});
const uniqueValues = Object.entries(valueCounts)
.sort((a, b) => b[1] - a[1])
.map(([value, count]) => ({ value, count }));
return {
sheet_name: args.sheet_name,
column: args.column,
total_values: values.length,
non_empty_count: values.filter((v) => v).length,
unique_values: uniqueValues.slice(0, 50), // Limit to top 50 unique values
all_values: values.length <= 100 ? values : undefined, // Only include all values if <= 100
};
}
/**
* Analyze sheet structure (headers, data types, sample data)
*/
export async function getSheetStructure(args: {
spreadsheet_id: string;
sheet_name?: string;
}): Promise<object> {
const service = getSheetsService();
const structures = await service.getSheetStructure(args.spreadsheet_id, args.sheet_name);
return {
spreadsheet_id: args.spreadsheet_id,
sheets: structures.map((s) => ({
sheet_name: s.sheetName,
headers: s.headers,
columns: s.headers.map((header, index) => ({
index,
name: header,
inferred_type: s.dataTypes[header] || 'unknown',
})),
sample_data: s.sampleData,
total_rows: s.totalRows,
})),
};
}
/**
* Search for a string in the sheet
*/
export async function searchInSheet(args: {
spreadsheet_id: string;
query: string;
sheet_name?: string;
}): Promise<object> {
const service = getSheetsService();
const results = await service.searchInSheet(
args.spreadsheet_id,
args.query,
args.sheet_name
);
return {
query: args.query,
match_count: results.length,
matches: results.slice(0, 100).map((r) => ({
sheet: r.sheetName,
cell: `${r.column}${r.row}`,
value: r.value,
})),
truncated: results.length > 100,
};
}
/**
* Check for errors in a sheet
*/
export async function checkSheetErrors(args: {
spreadsheet_id: string;
sheet_name?: string;
}): Promise<object> {
const service = getSheetsService();
const errors = await service.checkSheetErrors(
args.spreadsheet_id,
args.sheet_name
);
// Group errors by type for summary
const errorsByType: { [type: string]: CellError[] } = {};
errors.forEach((error) => {
if (!errorsByType[error.errorType]) {
errorsByType[error.errorType] = [];
}
errorsByType[error.errorType].push(error);
});
const summary = Object.entries(errorsByType).map(([type, errs]) => ({
error_type: type,
display: errs[0]?.errorDisplay || `#${type}`,
count: errs.length,
}));
return {
total_errors: errors.length,
has_errors: errors.length > 0,
summary,
errors: errors.slice(0, 100).map((e) => ({
sheet: e.sheetName,
cell: e.cell,
error_type: e.errorType,
error_display: e.errorDisplay,
message: e.errorMessage,
formula: e.formula,
})),
truncated: errors.length > 100,
};
}
