Regional tax rates change; keeping them on TaxRates lets finance edit rates without touching code.
calculateTaxFromSheet builds a region→rate map, then for each line computes tax = round(net * rate, 2) and gross.
Unknown regions default to 0 rate so bad data is visible as zero tax rather than crashing.
This is illustrative — real filing may need nexus rules, exemptions, and jurisdiction hierarchies.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| TaxRates | A Region | Key e.g. CA |
| TaxRates | B Rate | 0.0725 style decimal |
| LineItems | C Net | Pre-tax amount |
| LineItems | D Region | Lookup key |
| LineItems | E RateApplied | Output |
| LineItems | F Tax | Output |
| LineItems | G Gross | Output |
What this script does
Vector-style tax computation from a rate table.
Prerequisites
TaxRates maintained by finance; LineItems with net + region.
- Rates as decimals not percents
- Rounding policy agreed
- Region codes normalized
Walkthrough
Set CA=0.0725, line net 100 region CA; expect tax 7.25 gross 107.25.
Edge cases
Math.round to cents can differ from banker's rounding — document the policy.
- Missing region → 0
- Non-numeric net → 0
How to test
Compare against a spreadsheet formula ROUND(net*rate,2).
Hardening for production
Support exemption flags; multi-jurisdiction stacked rates.
Variations
Inclusive pricing: derive net = gross/(1+rate).
Full code: calculateTaxFromSheet()
Update TaxRates, then run calculateTaxFromSheet() before issuing invoices.
/**
* Calculate sales tax from LineItems using TaxRates by region.
*/
const LINES = "LineItems";
const RATES = "TaxRates";
function calculateTaxFromSheet() {
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]).toUpperCase()] = Number(rateRows[i][1]);
}
const sheet = ss.getSheetByName(LINES);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const net = Number(values[i][2]) || 0; // amount net
const region = String(values[i][3] || "").toUpperCase();
const rate = rates.hasOwnProperty(region) ? rates[region] : 0;
const tax = Math.round(net * rate * 100) / 100;
values[i][4] = rate;
values[i][5] = tax;
values[i][6] = net + tax;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 2: Editable rate table.
- Line 20: Dictionary lookup by region code.
- Line 21: Cent rounding.
- Line 22: Writes applied rate.
- Line 24: Gross column.
- Line 26: Persists all computed columns.
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: tax calc
- 1TaxRates use decimal rates
- 2Region codes match LineItems
- 3Rounding policy documented
- 4Exemptions handled outside or via flags
- 5Run before PDF invoice generation
- 6Finance reviews sample outputs
Frequently asked questions
Store 0.0725 not 7.25. If you store percents, divide by 100 in code.
Scripts help when rates live elsewhere or you snapshot values for audit.
Compute each jurisdiction into columns F1/F2 or child rows.
Invert with net = gross/(1+rate); tax = gross-net.
This assumes one currency; convert first if multi-currency.
Add a product tax class column and look up rate by region+class.
Copy rate applied onto each line as this script does in column E.