/***** CONFIG *****/
const SHEET_NAME = ‘Lead Scoring Sheet’; // The main scoring sheet
const LISTS_SHEET = ‘Lists’; // The sheet that holds dropdown ranges
const HEADER_ROW = 1; // Header is on row 1
// Columns (1-based indexing)
const COL_PERSONA = 3; // C
const COL_SCORE_START = 4; // D
const COL_SCORE_END = 11; // K
const COL_WEIGHTED = 12; // L
const COL_QUAL = 13; // M
const COL_STATUS = 18; // R

// Validation ranges in the ‘Lists’ sheet
const PERSONA_RANGE_A1 = “Lists!$A$2:$A”; // Personas list (column A, from row 2 down)
const STATUS_RANGE_A1 = “Lists!$B$2:$B”; // Status list (column B, from row 2 down)

/***** MENU *****/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu(‘Setup’)
.addItem(‘Backfill all rows’, ‘backfillAllRows’)
.addToUi();
}

/***** MAIN: auto-run on edits *****/
function onEdit(e) {
if (!e || !e.range || !e.range.getSheet()) return;
const sh = e.range.getSheet();
if (sh.getName() !== SHEET_NAME) return;

const row = e.range.getRow();
if (row <= HEADER_ROW) return; // Ensure validation on Persona (C) and Status (R) ensureDropdowns(sh, row); // If any of the scoring inputs (D..K) or Persona changes, set formulas for L & M const col = e.range.getColumn(); const changedScoreArea = (col >= COL_SCORE_START && col <= COL_SCORE_END) || col === COL_PERSONA; if (changedScoreArea) { setFormulasForRow(sh, row); } } /***** HELPERS *****/ function ensureDropdowns(sh, row) { // Persona dropdown in column C const personaCell = sh.getRange(row, COL_PERSONA); const personaRule = SpreadsheetApp.newDataValidation() .requireFormulaSatisfied(`=COUNTIF(${PERSONA_RANGE_A1}, C${row})`) .setAllowInvalid(false) .setHelpText('Choose a Persona from the Lists sheet') .build(); personaCell.setDataValidation( SpreadsheetApp.newDataValidation() .setAllowInvalid(false) .requireValueInRange(SpreadsheetApp.getActive().getRangeByName ? SpreadsheetApp.getActive().getRangeByName('') : SpreadsheetApp.getActive().getRange(PERSONA_RANGE_A1), true) .build() ); // Use formula-based validation fallback (works even if named range isn’t used): personaCell.setDataValidation(personaRule); // Status dropdown in column R const statusCell = sh.getRange(row, COL_STATUS); const statusRule = SpreadsheetApp.newDataValidation() .requireFormulaSatisfied(`=COUNTIF(${STATUS_RANGE_A1}, R${row})`) .setAllowInvalid(false) .setHelpText('Choose a Status from the Lists sheet') .build(); statusCell.setDataValidation( SpreadsheetApp.newDataValidation() .setAllowInvalid(false) .requireValueInRange(SpreadsheetApp.getActive().getRange(STATUS_RANGE_A1), true) .build() ); // Fallback rule as well: statusCell.setDataValidation(statusRule); } function setFormulasForRow(sh, row) { // Weighted Total (%) in L const weightedCell = sh.getRange(row, COL_WEIGHTED); const formulaWeighted = `=ROUND(((D${row}*5+E${row}*5+F${row}*4+G${row}*4+H${row}*3+I${row}*3+J${row}*2+K${row}*2)/(28*10))*100,0)`; if (!weightedCell.getFormula()) { weightedCell.setFormula(formulaWeighted); } else { // Always refresh formula to avoid “stale” references if row moved weightedCell.setFormula(formulaWeighted); } // Qualification in M const qualCell = sh.getRange(row, COL_QUAL); const formulaQual = `=IF(L${row}>=75,”HOT”,IF(L${row}>=50,”WARM”,”NURTURE”))`;
if (!qualCell.getFormula()) {
qualCell.setFormula(formulaQual);
} else {
qualCell.setFormula(formulaQual);
}
}

/***** UTILITIES *****/
function backfillAllRows() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName(SHEET_NAME);
if (!sh) throw new Error(`Sheet “${SHEET_NAME}” not found`);

const lastRow = sh.getLastRow();
if (lastRow <= HEADER_ROW) return; // Apply dropdowns + formulas to all populated rows for (let row = HEADER_ROW + 1; row <= lastRow; row++) { ensureDropdowns(sh, row); setFormulasForRow(sh, row); } SpreadsheetApp.getUi().alert('Backfill complete: dropdowns + formulas applied.'); }