Class: SheetAPI

Comprehensive Google Sheets API wrapper providing full CRUD operations, advanced querying, caching, and type-safe data management. ## Core Features ### Data Operations - **Full CRUD Support**: Create, read, update, delete operations with automatic ID generation - **Advanced Querying**: Filter, sort, paginate, and search across sheet data - **Type Safety**: Automatic type casting and validation based on schema definitions - **Batch Operations**: Efficient bulk operations for multiple records ### Caching System - **Multi-Level Caching**: Data cache, record cache, and query result cache - **TTL Management**: Time-based expiration with automatic cleanup - **Performance Tracking**: Cache hit/miss statistics and optimization metrics - **Auto-Optimization**: Dynamic cache duration adjustment based on usage patterns ### Authentication & Security - **OAuth2 Integration**: Automatic token refresh and management - **Secure Token Handling**: Encrypted storage and secure transmission - **Rate Limiting**: Built-in request throttling and retry logic - **Error Recovery**: Comprehensive error handling with detailed diagnostics ### Data Management - **Schema Definition**: Flexible column definitions with type specifications - **Data Validation**: Input validation and type coercion - **ID Generation**: Automatic unique identifier creation - **Data Transformation**: Custom field mapping and processing ## Architecture Overview The SheetAPI class implements a layered architecture: ``` ┌─────────────────┐ │ SheetAPI │ ← Public API layer │ (Main Class) │ └─────────────────┘ │ ▼ ┌─────────────────┐ │ SheetAPIToken- │ ← Authentication layer │ Manager │ └─────────────────┘ │ ▼ ┌─────────────────┐ │ Google Sheets │ ← External API layer │ API v4 │ └─────────────────┘ ``` ### Layer Responsibilities **Public API Layer (SheetAPI)**: - High-level CRUD operations - Query processing and optimization - Cache management and invalidation - Data transformation and validation - Error handling and user feedback **Authentication Layer (SheetAPITokenManager)**: - OAuth2 token lifecycle management - Automatic token refresh - Secure HTTP request authentication - Rate limiting and retry logic **External API Layer (Google Sheets API v4)**: - Raw HTTP communication - API-specific request formatting - Response parsing and normalization ## Performance Characteristics ### Caching Strategy - **Data Cache**: Full sheet data cached for 15-60 minutes (user-dependent) - **Record Cache**: Individual records cached for 30-120 minutes - **Query Cache**: Query results cached for 10-40 minutes - **Auto-optimization**: Cache durations adjust based on user count and access patterns ### Optimization Features - **Lazy Loading**: Data loaded only when needed - **Incremental Updates**: Only changed data transmitted - **Batch Processing**: Multiple operations combined for efficiency - **Connection Pooling**: Reused HTTP connections for reduced latency ## Usage Patterns ### Basic CRUD Operations ```javascript const api = new SheetAPI({ spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", accessToken: "ya29...", sheetName: "Users", headers: ["id", "name", "email", "age"] }); await api.init(); // Create const user = await api.create({ name: "John Doe", email: "john@example.com", age: 30 }); console.log(user.id); // Auto-generated ID // Read const allUsers = await api.getAll(); const specificUser = await api.getById(user.id); // Update await api.update(user.id, { age: 31 }); // Delete await api.delete(user.id); ``` ### Advanced Querying ```javascript // Filter and sort const activeUsers = await api.getAll({ filter: { status: "active" }, sort: { name: "asc" }, limit: 10 }); // Search across multiple fields const searchResults = await api.search("john", ["name", "email"]); // Paginated results const page1 = await api.getAll({ page: 1, limit: 50 }); const page2 = await api.getAll({ page: 2, limit: 50 }); ``` ### Schema Definition ```javascript const api = new SheetAPI({ spreadsheetId: "your-id", accessToken: "your-token", sheetName: "Products", headers: [ "id", { name: "name", type: "string" }, { name: "price", type: "number" }, { name: "inStock", type: "boolean" }, { name: "createdAt", type: "date" } ] }); ``` ### Cache Configuration ```javascript const api = new SheetAPI({ // ... other config cache: { userCount: 100, // Optimize for 100 concurrent users durationUnit: 'hours', // Use hours instead of minutes maxRecordCacheSize: 200 // Cache up to 200 records } }); ``` ## Error Handling The SheetAPI provides comprehensive error handling with detailed error messages: ```javascript try { await api.create({ name: "John" }); } catch (error) { if (error.code === 'VALIDATION_ERROR') { console.log('Validation failed:', error.details); } else if (error.code === 'AUTH_ERROR') { console.log('Authentication failed, token may be expired'); } else if (error.code === 'NETWORK_ERROR') { console.log('Network issue, will retry automatically'); } } ``` ## Security Considerations ### Token Security - Access tokens are never stored in plain text - Refresh tokens are encrypted using client credentials - Automatic token rotation prevents token exhaustion - Secure HTTPS-only communication with Google APIs ### Data Protection - Input validation prevents injection attacks - Type coercion ensures data integrity - Rate limiting prevents abuse - Audit logging for sensitive operations ### Access Control - OAuth2 scopes limit API permissions - Sheet-level access control through Google Sheets permissions - Row-level security through application logic ## Performance Optimization ### Caching Best Practices - Cache durations auto-adjust based on data volatility - Cache invalidation on data modifications - Memory-efficient cache storage with size limits - Cache statistics for monitoring and tuning ### Query Optimization - Client-side filtering reduces API calls - Pagination prevents large data transfers - Batch operations combine multiple requests - Connection reuse minimizes latency ## Extensibility The SheetAPI is designed for extension through: - Custom data transformers - Plugin architecture for additional features - Event hooks for operation monitoring - Custom validation rules - Integration with other data sources ## Dependencies - **SheetAPITokenManager**: Handles OAuth2 authentication - **Google Sheets API v4**: External REST API for sheet operations - **Fetch API**: Modern HTTP client for API communication - **ES6+ Features**: Promises, async/await, Map, Set ## Browser Compatibility - Modern browsers with ES6+ support - Node.js 14+ with fetch polyfill - HTTPS required for OAuth2 security - CORS-enabled environments for web applications