/**
* Manual test script for Spreadsheet MCP Server
*
* Usage: npm run test:manual
*/
import { getSheetsService } from '../google/sheets.js';
// Test spreadsheet ID from the provided URL
const TEST_SPREADSHEET_ID = '1DGU4f5-qWKzV_gsQfKFeqcP2o2VJOIuz_aA852mzko0';
async function runTests() {
const service = getSheetsService();
console.log('='.repeat(60));
console.log('Spreadsheet MCP Server - Manual Test');
console.log('='.repeat(60));
console.log(`Test Spreadsheet ID: ${TEST_SPREADSHEET_ID}`);
console.log('');
// Test 1: Get Metadata
console.log('\n--- Test 1: get_sheet_metadata ---');
try {
const metadata = await service.getMetadata(TEST_SPREADSHEET_ID);
console.log('✅ Success!');
console.log(` Title: ${metadata.title}`);
console.log(` Sheets: ${metadata.sheets.length}`);
metadata.sheets.forEach((sheet, i) => {
console.log(` ${i + 1}. ${sheet.title} (${sheet.rowCount} rows, ${sheet.columnCount} cols)`);
});
} catch (error) {
console.log('❌ Error:', error);
}
// Test 2: Get Sheet Structure
console.log('\n--- Test 2: get_sheet_structure ---');
try {
const structures = await service.getSheetStructure(TEST_SPREADSHEET_ID);
console.log('✅ Success!');
structures.forEach((s) => {
console.log(` Sheet: ${s.sheetName}`);
console.log(` Headers: ${s.headers.join(', ')}`);
console.log(` Data Types:`);
Object.entries(s.dataTypes).forEach(([col, type]) => {
console.log(` - ${col}: ${type}`);
});
console.log(` Sample Data (first ${s.sampleData.length} rows):`);
s.sampleData.slice(0, 3).forEach((row, i) => {
console.log(` Row ${i + 1}: ${row.join(' | ')}`);
});
});
} catch (error) {
console.log('❌ Error:', error);
}
// Test 3: Get Sheet Data (first sheet, A1:E10)
console.log('\n--- Test 3: get_sheet_data ---');
try {
const metadata = await service.getMetadata(TEST_SPREADSHEET_ID);
const firstSheet = metadata.sheets[0]?.title || 'Sheet1';
const range = `${firstSheet}!A1:E10`;
const data = await service.getData(TEST_SPREADSHEET_ID, range);
console.log('✅ Success!');
console.log(` Range: ${range}`);
console.log(` Rows returned: ${data.length}`);
data.slice(0, 5).forEach((row, i) => {
console.log(` Row ${i + 1}: ${row.join(' | ')}`);
});
if (data.length > 5) {
console.log(` ... and ${data.length - 5} more rows`);
}
} catch (error) {
console.log('❌ Error:', error);
}
// Test 4: Get Column Values
console.log('\n--- Test 4: get_column_values ---');
try {
const metadata = await service.getMetadata(TEST_SPREADSHEET_ID);
const firstSheet = metadata.sheets[0]?.title || 'Sheet1';
const values = await service.getColumnValues(TEST_SPREADSHEET_ID, firstSheet, 'A');
console.log('✅ Success!');
console.log(` Sheet: ${firstSheet}, Column: A`);
console.log(` Total values: ${values.length}`);
console.log(` Non-empty: ${values.filter(v => v).length}`);
console.log(` Sample values: ${values.slice(0, 10).join(', ')}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test 5: Search in Sheet
console.log('\n--- Test 5: search_in_sheet ---');
try {
// Search for a common term - we'll use the first non-empty cell value
const metadata = await service.getMetadata(TEST_SPREADSHEET_ID);
const firstSheet = metadata.sheets[0]?.title || 'Sheet1';
const sampleData = await service.getData(TEST_SPREADSHEET_ID, `${firstSheet}!A1:A5`);
const searchTerm = sampleData.find(row => row[0])?.[0] || 'test';
const results = await service.searchInSheet(TEST_SPREADSHEET_ID, searchTerm);
console.log('✅ Success!');
console.log(` Search term: "${searchTerm}"`);
console.log(` Matches found: ${results.length}`);
results.slice(0, 5).forEach((r) => {
console.log(` ${r.sheetName}!${r.column}${r.row}: ${r.value}`);
});
if (results.length > 5) {
console.log(` ... and ${results.length - 5} more matches`);
}
} catch (error) {
console.log('❌ Error:', error);
}
// Test 6: Check Sheet Errors
console.log('\n--- Test 6: check_sheet_errors ---');
try {
const errors = await service.checkSheetErrors(TEST_SPREADSHEET_ID);
console.log('✅ Success!');
console.log(` Total errors found: ${errors.length}`);
if (errors.length > 0) {
console.log(' Errors:');
errors.slice(0, 10).forEach((e) => {
console.log(` ${e.sheetName}!${e.cell}: ${e.errorDisplay}`);
console.log(` Type: ${e.errorType}`);
console.log(` Message: ${e.errorMessage}`);
if (e.formula) {
console.log(` Formula: ${e.formula}`);
}
});
if (errors.length > 10) {
console.log(` ... and ${errors.length - 10} more errors`);
}
} else {
console.log(' No errors found in the spreadsheet.');
}
} catch (error) {
console.log('❌ Error:', error);
}
console.log('\n' + '='.repeat(60));
console.log('Read-only tests completed!');
console.log('='.repeat(60));
console.log('\nNote: Write tests (create_spreadsheet, create_sheet, set_headers, append_rows)');
console.log('are not included in this automated test to avoid modifying data.');
console.log('Use --write flag to run write tests with a new spreadsheet.');
}
async function runWriteTests() {
const service = getSheetsService();
console.log('\n' + '='.repeat(60));
console.log('Write Tests (creates a new spreadsheet)');
console.log('='.repeat(60));
let testSpreadsheetId: string | null = null;
// Test: Create Spreadsheet
console.log('\n--- Test: create_spreadsheet ---');
try {
const result = await service.createSpreadsheet(
`MCP Test Spreadsheet - ${new Date().toISOString()}`,
['TestSheet1', 'TestSheet2']
);
console.log('✅ Success!');
console.log(` Spreadsheet ID: ${result.spreadsheetId}`);
console.log(` URL: ${result.url}`);
testSpreadsheetId = result.spreadsheetId;
} catch (error) {
console.log('❌ Error:', error);
return;
}
// Test: Create Sheet
console.log('\n--- Test: create_sheet ---');
try {
const result = await service.createSheet(testSpreadsheetId, 'NewSheet');
console.log('✅ Success!');
console.log(` Sheet ID: ${result.sheetId}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Set Headers (dry run first)
console.log('\n--- Test: set_headers (dry_run) ---');
try {
const result = await service.setHeaders(
testSpreadsheetId,
'TestSheet1',
['ID', 'Name', 'Description', 'Created At'],
true
);
console.log('✅ Dry run success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Set Headers (actual)
console.log('\n--- Test: set_headers (actual) ---');
try {
const result = await service.setHeaders(
testSpreadsheetId,
'TestSheet1',
['ID', 'Name', 'Description', 'Created At'],
false
);
console.log('✅ Success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Append Rows (dry run first)
console.log('\n--- Test: append_rows (dry_run) ---');
try {
const rows = [
['1', 'Item 1', 'First test item', new Date().toISOString()],
['2', 'Item 2', 'Second test item', new Date().toISOString()],
['3', 'Item 3', 'Third test item', new Date().toISOString()],
];
const result = await service.appendRows(testSpreadsheetId, 'TestSheet1', rows, true);
console.log('✅ Dry run success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Append Rows (actual)
console.log('\n--- Test: append_rows (actual) ---');
try {
const rows = [
['1', 'Item 1', 'First test item', new Date().toISOString()],
['2', 'Item 2', 'Second test item', new Date().toISOString()],
['3', 'Item 3', 'Third test item', new Date().toISOString()],
];
const result = await service.appendRows(testSpreadsheetId, 'TestSheet1', rows, false);
console.log('✅ Success!');
console.log(` Message: ${result.message}`);
console.log(` Rows added: ${result.rowsAdded}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Format Sheet (dry run first)
console.log('\n--- Test: format_sheet (dry_run) ---');
try {
const result = await service.formatSheet(
testSpreadsheetId,
'TestSheet1',
{
headerStyle: {
backgroundColor: '#4285f4',
textColor: '#ffffff',
bold: true,
fontSize: 11,
freeze: true,
},
columns: [
{ column: 'A', width: 80 },
{ column: 'B', width: 150 },
{ column: 'C', width: 250 },
{ column: 'D', width: 180 },
],
alternateRowColors: {
firstColor: '#ffffff',
secondColor: '#f3f3f3',
},
},
true
);
console.log('✅ Dry run success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Format Sheet (actual)
console.log('\n--- Test: format_sheet (actual) ---');
try {
const result = await service.formatSheet(
testSpreadsheetId,
'TestSheet1',
{
headerStyle: {
backgroundColor: '#4285f4',
textColor: '#ffffff',
bold: true,
fontSize: 11,
freeze: true,
},
columns: [
{ column: 'A', width: 80 },
{ column: 'B', width: 150 },
{ column: 'C', width: 250 },
{ column: 'D', width: 180 },
],
alternateRowColors: {
firstColor: '#ffffff',
secondColor: '#f3f3f3',
},
},
false
);
console.log('✅ Success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Set Data Validation - Dropdown
console.log('\n--- Test: set_data_validation (dropdown) ---');
try {
const result = await service.setDataValidation(
testSpreadsheetId,
'TestSheet1',
'B',
{
type: 'dropdown',
values: ['Item 1', 'Item 2', 'Item 3', 'Item 4', 'Item 5'],
helpText: 'アイテムを選択してください',
},
2,
false
);
console.log('✅ Success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
// Test: Set Data Validation - Number Range
console.log('\n--- Test: set_data_validation (number range) ---');
try {
const result = await service.setDataValidation(
testSpreadsheetId,
'TestSheet1',
'A',
{
type: 'number',
min: 1,
max: 1000,
helpText: '1〜1000の数値を入力してください',
},
2,
false
);
console.log('✅ Success!');
console.log(` Message: ${result.message}`);
} catch (error) {
console.log('❌ Error:', error);
}
console.log('\n' + '='.repeat(60));
console.log('Write & Format tests completed!');
console.log(`Test spreadsheet created: https://docs.google.com/spreadsheets/d/${testSpreadsheetId}`);
console.log('='.repeat(60));
}
// Main
async function main() {
const args = process.argv.slice(2);
const runWrite = args.includes('--write');
try {
await runTests();
if (runWrite) {
await runWriteTests();
}
} catch (error) {
console.error('Test failed with error:', error);
process.exit(1);
}
}
main();
