Volume tiers (1–9, 10–49, 50+) belong in a sheet so sales can edit bands without code deploys.
tieredPricingLookup finds the first band where qty is between min and max (blank max = Infinity).
Unit price and line total write to QuoteLines; unmatched qty leaves blanks.
Keep tiers non-overlapping and sorted by minQty for clarity.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| PriceTiers | A minQty | Inclusive min |
| PriceTiers | B maxQty | Inclusive max (blank = open) |
| PriceTiers | C unitPrice | Price in band |
| QuoteLines | B qty | Input qty |
| QuoteLines | C unitPriceOut | Output |
| QuoteLines | D lineTotal | Output |
What this script does
Quantity-band price resolution for quote lines.
Prerequisites
Non-overlapping tiers; quote qtys filled.
- Blank max means unlimited
- First match wins
- SKU-specific tiers need extra key
Walkthrough
Tier 10–49 price 8; qty 12 → unit 8 total 96.
Edge cases
Overlapping tiers — first row wins; clean the table.
- Unmatched → blank
- Cent rounding on total
How to test
qty equal to max boundary included.
Hardening for production
Per-SKU tiers via sku column on both sheets.
Variations
Graduated pricing (marginal) instead of all-units tier.
Full code: tieredPricingLookup()
Edit PriceTiers bands, then run tieredPricingLookup() when building quotes.
/**
* Look up unit price from quantity tiers.
*/
const TIERS = "PriceTiers"; // A minQty, B maxQty, C unitPrice
const LINES = "QuoteLines"; // A sku, B qty, C unitPriceOut, D lineTotal
function tieredPricingLookup() {
const ss = SpreadsheetApp.getActive();
const tiers = ss.getSheetByName(TIERS).getDataRange().getValues().slice(1).map(function (r) {
return { min: Number(r[0]) || 0, max: r[1] === "" || r[1] == null ? Infinity : Number(r[1]), price: Number(r[2]) || 0 };
});
const sheet = ss.getSheetByName(LINES);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const qty = Number(values[i][1]) || 0;
let unit = "";
for (let t = 0; t < tiers.length; t++) {
if (qty >= tiers[t].min && qty <= tiers[t].max) {
unit = tiers[t].price;
break;
}
}
values[i][2] = unit;
values[i][3] = unit === "" ? "" : Math.round(unit * qty * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 10: Open-ended top tier when max blank.
- Line 21: First matching tier wins.
- Line 25: Line total.
- Line 5: Column C.
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: tiered pricing
- 1Tiers non-overlapping
- 2max blank only on top tier
- 3Boundaries inclusive understood
- 4Quote qtys numeric
- 5Currency formatting on outputs
- 6Test edge quantities on boundaries
Frequently asked questions
This is all-units: entire qty gets the band price.
Filter tiers by sku before matching.
Store prices in one currency; convert afterward if needed.
No band matched — extend tiers or fix qty.
Supported if bands use decimal mins/maxes.
Write overrides to another column and prefer them if set.
Linear scan of few tiers is trivial.