Capture feedback for pivot table analysis
Every survey response lands on the Responses tab with score, category and comment, ready for a pivot table.
New submission on your feedback or survey form
Add a row to the Responses tab with the score, the product category and the comment
One-directional. A submission triggers the action — nothing is written back into your form.
Feedback is only worth collecting if someone can see the pattern in it. This flow writes each response into the Responses tab as a score, a category and a comment, which is the shape a pivot table wants — average score by product, updated as answers arrive.
Product teams set it up so the weekly review runs off a live tab rather than a CSV somebody exported on Monday. The comments sit beside the numbers, so a low score can be read rather than guessed at.
Setting it up
- 1
Publish the survey with "Which of our products or services did you use?" as a select question. A free-text answer is what turns one product into four pivot rows later on.
- 2
Create a Responses tab with the headers Received, Email, Score, Category, Detail, Reason, Improvement, and format Score as a number before any data lands in it.
- 3
In Zapier, choose formformform with the New Submission event, connect your account, select the survey form and load a test response.
- 4
Add the Google Sheets Create Spreadsheet Row action, connect your Google account and select the Responses worksheet.
- 5
Map the 0-10 recommendation question into Score, adding a Zapier Formatter Numbers step in front of it if the answer arrives with any text attached, so the column stays numeric.
- 6
Map "Which of our products or services did you use?" into Category and "Please name the specific product or service (if applicable)" into Detail, so the pivot groups on the fixed list and the free text stays beside it.
- 7
Map the two open questions into Reason and Improvement, then insert a pivot table on its own tab with the source set to Responses!A:G, Category as rows and AVERAGE of Score as values.
- 8
Test with two responses on different products, confirm both categories appear in the pivot with the right averages, then switch the Zap on.
What maps where
Using the Net Promoter Score Survey as the starting point. These are its real fields — swap in your own and the mapping works the same way.
| Form field | Google Sheets |
|---|---|
| On a scale of 0–10, how likely are you to recommend us to a friend or colleague? | Score column, formatted as a number for the pivot |
| Which of our products or services did you use? | Category column used as the pivot row |
| Please name the specific product or service (if applicable) | Detail column beside the category |
| What's the main reason for your score? | Reason column read alongside the score |
| What could we do to improve your experience? | Improvement column for the backlog review |
| Email Address | Email column, for following up on a low score |
Variations worth knowing
Fill Received from a Zapier Formatter Date/Time step, then add a column holding =ARRAYFORMULA(IF(A2:A="","",EOMONTH(A2:A,0))) and use it as a second pivot dimension. Average score by product by month is one drag away, and a chart built on the pivot redraws itself as rows arrive.
A tab holding =QUERY(Responses!A:G,"select A,B,C,D,F where C <= 6 order by A desc") gives you the responses worth a reply, newest first, with the reason attached. The Responses tab stays the complete record; the follow-up tab is a working list that empties itself as scores improve.
If something isn't arriving
Google Sheets received the score as text, which happens when the column was formatted after the first rows landed. Set Score to a plain number format, convert the existing cells with =VALUE(), and add a Formatter Numbers step in the Zap so later rows arrive clean.
Near-identical answers group separately: a typed product name, a trailing space, a different capitalisation. Make the category question a select field on the form so answers come from a fixed list, and add a lookup tab mapping the old variants onto one canonical name.
Frequently asked questions
Do I still need to export responses from formformform?
Not for this analysis. Rows arrive as people answer, so the pivot is current whenever you open it. Exports are still useful for a full archive or for a one-off pull of everything collected before the Zap existed.
What happens to responses collected before I switched the Zap on?
They are not backfilled. The trigger only fires on submissions made after the Zap goes live. Export the earlier responses from formformform and paste them above the automated rows, keeping the same column order so the pivot picks them up too.
Can I calculate a net promoter score in the sheet?
Yes, with COUNTIF over the Score column: promoters scoring nine or ten, detractors scoring six or below, each as a share of the total. Put it on the pivot tab so the number recalculates as new rows land underneath.
Related automations
- Build an RSVP list with a live headcount
Every registration adds a row to the RSVPs worksheet, and a SUM keeps the headcount current for catering.
- Collect job applications in a hiring tracker
Each application becomes a row on the Candidates tab, with the scoring columns left empty for reviewers to fill in.
- Log every lead in a master spreadsheet
Every lead form submission becomes a row on the Leads tab, with name, work email, company and source already filled in.
- Record orders in a packing list spreadsheet
Each order lands on the Orders tab with product, quantity, variant and customer, ready to pick and pack from.
- Track support requests in a Tickets tab
Each support request lands on the Tickets tab with issue type, priority and reporter email, ready to sort and mark resolved.
- Add every inbound enquiry to your Notion leads database
Every business enquiry lands in your Notion Leads database as a page tagged New, with the company and inquiry type already filled in.
Build the form first
The automation needs somewhere to fire from. Publish a form, connect it once, and every submission from then on runs this flow.
Start free