Won deals need a single reliable handoff email, not a spammy stream on every sheet edit.
dealWonNotification finds Won rows with empty notifiedAt, sends MailApp to finance@, CCs ownerEmail, then stamps notifiedAt.
Re-running is safe — stamped rows are skipped.
Change the finance address and consider Chat webhooks for faster ops channels.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Deals | A id | Deal id |
| Deals | B name | Deal name |
| Deals | C stage | Must be Won |
| Deals | D amount | Value in email |
| Deals | E ownerEmail | CC |
| Deals | F notifiedAt | Dedupe stamp |
What this script does
One-time won email with notifiedAt guard.
Prerequisites
MailApp auth; finance inbox; owner emails populated.
- Stage exactly Won
- notifiedAt blank for new wins
- Trigger after pipeline updates
Walkthrough
Set a deal to Won with blank F; run; check inbox and stamp.
Edge cases
Clearing notifiedAt re-sends — protect the column.
- cc omitted if owner blank
- Amount not formatted as currency
How to test
Second run sends nothing.
Hardening for production
Log to Notifications sheet; use LockService under concurrent triggers.
Variations
Post JSON to Slack/Chat instead of email.
Full code: dealWonNotification()
Authorize MailApp, set finance address, run after stages update (or on a short trigger).
/**
* When stage becomes Won, email finance and stamp notifiedAt.
*/
const DEALS = "Deals";
// A id, B name, C stage, D amount, E ownerEmail, F notifiedAt
function dealWonNotification() {
const sheet = SpreadsheetApp.getActive().getSheetByName(DEALS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
if (values[i][2] !== "Won") continue;
if (values[i][5]) continue; // already notified
const id = values[i][0];
const name = values[i][1];
const amount = values[i][3];
const owner = values[i][4];
MailApp.sendEmail({
to: "finance@example.com",
cc: owner || undefined,
subject: "Deal won: " + id + " — " + name,
body: "Deal " + id + " (" + name + ") marked Won.\nAmount: " + amount + "\nOwner: " + owner,
});
values[i][5] = new Date();
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 11: Only won deals are considered.
- Line 12: notifiedAt dedupe stamp.
- Line 17: Sends finance alert.
- Line 19: Copies the deal owner when present.
- Line 25: Persists notifiedAt timestamps.
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: deal won mail
- 1finance@ address correct
- 2MailApp authorized
- 3Stage spelling is Won
- 4Owner emails valid
- 5notifiedAt column protected
- 6Test with one deal on a copy
Frequently asked questions
Batch is simpler and avoids mailing mid-edit; onEdit works if you carefully detect stage transitions.
Add htmlBody with a formatted table of deal fields.
Comma-separate to addresses or use a group.
Date objects display in spreadsheet timezone.
Check spam and MailApp quota; log sends to a sheet.
Clearing stage does not clear notifiedAt — decide policy explicitly.
Generate a PDF quote and pass attachments: [blob].