Free-text categories break reporting. An allowlist with canonical casing keeps pivots clean.
expenseCategoryValidation maps lowercase inputs to ALLOWED labels, flags missing/invalid, and sets OK when valid.
Existing non-error statuses are left alone once valid so workflow states beyond OK can coexist — adjust if you need stricter resets.
Move ALLOWED to a Categories sheet when finance owns the list.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Expenses | A id | Expense id |
| Expenses | B category | Validated / canonicalized |
| Expenses | C amount | Amount |
| Expenses | D status | OK / MISSING / INVALID |
| ALLOWED | Travel, Meals, … | In-code allowlist |
What this script does
Allowlist validation with canonical casing and status flags.
Prerequisites
Expenses sheet; agreed category list.
- Trim inputs
- Decide status vocabulary
- Run before reimbursement export
Walkthrough
Enter meals → becomes Meals + OK; enter Foo → INVALID_CATEGORY.
Edge cases
Other is allowed — remove it if too broad.
- Case-insensitive match
- Blank → MISSING_CATEGORY
How to test
Mix of valid, invalid, blank rows.
Hardening for production
Read allowlist from a sheet; notify submitter on INVALID.
Variations
Per-department allowlists.
Full code: expenseCategoryValidation()
Edit ALLOWED, then run expenseCategoryValidation() before finance review.
/**
* Validate expense categories against an allowlist; flag bad rows.
*/
const EXP = "Expenses"; // A id, B category, C amount, D status
const ALLOWED = ["Travel", "Meals", "Software", "Office", "Other"];
function expenseCategoryValidation() {
const sheet = SpreadsheetApp.getActive().getSheetByName(EXP);
const values = sheet.getDataRange().getValues();
const allow = {};
ALLOWED.forEach(function (c) { allow[c.toLowerCase()] = c; });
for (let i = 1; i < values.length; i++) {
const raw = String(values[i][1] || "").trim();
const key = raw.toLowerCase();
if (!raw) {
values[i][3] = "MISSING_CATEGORY";
} else if (!allow[key]) {
values[i][3] = "INVALID_CATEGORY";
} else {
values[i][1] = allow[key]; // canonicalize casing
if (values[i][3] === "MISSING_CATEGORY" || values[i][3] === "INVALID_CATEGORY") {
values[i][3] = "OK";
} else if (!values[i][3]) {
values[i][3] = "OK";
}
}
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 5: Canonical category names.
- Line 18: Lowercase lookup map.
- Line 21: Writes proper casing back to column B.
- Line 17: Blank category flag.
- Line 19: Not in allowlist.
- Line 23: Valid row status.
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: expense validation
- 1Allowlist matches policy
- 2Status column free for flags
- 3Submitters know valid categories
- 4Run before AP export
- 5Decide whether to overwrite other statuses
- 6Sample invalid rows reviewed
Frequently asked questions
Read Categories!A:A into the allow object instead of the const array.
Map food→Meals in a synonym dictionary before lookup.
Use both — UI prevention plus script for imports.
So APPROVED rows do not regress to OK unexpectedly — tighten if undesired.
Normalize to codes (TRAVEL) and store display labels separately.
Add checks for amount > 0 and currency columns.
Possible per cell; batch is better after CSV import.