Weekly BambooHR PTO balance email report (Gmail + Zapier)

What the BambooHR time off balance report email includes

• A BambooHR report that includes the PTO/time-off balance fields you care about
• A Zapier scheduled automation that runs weekly
• A Gmail email step that sends a clean HTML table in the message body

Step 1: Create the right BambooHR report (so Zapier can pull it cleanly)

Recommended columns

• Employee name (or Employee ID)
• PTO policy (if you have multiple)
• Current balance
• As-of date (optional)
• Department or location (optional)

Report gotchas to watch for

• Column names matter. If you rename a field after building the Zap, the mapping in Zapier can break or come through blank.
• Data types can be inconsistent. Some balance fields come back as text instead of numbers, especially if the report is formatted for humans.
• Nulls happen. If someone has no policy assigned yet, expect empty values—plan your formatting to handle blanks.

Step 2: Schedule the workflow in Zapier

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger.
3. Pick the Every Week event and choose the day and time that match when HR reviews balances.

Step 3: Pull the BambooHR report data into Zapier

Option A: Use the BambooHR “API Request (Beta)” action (simplest)

1. Add a BambooHR action step and choose API Request (Beta)
2. Call BambooHR’s saved custom report endpoint with the ID of the report you created in Step 1
3. Test to confirm the result includes all expected rows and columns

Option B: Use BambooHR’s reporting endpoint via a Webhooks step (most flexible)

• Use this option when you need more control over report format, filters, or output structure.

Step 4: Convert the report rows into an HTML table (the key step)

What the HTML table should include

• A header row
• One row per employee
• Basic styling so it’s readable (padding + borders)

Formatting tips that prevent ugly emails

• Use a fixed header row (bold with a light background color).
• Keep numeric fields right-aligned for scanability.
• Round balances to 1–2 decimals (or match how your team thinks about PTO).

Example HTML table (copy/paste template)

<table style="border-collapse:collapse;width:100%;font-family:Arial,sans-serif;font-size:14px;">
  <thead>
    <tr>
      <th style="border:1px solid #ddd;padding:8px;background:#f4f6f8;text-align:left;">Employee</th>
      <th style="border:1px solid #ddd;padding:8px;background:#f4f6f8;text-align:left;">Policy</th>
      <th style="border:1px solid #ddd;padding:8px;background:#f4f6f8;text-align:right;">Balance</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td style="border:1px solid #ddd;padding:8px;">Example Name</td>
      <td style="border:1px solid #ddd;padding:8px;">PTO</td>
      <td style="border:1px solid #ddd;padding:8px;text-align:right;">40.0</td>
    </tr>
  </tbody>
</table>

Step 5: Send the email through Gmail (with the table in the body)

1. To: Your HR inbox or distribution list
2. Subject: Include the time window, for example: “PTO balances (weekly) — {{Week of}}”
3. Body: Paste the HTML output from the formatting step

Gmail-specific gotchas

• In Gmail’s Send Email action, set Body Type to HTML. If it stays on plain text, your table shows up as raw HTML code.
• Avoid complex CSS. Gmail strips style and head tags, so inline styles are the safest option.

QA checklist (run once, then you’re done)

When this breaks (and how to fix it fast)

• Someone renames a BambooHR report field: update the Zap mappings and retest.
• New PTO policies are added: confirm the report includes them and your table still looks right.
• The email suddenly shows raw HTML: check that the Gmail step’s Body Type is still set to HTML.

Need help building this workflow?