Allow only one Google Form submission per user

Question

I have a survey that expects no repeat responses. There does not appear to be any way to set this in the Google Forms controls, so I need to write a script. How do I ensure that I allow only one Google Form submission per unique username?

Consider a survey with a simple choice. If one submits "Choice A" and later repeats the survey to submit "Choice B", I'd like to disregard the latter submission; the choice that was just sent (B) would be deleted from the Google Sheet.

Mogsdad Mogsdad · Accepted Answer · 2013-11-22T17:03:46

You cannot stop a user from submitting multiple times, but as you say, you can delete subsequent submissions.

Let's assume that the "Username" is placed into column B, with that heading. If you're in a Google Apps domain, you can choose to have Usernames automatically collected. Outside a domain, you'll have to rely on respondents consistently filling in their Username.

The following function should be set up as a programmed form-submission-event trigger in your spreadsheet. When a new submission is received, the User

function onFormSubmit (event) {
  var sheet = event.range.getSheet();
  var data = sheet.getDataRange().getValues();

  // Read usernames from column B, into string
  var userNames = sheet.getDataRange()
                       .offset(1, 1, sheet.getLastRow()-1,1)
                       .getValues()
                       .join();

  // Use a regex search to count occurrences of the current
  // username. There should be just 1.
  var re = new RegExp(event.namedValues["Username"], "g");
  var count = userNames.match(re).length;

  // Delete submission if user previously submitted.
  if (count != 1) {
    sheet.deleteRow(event.range.getRow());
  }
}

Caveats

This removes entries from the Spreadsheet accepting form responses; it does not affect the responses attached to the form itself, available through Form.getResponses().
This will become slower as the number of submissions increases. It could be optimized by removing the need to search previous submissions, for instance by caching all unique Usernames.
There is a possibility that the submitted data that the trigger function receives is out of sync with the spreadsheet version that is read into the data variable, a glare event. The result would be that a duplicate entry could still be allowed. This concern could be mitigated by comparing the row number from event.range with the last row from .getDataRange(), and handling the discrepancy.

Allow only one Google Form submission per user

2 Answers

Caveats