Survey Data Cleaning: Google Forms to SPSS

How to clean Google Forms data for SPSS

1. Upload the response file. In Google Forms, open the Responses tab, click the three-dot menu and choose Download responses (.csv). Microsoft Forms and Genius Data exports work too; the tool recognises the source and drops e-mail, name and respondent-ID columns from the output.
2. Check the variable codes. Google Forms uses the whole question as the column header. If a question starts with a code, such as “TT1. Gender” or a grid row “How much do you agree… [PU1. The app helps me…]”, the tool takes that code. Forms built with the Survey Kit already carry codes: AC1 or ACHK is the attention check, SANGLOC or SCREEN the screening question, TT1, TT2… the demographics. If your form has no codes, paste a codebook so columns are matched by question text, or type codes into the table.
3. Choose the response scale. The tool detects 5- or 7-point scales and agreement, satisfaction or frequency labels. For any other wording choose Custom and enter one “label = number” pair per line.
4. Declare the exclusion rules, then click Clean the data. Enter the rules exactly as written in your proposal, plus the target sample size. The output shows how many responses each step removed and how many valid responses remain.
5. Download the results. The clean CSV holds only valid responses, all numeric. The SPSS syntax (.sps) reads that file and declares variable labels, value labels and the missing-value code (99 by default). The codebook CSV is ready for your appendix, and “all responses with exclusion reason” lets you audit every excluded case.

To try it first, click “Use the sample file” (simulated data). Your file is read in the browser and never uploaded.

How to reverse code Likert items

Type the codes of negatively worded items (for example PEOU4) in the “Reverse-worded items” box. The tool applies new score = lowest + highest − old score using the range you declared, so on a 1–5 scale a 5 becomes 1 and a 2 becomes 4, even if nobody chose the extreme points. Reverse-coded items are labelled “(reverse coded)” in SPSS, so do not reverse them again in the analysis tool.

The exclusion rules

Responses are checked in the order below and each one is counted only under the first rule it breaks. The column “Flagged on its own” shows how many responses each rule would catch by itself.

• Failed screening question: the respondent is outside the target population, for example answered “Never” to the screening question.
• Failed attention check: an item that asks for a specified answer to show the question was read (Oppenheimer et al., 2009). A blank answer also counts as a fail.
• Completed too fast: only possible when the file has a duration (Genius Data) or start and completion times (Microsoft Forms). Google Forms records only the submission time, so this rule cannot be applied to it. The suggested threshold is 2 seconds per item (Huang et al., 2012).
• Straight-lining: the same answer across many consecutive scale items (the longstring index; Johnson, 2005). By default only responses with one answer for every item are excluded; state a lower threshold in your proposal if you use one.
• Duplicate: every coded answer identical to an earlier response, usually a double submission. The earlier response is kept.
• Missing answers: more scale items left blank than the declared percentage. A 0% threshold equals listwise deletion in SPSS. Blank cells in retained responses get the missing-value code; nothing is imputed.

Meade and Craig (2012) and Curran (2016) recommend combining several indicators and fixing thresholds in advance. Changing the rules after seeing the results is cherry-picking, and the tool warns you if you do.

Worked example: 8 responses, one excluded by each rule

This small file is used in the tool’s automated tests: 8 responses exported from Microsoft Forms, a 5-point scale, a screening question SANGLOC, an attention check AC1 (correct answer 2), a reverse-worded item PEOU3 and a target of 10 valid responses. With all six rules on, the tool reports:

Step	Excluded	Remaining
Failed screening question (SANGLOC = never used)	1	7
Failed attention check (AC1 not 2)	1	6
Completed too fast (under 60 seconds)	1	5
Straight-lining (same answer to all 6 items)	1	4
Duplicate of an earlier response	1	3
Missing answers	1	2

Two valid responses remain, 8 short of the target of 10.

Reading the output

• The step table reads top to bottom. “Flagged on its own” counts every response that breaks a rule, so it can exceed “Excluded” when some of those responses were already removed at an earlier step.
• One rule removing a large share (say, the attention check removing most responses) usually signals a confusing item or a wrongly declared correct answer rather than careless respondents. Open the “all responses with exclusion reason” file to check.
• The shortfall against the target sample size tells you how many more valid responses you need.

How to report it (APA 7)

The tool drafts the data-cleaning paragraph for your Method section: the rules applied, the number excluded at each step and the final valid sample, with references. For the example above it would read, in part: “Data were collected through Microsoft Forms, yielding 8 responses. Exclusion rules were specified before the analysis, following recommendations for detecting careless responding (Meade & Craig, 2012; Curran, 2016)…” Click Copy or Download Word. The paragraph is only honest if the rules were fixed before you looked at the results and applied equally to every response. Put the downloaded codebook in an appendix.

When you are short of valid responses

The only legitimate way to close the gap is to collect more real responses and clean them with the same rules. The tool never fills in, duplicates or imputes answers to reach a number; that would be data fabrication. Work out the minimum with the sample size calculator and collect 15–20% extra, because some responses are always excluded.

FAQ

Can I send the data straight to the analysis tool?

Not yet. Download “Clean file (CSV)” and upload it to Survey Data Analysis for Cronbach's alpha, EFA and regression. Reverse-worded items are already reverse coded, so do not declare them again.

Why is there no .sav file?

Run the syntax in SPSS, then choose File → Save As to save the data as .sav.

How are demographic answers numbered?

By the codebook if you supply one. Otherwise labels containing numbers (“Under 3 million”, “Year 2”) are ordered by the number, other labels alphabetically, with “Other” last.

Does Google Forms record how long a respondent took?

No. It records only the submission timestamp, so the too-fast rule cannot be used; rely on attention checks and straight-lining instead, or use a form service that records start and end times.

References

• Curran, P. G. (2016). Methods for the detection of carelessly invalid responses in survey data. Journal of Experimental Social Psychology, 66, 4–19. https://doi.org/10.1016/j.jesp.2015.07.006
• Huang, J. L., Curran, P. G., Keeney, J., Poposki, E. M., & DeShon, R. P. (2012). Detecting and deterring insufficient effort responding to surveys. Journal of Business and Psychology, 27(1), 99–114. https://doi.org/10.1007/s10869-011-9231-8
• Johnson, J. A. (2005). Ascertaining the validity of individual protocols from Web-based personality inventories. Journal of Research in Personality, 39(1), 103–129. https://doi.org/10.1016/j.jrp.2004.09.009
• Meade, A. W., & Craig, S. B. (2012). Identifying careless responses in survey data. Psychological Methods, 17(3), 437–455. https://doi.org/10.1037/a0028085
• Oppenheimer, D. M., Meyvis, T., & Davidenko, N. (2009). Instructional manipulation checks: Detecting satisficing to increase statistical power. Journal of Experimental Social Psychology, 45(4), 867–872. https://doi.org/10.1016/j.jesp.2009.03.009