Build a weighted scoring and ranking model

When Northwind has to choose between three tools, four vendors, or a backlog of features, the decision usually gets made in a meeting where the loudest opinion wins. Nobody writes down what mattered, so the next decision starts from scratch and the one after that contradicts it.

A weighted scoring model fixes that by making the trade-offs explicit. You list the options, score each one against a fixed set of criteria, and decide once how much each criterion is worth. This script multiplies the scores by the weights, totals them, and writes a ranked table — so the output is a number you can defend, not a hunch you have to argue.

What you’ll need

• An Options sheet with a header row. Column A holds the option name, and every column after it is one criterion (for example Price, Support, Integrations). Each cell holds a raw score for that option on that criterion — use a consistent scale, such as 1 to 5.
• A Weights sheet with two columns: criterion and weight. The criterion names must match the Options header text exactly. Weights can be any numbers — they do not have to add up to 100.
• Nothing else — the script creates the Ranked tab itself.

The script

// The spreadsheet that holds the Options and Weights tabs.
const SCORING_SHEET_ID = '1abcScoringSheetId';

// Tab names. Change these if your sheet uses different labels.
const OPTIONS_TAB = 'Options';
const WEIGHTS_TAB = 'Weights';
const RANKED_TAB = 'Ranked';

/**
 * Reads the Options and Weights tabs, computes a weighted total for each
 * option, and writes a ranked table to the Ranked tab.
 */
function rankOptions() {
  const ss = SpreadsheetApp.openById(SCORING_SHEET_ID);

  // 1. Read both tabs in full, including their header rows.
  const optionData = ss.getSheetByName(OPTIONS_TAB).getDataRange().getValues();
  const weightData = ss.getSheetByName(WEIGHTS_TAB).getDataRange().getValues();

  // 2. Split the Options header from its data rows.
  const [optionHeader, ...rows] = optionData;
  if (!rows.length) {
    Logger.log('No options to rank — nothing to do.');
    return;
  }

  // 3. Turn the Weights tab (minus its header) into a criterion -> weight
  //    lookup, so a column header can find its weight in one step.
  const weights = Object.fromEntries(weightData.slice(1));

  // 4. Score every option. For each criterion column, multiply the raw
  //    score by that criterion's weight and add it to the running total.
  const scored = rows.map((row) => {
    let total = 0;
    for (let col = 1; col < optionHeader.length; col++) {
      const score = parseFloat(row[col]) || 0;
      const weight = parseFloat(weights[optionHeader[col]]) || 0;
      total += score * weight;
    }
    return { name: row[0], score: total };
  });

  // 5. Sort highest score first, so the winner sits at the top.
  scored.sort((a, b) => b.score - a.score);

  // 6. Rebuild the Ranked tab from scratch on every run.
  const out = ss.getSheetByName(RANKED_TAB) || ss.insertSheet(RANKED_TAB);
  out.clear();
  out.getRange(1, 1, 1, 3).setValues([['Rank', 'Name', 'Score']]);
  out.getRange(2, 1, scored.length, 3).setValues(
    scored.map((option, index) => [index + 1, option.name, option.score])
  );
  Logger.log('Ranked ' + scored.length + ' options.');
}

How it works

1. rankOptions opens the scoring spreadsheet and reads the Options and Weights tabs in full with getDataRange.
2. It separates the Options header row from the data rows. If there are no data rows, it logs a message and stops.
3. It builds a weights lookup from the Weights tab — Object.fromEntries turns the [criterion, weight] pairs into an object keyed by criterion name.
4. For each option, it walks every criterion column (column 1 onward, since column 0 is the name), multiplies the raw score by the matching weight, and sums the results into a single total. parseFloat(...) || 0 keeps a blank or stray text cell from breaking the maths.
5. It sorts the scored options highest-first.
6. It rebuilds the Ranked tab and writes one row per option, with a rank number, the name, and the weighted score.

Example run

Say the Options tab holds three vendors scored 1 to 5:

name	Price	Support	Integrations
Vendor A	5	2	3
Vendor B	3	5	4
Vendor C	4	4	2

And the Weights tab says support matters most:

criterion	weight
Price	2
Support	5
Integrations	3

After a run, the Ranked tab holds:

Rank	Name	Score
1	Vendor B	41
2	Vendor C	34
3	Vendor A	29

Vendor A has the best price but loses overall, because support is weighted heavily and Vendor B scores top marks there. That is the trade-off the model makes visible.

Trigger it

This is cheap to run, so the simplest setup is to recompute the ranking whenever a score or weight changes. Add an installable onEdit trigger:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose rankOptions, event source From spreadsheet, event type On edit.

If you would rather not recompute on every keystroke, skip the trigger and run rankOptions by hand from the editor, or add a custom menu so editors can trigger it from the spreadsheet.

Watch out for

• Criterion names must match exactly. A weight for Support (with a trailing space) or support (lower case) will not line up with the Options header, and that criterion silently contributes zero. Keep the labels identical.
• A missing weight counts as zero, not one. If you add a criterion column to Options but forget to add a row to Weights, that criterion is ignored entirely — the ranking will still produce a number, just not the one you expect.
• Use one consistent scale for the raw scores. Mixing a 1-to-5 column with a 1-to-100 column lets the larger scale dominate before any weighting happens.
• Scores are only as good as your weights. The model does not decide what matters — it just makes your decision explicit and repeatable. Agree the weights before you score, not after you see who is winning.
• The Ranked tab is rebuilt from scratch every run, so do not add notes or extra columns there — they will be wiped. Keep working notes on another tab.