Compare each Google Forms survey wave against the last

I run the same surveys in Google Forms every quarter, a staff pulse and a post purchase CSAT, and the built in response summary cannot compare one round against the last or break results down by team. Build me an insight console I open at the end of each wave, so I can see what moved, who is dragging the average, and what people actually wrote.

Set up: a Google Sheet is the store for everything that has to survive between waves. Use three tabs. A Waves tab holds each wave definition (wave name, form id, start timestamp, end timestamp, status of open or closed, and the readout Doc link once one exists). A Codebook tab holds the themes (theme id, theme name, short definition, example phrases, status of active, merged or proposed, and a merged-into column). A History tab holds the computed results of every closed wave (wave name, question id, question text, metric, value, response count, segment question, segment value, theme id, theme count). Read these with Get Values and Batch Get Values, write new rows with Append Values, and correct existing rows with Update Values.

Reading the survey: use Get Form to pull the form definition so the app knows every question's exact wording and type, and can classify questions as rating scales, choice questions usable as segments, and open text. Use List Responses to pull submissions, passing the wave's start and end timestamps as the filter so each wave is sliced cleanly, and following the page token until there are no more pages so a wave with tens of thousands of responses still loads completely. Use Get Response when the app needs to open a single submission behind a quote.

Main view, the headline trend. Show the current wave's headline scores as large numbers with the change against the previous wave, and plot each score across all previous waves as a trend line. Read the historical points from the History tab rather than recomputing old waves from the form every time, so the view opens fast and old numbers never quietly shift. Only the current wave is computed live from the form. Show the response count beside each score, and if the current wave is still open, label its numbers as provisional.

Second view, the cross tab. Let me pick any rating question and nominate any choice question as the segment, such as department, region or plan tier. Show a table of one row per segment value with the average score, the distribution, the response count, and the gap against the overall average, sorted so the group dragging the score sits at the top. Add the same breakdown for the previous wave beside it so I can see whether a group is improving or sliding. Suppress any segment row where the group is smaller than a configurable minimum (default five) and render it as hidden because the group is too small to be anonymous, never as a real number and never as zero.

Third view, the open text. Show the comment themes for the current wave as rows with the theme name, the count, the share of comments, and the change against the previous wave. Every theme expands to the verbatim quotes coded to it, and each quote links back to the individual submission it came from. This is a hard rule: never show a theme without its underlying quotes available underneath it. A theme with no quotes attached is a bug, not a row.

Add a Build this wave's readout button on the wave. It starts a background agent that does the whole end of wave job. The agent reads the Codebook tab with Get Values, pulls the wave's responses with List Responses using the timestamp filter and page token, codes every open text answer against the existing themes, and flags genuinely new patterns as proposed themes with their supporting quotes rather than silently inventing categories. It picks a few representative quotes per theme, computes the headline scores, the wave over wave changes and the notable cross tab findings, then appends the computed results and the theme counts to the History tab with Append Values and appends any proposed themes to the Codebook tab for a human to accept.

The same agent then creates the written readout with Create Document and formats it with Batch Update Document: a headline section with the scores and the trend, a section on the cross tab findings naming the groups that moved, and a themes section with each theme, its count and its representative quotes. It writes the Doc link back into the Waves tab with Update Values so the app shows a Readout link on that wave from then on, and posts the headline numbers plus the Doc link into Slack with Send a Message. Suppression applies to the Doc and the Slack post too, so a group too small to show in the app is never named in the readout either.

Keep the codebook editable by hand. A researcher should be able to open the spreadsheet, rename a theme, merge two themes by pointing one at another, or retire one, and the console picks up their version on the next load with history carried forward under the surviving theme. The agent may propose additions, but it never rewrites or reorders the codebook wholesale. That is what keeps wave over wave comparison honest instead of the themes being reinvented every round.

Also give me a small wave manager where I can define a new wave (name, form, start and end timestamps), close a wave once the field period ends, and see the list of past waves with their response counts and readout links.