PTO math is a ledger: opening balance, accruals, approved usage. A script keeps the balance column authoritative.
ptoBalanceTracker sums Approved requests per employee, then writes balanceOut on PtoBalances.
Pending/denied requests are ignored so drafts do not reduce balances.
Run after HR approves requests or nightly for dashboard accuracy.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| PtoBalances | A employee | Key |
| PtoBalances | B starting | Opening balance |
| PtoBalances | C accrued | YTD accrual |
| PtoBalances | D balanceOut | Computed |
| PtoRequests | A employee | Key |
| PtoRequests | B days | Requested days |
| PtoRequests | C status | approved/pending/denied |
What this script does
Ledger-style PTO balance recompute from approved requests.
Prerequisites
Consistent employee keys across both sheets.
- Status spelling approved
- Days numeric
- Accrual updated separately
Walkthrough
Start 10, accrue 2, approve 3 → balance 9.
Edge cases
Unknown employees in requests do not create balance rows — add them to PtoBalances first.
- Case-sensitive employee names
- Round to 2 decimals
How to test
Denied request should not change balance.
Hardening for production
Separate sick vs vacation buckets with type column.
Variations
Hourly PTO with hours instead of days.
Full code: ptoBalanceTracker()
Keep accruals and approvals updated, then run ptoBalanceTracker() to refresh balances.
/**
* Maintain PTO balances: starting balance - approved requests + accruals.
*/
const BAL = "PtoBalances"; // A employee, B starting, C accrued, D balanceOut
const REQ = "PtoRequests"; // A employee, B days, C status
function ptoBalanceTracker() {
const ss = SpreadsheetApp.getActive();
const reqs = ss.getSheetByName(REQ).getDataRange().getValues();
const used = {};
for (let i = 1; i < reqs.length; i++) {
if (String(reqs[i][2]).toLowerCase() !== "approved") continue;
const emp = reqs[i][0];
used[emp] = (used[emp] || 0) + (Number(reqs[i][1]) || 0);
}
const sheet = ss.getSheetByName(BAL);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const emp = values[i][0];
const starting = Number(values[i][1]) || 0;
const accrued = Number(values[i][2]) || 0;
const balance = starting + accrued - (used[emp] || 0);
values[i][3] = Math.round(balance * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 2: Only approved requests reduce balances.
- Line 14: Aggregates days per employee.
- Line 23: Credit side of the ledger.
- Line 4: Column D result.
- Line 24: Two-decimal day balances.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: PTO balances
- 1Employee keys match exactly
- 2Accrued column current
- 3Statuses normalized to approved
- 4No double-counting historical imports
- 5Protect balanceOut from manual edits
- 6Test with one employee end-to-end
Frequently asked questions
Store 0.5 in days — Number math handles it.
Clamp balanceOut with Math.min(balance, cap).
Still count once approved; filter by date if you need as-of balances.
Add a type column and maintain separate balance sheets.
Allowed here — clamp to 0 if policy forbids borrowing.
HR only; lock the sheet range.
Publish a filtered view or employee web app reading their row.