urarovite 1.3.3

A Google Sheets validation library

Urarovite 🔍

A comprehensive spreadsheet validation library with universal format support for both Google Sheets and Excel files. Urarovite provides robust data validation, modern authentication, formula preservation, and seamless integration with contemporary codebases through a clean abstraction layer.

🚀 Features

Universal Spreadsheet Support

• Google Sheets: Full gspread integration with modern authentication
• Excel Files: Native .xlsx and .xls support via openpyxl
• Format Agnostic: Validators work identically across all spreadsheet formats
• Formula Preservation: Maintains formulas during conversion between formats
• Intelligent Defaults: Smart target location detection and file management

Modern Architecture

• Abstraction Layer: Clean SpreadsheetInterface for format-independent operations
• Template Methods: Consistent validation patterns with automatic resource management
• Performance Optimized: Smart read-only mode detection and client caching
• Type Safety: Comprehensive type hints throughout (Python 3.9+)

Authentication & Security

• Service Account Only: Base64-encoded credentials (no file storage required)
• Domain-wide Delegation: Enterprise-grade user impersonation support
• Secure Credential Handling: No sensitive data in logs or error messages
• Environment Integration: Seamless .env file support

Validation System

• 19+ Built-in Validators: Data quality, range validation, platform neutralization
• Fix & Flag Modes: Automatic issue resolution or reporting-only modes
• Comprehensive Coverage: Empty cells, duplicates, formatting, tab names, ranges
• Excel Compatibility: Tab name validation, formula detection, range verification

📦 Installation

pip install urarovite

Optional Dependencies

# For Excel file support
pip install urarovite[excel]

# For development
pip install urarovite[dev]

# For Jupyter notebook support
pip install urarovite[notebook]

# Install all extras
pip install urarovite[excel,dev,notebook]

🔑 Authentication Setup

Service Account (Recommended)

1. Create a Google Cloud Project:

   • Go to Google Cloud Console
   • Create a new project or select an existing one

2. Enable APIs:

   • Navigate to "APIs & Services" > "Library"
   • Enable "Google Sheets API" and "Google Drive API"

3. Create Service Account:

   • Go to "APIs & Services" > "Credentials"
   • Click "Create Credentials" > "Service Account"
   • Download the JSON key file

4. Prepare Credentials:

   import base64
   import json
   
   # Load your service account JSON
   with open('path/to/service-account.json', 'r') as f:
       service_account = json.load(f)
   
   # Encode for use with urarovite
   encoded_creds = base64.b64encode(json.dumps(service_account).encode()).decode()
   

Domain-wide Delegation (Enterprise)

For enterprise users who need to impersonate other users:

1. Enable Domain-wide Delegation in your service account settings
2. Add OAuth Scopes in Google Admin Console:
   • https://www.googleapis.com/auth/spreadsheets
   • https://www.googleapis.com/auth/drive.readonly

💻 Usage

Basic Validation

from urarovite.core.api import execute_validation, get_available_validation_criteria

# List available validators
validators = get_available_validation_criteria()
print(validators)
# [{"id": "empty_cells", "name": "Fix Empty Cells (with range targeting)"}, ...]

# Google Sheets validation (requires authentication)
encoded_creds = "eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0="
result = execute_validation(
    check={"id": "empty_cells", "mode": "fix"},
    sheet_url="https://docs.google.com/spreadsheets/d/1ABC123/edit",
    auth_secret=encoded_creds,
    subject="[e-mail]"  # Optional: for domain-wide delegation
)

# Excel file validation (no authentication required)
result = execute_validation(
    check={"id": "empty_cells", "mode": "fix"},
    sheet_url="./data/spreadsheet.xlsx"
)

# Excel to Google Sheets conversion with validation
result = execute_validation(
    check={"id": "tab_names", "mode": "fix"},
    sheet_url="./data/spreadsheet.xlsx",
    auth_secret=encoded_creds,
    target="1hWMAXridd8Gd_ND6p8r4bGLQYZnL0b52",  # Drive folder ID
    target_format="sheets"
)

print(f"Fixed {result['fixes_applied']} flags")
print(f"Found {result['flags_found']} additional flags")
print(f"Logs: {result['automated_log']}")

Advanced Validation: Target Specific Ranges

The empty_cells validator now supports targeting specific cell ranges instead of checking all cells. This is useful when you only want to validate certain areas of your spreadsheet.

Range Targeting Examples

from urarovite.validators import get_validator

validator = get_validator("empty_cells")

# Target a specific range (e.g., 'Case'!D72:D76)
result = validator.validate(
    "spreadsheet.xlsx",
    mode="flag",
    target_ranges="Case!D72:D76"
)

# Target individual cells
result = validator.validate(
    "spreadsheet.xlsx", 
    mode="fix",
    target_ranges=["Case!D72", "Case!D73", "Case!D74"],
    fill_value="N/A"
)

# Target multiple ranges
result = validator.validate(
    "spreadsheet.xlsx",
    mode="flag", 
    target_ranges=["Sheet1!A1:B10", "Sheet1!D5:F15"]
)

# Without sheet name (uses default sheet)
result = validator.validate(
    "spreadsheet.xlsx",
    mode="flag",
    target_ranges="A1:C5"
)

Range Format Support

• Range notation: 'Sheet'!A1:B10 - checks all cells from A1 to B10
• Individual cells: 'Sheet'!A1, 'Sheet'!B2 - checks only specific cells
• Mixed formats: Combine ranges and individual cells in a list
• Sheet names: Use quotes for sheets with spaces: 'My Sheet'!A1
• Backward compatibility: No target_ranges parameter = check all cells (default behavior)

Command Line Usage with Ranges

# Target specific range
./run_validation.sh --check '{"id": "empty_cells", "mode": "flag", "target_ranges": "Case!D72:D76"}' 'spreadsheet.xlsx'

# Target individual cells  
./run_validation.sh --check '{"id": "empty_cells", "mode": "fix", "target_ranges": ["Case!D72", "Case!D73"], "fill_value": "N/A"}' 'spreadsheet.xlsx'

Sheet Crawling & Batch Validation

Urarovite includes powerful sheet crawling capabilities that can automatically discover and validate all sheets referenced in a metadata spreadsheet. This is perfect for processing large datasets with multiple input/output sheet pairs.

🚀 Super Simple Usage

# Just provide the URL - saves fixed sheets as Google Sheets in same folder as source
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/1Jx5CHYvKt3y2aO-1vFKT7botQUWnvp-CcZftvFGz2pQ/edit#gid=114720924"

What this does:

• ✅ Crawls through your metadata sheet
• ✅ Finds all input/output sheet URLs automatically
• ✅ Runs ALL available validations on each sheet
• ✅ Saves fixed sheets as Google Sheets in the same folder as the source (NEW DEFAULT!)
• ✅ Automatically adds fixed sheet URLs back to your metadata sheet (NEW!)
• ✅ Provides comprehensive results and statistics

Command Line Options

# With domain-wide delegation
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/your-sheet-id" "[e-mail]"

# Flag mode only (no fixes applied)
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/your-sheet-id" --mode flag

# Save to local Excel files instead
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/your-sheet-id" --target local --format excel

# Save to specific Google Drive folder
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/your-sheet-id" --target "1A2B3C4D5E6F7G8H9I0J"

📋 New Defaults (Google-First)

• Target: Same folder as source (intelligent default) ← was "local"
• Format: Google Sheets ← was "excel"

Why This is Better:

1. Seamless Google Workflow: Fixed sheets stay in Google Drive where you can easily access them
2. Same Folder Organization: Fixed sheets are created right next to your source sheets
3. No File Management: No need to upload/download Excel files
4. Team Collaboration: Everyone can access the fixed sheets immediately
5. Version History: Google Sheets maintains version history of fixes
6. Automatic Audit Trail: Fixed sheet URLs are automatically added to your metadata sheet

📝 New Metadata Columns

After running the crawling script, your metadata sheet will automatically get new columns added:

For Google Sheets Output (Default):

• input_sheet_url_fixed: URLs to the fixed versions of input sheets
• example_output_sheet_url_fixed: URLs to the fixed versions of output sheets
• input_fixes_applied: Number of fixes applied to input sheets
• input_flags_found: Number of flags found in input sheets
• input_validation_summary: Summary of validation results for input sheets
• input_validation_errors: Any validation errors for input sheets
• output_fixes_applied: Number of fixes applied to output sheets
• output_flags_found: Number of flags found in output sheets
• output_validation_summary: Summary of validation results for output sheets
• output_validation_errors: Any validation errors for output sheets

For Excel Output:

• input_sheet_path_fixed: Relative paths to the fixed Excel files for input sheets
• example_output_sheet_path_fixed: Relative paths to the fixed Excel files for output sheets
• Plus all the same validator output columns as above

Example Results

Before:

worker_id	input_sheet_url	example_output_sheet_url
ABC123	https://docs.google.com/.../input123	https://docs.google.com/.../output123

After (showing key columns):

worker_id	input_sheet_url_fixed	input_fixes_applied	input_flags_found	input_validation_summary	output_sheet_url_fixed	output_fixes_applied	output_flags_found
ABC123	https://docs.google.com/.../input123_fixed	15	3	✅ 12 successful; ❌ 1 failed	https://docs.google.com/.../output123_fixed	8	0

This creates a complete audit trail showing:

• 🔗 Where the fixed sheets are located (direct links)
• 📊 Exactly what was fixed (number of fixes applied)
• ⚠️ What flags remain (flags found but not fixed)
• ✅ Validation success rate (how many validators succeeded)
• 🚨 Any errors encountered (validation errors for troubleshooting)

📊 Expected Output

🚀 Starting Urarovite Sheet Crawling and Validation
==================================================
[INFO] Metadata Sheet: https://docs.google.com/spreadsheets/d/...
[INFO] Authentication: ✓ Configured
[INFO] Validation Mode: fix
[INFO] Target: Same folder as source (intelligent default)
[INFO] Format: sheets
[INFO] Preserve Formatting: true

🔍 Starting crawling and validation...
   Metadata Sheet: https://docs.google.com/spreadsheets/d/...
   Authentication: ✓ Configured
   Validation Mode: fix
   Target: Same folder as source (intelligent default)
   Format: sheets

📋 CRAWLING AND VALIDATION RESULTS
==================================================
✅ Overall Status: SUCCESS

📊 Summary Statistics:
   Total Sheet Pairs: 15
   Successful Pairs: 15
   Failed Pairs: 0
   Total Input Fixes: 47
   Total Output Fixes: 23
   Total Input flags: 12
   Total Output flags: 8
   Total Errors: 0

⏱️  Performance Metrics:
   Total Time: 125.30 seconds
   Crawling Time: 5.20 seconds
   Validation Time: 120.10 seconds
   Processing Rate: 0.12 pairs/second

💾 Output Files:
   Results JSON: ./output/crawl_validation_results_20241220_143022.json
   Processing Log: ./output/crawl_validation_20241220_143022.log
   Validated Files: Check Google Drive - fixed sheets created in source folders

🎉 Crawling and validation completed successfully!
   Applied 70 fixes across all sheets
   Check Google Drive for the fixed sheets

🎯 Perfect for Batch Processing

This crawling functionality is ideal for data cleaning tasks because:

1. Batch Processing: Processes all your input/output sheet pairs at once
2. Intelligent Detection: Automatically finds sheet URLs in your metadata
3. Comprehensive Validation: Runs all available validators on each sheet
4. Google-Native: Keeps everything in Google Drive for easy access
5. Detailed Reporting: Shows exactly what was fixed and where

Just run it once and get all your sheets validated and fixed! 🚀

Prerequisites for Crawling

# Set your authentication (required)
export AUTH_SECRET="eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0="

# Optional: For domain-wide delegation
export DELEGATION_SUBJECT="[e-mail]"

Advanced Usage with gspread

from urarovite.auth import get_gspread_client, create_sheets_service_from_encoded_creds
from urarovite.utils.sheets import extract_sheet_id, get_sheet_values

# Create gspread client (recommended)
client = get_gspread_client(encoded_creds, subject="[e-mail]")
spreadsheet = client.open_by_key(sheet_id)

# Or create traditional Google Sheets API service
service = create_sheets_service_from_encoded_creds(encoded_creds)

# Use utility functions
sheet_id = extract_sheet_id("https://docs.google.com/spreadsheets/d/1ABC123/edit")
data = get_sheet_values(service, sheet_id, "Sheet1!A1:Z1000")

⚙️ Configuration

Range Separator Configuration

Urarovite supports configurable range separators for parsing verification field ranges. By default, ranges are separated by @@, but you can configure this to use any separator (e.g., commas).

Global Configuration

from urarovite.utils.sheets import get_segment_separator, set_segment_separator

# Check current separator
print(f"Current separator: '{get_segment_separator()}'")  # Default: '@@'

# Change to comma separator
set_segment_separator(",")

# Now all functions will use comma by default
from urarovite.utils.sheets import split_segments
ranges = "Sheet1!A1:B2,Sheet2!C3:D4"
segments = split_segments(ranges)  # Uses comma separator
print(segments)  # ['Sheet1!A1:B2', 'Sheet2!C3:D4']

# Restore default
set_segment_separator("@@")

Per-Validator Configuration

You can also specify separators per validation call:

from urarovite.validators import get_validator

validator = get_validator("open_ended_ranges")

# Use comma separator for this validation
result = validator.validate(
    spreadsheet_source=sheet_url,
    mode="flag",
    auth_credentials=auth_creds,
    row=row_data,
    separator=","  # Override separator for this call
)

Supported Separators

• Default: @@ (double at-sign)
• Comma: , (comma)
• Pipe: | (pipe)
• Semicolon: ; (semicolon)
• Any string: Custom separators as needed

Backward Compatibility

All existing code continues to work unchanged:

• Functions default to @@ separator if no separator is specified
• Explicit separator parameters override global settings
• No breaking changes to existing APIs

🔧 Migration from OAuth

If you're migrating from OAuth-based authentication:

# OLD: OAuth-based authentication
from urarovite.checker.auth import get_credentials, get_sheets_service
creds = get_credentials()  # Interactive OAuth flow
service = get_sheets_service()

# NEW: Service account with base64 credentials
from urarovite.auth import create_sheets_service_from_encoded_creds
service = create_sheets_service_from_encoded_creds(encoded_creds)

# Or use modern gspread client (recommended)
from urarovite.auth import get_gspread_client
client = get_gspread_client(encoded_creds)

📚 Documentation

• Migration Guide: See MIGRATION_SUMMARY.md for detailed changes
• Validator Migration: See VALIDATOR_MIGRATION_GUIDE.md for validator development
• Spreadsheet Abstraction: See SPREADSHEET_ABSTRACTION_GUIDE.md for multi-format support
• API Reference: Full type hints and docstrings throughout the codebase
• Command Line Usage: Use ./run_validation.sh for batch validation operations
• Examples: Check the /tests directory for comprehensive usage examples

🧪 Testing

# Install with dev dependencies
pip install urarovite[dev]

# Run tests
pytest

# Run with coverage
pytest --cov=urarovite

🖥️ Command Line Usage

The repository includes a dynamic CLI and legacy shell scripts for running validations, plus powerful batch utilities with automatic result tracking.

🚀 CLI Utilities (New)

Urarovite provides a comprehensive set of CLI utilities for both single operations and batch processing. All batch utilities automatically add result columns to metadata spreadsheets, showing "Passed" or "Failed" for each processed row.

Available Utilities

# View all available utilities
uv run python -m urarovite.cli run_util --help

# Batch processing utilities (automatically add result columns)
uv run python -m urarovite.cli run_util batch-sanitize-tab-names "metadata_sheet_url" --url-columns "tabs_to_fix"
uv run python -m urarovite.cli run_util batch-validate-a1-ranges "metadata_sheet_url" --url-columns "tabs_to_fix" --column "formula_column"
uv run python -m urarovite.cli run_util batch-sheets-to-excel-drive "metadata_sheet_url" "drive_folder_id" --url-columns "sheet_url"
uv run python -m urarovite.cli run_util batch-excel-to-sheets-drive "metadata_sheet_url" "drive_folder_id" --url-columns "excel_url"
uv run python -m urarovite.cli run_util batch-rename-tabs-from-sheet "metadata_sheet_url" --url-column "spreadsheet_url" --old-name-column "old_tab" --new-name-column "new_tab"

# Single operation utilities
uv run python -m urarovite.cli run_util sanitize-tab-names "spreadsheet_url"
uv run python -m urarovite.cli run_util validate-a1-ranges "spreadsheet_url" --column "formula_column"
uv run python -m urarovite.cli run_util sheets-to-excel-drive "sheet_url" "drive_folder_id"
uv run python -m urarovite.cli run_util excel-to-sheets-drive "excel_file" "drive_folder_id"

# Specialized utilities
uv run python -m urarovite.cli run_util process-forte "csv_file" --target "drive_folder_id"
uv run python -m urarovite.cli run_util folder-batch "input_folder" "drive_folder_id"
uv run python -m urarovite.cli run_util validate "spreadsheet_url" --validator "empty_cells" --validation-mode fix

🔄 Automatic Result Column Tracking

Batch utilities automatically add result columns to your metadata spreadsheet:

• Result Column: "[utility-name] result" (e.g., "batch-sanitize-tab-names result")
  • Values: "Passed" for successful operations, "Failed" for failed operations
• Converted File URL Columns: Automatically added based on utility type
  • Sheets → Excel: "excel_url" column with Google Drive URLs
  • Excel → Sheets: "sheets_url" column with Google Sheets URLs
  • Custom Names: Use --excel-url-column or --sheets-url-column to customize
• Position: Added to the right of existing data
• Automatic: No manual configuration required

Example Result Columns:

| sheet_url | batch-sheets-to-excel-drive result | excel_url                    |
|-----------|-----------------------------------|------------------------------|
| sheet1    | Passed                            | https://drive.google.com/... |
| sheet2    | Passed                            | https://drive.google.com/... |
| sheet3    | Failed                            |                              |

📋 Requirements for Result Columns

• Google Sheets URLs: Must be https://docs.google.com/spreadsheets/... format
• Authentication: Requires valid AUTH_SECRET in .env file
• Batch Mode: Automatically detected when using --url-columns
• Metadata Structure: Input sheet must have URL columns specified

🎯 Smart Mode Detection

• Automatic: Utilities with --url-columns automatically switch to batch mode
• Manual Override: Use --mode batch or --mode single to override
• Result Tracking: Only batch mode adds result columns to metadata sheets

Per-Validator CLI Commands (Legacy)

Per-Validator CLI Commands (New)

Each validator now has its own subcommand that accepts explicit parameters mapped to its validate() signature. Run urarovite <validator-id> --help to see arguments.

Examples:

# Empty cells: fill all empty values with N/A on a local Excel file
urarovite empty_cells ./data/spreadsheet.xlsx --mode fix --fill-value "N/A"

# Empty cells with targeted ranges
urarovite empty_cells ./data/spreadsheet.xlsx --mode flag --target-ranges "'Case'!D72:D76"

# Tab names with custom replacement char
urarovite tab_names 'https://docs.google.com/spreadsheets/d/abc123' --mode fix --auth-secret "$AUTH_SECRET" --replacement-char _

# Numeric rounding (flag-only example)
urarovite numeric_rounding ./data/spreadsheet.xlsx --mode flag

# Cell value validation using JSON for expected_values
urarovite cell_value_validation ./data/spreadsheet.xlsx --mode fix \
  --expected-values '{"A1": "Title", "B2": 100}' --tolerance 0.01

# Spreadsheet differences with verification ranges
urarovite run_util spreadsheet-differences "task_sheet_url" --validation-mode flag --input-url-column "I" --output-url-column "J" --verification-range-column "H"

# Spreadsheet differences with custom columns and whole-sheet comparison disabled
urarovite run_util spreadsheet-differences "task_sheet_url" --validation-mode fix --input-url-column "A" --output-url-column "B" --verification-range-column "C" --compare-whole-sheet false

Notes:

• Common options on all validator commands:
  • --mode {flag,fix}: required
  • --auth-secret: base64 service account for Google Sheets
  • --subject: delegation subject (optional)
  • --output {table,json}: result display format (default: table)

Single Sheet Validation (run_validation.sh) (Legacy)

For validating individual spreadsheets with the legacy script:

Prerequisites

1. Make the script executable (if needed):

   chmod +x run_validation.sh
   

2. For Google Sheets validation, create a .env file with your base64-encoded service account:

   # .env file
   AUTH_SECRET=eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0=
   

Usage Examples

# Run all validations on Google Sheets
./run_validation.sh --all 'https://docs.google.com/spreadsheets/d/abc123'

# Run all validations on local Excel file
./run_validation.sh --all './data/spreadsheet.xlsx'

# Run single validation with JSON
./run_validation.sh --check '{"id": "empty_cells", "mode": "fix"}' 'https://docs.google.com/spreadsheets/d/abc123'

# Run single validation on Excel file
./run_validation.sh --check '{"id": "tab_names", "mode": "fix"}' './spreadsheet.xlsx'

# With delegation subject (Google Sheets only)
./run_validation.sh --all 'https://docs.google.com/spreadsheets/d/abc123' '[e-mail]'

# Load check from JSON file
echo '{"id": "duplicate_rows", "mode": "flag"}' > check.json
./run_validation.sh --check check.json 'https://docs.google.com/spreadsheets/d/abc123'

Script Options

• --all: Run all available validation criteria
• --check <json_or_file>: Run a single validation check (JSON string or file path)

Supported Input Types

• Google Sheets: URLs containing docs.google.com (requires authentication)
• Excel Files: Local .xlsx or .xls files (no authentication required)

The script automatically detects the input type and applies appropriate authentication requirements.

Batch Validation with Crawling (run_crawl_validation.sh)

For processing multiple sheets referenced in a metadata spreadsheet, see the Sheet Crawling & Batch Validation section above. This script can automatically discover and validate all sheets in your data processing workflow.

📚 Documentation

For comprehensive guides and technical details:

• CLI Utilities Complete Guide - Complete CLI usage, technical implementation, and quick reference
• CLI Utilities README - CLI utilities overview
• Validation Guide - Validator usage and examples
• API Documentation - Programmatic usage patterns
• Authentication Guide - Detailed authentication setup

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Install development dependencies: pip install urarovite[dev]
4. Make your changes with proper type hints and tests
5. Run tests and linting: pytest && ruff check
6. Submit a pull request

📄 License

This project is licensed under the GNU General Public License v3 (GPLv3) - see the LICENSE file for details.

🔗 Links

• PyPI: https://pypi.org/project/urarovite/
• GitHub: https://github.com/ParetoWorkers/Urarovite
• flags: https://github.com/ParetoWorkers/Urarovite/flags