Payroll Generator

Overview

The Payroll Generator is a web-based tool that automates payroll processing for home health care agencies. It connects to HHA Exchange, downloads visit and schedule data, generates color-coded payroll reports, writes results directly to your Google Sheets master spreadsheet, and can automatically adjust caregiver schedules.

Getting Started

Account Setup

The Payroll Generator uses individual accounts — each user has their own login with their own Google Sheet and HHA Exchange credentials stored separately.

1. Go to the application URL and click "Sign Up"
2. Enter your email address and choose a password
3. Log in with your new credentials

Navigation

After login you will see a tabbed dashboard with three sections:

1. Scheduler — Configure and run your Patient Payroll automation and Schedule Bot
2. Manual Upload — Upload HHA report files directly without needing stored credentials
3. Settings — Set your notification email and save your HHA Exchange credentials

Functionality

Patient Payroll Generator

The Patient Payroll Generator is the application's core automation. It runs the entire payroll pipeline end-to-end:

1. Logs into HHA Exchange and downloads all required reports
2. Fetches your existing Google Sheets master spreadsheet
3. Generates payroll with color-coded cells
4. Writes results directly back to the correct weekly tab in your spreadsheet
5. Sends you an email notification when complete

This can run manually (click "Run Now") or on an automatic schedule (daily or weekly). To use Run Now or scheduling, your HHA credentials and Google Sheet URL must be configured first.

Before using the Patient Payroll Generator, configure three things:

1. Google Sheet URL — The share link of your master spreadsheet. See Setting Up Your Master Sheet below.
2. HHA Exchange Credentials — Your HHA Exchange username and password, saved in the Settings tab. Credentials are encrypted before storage.
3. Notification Email (optional) — The address where success and failure emails are sent, configured in Settings.

How It Writes to Google Sheets

The automation is smart about merging data into your spreadsheet:

• Auto-detects the current week from the visit report dates and finds or creates the matching weekly tab (e.g., 2/3-2/9)
• Only updates completed days — if today is Wednesday, only Monday and Tuesday columns are written. Columns for days still in progress are left untouched.
• Fuzzy name matching connects patient names between HHA data and your spreadsheet, handling minor spelling differences automatically
• Adds new patients alphabetically into the sheet if they are not already present

Manual Upload

The Manual Upload tab lets you generate a payroll Excel file by uploading HHA report files directly — no stored HHA credentials needed.

File	Description
Visit Report	Contains visit data — dates, caregivers, times, and visit status
Schedule Mismatch Report	Covers patient shifts with alternative name formats
Call Maintenance Report (EVV)	Flags EVV exceptions such as out-of-window visits and GPS issues
Prebilling Review	Flags authorization issues with a light red highlight
Comparison of Hours Report	Populates the authorized hours column (B) for each patient

Click "Generate Patient Payroll" and the color-coded Excel file will download automatically.

Schedule Bot

The Schedule Bot logs into HHA Exchange and automatically adjusts caregiver schedules to match actual clock-in and clock-out times. This saves you from manually editing schedules in HHA Exchange each week.

Like the Patient Payroll Generator, the Schedule Bot supports a daily or weekly schedule with the option to "Run Now" at any time. HHA credentials must be saved in Settings to use it.

The bot applies these rules when correcting a schedule:

• Actual times are always rounded to the nearest quarter hour first. The scheduled "In" time is then always updated to match the rounded actual "In" time.
  Example: Scheduled 7:00AM–2:00PM, Actual 7:08AM–2:00PM → Rounded actual in: 7:15AM → New schedule: 7:15AM–2:15PM
• If the caregiver worked at least the scheduled hours, the original scheduled duration is preserved — the "Out" time is shifted forward from the new rounded "In" time to maintain that same duration.
  Example: Scheduled 7:00AM–2:00PM (7 hrs), Actual 7:08AM–2:30PM → Rounded actual in: 7:15AM → New schedule: 7:15AM–2:15PM (7 hrs kept)
• If the caregiver worked fewer than the scheduled hours, both "In" and "Out" are updated to the rounded actual times.
  Example: Scheduled 7:00AM–2:00PM (7 hrs), Actual 7:08AM–12:00PM → Rounded actual: 7:15AM–12:00PM (4 hrs 45 min) → New schedule: 7:15AM–12:00PM

Setting Up Your Master Sheet

The Patient Payroll Generator reads from and writes to a Google Sheets spreadsheet you own. A template is available to get you started quickly.

1. Copy the template — click the "Click here to copy the template" link shown in the Patient Payroll setup card on your dashboard. This creates a copy in your Google Drive.
2. Share the sheet with the service account — open your new copy, click Share (top-right), and add the service account email shown on the dashboard as an Editor. This allows the automation to read and write your sheet.
3. Copy the sheet link — while the Share dialog is still open, click the Copy link button at the bottom to copy the shareable URL to your clipboard.
4. Click the edit (pencil) icon on the Patient Payroll card, paste the URL, and save.

The sheet must follow this column structure:

Column	Content
A	Patient Name
B	Authorized Hours
C – I	Monday through Sunday (daily hours)
J	Total (SUM formula)
K	Difference (Total minus Authorized Hours)
L	Caregiver name(s)

Weekly tabs should be named in the format 2/3-2/9 (the Monday and Sunday of that week). The automation will find the correct tab based on this naming format, or create one if it does not exist yet.

Color Legend

Each daily cell in the payroll output is color-coded to indicate the status of that patient's visit for that day.

Color	Meaning	Action Needed
Green	Normal hours — visit completed, times match schedule within 7 minutes	None
Cyan	Caregiver worked 8 or more minutes over scheduled hours	Review — may need timesheet if it causes the patient to go over weekly authorized hours
Yellow	EVV exception — out-of-window, GPS issue, or missing clock-in/out	Review — check the call dashboard to link the shift; once confirmed, change to red (unless you linked an out-of-window)
Red	Incomplete visit — missing clock-out or no EVV data	Review the visit and request a timesheet from the caregiver
Pink	Authorization issue flagged in Prebilling Review	Check patient authorization (requires Prebilling file upload)
Gray	No service scheduled for this day	None — informational only

Timesheet Confirmation

We recommend users to use orange cells to indicate that a timesheet has been received for that day from the caregiver.

To confirm a timesheet, verify it meets all of the following criteria:

• Written in ink
• Minimum of 5 tasks recorded for the day
• Correct date and shift times written
• Patient's signature with date
• Caregiver's signature with date
• Location's full address

Once confirmed, change the cell's font color to yellow. This gives you a clear visual system at a glance:

• Red or Cyan cell — visit still needs a timesheet from the caregiver
• Orange cell, default font — timesheet received, pending confirmation
• Orange cell, yellow font — timesheet confirmed

Orange cells are always preserved during automated payroll runs. If the generator has not yet processed a given day, any orange cell you have applied will remain intact after the run completes.

Maintenance

New Year Sheet Rotation

At the start of each year, create a new Google Sheets spreadsheet for the new year and repeat the setup process:

1. Create a new spreadsheet (or copy the template again)
2. Share it with the service account email as an Editor
3. Update the Google Sheet URL in the Patient Payroll card on your dashboard
4. Click "Run Now" to confirm everything works before relying on the schedule

HHA Credentials

If your HHA Exchange password changes, update your stored credentials in the Settings tab. The automation will immediately start using the new credentials on its next run. Stored credentials are encrypted and are only used by your account's scheduled tasks.