Sigma Linehaul Payroll Automation (Google Sheets + Apps Script + Drive) OVERVIEW We run weekly payroll for a linehaul operation with two companies: * Sigma Transport – BING (ADP Company Code T3C) * Sigma Transport – LEHI (ADP Company Code T43) We need a senior Google Apps Script engineer to build a stable automation system that cuts weekly payroll admin time to less than 15 minutes hands-on. This is not a toy spreadsheet script — it must be reliable, idempotent, and fast. TARGET OUTCOME Weekly hands-on time must be limited to: 1. select pay period end date 2. drop source files into Drive folders 3. click Stage buttons 4. download ADP CSV exports 5. upload to ADP Everything else is automated: foldering, ingestion, report generation scaffolding, exceptions, scorecards, emailing, logging. SCOPE (3 STAGES) STAGE 1 — Folder creation + Active Employee ingestion + Weekly Activity population Folder-drop approach (required). For each pay period end date YYYY-MM-DD, the system must create or verify this Drive structure: Sigma Linehaul Payroll/YYYY/YYYY-MM-DD/ 01 Payroll/BING/ 01 Payroll/LEHI/ 02 Settlements/BING/ 02 Settlements/LEHI/ 03 Outputs/Driver Scorecards/ Active Employee Reports are dropped weekly into: * 01 Payroll/BING/EPIT3CAA.csv * 01 Payroll/LEHI/EPIT43AA.csv Stage 1 must: * detect those files by name * import them into raw tabs (locked or protected) * create clean rosters BING and LEHI * populate Weekly Activity driver grids * log status and errors Critical reliability: schema drift must hard-stop by default (with a one-time override toggle). STAGE 2 — Settlement ingestion to Reports workbook to ADP Export tabs to CSV export to email managers Settlement CSV and PDF files will be dropped into: * 02 Settlements/BING/ * 02 Settlements/LEHI/ Stage 2 must: * copy a Linehaul Reports template into 03 Outputs * load raw settlement data * generate ADP Export tabs for each company (format described below) * export Drive CSV files: * ADP Export - BING - YYYY-MM-DD.csv * ADP Export - LEHI - YYYY-MM-DD.csv * exceptions: * settlement driver not found in roster goes to Exceptions tab and is excluded from exports * email managers upon completion with links and exception count STAGE 3 — Driver scorecards + email drivers (batched + resumable) Stage 3 must: * generate scorecard PDFs per active driver * save in 03 Outputs/Driver Scorecards/ * email drivers using email address from Active Employee report * send in batches (quota-safe) and support resume ADP EXPORT FORMAT (IMPORTANT — READ THIS CAREFULLY) This is a multi-slot CSV format (not one row per earning code). Company code goes in Column A. Pay codes (Earnings 3 Code): * MIL = Mileage Earnings * SPT = Spot Earnings * GBN = General Bonus * SAF = Safety and Service Adjust and Ded codes: * ADV = Advance * REI = Reimbursement Export must pack codes into the next available Earnings and Deduction slots. UI REQUIREMENTS (BEST UX) Deliver as a control-plane app inside Google Sheets: * Custom menu: Payroll Automation * Sidebar Control Panel with: * pay period end date picker * Preflight validation (check folders and required files) * Stage 1 / Stage 2 / Stage 3 run buttons * Email managers / Email drivers buttons * Status display and output links * Logs shortcut (RUN_LOG and ERRORS) ENGINEERING REQUIREMENTS (NON-NEGOTIABLE) Performance: * No cell-by-cell writes for large datasets * Use bulk setValues * Parse CSV in memory * Avoid formula-heavy sheet designs Reliability: * Stages must be idempotent (safe to re-run) * Use LockService to prevent double runs * Hard-stop schema validation by default plus one-time override switch * Full logging: * RUN_LOG (stage, action, runtime) * ERRORS (message, raw context) DELIVERABLES 1. Working Apps Script project bound to Control Spreadsheet 2. Folder-drop automation implemented 3. Stage 1/2/3 working end-to-end on real data 4. ADP CSV exports import-ready 5. Exception reporting implemented 6. Manager email automation (Stage 2) 7. Driver email automation (Stage 3) 8. Documentation: setup + weekly workflow + troubleshooting MILESTONES (FIXED ACCEPTANCE CRITERIA) Milestone 1 (25 percent) — Control plane + Stage 1 stable * Sidebar UI + pay period selection * Folder creation * EPI file detection + import * Roster build + Weekly Activity population * Schema validation + logging Milestone 2 (40 percent) — Stage 2 stable + ADP exports * Settlement ingest * Reports template copy + raw load * Exceptions logic * ADP Export tab generation + CSV exports to Drive * Manager email with links + exception counts Milestone 3 (25 percent) — Stage 3 scorecards + driver emails * Scorecard PDFs created and saved correctly * Batched/resumable driver emails Milestone 4 (10 percent) — Hardening + documentation * Performance pass * Idempotency verified * Setup + runbook delivered ESTIMATED EFFORT If you’re competent, this is 30–50 hours for MVP and 60–90 hours for full production quality with robust scorecards and hardening. If you claim you can do it in 5–10 hours, you’re not a fit. REQUIRED SKILLS * Advanced Google Apps Script * Large dataset Sheets automation (performance optimized) * Drive folder and file automation * CSV parsing and output generation * GmailApp automation * Strong debugging and logging discipline TO APPLY (MUST INCLUDE) 1. 2–3 examples of prior Apps Script automation projects (links or descriptions) 2. Your approach to performance optimization in Apps Script 3. How you would implement idempotency + schema drift protection 4. Estimated hours for Stage 2 settlement parsing + ADP export packing (separately) SCREENING QUESTION (REQUIRED) In 5–10 sentences, explain how you would: * parse weekly settlement CSVs, * map drivers reliably, * generate ADP exports in multi-slot format, * and prevent slowdowns as data grows. https://docs.google.com/spreadsheets/d/1d4DlBYgqpOLtXAIV9ONxYB6K4AO1SqZtvEdWamTxOsM/edit?usp=sharing