NPS = %promoters − %detractors using 0–10 scores (9–10 promoters, 7–8 passives, 0–6 detractors).
npsScoreAggregation scans NpsResponses and writes counts plus the rounded NPS integer to NpsSummary.
Empty surveys yield blank nps rather than 0 to avoid fake zeros.
Filter RESP by date in a staging sheet if you need period-specific scores.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| NpsResponses | A date | Response date |
| NpsResponses | B score | 0–10 |
| NpsResponses | C segment | Optional segment |
| NpsSummary | metric/value | Rollup output |
What this script does
Standard NPS rollup to a summary sheet.
Prerequisites
Scores in 0–10; responses sheet populated.
- Non-numeric skipped
- Summary disposable
- Period filter upstream if needed
Walkthrough
Ten 10s → NPS 100; mix 10s and 0s to see math.
Edge cases
Scores outside 0–10 still classify by thresholds — validate on entry.
- n blank → nps blank
- Passives affect denominator
How to test
3 promoters, 1 passive, 1 detractor → NPS 40.
Hardening for production
Segment breakouts by column C into multiple summary blocks.
Variations
Rolling 90-day window using date filter.
Full code: npsScoreAggregation()
Collect 0–10 scores on NpsResponses, then run npsScoreAggregation().
/**
* Aggregate NPS responses into promoter/passive/detractor counts and score.
*/
const RESP = "NpsResponses"; // A date, B score(0-10), C segment
const OUT = "NpsSummary";
function npsScoreAggregation() {
const ss = SpreadsheetApp.getActive();
const values = ss.getSheetByName(RESP).getDataRange().getValues();
let promoters = 0, passives = 0, detractors = 0;
for (let i = 1; i < values.length; i++) {
const s = Number(values[i][1]);
if (isNaN(s)) continue;
if (s >= 9) promoters++;
else if (s >= 7) passives++;
else detractors++;
}
const n = promoters + passives + detractors;
const score = n === 0 ? "" : Math.round(((promoters - detractors) / n) * 100);
const sheet = ss.getSheetByName(OUT) || ss.insertSheet(OUT);
sheet.clearContents();
sheet.getRange(1, 1, 6, 2).setValues([
["metric", "value"],
["responses", n],
["promoters", promoters],
["passives", passives],
["detractors", detractors],
["nps", score],
]);
}- Line 14: Promoter bucket.
- Line 15: Passive bucket.
- Line 10: Scores 0–6.
- Line 19: Core NPS formula.
- Line 5: Rollup destination.
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: NPS aggregate
- 1Scores are 0–10 integers
- 2Duplicates removed if multi-submit
- 3Period scope correct
- 4NpsSummary ok to clear
- 5Dashboard points at summary
- 6Validate with a known fixture
Frequently asked questions
They sit in the denominator but not numerator — yes they affect the score.
Common for dashboards; keep unrounded if you prefer.
Filter rows by segment before tallying or group in a loop.
Different scale/math — do not mix.
Weight by MRR if enterprise responses should count more.
Skipped via isNaN.
You can compute NPS there too; sheet summary helps Sheets-native charts.