Pivot your Google Forms 360 reviews by person, not submission

Build me an app for running our 360 review cycle. We collect the reviews with a Google Form: each rater picks the colleague they are reviewing from a question on the form, scores them on a handful of competencies, and leaves written comments. Google Forms hands that back as a flat list of submissions, and what I actually need is a view per person being reviewed. The pivot by subject is the entire point of this app.

Start by reading the form itself. Use Google Forms Get Form on our review form to pull its full structure, and from the items work out which question identifies the review subject (the one where the rater picks the colleague they are reviewing), which questions are competency ratings, and which are free text comment questions. Then use Google Forms List Responses to pull every submission, following the page token until you have all of them. Group the responses by the answer to the subject question so each employee ends up with a bundle of responses about them. Let me confirm or correct the question mapping in a settings screen in case the form wording is ambiguous.

Google Sheets is the backbone for everything the form cannot tell you, read with Google Sheets Get Values. A Roster tab lists everyone in the review cycle with their email, their team, and their manager's email. A Raters tab lists, for each person being reviewed, the raters expected to submit feedback about them, with each rater's email and their relationship to the subject such as peer, manager, or report. A Cycle tab holds the cycle name, the form ID, the open and close dates, and the cycle status. That expected rater list is what makes completion tracking possible: without it we would know how many people responded but not how many were supposed to.

The main screen is the cycle overview: one row per employee in the roster showing their name and team, how many of their expected raters have submitted out of the total expected, a completion bar, and their average score on each competency once enough feedback is in. Let me sort and filter by completion so I can immediately see who is behind. Show the cycle name, the close date, and overall cycle completion at the top.

Clicking a person opens their page. Show their score on each competency along with the spread across raters, because a 4.0 where every rater said 4 means something very different from a 4.0 that averages a 2 and a 6. Give me the average, the range or distribution, and the number of raters behind each competency. Below that, list every written comment about them grouped by the question it answered, with rater identity completely stripped: no names, no emails, no response IDs, no submission timestamps that could be matched back to a person, and no ordering that mirrors the rater list. Also show how many of their expected raters are still outstanding.

Bake in an anonymity floor and treat it as the hard rule of the app. Until at least three raters have submitted about a person, do not display their individual comments, their per competency scores, or any development summary. Show only the completion state: how many expected raters have submitted and how many are missing. Never show rater names anywhere in the interface, on any screen, in any export, or in any generated text. Make the floor a single configurable number so we can raise it if our policy changes.

Add a "Nudge missing raters" action on a person's page. Compare the expected raters in the Raters tab against the responses received to work out who has not submitted, then use Gmail Create a Draft to prepare a reminder email to each of them, naming the colleague they still owe feedback on, linking to the form, and mentioning the cycle close date from the Cycle tab. Create drafts rather than sending, so whoever runs the cycle can read and edit before anything goes out. Because knowing who has not submitted also implies who has, restrict the missing rater list to HR admins, keep it behind the nudge action rather than showing it beside the feedback, and never surface it to a manager viewing their reports.

Add a "Draft development summary" button on each person's page that fires a background agent. The agent reads all the feedback about that one employee, the competency scores and the written comments together, and writes a manager-ready narrative: what they are consistently strong at, where the feedback points to growth, and where raters disagreed with each other. The agent must never attribute anything to an identifiable rater: no names, no phrasing like "their manager said", no quoting a comment so distinctive it gives away the author, and no breakdown by rater relationship thin enough to single someone out. Paraphrase themes, and only call something a pattern when more than one rater raised it. When it finishes, save the summary into the app so it renders on the person's page, and write it back to that person's row in the sheet with Google Sheets Update Values, using Append Values to add the row if that person does not have a summary row yet. Show the agent's progress in the app while it runs, and disable the button entirely for anyone below the anonymity floor.

Two roles. HR admins see the whole cycle: every person, every completion bar, the missing rater lists, and both actions. A manager sees only their own reports, matched on the manager email column in the Roster tab, with the rest of the cycle scoped out of their data entirely rather than merely hidden in the UI. Managers get the anonymity floor and the stripped comments just like everyone else. Read the role assignment from the sheet or from the signed in user's email so we can maintain it without editing the app.

Add a refresh that re-pulls the form responses and the sheet, and show when the data was last pulled. A single cycle is at most a few hundred submissions, so a full re-pull on refresh is fine.