Lead scoring works when weights are explicit and capped so one noisy field cannot dominate.
leadScoreCalculation reads titleScore, pagesViewed, emailsOpened, and hasDemo, then writes a bounded score to column F.
Pages and opens are capped before weighting; demo adds a flat 25 when marked y.
Tune weights with sales — keep them in Script Properties if non-engineers need to edit.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Leads | A email | Lead id |
| Leads | B titleScore | 0–20 firmographic |
| Leads | C pagesViewed | Engagement |
| Leads | D emailsOpened | Engagement |
| Leads | E hasDemo | y/n |
| Leads | F score | Output 0–100 |
What this script does
Deterministic weighted score with caps and a demo bonus.
Prerequisites
Leads fields populated by CRM sync or forms.
- titleScore scaled 0–20
- hasDemo is y/n
- Agree weight formula
Walkthrough
Set demo y with mid engagement; confirm score near 25+components.
Edge cases
Non-numeric engagement becomes 0 — validate upstream.
- Score clamped 0–100
- Math.round to integer
How to test
Max inputs should not exceed 100 due to clamp.
Hardening for production
Version the formula in a Meta sheet; snapshot scores daily.
Variations
Negative scores for unsubscribes; decay by days since last activity.
Full code: leadScoreCalculation()
Populate Leads engagement fields, then run leadScoreCalculation() before routing.
/**
* Score leads from activity fields into a 0–100 score.
*/
const LEADS = "Leads";
function leadScoreCalculation() {
const sheet = SpreadsheetApp.getActive().getSheetByName(LEADS);
const values = sheet.getDataRange().getValues();
// A email, B titleScore(0-20), C pagesViewed, D emailsOpened, E hasDemo(y/n), F score out
for (let i = 1; i < values.length; i++) {
const title = Number(values[i][1]) || 0;
const pages = Math.min(Number(values[i][2]) || 0, 50);
const opens = Math.min(Number(values[i][3]) || 0, 20);
const demo = String(values[i][4]).toLowerCase() === "y" ? 25 : 0;
let score = title + pages * 0.5 + opens * 1.5 + demo;
score = Math.max(0, Math.min(100, Math.round(score)));
values[i][5] = score;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 9: Flat bonus when y.
- Line 17: Clamps final score.
- Line 18: Writes score column.
- Line 20: Persists all scores.
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: lead score
- 1Weights reviewed with sales
- 2titleScore scale documented
- 3hasDemo normalized to y/n
- 4Run after nightly CRM sync
- 5Routing rules use column F
- 6Sample 10 leads manually sanity-checked
Frequently asked questions
Start in code; move to a Config sheet when marketing iterates weekly.
Call this from onFormSubmit or a short trigger after sync.
Prevent a single scraped session from outranking fit signals.
Remove Math.round if you prefer decimals.
Different problem — keep scoring separate from attribution models.
Add column G with IF score>=80 Hot etc. in sheet or script.
Scoring personal data still needs a lawful basis — keep fields minimal.