About

We are looking for an experienced freelancer to design and build a streamlined, semi-automated system to manage our employee payroll, PTO tracking, and bonuses. While our employees track their hours in a separate CRM, we need this Google Sheet to act as the central hub for manually entering those hours, tracking PTO, and processing payroll. The goal is to move away from purely manual tracking and create an efficient workflow that saves time and reduces errors. The core of the system should be a master Google Sheet. The most critical part of this project is building an automated workflow for employee PTO requests. Core Workflow Vision: An employee submits a time-off request via a simple web form (like a Google Form) or a Slack integration. The request is automatically sent to a manager for approval (via email or Slack DM). Once approved, the employee's PTO balance in the master Google Sheet is automatically updated to reflect the deduction. Bonuses calculated in our separate "Bonus Calculator Sheet" are automatically pulled into the master payroll sheet, ready for processing. Key Deliverables & Scope of Work: 1. Master Google Sheet (The "Backend") A clean, well-organized Google Sheet with interconnected tabs. Employee Database Tab: A simple roster (Name, Email, Hire Date, PTO Accrual Rate, Role, Salary/Hourly Rate). PTO Tracking Tab: A dashboard showing each employee's: Total PTO accrued (based on their accrual rate and time with the company). PTO taken (pulled automatically from approved requests). Current remaining PTO balance. Payroll Submission Tab: A tab designed for easy manual entry of hours worked for each pay period (since this data comes from our separate CRM). This tab will be our central place to finalize payroll and should automatically pull in: Approved PTO hours (to be paid). Any bonuses (from the Bonus Data Tab). It should calculate total gross pay for each employee. Bonus Data Tab: This tab will automatically import or connect to our existing "Bonus Calculator Sheet." It will act as the data source for bonuses, which then feeds into the Payroll tab for the correct pay period. (Please specify how you would achieve this connection.) PTO Request Log: A tab that logs all submitted PTO requests (Date, Employee, Dates Requested, Status: Pending/Approved/Denied). 2. Automated PTO Request System (The "Frontend") Employee-Facing Form: Option A (Preferred): A simple, user-friendly web form (e.g., Google Form, Tally, or similar) where employees can select dates, type of leave (PTO, Sick, etc.), and add a note. Option B (Nice-to-Have): A Slack integration (e.g., a slash command or app) to submit requests directly from Slack. Approval Workflow: When a form is submitted, a manager (or specific person) must receive an email or Slack message with "Approve" and "Deny" buttons. Automation (The Magic): This is the most important part. You must use Google Apps Script (or a tool like Zapier/Make) to connect the form, the approval, and the spreadsheet. When a manager clicks "Approve," the script must: Find the correct employee in the "PTO Tracking Tab." Deduct the requested hours/days from their balance. Mark the request as "Approved" in the "PTO Request Log." 3. Handover & Documentation A brief video walkthrough or written guide explaining how to: Add or remove an employee. Manually adjust a PTO balance (if needed). Run the payroll process.