Affiliate payouts need deterministic commission lines before finance exports.
affiliateCommissionCalc looks up each affiliateId rate and writes orderTotal × rate rounded to cents.
Missing affiliates get 0 commission rather than throwing — review zeros as data quality issues.
For tiered rates by volume, precompute the rate on AffiliateRates monthly.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| AffiliateRates | A affiliateId | Partner key |
| AffiliateRates | B rate | Decimal rate |
| AffiliateOrders | A orderId | Order |
| AffiliateOrders | B affiliateId | Partner |
| AffiliateOrders | C orderTotal | Basis |
| AffiliateOrders | D commissionOut | Output |
What this script does
Per-order commission from a rate dictionary.
Prerequisites
Rates sheet current; orders attributed.
- Rates as decimals
- Affiliate ids match
- Refunds handled upstream
Walkthrough
Rate 0.1, total 250 → commission 25.
Edge cases
Unknown affiliate → 0 commission.
- Cent rounding
- Does not net refunds
How to test
Two affiliates different rates on equal totals.
Hardening for production
Cap monthly commission; exclude discounted orders.
Variations
Flat fee plus percent; SKU-level rates.
Full code: affiliateCommissionCalc()
Update AffiliateRates, then run affiliateCommissionCalc() before payout export.
/**
* Calculate affiliate commission from orders × rate table by tier.
*/
const ORDERS = "AffiliateOrders"; // A orderId, B affiliateId, C orderTotal, D commissionOut
const RATES = "AffiliateRates"; // A affiliateId, B rate
function affiliateCommissionCalc() {
const ss = SpreadsheetApp.getActive();
const rateRows = ss.getSheetByName(RATES).getDataRange().getValues();
const rates = {};
for (let i = 1; i < rateRows.length; i++) {
rates[String(rateRows[i][0])] = Number(rateRows[i][1]) || 0;
}
const sheet = ss.getSheetByName(ORDERS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const aff = String(values[i][1]);
const total = Number(values[i][2]) || 0;
const rate = rates.hasOwnProperty(aff) ? rates[aff] : 0;
values[i][3] = Math.round(total * rate * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 5: Per-affiliate commission rates.
- Line 19: Dictionary lookup.
- Line 20: Commission basis.
- Line 4: Column D.
- Line 19: Missing affiliates → 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: affiliate commission
- 1Rates decimals correct
- 2Affiliate ids aligned with orders
- 3Refunded orders removed or negative
- 4Cent rounding accepted
- 5Payout export uses commissionOut
- 6Zero-commission rows reviewed
Frequently asked questions
Compute monthly sales per affiliate, set rate, then run this.
Separate step — this is gross commission.
Convert orderTotal to payout currency first.
Solved upstream when writing AffiliateOrders.
Sum commissionOut by affiliate and gate export.
Divide by 100 or store 0.10.
Fine for typical order volumes.