Build a peer-nomination and voting system

Every quarter Northwind runs a peer-recognition award — anyone can nominate a colleague. The old approach was a flurry of emails to an organiser, who then counted names on a notepad. It was slow, easy to miscount, and not remotely anonymous: the organiser saw exactly who nominated whom.

A Google Form fixes both problems. Staff submit one nomination through a form, and this trigger tallies the votes live in a sheet — incrementing a count for each nominee. Crucially, it only ever stores the nominee’s name and a running total. The voter’s email is never written down, so the ballot stays anonymous and the standings update themselves.

What you’ll need

• A Google Form with a single question, Who do you nominate?, where staff type a colleague’s name. Turn off “Collect email addresses” so the form itself stores nothing identifying.
• A Google Sheet to hold the tally. The script writes the nominee name to column A and the vote count to column B; it adds rows as new nominees appear, so the sheet can start empty.
• An installable onFormSubmit trigger linking the form to the script (set up below).

The script

// The spreadsheet that holds the live vote tally.
const VOTES_SHEET_ID = '1abcVotesId';

// The exact form question text — must match the form field name.
const NOMINEE_QUESTION = 'Who do you nominate?';

/**
 * Runs on every form submission. Reads the nominated name and either
 * adds a new nominee row or increments an existing one. Voter identity
 * is never touched, so the tally stays anonymous.
 */
function onFormSubmit(e) {
  // 1. Pull the nominated name from the submission's named values.
  const answers = e.namedValues[NOMINEE_QUESTION];
  if (!answers || !answers[0]) {
    Logger.log('Submission had no nominee — skipping.');
    return;
  }
  const nominee = answers[0].trim();

  // 2. Read the current tally so we can find this nominee, if present.
  const sheet = SpreadsheetApp.openById(VOTES_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const row = values.findIndex((r) => r[0] === nominee);

  // 3. New nominee — append a row starting at one vote.
  if (row === -1) {
    sheet.appendRow([nominee, 1]);
    Logger.log('First vote for ' + nominee + '.');
    return;
  }

  // 4. Existing nominee — bump the count in column B by one.
  const newCount = values[row][1] + 1;
  sheet.getRange(row + 1, 2).setValue(newCount);
  Logger.log(nominee + ' now has ' + newCount + ' votes.');
}

How it works

1. onFormSubmit receives the event object e. It reads e.namedValues, keyed by the question text, and pulls the first answer — the nominated name. If the question was left blank, it logs and stops.
2. It opens the votes sheet and reads the whole tally into memory, then uses findIndex to see whether this nominee already has a row.
3. If findIndex returns -1, the nominee is new, so appendRow adds them with a starting count of one.
4. If the nominee already exists, it reads their current count, adds one, and writes the new total back to column B of that row.
5. At no point does the script touch the voter’s email or the submission timestamp — only the nominee name and count ever reach the sheet.

Example run

The sheet starts empty. Three nominations come in: Priya, Tom, then Priya again. After the third submission the sheet reads:

Nominee (A)	Votes (B)
Priya Anand	2
Tom Becker	1

The execution log shows First vote for Priya Anand., First vote for Tom Becker., then Priya Anand now has 2 votes. — and nothing about who cast those votes.

Trigger it

The form submission is the trigger, but it must be installed manually so the script can write to the sheet:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger. Choose onFormSubmit, event source From form, event type On form submit.
3. Save and approve the authorisation prompt.
4. Submit a test nomination and confirm a row appears in the votes sheet.

Watch out for

• Name spelling has to match. “Tom Becker” and “Tom becker” become two separate rows because findIndex compares strings exactly. A dropdown question on the form, rather than free text, removes this problem entirely.
• Concurrent submissions can collide. If two people vote within the same instant, both may read the same count and one increment is lost. For a busy ballot, wrap the read-modify-write in LockService.getScriptLock().
• Anonymity depends on the form too. If “Collect email addresses” is on, the form-linked response sheet still records who submitted — turn it off at the form, not just in the script.
• One person can submit the form repeatedly. If you need one ballot per voter, enable “Limit to 1 response” on the form, which does require sign-in and so trades a little anonymity for integrity.
• The tally is unsorted — nominees appear in first-vote order. Sort column B descending when you want to read the standings.