How to clean up Google Form™ responses
A step-by-step run through the add-on, from install to a clean tab that refreshes itself. Findings and the clean tab for up to 250 responses are free. Up to 50,000 responses per form, saved recipes, one-click Refresh, the Choices and Summary tabs and CSV export are paid, and each is marked Pro where it appears.
What the problem is
A Google Form™ writes its answers into the linked sheet exactly as they were given: checkbox answers crammed into one cell, headers that are whole questions, the odd double submission, phone numbers with the leading zero gone. None of that stops a person reading one row, and all of it stops a pivot table, a filter or a COUNTIF from giving a straight answer.
This add-on reads the responses tab and writes a clean tab beside it — split, shortened, de-duplicated and typed — then re-runs the same recipe as new responses arrive. Your responses tab and your form are never changed.
Before you start
- Google Sheets™ in a desktop browser. The add-on does not run in the Google Sheets™ mobile apps.
- The add-on installed from the Google Workspace™ Marketplace.
- A spreadsheet with a Form Responses tab in it.
- Nothing leaves Google. The rules run in the sidebar, in your browser, against the Sheets API. The add-on's only network traffic is to the licence server, and only ever a licence key and an anonymous install id. The privacy notice says so in full.
Step 1 — Install
Extensions › Add-ons › Get add-ons, then search for “Form Response Cleaner”.
Step 2 — Open the cleaner
In the spreadsheet, open Extensions › Form Response Cleaner › Open cleaner. A sidebar opens on the right. It finds the Forms-linked tab itself and lists what is wrong before asking anything of you: “3 checkbox questions, 12 duplicates, 4 test entries, 38 long headers”.
Step 3 — Confirm the wizard
The first run is a short wizard. It asks only what it cannot infer: the checkbox option lists, the short header aliases, the duplicate key and the test rules. Every step has a sensible default and a Skip, and every guided rule shows a 5-row before/after preview. A wrong split is worse than no split, so nothing is guessed silently.
Step 4 — Clean
Press Clean. The clean tab is written to new tabs beside the responses tab: one row per kept response, a stable Response ID in column A, a frozen header row and a filter on. Duplicates and test rows go to the Excluded tab with a reason each, and the cleaning log records the recipe, the rules applied with counts, and anything left unparsed.
Step 5 — Refresh Pro
As new responses arrive, Refresh re-runs the saved recipe in one click. A new question appears as a new column with a “1 new question: review” banner; a new option becomes a new column and is flagged in the log, never dropped. Columns you added yourself to the right of the clean data are preserved and re-attached by Response ID.
If you edited the cleaned data itself, Refresh refuses and says so: write your notes in your own columns or a new tab, or overwrite.
Step 6 — The recipe Pro
Your wizard answers are saved as a recipe that travels with the spreadsheet, so a colleague opening the same file Refreshes with your choices. Export and Import recipe (JSON) cover copying a form and its sheet for next term. Saving and editing the recipe is Pro; Refreshing with a recipe someone else saved is free within the 250-response limit.
Step 7 — Entering a licence key
Expand Licence at the bottom of the sidebar, paste the key from the end of checkout, and press Activate. It covers 3 Google accounts, and takes effect immediately.
The rules, one by one
Each rule below says what it is, why it matters, and how to do it by hand with formulas — so the guide stays useful even before you install anything. Every finding in the sidebar links to its section here.
FR-01 — Checkbox answers crammed into one cell
What it is. A checkbox question lands as one cell per response:
Vegetarian, Gluten free, Other: no nuts. The add-on learns the option list from the
data, asks you to confirm it, and writes one TRUE/FALSE column per option (wide), or one row per
choice on a separate long tab (Pro). Options containing a comma are matched longest-first, so
“Tea, coffee” does not split into two.
Why it matters. You cannot count, filter or pivot a cell that holds three answers at once. COUNTIF on the raw column undercounts every multi-tick response.
By hand. One helper column per option:
=ARRAYFORMULA(REGEXMATCH(B2:B, "Vegetarian")), then count the TRUEs. It breaks the
moment the form gains an option, which is the point where the one-click version earns its keep.
More: the full walkthrough, with the formula that survives new responses.
FR-02 — “Other” text mixed into choice answers
What it is. Google Forms™ writes an “Other” answer as the respondent’s
own words, straight into the list with no marker: Vegan, no nuts please. Form Response
Cleaner compares each answer with the options you confirmed; whatever is left over goes into its own
“…(other)” column, and the choice columns stay clean.
Why it matters. “no nuts please” and “halal” are different answers to anything counting exact text, and the interesting part — the free text — is stuck inside a longer string.
By hand. List the form’s options on a tab called Options (A2 down), then
=TEXTJOIN(", ", TRUE, IFERROR(FILTER(SPLIT(B2, ", ", FALSE), ISNA(MATCH(SPLIT(B2, ", ", FALSE), Options!$A$2:$A, 0)))))
returns the typed text for that row, or nothing. An option that itself contains a comma
(“Tea, coffee”) splits in two here, so list its halves as well.
FR-03 — Grid questions
What it is. A multiple-choice grid arrives as headers like
Rate the day [Venue], Rate the day [Food] — one column per row of
the grid. The add-on groups them as a block and can write them long: one row per respondent per
grid row.
Why it matters. Ten grid rows are ten columns with near-identical headers, and an average “score for the day” needs all ten treated as one question.
By hand. Keep the block together and average across it per respondent with
=AVERAGE(C2:G2) once the text answers are mapped to numbers
(see FR-11); for the long form, copy the block and paste
transposed per respondent, or use a QUERY per grid row.
FR-04 — Headers that are whole questions
What it is. Any header over 30 characters gets a short alias, auto-proposed and editable, saved in the recipe. The full question goes into a note on the header cell, so nothing is lost.
Why it matters. Ninety-character headers make every formula unreadable and every pivot a sideways scroll. Never rename them on the responses tab itself — work on a copy.
By hand. On a new tab:
=QUERY('Form Responses 1'!A1:Z,"select A, B label B 'Diet'") renames as it copies.
More: short names on a working tab, and the QUERY trap.
FR-05 — Duplicate submissions
What it is. Same respondent twice — a double-click, or “I wasn't sure it went through”. Matches on email where one is collected, else on columns you pick. The latest is kept by default; the rest go to the Excluded tab with the row they duplicate. Exact whole-row duplicates within 2 minutes always count.
Why it matters. Every double submission is a double-counted vote, booking or payment.
By hand. On a copy sorted oldest-first, a running count per email:
=COUNTIF($C$2:C2,C2) — anything over 1 is a repeat. Filter them out, keeping
the last.
More: keeping the latest with SORTN.
FR-06 — Test submissions
What it is. Your own trial runs, excluded. Three signals: submitted before the
“went live” time you set, test/asdf/xxx in
name-like columns, or your own email typed in once.
Why it matters. “Test Test” wins every count it appears in, and it appears in all of them.
By hand. Filter the timestamp column to before go-live and delete those rows on
your copy, then filter name columns with
=REGEXMATCH(C2,"(?i)test|asdf|xxx") for stragglers.
FR-07 — Numbers stored as text
What it is. £20, 1,200, 20 quid,
approx 15 in a number question. Clean parses become a numeric column automatically;
anything the parser cannot read is listed as unparsed in the log — never guessed.
Why it matters. Text does not sum, sort or average. One “20 quid” silently drops out of every total.
By hand. =VALUE(REGEXREPLACE(A2,"[^0-9.,-]","")) strips the words,
wrapped in IFERROR to list what fails. Check thousand separators match your locale.
FR-08 — Phone numbers losing the leading zero
What it is. 07700… arriving as 7700…, or
+44… turning into #ERROR! because Sheets reads the plus as a
formula. Phone-like columns are written as text, normalised to one format.
Why it matters. A phone number without its leading zero does not dial, and you
cannot tell after the fact whether 7700… lost a zero or never had one.
By hand. Damage already done cannot be reliably undone — that is the honest
answer. Going forward, set the column to Plain text before responses arrive, and normalise spacing
with =REGEXREPLACE(A2,"[^\d+]","") on a copy.
More: recovering the original and stopping it next time.
FR-09 — Emails with stray spaces, capitals and typos
What it is. Trimmed and lowercased automatically. Likely typos
(gmial.com, .con) are flagged in the log and never changed — a
guessed correction to an email address goes nowhere good.
Why it matters. “Smith@x.com” and “smith@x.com ” are two different people to a mail merge and a de-duplicator alike.
By hand. =LOWER(TRIM(A2)). To flag likely typos:
=REGEXMATCH(A2,"gmial\.com|\.con$|\.cm$").
FR-10 — One timestamp column, unpivoted
What it is. The timestamp (always column A in a Forms tab) gains Date, Time, Week and Month columns for pivots. Date and time answers given as text are parsed too.
Why it matters. “Responses per day” and “morning vs afternoon” both need the timestamp broken apart before a pivot will group it.
By hand. =INT(A2) for the date, =MOD(A2,1) for the time,
=TEXT(A2,"yyyy-mm") for the month, =WEEKNUM(A2) for the week.
FR-11 — Yes/no and scale answers as inconsistent text
What it is. Y, yes , Yes! mapped to
one Yes — you confirm the mapping from a preview. Same for scales typed as
text.
Why it matters. Three spellings of yes are three groups in a pivot where you wanted one.
By hand. Normalise first with =PROPER(TRIM(A2)), then map the
leftovers with a small lookup table and XLOOKUP.
FR-12 — One question split across two columns
What it is. Renaming a question keeps its column, but deleting it and adding it back, relinking the form, or pasting in a second export leaves the early answers in one column and the later ones in another. Near-identical headers with complementary blanks are offered as a merge into one column.
Why it matters. Half your answers sit in each of two columns, and every count covers only half the respondents.
By hand. Coalesce on a copy: =IF(A2<>"",A2,B2) takes whichever
of the pair is filled.
FR-13 — Dead columns nobody answers
What it is. Columns blank throughout, or blank since a date — a question nobody answers any more, or one left behind by an older version of the form. Dropped from the clean tab and listed in the log.
Why it matters. They widen every scroll and every select-all, and invite formulas to reference columns that will never fill.
By hand. =COUNTA(B2:B)=0 confirms a column is fully blank; hide it
on your working copy.
FR-14 — File-upload answers
What it is. Upload answers arrive as comma-separated Drive links in one cell. The add-on writes one link per column, or just the count.
Why it matters. Three links in one cell cannot be opened one at a time, and “how many files did each respondent send” needs the count pulled out.
By hand. =SPLIT(B2,", ") spreads the links across columns; for the
count, =IF(B2="",0,1+LEN(B2)-LEN(SUBSTITUTE(B2,",",""))).
FR-15 — Quiz scores
What it is. The 7 / 10 in the Score column becomes numeric Score and
Max columns plus a percentage.
Why it matters. “7 / 10” is text: it will not average, chart or grade until the numbers come out.
By hand. =VALUE(REGEXEXTRACT(A2,"(\d+)\s*/")) for the score,
=VALUE(REGEXEXTRACT(A2,"/\s*(\d+)")) for the max, and one divided by the other.
FR-16 — Answers that look like formulas
What it is. An answer starting with =, +,
- or @ is written as text, always — every text cell is protected
at write time, not just the ones that look suspicious.
Why it matters. =1+1 typed as an answer becomes 2 the
moment it is written back unprotected — or #ERROR!, or worse.
By hand. Set the destination column to Plain text before pasting, or prefix each
cell with an apostrophe ('=1+1), which Sheets stores as text.
FR-17 — Stray spaces and line breaks in short answers
What it is. Leading, trailing and double spaces, non-breaking spaces and stray line breaks in short answers are tidied automatically.
Why it matters. “Smith”, “ Smith” and “SMITH” group as three people; a trailing line break hides a cell's real end.
By hand. =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for spaces and
non-breaking spaces; add CLEAN around it for line breaks in answers that should be
one line.
FR-18 — Edited responses (explained, not fixed)
What it is. A respondent who edits after submitting keeps their row; only the timestamp changes. There is no reliable signal that an edit happened, so the add-on says so honestly and takes the current values.
Why it matters. If you export responses daily, an edited row changes under you without a new row to announce it.
By hand. Sort a copy by timestamp and scan for timestamps newer than their neighbours' position suggests — that is all anyone can do, which is why this rule stays manual.
Limits
- Desktop browser only. The add-on does not run in the Google Sheets™ mobile apps.
- Free tier: 250 responses. Past that, “Clean the first 250 to try it” or upgrade to Pro.
- Output size. Wide checkbox forms widen the clean tab a lot. A preflight estimates the output against the spreadsheet's cell limit and refuses by name rather than writing half a tab.
- Your own columns survive Refresh only if they sit to the right of the managed block. Edits inside the managed block make Refresh refuse, with the reason stated.
FAQ
Does my data leave Google? No. The rules run in the sidebar, in your browser, against the Sheets API. The only network call this add-on makes is to the licence server, for a licence key and an anonymous install id.
Will it change my form or my responses? Never. It only reads the responses tab; every output goes to new tabs.
Can my colleague refresh it? Yes — the recipe travels with the spreadsheet. The licence belongs to the Google account, not the sheet.
What if the form changes mid-campaign? Refresh picks up new questions and new options and tells you about them. Renamed questions are offered as a merge. Nothing is silently dropped.
Does it work with Excel? No — Google Sheets™ only. There is no Excel version of this add-on.
If something looks wrong
Email support@appgarden.co.uk, and say which rule looked wrong. A copy of the responses that produced a wrong finding — or missed a real one — is the most useful thing you can send. Only send one you are content to share, and read the privacy notice first.
Google, Google Forms™, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Form Response Cleaner is not affiliated with or endorsed by Google.