Building an AI-Powered Browser-Based Spreadsheet

Core Architecture

The spreadsheet is built using vanilla JavaScript, HTML, and CSS, with minimal external dependencies. This choice was made to ensure fast loading times and maximum compatibility. The core components include:

• A grid system for cell layout and navigation
• Local storage for data persistence
• Formula parsing and evaluation engine
• AI-powered formula assistance via Gemini/OpenAI APIs
• JSON import/export functionality
• Theme support for light/dark modes

Grid System Implementation

The spreadsheet grid is implemented using a table structure with dynamic row and column headers. Key features include:

• Dynamic cell creation and management
• Keyboard navigation support
• Cell selection and editing
• Row and column operations (insert/delete)

// Example of cell creation
function createCell(row, col) {
    const cell = document.createElement('td');
    cell.contentEditable = true;
    cell.dataset.row = row;
    cell.dataset.col = col;
    cell.dataset.address = `${COLUMN_NAMES[col]}${row + 1}`;
    return cell;
}

Data Management

Data persistence is handled through the browser's local storage API. The spreadsheet data is stored as a JSON object where keys are cell addresses and values are either raw data or formulas:

// Example of data structure
{
    "A1": "Hello",
    "B2": "=SUM(A1:A5)",
    "C3": "42"
}

Auto-save functionality ensures that changes are persisted immediately, preventing data loss.

Formula Engine

The formula engine supports mathematical operations, range references, and a variety of functions. Key features include:

• Basic arithmetic operations (+, -, *, /)
• Range references (A1:A5)
• Built-in functions (SUM, AVERAGE, MIN, MAX, COUNT)
• Error handling and validation
• Automatic formula updates when referenced cells change
• Dependency tracking for both individual cells and ranges
• Circular reference detection and handling

// Example of formula evaluation with dependency tracking
function calculateFormula(formula, cellAddress) {
    if (isCalculating) {
        return '#CIRCULAR!';
    }
    
    isCalculating = true;
    
    try {
        // Remove the = sign
        formula = formula.substring(1);
        
        // Handle function calls
        const functionMatch = formula.match(/^([A-Z]+)\((.*)\)$/);
        if (functionMatch) {
            const [_, funcName, argsStr] = functionMatch;
            const func = FUNCTIONS[funcName];
            
            if (!func) {
                return '#NAME?';
            }
            
            // Parse arguments and track dependencies
            const args = [];
            let currentArg = '';
            let inQuotes = false;
            let bracketCount = 0;
            
            for (let i = 0; i < argsStr.length; i++) {
                const char = argsStr[i];
                
                if (char === '"' && argsStr[i-1] !== '\\') {
                    inQuotes = !inQuotes;
                } else if (char === '(' && !inQuotes) {
                    bracketCount++;
                } else if (char === ')' && !inQuotes) {
                    bracketCount--;
                } else if (char === ',' && !inQuotes && bracketCount === 0) {
                    args.push(currentArg.trim());
                    currentArg = '';
                    continue;
                }
                
                currentArg += char;
            }
            
            if (currentArg) {
                args.push(currentArg.trim());
            }
            
            // Process arguments and track dependencies
            const processedArgs = args.map(arg => {
                // Handle cell references
                if (/^[A-Z]+\d+$/.test(arg)) {
                    // Track dependency
                    if (!cellDependencies[arg]) {
                        cellDependencies[arg] = new Set();
                    }
                    cellDependencies[arg].add(cellAddress);
                    return getCellValue(arg);
                }
                
                // Handle ranges
                if (arg.includes(':')) {
                    const [start, end] = arg.split(':');
                    const rangeValues = getRangeValues(start, end);
                    // Track dependencies for each cell in the range
                    const rangeCells = getRangeCells(start, end);
                    rangeCells.forEach(rangeCell => {
                        if (!cellDependencies[rangeCell]) {
                            cellDependencies[rangeCell] = new Set();
                        }
                        cellDependencies[rangeCell].add(cellAddress);
                    });
                    return rangeValues;
                }
                
                return arg;
            });
            
            // Call the function
            const result = func(processedArgs);
            isCalculating = false;
            return result;
        }
        
        // Handle arithmetic expressions
        const result = evaluateArithmetic(formula);
        isCalculating = false;
        return result;
    } catch (error) {
        isCalculating = false;
        return '#ERROR!';
    }
}

AI-Powered Formula Assistance

One of the most innovative features of our spreadsheet is AI-powered formula assistance. This feature allows users to describe what they want to calculate in natural language, and the system suggests appropriate formulas using the built-in functions.

RAG Architecture

We implemented a Retrieval-Augmented Generation (RAG) approach where the system:

1. Retrieves the complete list of available spreadsheet functions and their descriptions
2. Sends this context along with the user's natural language query to an LLM
3. Receives formula suggestions that are guaranteed to work with the existing function set

Implementation Details

The RAG implementation includes several components:

The implementation supports both Google's Gemini API and OpenAI-compatible endpoints. Users can configure their preferred AI service through the settings dialog:

// Get AI formula suggestions from the API
async function getAIFormulaSuggestions(query) {
    // Get appropriate endpoint based on API URL
    const isOpenAIEndpoint = aiSettings.apiUrl.includes('openai');
    const endpoint = isOpenAIEndpoint
        ? aiSettings.apiUrl
        : `${aiSettings.apiUrl}${aiSettings.model}:generateContent?key=${aiSettings.apiKey}`;
    
    // Build detailed function context
    const availableFunctions = Object.keys(FUNCTIONS);
    const functionCategories = Object.entries(FUNCTION_CATEGORIES).map(([category, funcs]) => {
        return `${category}: ${funcs.join(', ')}`;
    }).join('\n');
    
    // Build detailed function descriptions
    const functionList = Object.entries(FUNCTIONS)
        .map(([name, func]) => {
            const description = typeof func === 'function' 
                ? `Function to ${name.toLowerCase()} values` 
                : (func.description || '');
            const syntax = func.syntax || `${name}(values)`;
            return `${name}: ${description}\nSyntax: ${syntax}\nCategory: ${getCategoryForFunction(name)}`;
        })
        .join('\n\n');
    
    // Clear instruction with strict function list
    const promptText = `You are a spreadsheet formula assistant for a specific spreadsheet application. 
Generate formula suggestions based on the user's natural language description.

IMPORTANT: You must ONLY suggest formulas using the functions listed below. Do not suggest any functions not in this list.

Available Function Categories:
${functionCategories}

Detailed Function Reference:
${functionList}

Complete List of Available Functions (ONLY use these): ${availableFunctions.join(', ')}

User Description: "${query}"

Return exactly 3 appropriate formula suggestions with explanations. Format your response as JSON...`;

    // Make API request and process response...
}

User Experience

From the user's perspective, the flow is simple:

1. The user types =describe followed by their natural language query
2. The system displays a loading indicator while processing
3. A popup shows formula suggestions with explanations
4. The user clicks on their preferred formula to apply it to the cell

This feature dramatically improves usability for users who may not be familiar with spreadsheet functions or syntax.

User Interface

The UI is designed to be clean and intuitive, with features like:

• Toolbar for common operations
• Cell formatting and styling
• Theme support with light/dark modes
• AI settings configuration
• Responsive design for different screen sizes

Challenges and Solutions

Some of the key challenges we faced and how we solved them:

• Formula Dependencies: Implemented a dependency graph to track and update dependent cells
• Performance: Optimized cell updates and formula evaluation for large spreadsheets
• Data Integrity: Added validation and error handling for formulas and data input
• AI Integration: Developed a precise prompt engineering approach to ensure suggested formulas use only available functions
• API Compatibility: Created an abstraction layer that supports both Gemini and OpenAI-compatible endpoints
• Security: Storing API keys securely in local storage with appropriate authorization headers