# Urarovite 🔍
[](https://badge.fury.io/py/urarovite)
[](https://pypi.org/project/urarovite/)
[](https://opensource.org/licenses/MIT)
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
```bash
pip install urarovite
```
### Optional Dependencies
```bash
# 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](https://console.cloud.google.com/)
- 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**:
```python
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
```python
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"}, ...]
# 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="user@domain.com" # 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']} issues")
print(f"Found {result['issues_flagged']} additional issues")
print(f"Logs: {result['automated_logs']}")
```
### 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
```bash
# 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
```bash
# With domain-wide delegation
./run_crawl_validation.sh "https://docs.google.com/spreadsheets/d/your-sheet-id" "user@yourdomain.com"
# 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_issues_found`**: Number of issues 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_issues_found`**: Number of issues 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_issues_found | input_validation_summary | output_sheet_url_fixed | output_fixes_applied | output_issues_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 issues remain** (issues 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 Issues: 12
Total Output Issues: 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
```bash
# Set your authentication (required)
export AUTH_SECRET="eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0="
# Optional: For domain-wide delegation
export DELEGATION_SUBJECT="user@yourdomain.com"
```
### Advanced Usage with gspread
```python
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="user@domain.com")
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")
```
## 🔧 Migration from OAuth
If you're migrating from OAuth-based authentication:
```python
# 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](MIGRATION_SUMMARY.md) for detailed changes
- **Validator Migration**: See [VALIDATOR_MIGRATION_GUIDE.md](VALIDATOR_MIGRATION_GUIDE.md) for validator development
- **Spreadsheet Abstraction**: See [SPREADSHEET_ABSTRACTION_GUIDE.md](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
```bash
# Install with dev dependencies
pip install urarovite[dev]
# Run tests
pytest
# Run with coverage
pytest --cov=urarovite
```
## 🖥️ Command Line Usage
The repository includes convenient shell scripts for running validations from the command line:
### Single Sheet Validation (`run_validation.sh`)
For validating individual spreadsheets:
### Prerequisites
1. **Make the script executable** (if needed):
```bash
chmod +x run_validation.sh
```
2. **For Google Sheets validation**, create a `.env` file with your base64-encoded service account:
```bash
# .env file
AUTH_SECRET=eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0=
```
### Usage Examples
```bash
# 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' 'user@domain.com'
# 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](#sheet-crawling--batch-validation) section above. This script can automatically discover and validate all sheets in your data processing workflow.
## 🤝 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](LICENSE) file for details.
## 🔗 Links
- **PyPI**: <https://pypi.org/project/urarovite/>
- **GitHub**: <https://github.com/ParetoWorkers/Urarovite>
- **Issues**: <https://github.com/ParetoWorkers/Urarovite/issues>
Raw data
{
"_id": null,
"home_page": null,
"name": "urarovite",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": "data-quality, google-sheets, gspread, spreadsheets, validation",
"author": null,
"author_email": "Mo Dorff <mohonrid@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/66/1b/371a8a49523290466bb733c7b4f8d685c37e4dc42c10bda1906579760813/urarovite-1.0.2.tar.gz",
"platform": null,
"description": "# Urarovite \ud83d\udd0d\n\n[](https://badge.fury.io/py/urarovite)\n[](https://pypi.org/project/urarovite/)\n[](https://opensource.org/licenses/MIT)\n\nA 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.\n\n## \ud83d\ude80 Features\n\n### Universal Spreadsheet Support\n- **Google Sheets**: Full gspread integration with modern authentication\n- **Excel Files**: Native `.xlsx` and `.xls` support via openpyxl\n- **Format Agnostic**: Validators work identically across all spreadsheet formats\n- **Formula Preservation**: Maintains formulas during conversion between formats\n- **Intelligent Defaults**: Smart target location detection and file management\n\n### Modern Architecture\n- **Abstraction Layer**: Clean SpreadsheetInterface for format-independent operations\n- **Template Methods**: Consistent validation patterns with automatic resource management\n- **Performance Optimized**: Smart read-only mode detection and client caching\n- **Type Safety**: Comprehensive type hints throughout (Python 3.9+)\n\n### Authentication & Security\n- **Service Account Only**: Base64-encoded credentials (no file storage required)\n- **Domain-wide Delegation**: Enterprise-grade user impersonation support\n- **Secure Credential Handling**: No sensitive data in logs or error messages\n- **Environment Integration**: Seamless .env file support\n\n### Validation System\n- **19+ Built-in Validators**: Data quality, range validation, platform neutralization\n- **Fix & Flag Modes**: Automatic issue resolution or reporting-only modes\n- **Comprehensive Coverage**: Empty cells, duplicates, formatting, tab names, ranges\n- **Excel Compatibility**: Tab name validation, formula detection, range verification\n\n## \ud83d\udce6 Installation\n\n```bash\npip install urarovite\n```\n\n### Optional Dependencies\n\n```bash\n# For Excel file support\npip install urarovite[excel]\n\n# For development\npip install urarovite[dev]\n\n# For Jupyter notebook support\npip install urarovite[notebook]\n\n# Install all extras\npip install urarovite[excel,dev,notebook]\n```\n\n## \ud83d\udd11 Authentication Setup\n\n### Service Account (Recommended)\n\n1. **Create a Google Cloud Project**:\n - Go to [Google Cloud Console](https://console.cloud.google.com/)\n - Create a new project or select an existing one\n\n2. **Enable APIs**:\n - Navigate to \"APIs & Services\" > \"Library\"\n - Enable \"Google Sheets API\" and \"Google Drive API\"\n\n3. **Create Service Account**:\n - Go to \"APIs & Services\" > \"Credentials\"\n - Click \"Create Credentials\" > \"Service Account\"\n - Download the JSON key file\n\n4. **Prepare Credentials**:\n\n ```python\n import base64\n import json\n \n # Load your service account JSON\n with open('path/to/service-account.json', 'r') as f:\n service_account = json.load(f)\n \n # Encode for use with urarovite\n encoded_creds = base64.b64encode(json.dumps(service_account).encode()).decode()\n ```\n\n### Domain-wide Delegation (Enterprise)\n\nFor enterprise users who need to impersonate other users:\n\n1. **Enable Domain-wide Delegation** in your service account settings\n2. **Add OAuth Scopes** in Google Admin Console:\n - `https://www.googleapis.com/auth/spreadsheets`\n - `https://www.googleapis.com/auth/drive.readonly`\n\n## \ud83d\udcbb Usage\n\n### Basic Validation\n\n```python\nfrom urarovite.core.api import execute_validation, get_available_validation_criteria\n\n# List available validators\nvalidators = get_available_validation_criteria()\nprint(validators)\n# [{\"id\": \"empty_cells\", \"name\": \"Fix Empty Cells\"}, ...]\n\n# Google Sheets validation (requires authentication)\nencoded_creds = \"eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0=\"\nresult = execute_validation(\n check={\"id\": \"empty_cells\", \"mode\": \"fix\"},\n sheet_url=\"https://docs.google.com/spreadsheets/d/1ABC123/edit\",\n auth_secret=encoded_creds,\n subject=\"user@domain.com\" # Optional: for domain-wide delegation\n)\n\n# Excel file validation (no authentication required)\nresult = execute_validation(\n check={\"id\": \"empty_cells\", \"mode\": \"fix\"},\n sheet_url=\"./data/spreadsheet.xlsx\"\n)\n\n# Excel to Google Sheets conversion with validation\nresult = execute_validation(\n check={\"id\": \"tab_names\", \"mode\": \"fix\"},\n sheet_url=\"./data/spreadsheet.xlsx\",\n auth_secret=encoded_creds,\n target=\"1hWMAXridd8Gd_ND6p8r4bGLQYZnL0b52\", # Drive folder ID\n target_format=\"sheets\"\n)\n\nprint(f\"Fixed {result['fixes_applied']} issues\")\nprint(f\"Found {result['issues_flagged']} additional issues\")\nprint(f\"Logs: {result['automated_logs']}\")\n```\n\n### Sheet Crawling & Batch Validation\n\nUrarovite 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.\n\n#### \ud83d\ude80 Super Simple Usage\n\n```bash\n# Just provide the URL - saves fixed sheets as Google Sheets in same folder as source\n./run_crawl_validation.sh \"https://docs.google.com/spreadsheets/d/1Jx5CHYvKt3y2aO-1vFKT7botQUWnvp-CcZftvFGz2pQ/edit#gid=114720924\"\n```\n\n**What this does:**\n- \u2705 Crawls through your metadata sheet\n- \u2705 Finds all input/output sheet URLs automatically\n- \u2705 Runs ALL available validations on each sheet\n- \u2705 **Saves fixed sheets as Google Sheets in the same folder as the source** (NEW DEFAULT!)\n- \u2705 **Automatically adds fixed sheet URLs back to your metadata sheet** (NEW!)\n- \u2705 Provides comprehensive results and statistics\n\n#### Command Line Options\n\n```bash\n# With domain-wide delegation\n./run_crawl_validation.sh \"https://docs.google.com/spreadsheets/d/your-sheet-id\" \"user@yourdomain.com\"\n\n# Flag mode only (no fixes applied)\n./run_crawl_validation.sh \"https://docs.google.com/spreadsheets/d/your-sheet-id\" --mode flag\n\n# Save to local Excel files instead\n./run_crawl_validation.sh \"https://docs.google.com/spreadsheets/d/your-sheet-id\" --target local --format excel\n\n# Save to specific Google Drive folder\n./run_crawl_validation.sh \"https://docs.google.com/spreadsheets/d/your-sheet-id\" --target \"1A2B3C4D5E6F7G8H9I0J\"\n```\n\n#### \ud83d\udccb New Defaults (Google-First)\n\n- **Target**: Same folder as source (intelligent default) \u2190 was \"local\"\n- **Format**: Google Sheets \u2190 was \"excel\"\n\n**Why This is Better:**\n1. **Seamless Google Workflow**: Fixed sheets stay in Google Drive where you can easily access them\n2. **Same Folder Organization**: Fixed sheets are created right next to your source sheets\n3. **No File Management**: No need to upload/download Excel files\n4. **Team Collaboration**: Everyone can access the fixed sheets immediately\n5. **Version History**: Google Sheets maintains version history of fixes\n6. **Automatic Audit Trail**: Fixed sheet URLs are automatically added to your metadata sheet\n\n#### \ud83d\udcdd New Metadata Columns\n\nAfter running the crawling script, your metadata sheet will automatically get new columns added:\n\n**For Google Sheets Output (Default):**\n- **`input_sheet_url_fixed`**: URLs to the fixed versions of input sheets\n- **`example_output_sheet_url_fixed`**: URLs to the fixed versions of output sheets\n- **`input_fixes_applied`**: Number of fixes applied to input sheets\n- **`input_issues_found`**: Number of issues found in input sheets\n- **`input_validation_summary`**: Summary of validation results for input sheets\n- **`input_validation_errors`**: Any validation errors for input sheets\n- **`output_fixes_applied`**: Number of fixes applied to output sheets\n- **`output_issues_found`**: Number of issues found in output sheets\n- **`output_validation_summary`**: Summary of validation results for output sheets\n- **`output_validation_errors`**: Any validation errors for output sheets\n\n**For Excel Output:**\n- **`input_sheet_path_fixed`**: Relative paths to the fixed Excel files for input sheets\n- **`example_output_sheet_path_fixed`**: Relative paths to the fixed Excel files for output sheets\n- **Plus all the same validator output columns as above**\n\n#### Example Results\n\n**Before:**\n| worker_id | input_sheet_url | example_output_sheet_url |\n|-----------|----------------|--------------------------|\n| ABC123 | https://docs.google.com/.../input123 | https://docs.google.com/.../output123 |\n\n**After (showing key columns):**\n| worker_id | input_sheet_url_fixed | input_fixes_applied | input_issues_found | input_validation_summary | output_sheet_url_fixed | output_fixes_applied | output_issues_found |\n|-----------|----------------------|--------------------|--------------------|-------------------------|----------------------|--------------------|--------------------|\n| ABC123 | https://docs.google.com/.../input123_fixed | 15 | 3 | \u2705 12 successful; \u274c 1 failed | https://docs.google.com/.../output123_fixed | 8 | 0 |\n\nThis creates a **complete audit trail** showing:\n- \ud83d\udd17 **Where the fixed sheets are located** (direct links)\n- \ud83d\udcca **Exactly what was fixed** (number of fixes applied)\n- \u26a0\ufe0f **What issues remain** (issues found but not fixed)\n- \u2705 **Validation success rate** (how many validators succeeded)\n- \ud83d\udea8 **Any errors encountered** (validation errors for troubleshooting)\n\n#### \ud83d\udcca Expected Output\n\n```\n\ud83d\ude80 Starting Urarovite Sheet Crawling and Validation\n==================================================\n[INFO] Metadata Sheet: https://docs.google.com/spreadsheets/d/...\n[INFO] Authentication: \u2713 Configured\n[INFO] Validation Mode: fix\n[INFO] Target: Same folder as source (intelligent default)\n[INFO] Format: sheets\n[INFO] Preserve Formatting: true\n\n\ud83d\udd0d Starting crawling and validation...\n Metadata Sheet: https://docs.google.com/spreadsheets/d/...\n Authentication: \u2713 Configured\n Validation Mode: fix\n Target: Same folder as source (intelligent default)\n Format: sheets\n\n\ud83d\udccb CRAWLING AND VALIDATION RESULTS\n==================================================\n\u2705 Overall Status: SUCCESS\n\n\ud83d\udcca Summary Statistics:\n Total Sheet Pairs: 15\n Successful Pairs: 15\n Failed Pairs: 0\n Total Input Fixes: 47\n Total Output Fixes: 23\n Total Input Issues: 12\n Total Output Issues: 8\n Total Errors: 0\n\n\u23f1\ufe0f Performance Metrics:\n Total Time: 125.30 seconds\n Crawling Time: 5.20 seconds\n Validation Time: 120.10 seconds\n Processing Rate: 0.12 pairs/second\n\n\ud83d\udcbe Output Files:\n Results JSON: ./output/crawl_validation_results_20241220_143022.json\n Processing Log: ./output/crawl_validation_20241220_143022.log\n Validated Files: Check Google Drive - fixed sheets created in source folders\n\n\ud83c\udf89 Crawling and validation completed successfully!\n Applied 70 fixes across all sheets\n Check Google Drive for the fixed sheets\n```\n\n#### \ud83c\udfaf Perfect for Batch Processing\n\nThis crawling functionality is ideal for data cleaning tasks because:\n\n1. **Batch Processing**: Processes all your input/output sheet pairs at once\n2. **Intelligent Detection**: Automatically finds sheet URLs in your metadata\n3. **Comprehensive Validation**: Runs all available validators on each sheet\n4. **Google-Native**: Keeps everything in Google Drive for easy access\n5. **Detailed Reporting**: Shows exactly what was fixed and where\n\nJust run it once and get all your sheets validated and fixed! \ud83d\ude80\n\n#### Prerequisites for Crawling\n\n```bash\n# Set your authentication (required)\nexport AUTH_SECRET=\"eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0=\"\n\n# Optional: For domain-wide delegation\nexport DELEGATION_SUBJECT=\"user@yourdomain.com\"\n```\n\n### Advanced Usage with gspread\n\n```python\nfrom urarovite.auth import get_gspread_client, create_sheets_service_from_encoded_creds\nfrom urarovite.utils.sheets import extract_sheet_id, get_sheet_values\n\n# Create gspread client (recommended)\nclient = get_gspread_client(encoded_creds, subject=\"user@domain.com\")\nspreadsheet = client.open_by_key(sheet_id)\n\n# Or create traditional Google Sheets API service\nservice = create_sheets_service_from_encoded_creds(encoded_creds)\n\n# Use utility functions\nsheet_id = extract_sheet_id(\"https://docs.google.com/spreadsheets/d/1ABC123/edit\")\ndata = get_sheet_values(service, sheet_id, \"Sheet1!A1:Z1000\")\n```\n\n## \ud83d\udd27 Migration from OAuth\n\nIf you're migrating from OAuth-based authentication:\n\n```python\n# OLD: OAuth-based authentication\nfrom urarovite.checker.auth import get_credentials, get_sheets_service\ncreds = get_credentials() # Interactive OAuth flow\nservice = get_sheets_service()\n\n# NEW: Service account with base64 credentials\nfrom urarovite.auth import create_sheets_service_from_encoded_creds\nservice = create_sheets_service_from_encoded_creds(encoded_creds)\n\n# Or use modern gspread client (recommended)\nfrom urarovite.auth import get_gspread_client\nclient = get_gspread_client(encoded_creds)\n```\n\n## \ud83d\udcda Documentation\n\n- **Migration Guide**: See [MIGRATION_SUMMARY.md](MIGRATION_SUMMARY.md) for detailed changes\n- **Validator Migration**: See [VALIDATOR_MIGRATION_GUIDE.md](VALIDATOR_MIGRATION_GUIDE.md) for validator development\n- **Spreadsheet Abstraction**: See [SPREADSHEET_ABSTRACTION_GUIDE.md](SPREADSHEET_ABSTRACTION_GUIDE.md) for multi-format support\n- **API Reference**: Full type hints and docstrings throughout the codebase\n- **Command Line Usage**: Use `./run_validation.sh` for batch validation operations\n- **Examples**: Check the `/tests` directory for comprehensive usage examples\n\n## \ud83e\uddea Testing\n\n```bash\n# Install with dev dependencies\npip install urarovite[dev]\n\n# Run tests\npytest\n\n# Run with coverage\npytest --cov=urarovite\n```\n\n## \ud83d\udda5\ufe0f Command Line Usage\n\nThe repository includes convenient shell scripts for running validations from the command line:\n\n### Single Sheet Validation (`run_validation.sh`)\n\nFor validating individual spreadsheets:\n\n### Prerequisites\n\n1. **Make the script executable** (if needed):\n ```bash\n chmod +x run_validation.sh\n ```\n\n2. **For Google Sheets validation**, create a `.env` file with your base64-encoded service account:\n ```bash\n # .env file\n AUTH_SECRET=eyJ0eXBlIjogInNlcnZpY2VfYWNjb3VudCIsIC4uLn0=\n ```\n\n### Usage Examples\n\n```bash\n# Run all validations on Google Sheets\n./run_validation.sh --all 'https://docs.google.com/spreadsheets/d/abc123'\n\n# Run all validations on local Excel file\n./run_validation.sh --all './data/spreadsheet.xlsx'\n\n# Run single validation with JSON\n./run_validation.sh --check '{\"id\": \"empty_cells\", \"mode\": \"fix\"}' 'https://docs.google.com/spreadsheets/d/abc123'\n\n# Run single validation on Excel file\n./run_validation.sh --check '{\"id\": \"tab_names\", \"mode\": \"fix\"}' './spreadsheet.xlsx'\n\n# With delegation subject (Google Sheets only)\n./run_validation.sh --all 'https://docs.google.com/spreadsheets/d/abc123' 'user@domain.com'\n\n# Load check from JSON file\necho '{\"id\": \"duplicate_rows\", \"mode\": \"flag\"}' > check.json\n./run_validation.sh --check check.json 'https://docs.google.com/spreadsheets/d/abc123'\n```\n\n### Script Options\n\n- `--all`: Run all available validation criteria\n- `--check <json_or_file>`: Run a single validation check (JSON string or file path)\n\n### Supported Input Types\n\n- **Google Sheets**: URLs containing `docs.google.com` (requires authentication)\n- **Excel Files**: Local `.xlsx` or `.xls` files (no authentication required)\n\nThe script automatically detects the input type and applies appropriate authentication requirements.\n\n### Batch Validation with Crawling (`run_crawl_validation.sh`)\n\nFor processing multiple sheets referenced in a metadata spreadsheet, see the [Sheet Crawling & Batch Validation](#sheet-crawling--batch-validation) section above. This script can automatically discover and validate all sheets in your data processing workflow.\n\n## \ud83e\udd1d Contributing\n\n1. Fork the repository\n2. Create a feature branch\n3. Install development dependencies: `pip install urarovite[dev]`\n4. Make your changes with proper type hints and tests\n5. Run tests and linting: `pytest && ruff check`\n6. Submit a pull request\n\n## \ud83d\udcc4 License\n\nThis project is licensed under the GNU General Public License v3 (GPLv3) - see the [LICENSE](LICENSE) file for details.\n\n## \ud83d\udd17 Links\n\n- **PyPI**: <https://pypi.org/project/urarovite/>\n- **GitHub**: <https://github.com/ParetoWorkers/Urarovite>\n- **Issues**: <https://github.com/ParetoWorkers/Urarovite/issues>\n",
"bugtrack_url": null,
"license": "GNU GENERAL PUBLIC LICENSE\n Version 3, 29 June 2007\n \n Copyright (c) 2024 Mo Dorff\n \n Everyone is permitted to copy and distribute verbatim copies\n of this license document, but changing it is not allowed.\n \n This program is free software: you can redistribute it and/or modify\n it under the terms of the GNU General Public License as published by\n the Free Software Foundation, either version 3 of the License, or\n (at your option) any later version.\n \n This program is distributed in the hope that it will be useful,\n but WITHOUT ANY WARRANTY; without even the implied warranty of\n MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the\n GNU General Public License for more details.\n \n You should have received a copy of the GNU General Public License\n along with this program. If not, see <https://www.gnu.org/licenses/>.",
"summary": "A Google Sheets validation library",
"version": "1.0.2",
"project_urls": {
"Changelog": "https://github.com/ParetoWorkers/Urarovite/blob/main/MIGRATION_SUMMARY.md",
"Documentation": "https://github.com/ParetoWorkers/Urarovite#readme",
"Homepage": "https://github.com/ParetoWorkers/Urarovite",
"Issues": "https://github.com/ParetoWorkers/Urarovite/issues",
"Repository": "https://github.com/ParetoWorkers/Urarovite.git"
},
"split_keywords": [
"data-quality",
" google-sheets",
" gspread",
" spreadsheets",
" validation"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "8dce8cb414188db2cba6047ae2d49a2db176bdf0d78d04cc12dafd123514e6d9",
"md5": "b1862f03a0fd2c44cf2d769d5625ec0b",
"sha256": "e68de78a8cdc688b4fa71c7b7b2c37c59fb5635a12e5638d5f6bd47739ad6203"
},
"downloads": -1,
"filename": "urarovite-1.0.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "b1862f03a0fd2c44cf2d769d5625ec0b",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 185540,
"upload_time": "2025-08-18T10:04:37",
"upload_time_iso_8601": "2025-08-18T10:04:37.653567Z",
"url": "https://files.pythonhosted.org/packages/8d/ce/8cb414188db2cba6047ae2d49a2db176bdf0d78d04cc12dafd123514e6d9/urarovite-1.0.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "661b371a8a49523290466bb733c7b4f8d685c37e4dc42c10bda1906579760813",
"md5": "992f55da5bbc92db8aa605de8e8e1ef6",
"sha256": "f93ddcdf33e90534830cdac94311b98d96b4323f324a8b9ac3478c81f4c9fa76"
},
"downloads": -1,
"filename": "urarovite-1.0.2.tar.gz",
"has_sig": false,
"md5_digest": "992f55da5bbc92db8aa605de8e8e1ef6",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 218881,
"upload_time": "2025-08-18T10:04:39",
"upload_time_iso_8601": "2025-08-18T10:04:39.305938Z",
"url": "https://files.pythonhosted.org/packages/66/1b/371a8a49523290466bb733c7b4f8d685c37e4dc42c10bda1906579760813/urarovite-1.0.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-18 10:04:39",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ParetoWorkers",
"github_project": "Urarovite",
"github_not_found": true,
"lcname": "urarovite"
}
