It’s 3:12 PM. You just got a Slack message from Finance: 'Can you auto-generate the Q2 commission summary using the new payout logic? It’s all in JS on our internal API docs.' You open Excel, type =JS() in A1—and get #NAME?. Your stomach drops.
The Setup
You’re working with a raw sales log pulled from your CRM—10 rows, messy formatting, inconsistent date strings, and commission rates buried in JSON-like notes. No macros. No VBA access (IT blocked it). Just Excel for the web and a tight deadline.
| Sales ID | Rep Name | Close Date | Deal Size ($) | Notes |
|---|---|---|---|---|
| SAL-7821 | Sarah Chen | 2024-04-11 | $24,500 | {"tier":"Gold","rate":0.075} |
| SAL-7822 | Diego Morales | 04/15/2024 | $18,900 | {"tier":"Silver","rate":0.05} |
| SAL-7823 | Amina Patel | 2024-04-18 | $31,200 | {"tier":"Platinum","rate":0.095} |
| SAL-7824 | James Wu | Apr 20 2024 | $14,600 | {"tier":"Bronze","rate":0.03} |
| SAL-7825 | Lena Dubois | 2024/04/22 | $29,800 | {"tier":"Gold","rate":0.075} |
| SAL-7826 | Tariq Johnson | 04/25/2024 | $36,100 | {"tier":"Platinum","rate":0.095} |
| SAL-7827 | Maya Rodriguez | 2024-04-28 | $22,400 | {"tier":"Silver","rate":0.05} |
| SAL-7828 | Kenji Tanaka | Apr 30 2024 | $41,300 | {"tier":"Platinum","rate":0.095} |
| SAL-7829 | Priya Mehta | 2024/05/02 | $19,700 | {"tier":"Gold","rate":0.075} |
| SAL-7830 | Omar Hassan | 05/05/2024 | $27,600 | {"tier":"Silver","rate":0.05} |
The Challenge
You need to calculate Commission = Deal Size × Rate, but the rate is trapped inside JSON-like strings in column E. You can’t use TEXTSPLIT reliably—it fails on escaped quotes. You can’t run VBA (no desktop app). And no, you can’t paste raw JavaScript into a cell and hit Enter.
This is where most people stop—or worse, waste hours Googling “how to run JS in Excel” and end up trying browser dev tools on Excel Online (which does nothing).
So—can you use JavaScript in Excel? Yes—but only in one place: Office Scripts, and only in Excel for the web (not desktop, not Mac, not mobile). That’s the critical detail most miss.
Walking Through It
Open Excel for the web. Go to the Automation tab → New Script. This opens the Code Editor. Paste this script:
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet();
const dataRange = sheet.getRange("A1:E11");
const values = dataRange.getValues();
// Add header for Commission
values[0].push("Commission ($)");
// Process rows 1–10 (skip header)
for (let i = 1; i < values.length; i++) {
const notes = values[i][4] as string;
try {
const parsed = JSON.parse(notes.replace(/'/g, '"'));
const rate = parseFloat(parsed.rate);
const dealSize = parseFloat(values[i][3].toString().replace(/\$/g, '').replace(/,/g, ''));
const commission = Math.round(dealSize * rate * 100) / 100;
values[i].push(commission.toFixed(2));
} catch {
values[i].push("ERROR");
}
}
// Write back
sheet.getRange(`A1:F${values.length}`).setValues(values);
}Click Run (or press Alt+R). That’s it. No add-ins. No admin approval needed if your tenant allows Office Scripts.
Before:
| Sales ID | Rep Name | Close Date | Deal Size ($) | Notes |
|---|---|---|---|---|
| SAL-7821 | Sarah Chen | 2024-04-11 | $24,500 | {"tier":"Gold","rate":0.075} |
| SAL-7822 | Diego Morales | 04/15/2024 | $18,900 | {"tier":"Silver","rate":0.05} |
After running the script:
| Sales ID | Rep Name | Close Date | Deal Size ($) | Notes | Commission ($) |
|---|---|---|---|---|---|
| SAL-7821 | Sarah Chen | 2024-04-11 | $24,500 | {"tier":"Gold","rate":0.075} | $1,837.50 |
| SAL-7822 | Diego Morales | 04/15/2024 | $18,900 | {"tier":"Silver","rate":0.05} | $945.00 |
The Result
Here’s your final table—clean, calculated, and ready to paste into your Friday 4:55 PM email:
| Sales ID | Rep Name | Close Date | Deal Size ($) | Notes | Commission ($) |
|---|---|---|---|---|---|
| SAL-7821 | Sarah Chen | 2024-04-11 | $24,500 | {"tier":"Gold","rate":0.075} | $1,837.50 |
| SAL-7822 | Diego Morales | 04/15/2024 | $18,900 | {"tier":"Silver","rate":0.05} | $945.00 |
| SAL-7823 | Amina Patel | 2024-04-18 | $31,200 | {"tier":"Platinum","rate":0.095} | $2,964.00 |
| SAL-7824 | James Wu | Apr 20 2024 | $14,600 | {"tier":"Bronze","rate":0.03} | $438.00 |
| SAL-7825 | Lena Dubois | 2024/04/22 | $29,800 | {"tier":"Gold","rate":0.075} | $2,235.00 |
| SAL-7826 | Tariq Johnson | 04/25/2024 | $36,100 | {"tier":"Platinum","rate":0.095} | $3,429.50 |
| SAL-7827 | Maya Rodriguez | 2024-04-28 | $22,400 | {"tier":"Silver","rate":0.05} | $1,120.00 |
| SAL-7828 | Kenji Tanaka | Apr 30 2024 | $41,300 | {"tier":"Platinum","rate":0.095} | $3,923.50 |
| SAL-7829 | Priya Mehta | 2024/05/02 | $19,700 | {"tier":"Gold","rate":0.075} | $1,477.50 |
| SAL-7830 | Omar Hassan | 05/05/2024 | $27,600 | {"tier":"Silver","rate":0.05} | $1,380.00 |
What Could Go Wrong
Mistake #1: Running Office Scripts in Excel Desktop
Office Scripts only work in Excel for the web. If you open the same file in Excel desktop and click Automation → New Script, the button is grayed out. No error. No warning. Just silence. That’s why checking your app version first saves 20 minutes.
Mistake #2: Assuming JSON.parse() handles single quotes
Your CRM exports notes like {'tier':'Gold','rate':0.075}. JavaScript’s JSON.parse() rejects single quotes. The fix? notes.replace(/'/g, '"') — a one-line swap before parsing. Skip it, and every row returns “ERROR”.
Mistake #3: Forgetting the tenant-level toggle
Even in Excel for the web, Office Scripts won’t appear unless your Microsoft 365 admin enabled them at the tenant level (under Settings → Org settings → Security → Scripting). If “Automation” tab is missing entirely, it’s not your fault — it’s a policy setting.
Next step: Open Excel for the web right now and test this — it takes 90 seconds. If the Automation tab is missing, forward this link to your IT team: Microsoft’s Office Scripts setup guide.
