Paylocity HR2AD Sync

What happened

Every two weeks, HR would export a spreadsheet from Paylocity with all current employees. New hires, terminations, department changes, rehires. And every two weeks, someone on my team had to manually cross-reference that spreadsheet with Active Directory and make the updates by hand. It was slow, it was error-prone, and people kept slipping through the cracks.

So I started writing a PowerShell script to automate it. Version one was simple: compare two CSVs, figure out who was added and who was removed, spit out a report. That took maybe an afternoon. What I didn't realize was that this script would go through twelve more revisions over the next five months.

The edge cases never stopped

The first few versions handled the basics. Diff the old export against the new one, flag additions and removals. But then Paylocity started throwing curveballs. Some exports had duplicate header rows embedded in the data. Others had a "Report Total Records" footer row that would get parsed as an employee. Role IDs came through as 5 digits sometimes and 6 digits other times for the same person.

Version 3 added record normalization and header trimming. Version 4 handled duplicate Role IDs. Version 9 specifically dealt with that footer row. Each version was a direct response to something that broke in production.

From reports to real AD writes

For the first six versions, the script only generated reports. I wasn't about to let it touch Active Directory until I trusted it. Version 7 introduced the -Apply flag, which actually enabled AD writes. Without it, the script runs in simulation mode. That flag was critical for my sanity, because I could run it dry against real data all week and only flip the switch when I was confident.

# Dry run - just generate the report
.\Sync-PaylocityToAD.ps1 -Report "C:\Reports\Paylocity_Data_Latest.xlsx"

# Actually make AD changes
.\Sync-PaylocityToAD.ps1 -Report "C:\Reports\Paylocity_Data_Latest.xlsx" -Apply

OU routing and Google licensing

Version 11 was where it got interesting. We had two different OU structures: one for corporate employees and one for club-level staff. The script needed to figure out where to place a new user based on their Location Description from Paylocity. Corporate employees went into one OU tree, club employees into another, and each got different security group memberships.

Version 12 added Google Workspace license group assignment. When a new user was created in AD, the script would automatically add them to the right Google license group based on whether they were corporate (Enterprise Standard) or club-level (Frontline). It also started storing the employee's personal email in extensionAttribute2, so that when someone got terminated, we had a way to reach them outside the corporate email that was about to get disabled.

The final version

Version 13 was the big rewrite. Instead of comparing two CSV files, it switched to reading a single Excel export directly using the ImportExcel module. It handles the full lifecycle now: onboarding, offboarding, and rehires, all in one run. It checks if a terminated user already exists and was previously active (rehire detection), generates a multi-tab Excel report with separate sheets for new users, terminations, rehires, and errors. If the SamAccountName already exists, it appends a suffix automatically.

# Import, skip title row, normalize, drop footer
$data = Import-Excel -Path $Report -StartRow 2
$data = Normalize-Records -records $data
$data = $data | Where-Object { $_.Role -notmatch 'Report\s*Total' }

Closing the loop

Once the sync script was stable, there was still one manual step left: the Excel file had to land in the right folder before the script could run. It turned out Paylocity could deposit it automatically to an AWS Transfer Family SFTP endpoint. So I built a companion script - Paylocity_Report.ps1 - that pulls it down.

It connects via SSH key auth using Posh-SSH, downloads the export, renames it to Paylocity_Data_Latest.xlsx, archives a dated copy, and sends a confirmation email to the ops team. The whole thing runs as a scheduled task before the sync job fires.

# Step 1 - pull latest export from SFTP
.\Paylocity_Report.ps1

# Step 2 - sync to AD
.\Sync-PaylocityToAD.ps1 -Report "C:\Reports\Paylocity\Paylocity_Data_Latest.xlsx" -Apply

It's been running on a scheduled task since early 2025. Paylocity deposits the file, the download script pulls it, and the sync script processes it. HR stopped emailing me about it, which is the best possible outcome.