2-Way Validation Automation

Executive Summary

The client's payroll team was spending 2 full days every month manually cross-checking HRIS data (HRIS) against Payroll inputs (Payroll Vendor) for 500+ employees. This visual "stare-and-compare" method was prone to human error and caused high stress levels during the payroll cutoff window. I automated this entire workflow.

The Challenge

The manual process was unsustainable:

• Data Volume: 500+ rows of data with 30+ columns (salary, bonus, address, tax code, etc.).
• Format Mismatch: HRIS exported reports in one format, Payroll System required a completely different structure.
• Human Error: Fatigue led to missed updates, resulting in 3-5 payroll errors per month.

The Solution

I developed a "One-Click" Validation Engine using Google Sheets and Apps Script:

1. Data Ingestion: The script automatically pulled the latest reports from specific Google Drive folders where the HRIS team dropped their files.
2. Normalization: It mapped columns from both sources to a common schema (e.g., mapping "Annual Base Pay" to "Basic Salary").
3. Logic Engine: It compared the datasets based on unique Employee IDs.
4. Exception Reporting: It generated a color-coded PDF report showing *only* the discrepancies (e.g., "Salary in HRIS is 5000, but in Payroll is 4500").