Build a 360-degree feedback collector

360-degree reviews only work if people answer honestly, and people only answer honestly if they trust the process is anonymous. Northwind wants every teammate’s manager, peers, and reports to weigh in — but the moment a reviewer suspects their name is attached to a comment, the feedback turns bland and useless.

This automation keeps the raw answers and the running totals apart. A shared Form collects the reviews; the raw responses land on a restricted sheet that only HR can open. Meanwhile this trigger maintains a separate Aggregates sheet that holds counts only — never the comments — so the people being reviewed can see how many responses they have without ever seeing who said what.

What you’ll need

• A shared Google Form with at least three questions, titled exactly: Who are you reviewing, Strengths, and Areas to improve.
• The Form’s linked responses sheet — keep this one restricted to HR. The raw, attributable answers live here.
• A separate spreadsheet for the rollup, with a first tab carrying the headers subject, strengthsCount, improveCount. This is the safe-to-share sheet.

The script

The aggregator runs on form submit. Bind this script to the Form (or to its response sheet) so the onFormSubmit trigger has somewhere to attach.

// The non-sensitive rollup spreadsheet — counts only, safe to share.
const AGGREGATES_SHEET_ID = '1abcAggregatesId';

// The Form question whose answer identifies the person being reviewed.
const SUBJECT_QUESTION = 'Who are you reviewing';

/**
 * Form-submit trigger. Increments the running counts for the reviewed
 * teammate on the Aggregates sheet — without ever copying the comments.
 *
 * @param {Object} e  The form-submit event Apps Script passes in.
 */
function onFormSubmit(e) {
  // 1. Bail out if the event or the subject answer is missing.
  if (!e || !e.namedValues || !e.namedValues[SUBJECT_QUESTION]) {
    Logger.log('No subject in submission — nothing to aggregate.');
    return;
  }

  // 2. Read who this review is about.
  const subject = e.namedValues[SUBJECT_QUESTION][0];

  // 3. Open the Aggregates sheet and read its current rows.
  const sheet = SpreadsheetApp.openById(AGGREGATES_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // 4. Find an existing row for this subject (column A).
  const row = values.findIndex((r) => r[0] === subject);

  if (row === -1) {
    // 5a. First review for this subject — start their counts at 1.
    sheet.appendRow([subject, 1, 1]);
  } else {
    // 5b. Existing subject — bump both counts and write the row back.
    values[row][1]++;
    values[row][2]++;
    sheet.getRange(row + 1, 1, 1, 3).setValues([values[row]]);
  }

  Logger.log('Aggregated a review for ' + subject + '.');
}

How it works

1. onFormSubmit first checks the event and the Who are you reviewing answer exist — a malformed submission is logged and ignored rather than throwing.
2. It pulls the subject’s name out of e.namedValues, which maps each Form question title to its answer.
3. It opens the Aggregates spreadsheet — the counts-only one, deliberately separate from the raw responses — and reads every existing row.
4. It searches column A for a row already belonging to this subject.
5. If there is no row yet, it appends a new one with both counts at 1. If a row exists, it increments strengthsCount and improveCount and writes the row back in place.

Crucially, the comment text in Strengths and Areas to improve is never copied here. Only counts move to the shareable sheet; the comments stay on the restricted response sheet.

Example run

Three colleagues submit reviews for Priya Shah. After the first submission, the Aggregates sheet reads:

subject	strengthsCount	improveCount
Priya Shah	1	1

After the third:

subject	strengthsCount	improveCount
Priya Shah	3	3

Priya — and her manager — can open this sheet and see that three reviews are in, without any way to read the comments or work out who wrote them. The actual feedback stays on the HR-only response sheet.

Trigger it

This must run on a form-submit trigger, not a schedule:

1. In the bound script’s editor, open Triggers (the clock icon).
2. Add a trigger for onFormSubmit, choose event source From form (or From spreadsheet if bound to the response sheet), and event type On form submit.
3. Approve the authorisation prompt the first time.

A simple onFormSubmit will not have the permissions to open another spreadsheet, so the installable trigger above is required.

Watch out for

• Anonymity depends on access control, not on this script. Lock the raw response sheet down to HR only — if reviewers can open it, the whole exercise collapses.
• strengthsCount and improveCount always move together here, so they are effectively the same number. If you want them to differ — for example, counting only non-blank answers — read each field and increment conditionally.
• Concurrent submissions can race. If two reviews land at once, both may read the same starting count and one increment is lost. Wrap the read-modify-write in LockService to make it safe.
• The script matches subjects by an exact string. If the Form lets reviewers type a name freely, “Priya Shah” and “Priya Shah” become two rows. Use a dropdown of fixed names instead.
• Very small review groups erode anonymity on their own. If only one person reviews a teammate, the count of 1 plus the comment effectively identifies the author — set a minimum number of reviewers before sharing anything.