Volume discounts usually unlock at quantity thresholds. Sorting rules by minQty descending makes first-match easy.
volumeDiscountRules finds the best (highest min) rule with qty ≥ min, applies percent to subtotal, and writes final.
pct is a decimal (0.05 = 5%). Store it that way on VolumeRules.
Unlike tiered unit prices, this adjusts the whole order subtotal.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| VolumeRules | A minQty | Threshold |
| VolumeRules | B discountPct | Decimal percent |
| Orders | B qty | Order qty |
| Orders | C subtotal | Pre-discount |
| Orders | D discountPctOut | Applied pct |
| Orders | E discountAmt | Money off |
| Orders | F final | Net total |
What this script does
Threshold percent discounts on order subtotals.
Prerequisites
VolumeRules mins unique; orders have qty+subtotal.
- pct decimals
- Sort handled in code
- qty basis defined (units not SKUs mixed)
Walkthrough
Rules 10→0.05, 50→0.1; qty 50 → 10% off.
Edge cases
qty below all mins → 0 discount.
- Highest min wins
- Cent rounding
How to test
qty exactly equal to a min unlocks that rule.
Hardening for production
Exclude certain SKUs from qty basis.
Variations
Discount amount caps; coupon stacking gates.
Full code: volumeDiscountRules()
Maintain VolumeRules, then run volumeDiscountRules() before checkout export.
/**
* Apply volume discount percent based on total order quantity.
*/
const RULES = "VolumeRules"; // A minQty, B discountPct
const ORDERS = "Orders"; // A orderId, B qty, C subtotal, D discountPctOut, E discountAmt, F final
function volumeDiscountRules() {
const ss = SpreadsheetApp.getActive();
const rules = ss.getSheetByName(RULES).getDataRange().getValues().slice(1)
.map(function (r) { return { min: Number(r[0]) || 0, pct: Number(r[1]) || 0 }; })
.sort(function (a, b) { return b.min - a.min; }); // highest min first
const sheet = ss.getSheetByName(ORDERS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const qty = Number(values[i][1]) || 0;
const subtotal = Number(values[i][2]) || 0;
let pct = 0;
for (let r = 0; r < rules.length; r++) {
if (qty >= rules[r].min) { pct = rules[r].pct; break; }
}
const discountAmt = Math.round(subtotal * pct * 100) / 100;
values[i][3] = pct;
values[i][4] = discountAmt;
values[i][5] = Math.round((subtotal - discountAmt) * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 11: Sorts thresholds high-to-low.
- Line 20: Unlock condition.
- Line 22: Discount amount.
- Line 5: Audit of applied percent.
- Line 5: Net after discount.
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: volume discounts
- 1Rules use decimal percents
- 2Threshold mins correct
- 3Order qty basis agreed
- 4Subtotals pre-tax or post-tax policy clear
- 5Test boundary quantities
- 6Export uses final column
Frequently asked questions
Volume rules discount the subtotal percent; tiered pricing changes unit price bands.
Compute in order currency; rules are qty-based not money-based here.
Highest matching min wins by sort — overlapping mins are OK if intentional.
Optional explicit 0 min row; otherwise default pct stays 0.
Apply in a defined order in a larger pricing pipeline.
Decide whether returns reduce qty before running.
Use 0.05; divide if marketers enter whole numbers.