This is how I solved the duplicate response problem in Google Forms

Introduction

Google Forms is an incredible tool that lets you create surveys, quizzes, and feedback forms with ease. However, it’s frustrating that Google Forms doesn’t allow to mark a response unique to prevent people from submitting duplicate responses. However, using Google Apps Script I have managed to derive a workaround that will help us tackle this issue and create unique fields in our Google Form. So, let’s dive into how you can disallow duplicates in Google Forms like a pro.

The Challenge

Picture this: you’re running a survey or maybe organising an event, and you’ve got your Google Form all set up. To ensure accurate data representation, you want to limit participants to one response per social ID (or any other parameter you want to be unique). You start getting responses rolling in, but uh-oh, some folks are hitting that submit button more times than necessary. Suddenly, you’ve got multiple entries from the same social ID cluttering up your data. Not exactly ideal, is it?

What Google Forms already offers and why it is not useful

When crafting a Google Form, you may have noticed a feature named “Limit to 1 response” under the settings tab. At first glance, it seems like the perfect solution to our duplicate entry woes. However, upon closer inspection, it falls short of our expectations.

So, this feature sounds pretty handy, right? It’s supposed to ensure that each person can only submit one response, tied to their Google ID. But hold on a sec — what about situations where multiple folks are sharing a device or using the same Google account? Take, for example, a group of volunteers at an event, all armed with tablets to collect survey data. Each volunteer interacts with different people, bringing in unique perspectives. Shouldn’t they all be able to submit their own responses?

The Workaround

With a little help from Google Apps Scripts and leveraging the Response Validation feature of Google Forms, we can disallow duplicate value in our response. Here’s the step by step guide:

Step 1: Set Up Your Google Form and Link to Google Sheets

For the sake of simplicity I have created a form with fields “Name”, “Social ID” (which we want to be unique), and Product Rating (1 to 5)

Step 2: Link Form to Google Sheets

Once your form is good to go, connect it to a Google Sheets spreadsheet.

Step 3: Open the Google Apps Script (Google Sheet bound)

Go to the Response Google sheets by clicking on “View in Sheets” under responses tab.

In the Google sheets, go to Extensions -> Apps Script; This action would create a new untitled Apps Script project [having the response sheets as container].

Step 4: Create function to add dynamic Response validation in Google Form

In your code.gs file paste the below code

const SHEETS_ID = "YOUR GOOGLE SHEETS ID";
const SHEET_NAME = "YOUR SHEET NAME";  // Genarlly it is "Form responses 1"
const FORM_ID = "YOUR GOOGLE FORMS ID";
const UNIQUE_COLUMN_NAME = "Social ID";  //Replace 'Social ID' with your column name

function disallowDuplicates() {
  let workSheet = SpreadsheetApp.openById(SHEETS_ID);
  let sheet = workSheet.getSheetByName(SHEET_NAME);

  let form = FormApp.openById(FORM_ID);

  // Get all the data available at column 'C' and create a regex 
  let sheetData = [...new Set(sheet.getRange("C2:C").getDisplayValues().map(row => row[0]))].join('|');

  // Get the question from Google form for which we want unique response
  let item = form.getItems().filter(item => item.getTitle() == UNIQUE_COLUMN_NAME)[0].asTextItem(); 

  // Set Response Validation to the question, where it checks if the entry alreaedy exist in google sheets
  let pattern = `(${sheetData})`;
  let textval = FormApp.createTextValidation().setHelpText('Social ID already exist !!').requireTextDoesNotMatchPattern(pattern).build();
  item.setValidation(textval);
}

We’re essentially getting the existing response data from the spreadsheet and generating a regex based pattern to detect duplicates and apply it as a dynamic text validation rule to prevent duplicate submissions.

• P.S. You have to grant permission to run the code

Step 5: Create a Trigger to run the function

Now you have created the function, but you need to provide that information as to when the function should run. For this we would create a Trigger on the “On Form Submit” event.

Here are the parameters to create the trigger.

Once you have created the trigger, you should see it in the Triggers list.

Step 6: Test Drive

Time to put our little setup to the test! Submit a couple of duplicate Social IDs and watch as the script works its magic, highlighting those pesky duplicates in your spreadsheet.

Conclusion:

And there you have it! With just a bit of tinkering, you’ve transformed your Google Form to disallow duplicate answers. By implementing this simple hack, you can streamline your data collection process.

So go ahead, unleash the full potential of Google Forms with confidence, knowing that your data is in good hands. Happy form building!