Marketing codes should live in a sheet, not hardcoded ifs. DiscountRules drives percent and fixed discounts with a minimum subtotal gate.
applyDiscountRules sorts rules by priority, finds the code on each order, and computes discount then final = max(0, subtotal - discount).
Rounding to cents happens after the math so sheet currency formats stay clean.
Stacking multiple codes is omitted on purpose — extend by looping allowed codes if you need combinations.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| DiscountRules | A code | Discount code |
| DiscountRules | B percentOff | e.g. 0.1 for 10% |
| DiscountRules | C fixedOff | Flat amount |
| DiscountRules | D minSubtotal | Eligibility floor |
| DiscountRules | E priority | Sort order |
| Orders | B subtotal | Input |
| Orders | C code | Input |
| Orders | D discount | Output |
| Orders | E final | Output |
What this script does
Rule-table driven discount application onto Orders.
Prerequisites
DiscountRules and Orders populated; percent as decimal.
- Unique codes preferred
- Priority understood
- minSubtotal enforced
Walkthrough
Create SAVE10 = 0.1 percent, order 50 with code; expect discount 5 final 45.
Edge cases
Unknown codes leave discount 0 and final = subtotal.
- final floored at 0
- percent + fixed combined
How to test
Order below minSubtotal should not discount.
Hardening for production
Record rule version on the order row for audit.
Variations
First-order only flags via customer sheet lookup.
Full code: applyDiscountRules()
Edit DiscountRules, then run applyDiscountRules() before charging or invoicing.
/**
* Apply stacked discount rules: percent off, then fixed credit, min floor.
*/
const RULES = "DiscountRules";
const ORDERS = "Orders";
function applyDiscountRules() {
const ss = SpreadsheetApp.getActive();
const ruleValues = ss.getSheetByName(RULES).getDataRange().getValues();
// Rules: A code, B percentOff, C fixedOff, D minSubtotal, E priority
const rules = ruleValues.slice(1).map(function (r) {
return {
code: r[0],
percentOff: Number(r[1]) || 0,
fixedOff: Number(r[2]) || 0,
minSubtotal: Number(r[3]) || 0,
priority: Number(r[4]) || 0,
};
}).sort(function (a, b) { return a.priority - b.priority; });
const sheet = ss.getSheetByName(ORDERS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const code = values[i][2];
const subtotal = Number(values[i][1]) || 0;
const rule = rules.filter(function (r) { return r.code === code; })[0];
let discount = 0;
let final = subtotal;
if (rule && subtotal >= rule.minSubtotal) {
discount = subtotal * rule.percentOff + rule.fixedOff;
final = Math.max(0, subtotal - discount);
discount = Math.round(discount * 100) / 100;
final = Math.round(final * 100) / 100;
}
values[i][3] = discount;
values[i][4] = final;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 10: Lower numbers apply first when multiple matches exist.
- Line 10: Eligibility gate.
- Line 10: Decimal percent of subtotal.
- Line 10: Additional flat reduction.
- Line 31: Prevents negative totals.
- Line 35: Discount output column.
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: discount rules
- 1percentOff stored as decimals
- 2Codes on Orders match Rules
- 3minSubtotal cases tested
- 4Stacking policy documented
- 5Cent rounding accepted by finance
- 6Run after cart subtotals finalize
Frequently asked questions
This sample adds both. Change to if/else if you want mutually exclusive types.
Yes as written — normalize with toUpperCase on both sides if needed.
Split codes and apply in priority order with a running subtotal.
Prepares for multi-match logic; with unique codes it is harmless.
Add a rule type column and branch math.
Add start/end date columns and filter rules by today.
Index rules by code in an object instead of filter() per row for large N.