Benefits deductions should come from an election table, not hardcoded plan costs in formulas scattered across columns.
benefitsDeductionCalc maps planCode → employeeCostPerPay, adds medical plan cost, and optionally DENTAL when dentalYN is y.
deductionOut is the per-paycheck employee share — employer portions stay off this sheet.
Update BenefitPlans when open enrollment rates change, then recompute.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| BenefitPlans | A planCode | e.g. MED_GOLD, DENTAL |
| BenefitPlans | B employeeCostPerPay | Per paycheck cost |
| Employees | B gross | Optional context |
| Employees | C medicalPlan | Plan code |
| Employees | D dentalYN | y/n |
| Employees | E deductionOut | Total benefits deduction |
What this script does
Election-driven per-pay benefits deduction totals.
Prerequisites
BenefitPlans rates current; employee elections entered.
- DENTAL plan code present if used
- medicalPlan matches planCode
- Cents rounding
Walkthrough
MED_GOLD 80, dental y with DENTAL 15 → deduction 95.
Edge cases
Unknown medical plan → 0 medical portion.
- dentalYN case-insensitive y
- Does not cap vs gross
How to test
Employee without dental gets medical only.
Hardening for production
Pre-tax vs post-tax flags; FSA amounts; mid-period changes.
Variations
Percent-of-gross life insurance: ded += gross * rate.
Full code: benefitsDeductionCalc()
Maintain BenefitPlans and elections, then run benefitsDeductionCalc() before net pay export.
/**
* Calculate benefits deductions from elections × employee gross.
*/
const EMP = "Employees"; // A id, B gross, C medicalPlan, D dentalYN, E deductionOut
const PLANS = "BenefitPlans"; // A planCode, B employeeCostPerPay
function benefitsDeductionCalc() {
const ss = SpreadsheetApp.getActive();
const planRows = ss.getSheetByName(PLANS).getDataRange().getValues();
const costs = {};
for (let i = 1; i < planRows.length; i++) {
costs[String(planRows[i][0])] = Number(planRows[i][1]) || 0;
}
const dentalCost = costs["DENTAL"] || 0;
const sheet = ss.getSheetByName(EMP);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const medical = String(values[i][2] || "");
const dental = String(values[i][3]).toLowerCase() === "y";
let ded = costs.hasOwnProperty(medical) ? costs[medical] : 0;
if (dental) ded += dentalCost;
values[i][4] = Math.round(ded * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 5: Per-pay employee costs by plan code.
- Line 21: Medical plan lookup.
- Line 14: Optional add-on when dentalYN is y.
- Line 4: Column E total.
- Line 21: Unknown plans contribute 0.
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: benefits deductions
- 1Open enrollment rates loaded
- 2Plan codes match elections
- 3Dental flag normalized
- 4Pre-tax treatment decided elsewhere
- 5Compare totals to broker invoice
- 6Protect BenefitPlans from casual edits
Frequently asked questions
Track separately — this writes employee deductions only.
Extend with gross × rate for those plan types.
Prorate costs or use effective-dated plan tables.
Store plan code in column D instead of y/n.
Add fixed election amounts from another column.
Sample medical/dental are flat per-pay; gross is unused context.
Yes — net = gross − tax − benefits − other.