Google Sheets Validation

Interface

Primary Script: validate-state.sh

Required Parameters:

• --spreadsheet-id: Google Sheets spreadsheet ID
• --sheet-name: Name of sheet to validate

Optional Parameters:

• --check: Specific validation check to run: schema, data, formulas, all (default: all)
• --api-key: Google Sheets API key (or use GOOGLE_API_KEY env var)
• --verbose: Enable detailed validation reporting
• --output-format: json, report, brief (default: json)

Usage:

# Validate all aspects of sheet
./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "abc123def456" \
  --sheet-name "Operations"

# Validate schema only
./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "abc123def456" \
  --sheet-name "Operations" \
  --check schema

# Validate with detailed reporting
./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "abc123def456" \
  --sheet-name "Operations" \
  --verbose \
  --output-format report

Validation Rules

Schema Validation

Checks:

1. Sheet exists - Named sheet is present in spreadsheet
2. Header row present - First row contains column headers
3. Column count consistent - All rows have same number of columns
4. Header uniqueness - No duplicate column names
5. Data types correct - Columns match expected types

{
  "check": "schema",
  "passed": true,
  "details": {
    "sheet_exists": true,
    "header_row_present": true,
    "column_count": 5,
    "columns": ["id", "name", "value", "status", "timestamp"],
    "data_type_matches": true
  }
}

Data Validation

Checks:

1. Data present - Sheet contains at least 1 data row
2. No empty required fields - Required columns have values
3. Data format correctness - Values match expected formats
4. Row count within limits - Sheet hasn't exceeded size limits
5. Uniqueness constraints - No duplicate primary keys
6. Referential integrity - Foreign keys reference valid rows

{
  "check": "data",
  "passed": true,
  "details": {
    "row_count": 100,
    "rows_with_errors": 0,
    "empty_fields_found": 0,
    "format_errors": 0,
    "referential_integrity_errors": 0,
    "sample_rows": [{...}]
  }
}

Formula Validation

Checks:

1. Formula syntax correct - All formulas parse without errors
2. Cell references valid - Formulas reference existing cells
3. No circular references - Formulas don't create loops
4. Calculations accurate - Results match expected calculations
5. Error cells - No #ERROR, #REF!, #DIV/0! values
6. Range references valid - Array formulas reference correct ranges

{
  "check": "formulas",
  "passed": true,
  "details": {
    "formula_count": 12,
    "syntax_errors": 0,
    "reference_errors": 0,
    "circular_references": 0,
    "error_cells": [],
    "formulas": [{"cell": "D2", "formula": "=SUM(A2:C2)", "valid": true}]
  }
}

Output Format

JSON structure for validation results:

{
  "success": true,
  "confidence": 0.96,
  "validation_timestamp": "2025-11-18T10:30:00Z",
  "spreadsheet_id": "abc123def456",
  "sheet_name": "Operations",
  "validations": {
    "schema": {
      "passed": true,
      "errors": [],
      "warnings": []
    },
    "data": {
      "passed": true,
      "errors": [],
      "warnings": []
    },
    "formulas": {
      "passed": true,
      "errors": [],
      "warnings": []
    }
  },
  "overall_status": "valid",
  "error_count": 0,
  "warning_count": 0,
  "deliverables": ["validation_report.json"],
  "errors": []
}

Error Messages

Schema Errors

ERROR: Sheet 'Operations' not found in spreadsheet
ERROR: Header row missing in sheet
ERROR: Column count mismatch: row 5 has 4 columns, expected 5
ERROR: Duplicate column name 'status' found
ERROR: Data type mismatch in column 'timestamp': expected date, got text

Data Errors

ERROR: No data rows found in sheet
ERROR: Empty required field in column 'id', row 5
ERROR: Date format error in column 'created_at', row 12: "invalid-date"
ERROR: Duplicate primary key value '42' in rows 5 and 12
ERROR: Foreign key reference invalid: row 8 references non-existent user_id '999'

Formula Errors

ERROR: Syntax error in cell D2: "=SUM(A2:C2" missing closing parenthesis
ERROR: Cell reference error in E5: references deleted column 'old_column'
ERROR: Circular reference detected: C2 → D2 → C2
ERROR: Error value in cell F3: #DIV/0! (division by zero)
ERROR: Invalid range in array formula L2: "={A1:B}" malformed range

Integration with CFN Loop

Loop 3 Agents (Implementers)

After each operation phase completes:

# Validate schema after creation
VALIDATION=$(./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "$SHEET_ID" \
  --sheet-name "Operations" \
  --check schema)

PASSED=$(echo "$VALIDATION" | jq -r '.validations.schema.passed')
if [ "$PASSED" = "true" ]; then
  echo "Schema validation passed, proceeding to data population"
else
  ERRORS=$(echo "$VALIDATION" | jq -r '.validations.schema.errors[]')
  echo "Schema validation failed: $ERRORS"
  exit 1
fi

Loop 2 Validators

Comprehensive validation of completed work:

# Run full validation on agent deliverables
VALIDATION=$(./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "$SHEET_ID" \
  --sheet-name "Operations" \
  --verbose \
  --output-format report)

OVERALL_STATUS=$(echo "$VALIDATION" | jq -r '.overall_status')
ERROR_COUNT=$(echo "$VALIDATION" | jq -r '.error_count')

if [ "$OVERALL_STATUS" = "valid" ] && [ "$ERROR_COUNT" -eq 0 ]; then
  echo "Validation passed with 0.96 confidence"
else
  echo "Validation failed: $ERROR_COUNT errors found"
fi

Product Owner Decision

Use validation results to inform go/no-go decision:

# Get validation results
VALIDATION=$(./.claude/cfn-extras/skills/google-sheets-validation/validate-state.sh \
  --spreadsheet-id "$SHEET_ID" \
  --sheet-name "Operations")

if [ "$(echo "$VALIDATION" | jq -r '.success')" = "true" ]; then
  echo "PROCEED - All validations passed"
else
  echo "ITERATE - Validation failures require fixes"
fi

Success Criteria

• Pass rate: ≥0.95 (standard mode)
• Validation time: <2000ms for typical spreadsheets
• Error detection: 0 false negatives (catches all real issues)
• False positive rate: <0.05 (max 5% of validation failures invalid)
• API reliability: 0 timeouts, graceful rate limit handling

Configuration

Environment Variables

export GOOGLE_API_KEY="your-api-key-here"
export GOOGLE_SHEETS_QUOTA_LIMIT=100  # Requests per minute
export VALIDATION_TIMEOUT_MS=5000      # Max validation time

Rate Limiting

The skill implements rate limiting to respect Google Sheets API quotas:

# Automatic rate limiting with exponential backoff
# Quota: 60 requests per minute per user
# Delays: 100ms initial, doubles on quota exceed (max 5s)

Best Practices

1. Validate early: Run validation after each phase completes
2. Use specific checks: Run only needed validations (--check schema)
3. Capture results: Store validation JSON for audit trails
4. Handle errors gracefully: Use validation errors to inform retry logic
5. Log everything: Enable --verbose for troubleshooting

Anti-Patterns

❌ Skipping validation - Assuming upstream operations are correct ❌ Ignoring warnings - Warnings often indicate data quality issues ❌ One-time validation - Validate after each phase, not just at end ❌ No error context - Implement proper error handling and logging ❌ API quota ignorance - Don't exceed Google Sheets API limits

Testing

Comprehensive test suite included:

# Run all validation tests
./.claude/cfn-extras/skills/google-sheets-validation/test.sh

# Run specific test category
./.claude/cfn-extras/skills/google-sheets-validation/test.sh --category schema

# Validate skill itself
./.claude/cfn-extras/skills/google-sheets-validation/validate.sh

Test Categories

1. Schema validation tests - Header presence, column consistency
2. Data validation tests - Format correctness, referential integrity
3. Formula validation tests - Syntax, circular references, calculations
4. Error handling tests - Missing sheets, malformed data, API errors
5. Performance tests - Execution time under load

References

• Google Sheets API: https://developers.google.com/sheets/api
• Data Integrity Patterns: .claude/skills/cfn-defense-in-depth/SKILL.md
• CFN Loop Validation: .claude/skills/cfn-loop-validation/SKILL.md
• Agent Output Standards: docs/AGENT_OUTPUT_STANDARDS.md