Protecting Student Data in Educational Spreadsheets

#!/usr/bin/env python3
"""
Educational Excel Metadata Scanner
FERPA-compliant metadata audit tool for schools and districts
"""

import os
import json
import logging
from datetime import datetime
from pathlib import Path
from openpyxl import load_workbook

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - EDU_METADATA_SCAN - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('metadata_scan_results.log'),
        logging.StreamHandler()
    ]
)

# Keywords indicating student PII or sensitive educational data
SENSITIVE_KEYWORDS = [
    'ssn', 'social security', 'student id', 'date of birth', 'dob',
    'iep', 'individualized education', '504 plan', 'accommodation',
    'disability', 'diagnosis', 'medication', 'counseling',
    'discipline', 'suspension', 'expulsion', 'incident',
    'free lunch', 'reduced lunch', 'frl', 'homeless',
    'ell', 'english learner', 'immigration', 'undocumented',
    'foster', 'custody', 'abuse', 'neglect', 'cps',
    'behavioral', 'threat assessment', 'self-harm',
    'fafsa', 'financial aid', 'family income', 'efc',
    'gpa', 'class rank', 'test score', 'sat', 'act'
]

def scan_educational_spreadsheet(file_path: str) -> dict:
    """
    Scan Excel file for metadata containing student PII.
    Returns findings suitable for FERPA compliance reporting.
    """
    findings = {
        'file_path': file_path,
        'scan_time': datetime.utcnow().isoformat() + 'Z',
        'risk_level': 'LOW',
        'ferpa_concern': False,
        'findings': []
    }

    try:
        wb = load_workbook(file_path, keep_vba=True)
        props = wb.properties

        # Check document properties for PII indicators
        prop_fields = {
            'author': props.creator,
            'last_modified_by': props.lastModifiedBy,
            'company': props.company,
            'description': props.description,
            'keywords': props.keywords,
            'subject': props.subject,
            'title': props.title,
        }

        for field_name, field_value in prop_fields.items():
            if field_value:
                findings['findings'].append({
                    'type': 'document_property',
                    'field': field_name,
                    'has_value': True,
                    'preview': field_value[:30] + '...' if len(str(field_value)) > 30 else field_value
                })

        # Check for hidden sheets (common in grade books)
        for sheet in wb.worksheets:
            if sheet.sheet_state == 'hidden':
                findings['findings'].append({
                    'type': 'hidden_sheet',
                    'sheet_name': sheet.title,
                    'row_count': sheet.max_row,
                    'risk': 'May contain student data not intended for sharing'
                })
                findings['risk_level'] = 'HIGH'
                findings['ferpa_concern'] = True

        # Scan comments for sensitive student data keywords
        for sheet in wb.worksheets:
            for row in sheet.iter_rows():
                for cell in row:
                    if cell.comment:
                        comment_text = str(cell.comment.text).lower()
                        for keyword in SENSITIVE_KEYWORDS:
                            if keyword in comment_text:
                                findings['findings'].append({
                                    'type': 'sensitive_comment',
                                    'sheet': sheet.title,
                                    'cell': cell.coordinate,
                                    'keyword_match': keyword,
                                    'risk': 'FERPA-protected data in cell comment'
                                })
                                findings['risk_level'] = 'HIGH'
                                findings['ferpa_concern'] = True
                                break

        # Check for named ranges with sensitive labels
        for name in wb.defined_names.definedName:
            name_lower = str(name.name).lower()
            for keyword in SENSITIVE_KEYWORDS:
                if keyword in name_lower:
                    findings['findings'].append({
                        'type': 'sensitive_named_range',
                        'name': name.name,
                        'keyword_match': keyword
                    })
                    if findings['risk_level'] == 'LOW':
                        findings['risk_level'] = 'MEDIUM'

        if not findings['findings']:
            findings['risk_level'] = 'LOW'
        elif findings['risk_level'] == 'LOW':
            findings['risk_level'] = 'MEDIUM'

        logging.info(
            f"Scan: {file_path} | Risk: {findings['risk_level']} | "
            f"FERPA: {findings['ferpa_concern']} | "
            f"Findings: {len(findings['findings'])}"
        )

    except Exception as e:
        logging.error(f"Scan failed: {file_path}: {str(e)}")
        findings['error'] = str(e)

    return findings

def scan_school_directory(base_path: str, output_file: str) -> None:
    """Scan school/district shared drives for metadata risks."""
    all_findings = []

    for ext in ['*.xlsx', '*.xlsm', '*.xltx']:
        for file_path in Path(base_path).rglob(ext):
            result = scan_educational_spreadsheet(str(file_path))
            if result['findings']:
                all_findings.append(result)

    with open(output_file, 'w') as f:
        json.dump(all_findings, f, indent=2)

    ferpa_risks = sum(1 for f in all_findings if f['ferpa_concern'])
    logging.warning(
        f"Scan complete. Files with findings: {len(all_findings)}. "
        f"FERPA concerns: {ferpa_risks}"
    )

if __name__ == '__main__':
    scan_school_directory(
        '/data/shared/district-files',
        'metadata_audit_report.json'
    )

# Educational Excel Metadata Sanitizer
# Deploy via Group Policy for teacher workstations
# FERPA Compliance: Student PII Protection

param(
    [Parameter(Mandatory=$true)]
    [string]$FilePath,
    [switch]$AuditOnly,
    [switch]$GenerateReport
)

function Remove-StudentDataMetadata {
    param([string]$Path, [bool]$DryRun)

    $findings = @()

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    try {
        $workbook = $excel.Workbooks.Open($Path)

        # Audit and clear document properties
        $propsToClean = @('Author', 'Last Author', 'Company',
                          'Manager', 'Subject', 'Comments', 'Keywords')

        foreach ($prop in $propsToClean) {
            try {
                $value = $workbook.BuiltinDocumentProperties[$prop].Value
                if ($value -and $value -ne '') {
                    $findings += [PSCustomObject]@{
                        Type = 'DocumentProperty'
                        Detail = $prop
                        Value = $value
                        Action = if ($DryRun) { 'WOULD_REMOVE' } else { 'REMOVED' }
                    }
                    if (-not $DryRun) {
                        $workbook.BuiltinDocumentProperties[$prop].Value = ''
                    }
                }
            } catch { }
        }

        # Flag hidden sheets for review
        foreach ($sheet in $workbook.Sheets) {
            if ($sheet.Visible -ne -1) {  # Not visible
                $findings += [PSCustomObject]@{
                    Type = 'HiddenSheet'
                    Detail = $sheet.Name
                    Value = "Rows: $($sheet.UsedRange.Rows.Count)"
                    Action = 'REQUIRES_MANUAL_REVIEW'
                }
                Write-Warning "Hidden sheet: $($sheet.Name) - review for student data"
            }
        }

        # Remove all comments (may contain student observations)
        foreach ($sheet in $workbook.Sheets) {
            try {
                $comments = $sheet.Comments
                if ($comments.Count -gt 0) {
                    $findings += [PSCustomObject]@{
                        Type = 'Comments'
                        Detail = "Sheet: $($sheet.Name)"
                        Value = "$($comments.Count) comments found"
                        Action = if ($DryRun) { 'WOULD_REMOVE' } else { 'REMOVED' }
                    }
                    if (-not $DryRun) {
                        $sheet.Cells.ClearComments()
                    }
                }
            } catch { }
        }

        if (-not $DryRun) {
            $workbook.RemovePersonalInformation = $true
            $workbook.Save()
            Write-Host "Metadata sanitized: $Path" -ForegroundColor Green
        }

    } finally {
        $workbook.Close($false)
        $excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    }

    return $findings
}

$results = Remove-StudentDataMetadata -Path $FilePath -DryRun $AuditOnly.IsPresent

if ($GenerateReport) {
    $reportPath = [System.IO.Path]::ChangeExtension($FilePath, '_ferpa_audit.csv')
    $results | Export-Csv -Path $reportPath -NoTypeInformation
    Write-Host "FERPA audit report saved: $reportPath"
}