Sheet2Code - Convert Excel & Google Sheets to TypeScript & Python Code

What is Sheet2Code?

Sheet2Code is an open-source tool that automatically converts both Microsoft Excel and Google Sheets formulas, calculations, and business logic into clean, executable TypeScript or Python code. It's perfect for developers who need to migrate spreadsheet-based calculations into production applications, automate spreadsheet workflows, or understand complex spreadsheet formulas programmatically.

Whether you're working with Excel (.xlsx) files or Google Sheets documents, dealing with financial models, data analysis spreadsheets, or complex VLOOKUP chains, Sheet2Code transforms your spreadsheet logic into maintainable, testable, and version-controlled code. The tool preserves the exact calculation logic from either Excel or Google Sheets, ensuring accuracy in the conversion process.

Excel & Google Sheets Compatibility

Sheet2Code works seamlessly with both Microsoft Excel and Google Sheets:

• Excel Support: Import your .xlsx, .xls, or .xlsm files to Google Sheets for processing
• Google Sheets Native: Direct API integration for real-time formula extraction
• Formula Compatibility: Supports the common formula syntax shared by both platforms
• Function Parity: All major functions (VLOOKUP, SUMIF, INDEX/MATCH) work identically
• Cross-Platform: Generated code works regardless of original spreadsheet source

The conversion process preserves the exact calculation logic whether your source is an Excel workbook or a Google Sheets document, making it the perfect tool for organizations using either or both platforms.

Key Features

How It Works

1. Authentication & Data Extraction

For Google Sheets, Sheet2Code uses the Google Sheets API to read your spreadsheet data. It supports both OAuth2 for interactive use and Service Account authentication for automation. For Excel files, you can export to Google Sheets or use the Excel-to-CSV export feature. The tool fetches all formulas, values, and metadata from specified sheets.

2. Formula Parsing & AST Generation

Using a sophisticated Chevrotain-based parser, Sheet2Code tokenizes and parses each formula into an Abstract Syntax Tree (AST). This handles everything from simple arithmetic to complex nested functions with multiple arguments.

=IF(VLOOKUP(A2,Sheet2!A:D,4,FALSE)>100,SUM(B2:B10)*1.1,B2)

Gets transformed into a structured AST that represents the formula's logic programmatically.

3. Dependency Graph Construction

The dependency analyzer builds a directed graph of all cell relationships. It identifies which cells depend on others, detects circular references, and determines the optimal calculation order using topological sorting.

4. Code Generation

Finally, the code generator traverses the dependency graph and AST to produce clean, readable code in your chosen language:

// TypeScript Output
cells['Results!A1'] = vlookup(cells['Input!A2'], getRange('Sheet2!A:D', cells), 4, false) > 100 
  ? sum(...getRange('Input!B2:B10', cells)) * 1.1 
  : cells['Input!B2'];

Installation & Quick Start

For Google Sheets:

# Clone the repository
git clone https://github.com/joemastersemison/google-sheets-to-code.git
cd google-sheets-to-code

# Install dependencies
npm install

# Set up Google Sheets API credentials
npm run cli -- setup

# Convert your Google Sheets document
npm run cli -- convert \
  --url "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit" \
  --input-tabs "Input,Parameters" \
  --output-tabs "Results,Summary" \
  --language typescript \
  --output-file generated-code.ts

# Watch mode - automatically regenerate on changes
npm run cli -- convert --config config.json --watch
npm run cli -- convert --config config.json --watch --watch-interval 60

# Run the generated code
node generated-code.js --json

For Excel Files:

# 1. Go to Google Sheets
# 2. File -> Import -> Upload your .xlsx file
# 3. Use the Google Sheets URL with the tool

Example Configurations:

The project includes ready-to-use XLSX files and configurations for common use cases:

# Financial modeling with loan calculations (PMT, NPV, IRR)
# Includes financial-model.xlsx ready for Google Sheets import
npm run cli -- convert --config examples/financial-model.json

# Statistical data analysis with 100 rows of sample data
# Includes data-analysis.xlsx with MEDIAN, PERCENTILE, SUMIFS
npm run cli -- convert --config examples/data-analysis.json

# Complete inventory management system
# Includes inventory-tracking.xlsx with EOQ and ABC classification
npm run cli -- convert --config examples/inventory-tracking.json

# Watch mode for automatic regeneration
npm run cli -- convert --config examples/financial-model.json --watch

Use Cases

• Financial Modeling: Convert complex Excel or Google Sheets financial models with NPV, IRR, and amortization calculations into maintainable code
• Data Migration: Transform Excel or Google Sheets-based business logic into microservices or serverless functions
• Testing & Validation: Generate test cases from spreadsheet examples to validate application logic
• Documentation: Understand and document complex spreadsheet formulas by examining generated code
• Automation: Convert manual spreadsheet processes into automated workflows and batch jobs
• Legacy Modernization: Migrate decades-old Excel-based systems to modern web applications

Supported Formula Types

Sheet2Code handles a comprehensive range of Excel and Google Sheets formulas and functions that work identically in both platforms:

• Math & Statistics: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, ABS, SQRT, STDEV, VAR, MEDIAN, PERCENTILE
• Conditional: COUNTIF, SUMIF, SUMIFS, AVERAGEIF, COUNTIFS
• Logical: IF, IFS, AND, OR, NOT, IFERROR, IFNA, SWITCH
• Lookup & Reference: VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, INDIRECT
• Text: CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, SUBSTITUTE
• Date & Time: TODAY, NOW, DATE, DATEVALUE, YEAR, MONTH, DAY
• Array Formulas: Array literals, ARRAYFORMULA, FILTER, SORT, UNIQUE, RANK, LARGE, SMALL
• Financial: PMT, PV, FV, RATE, NPV, IRR (with Newton-Raphson solver)

Advanced Features

Watch Mode for Development

Automatically regenerate code when your Google Sheet changes. Perfect for iterative development with configurable refresh intervals (--watch flag with --watch-interval option).

Hybrid Sheet Support

Intelligently handles sheets that contain both static data and formulas. Automatically detects and loads non-formula cells from output sheets when needed.

Column-Only Range References

Full support for column-only ranges like A:A or D:D, commonly used in functions like SUMIF and COUNTIF for dynamic data ranges.

Named Range Resolution

Automatically fetches and resolves named ranges from your Google Sheets, converting them to their actual cell references in the generated code.

Circular Dependency Handling

Sheet2Code detects circular references and handles them gracefully, generating code with #REF! errors and clear warnings, just like Excel and Google Sheets would display.

Quoted Sheet Names

Properly handles sheet names with spaces and special characters, like 'Q4 Sales Data'!A1 or 'John''s Report'!B2:B10.

Absolute & Relative References

Understands the difference between $A$1, A$1, $A1, and A1 reference styles and converts them appropriately.

CLI Execution Support

Generated code can be executed directly from the command line with support for custom inputs and JSON output.

Technical Architecture

Sheet2Code is built with modern TypeScript and follows clean architecture principles:

• Parser Layer: Chevrotain-based lexer and parser for formula tokenization and AST generation
• Analysis Layer: Dependency graph construction and topological sorting algorithms
• Generator Layer: Template-based code generation with language-specific optimizations
• API Layer: Google Sheets API integration with retry logic and error handling
• CLI Layer: Commander.js-based command-line interface with progress indicators

Why Choose Sheet2Code?

• Open Source: MIT licensed, free to use and modify for any purpose
• Well Tested: Comprehensive test suite with 120+ tests ensuring reliability
• Actively Maintained: Regular updates, bug fixes, and new feature additions
• Production Ready: Used in real-world projects for financial modeling and data migration
• Developer Friendly: Clean code output with proper formatting and type safety
• Extensible: Easy to add new functions or output languages