Apps Script: Detect and Flag Duplicate Google Form Responses

Duplicate form responses do not just skew your data - they force you into hours of manual cleanup before you can trust a single chart.

Google Forms has a built-in toggle to limit users to one response, but it requires them to sign into a Google account.

If you need to track duplicates without forcing a login, or if you want to flag repeat entries rather than block them entirely, you have to build your own logic.

Writing a custom Apps Script trigger gives you total control over how duplicate submissions are identified, tagged, and handled the moment they hit your spreadsheet.

Here is the exact code and setup you need to automate duplicate detection in the background.

What do you need before writing a duplicate detection script?

Before writing a single line of code, your form and spreadsheet environment must be structured correctly. The script runs on the spreadsheet side, meaning the layout of your data dictates how the logic operates.

If you are digitizing old paper processes - perhaps using a tool to convert a survey PDF to a Google Form - you need to ensure your new digital fields map cleanly to the spreadsheet columns.

Here is what you must have in place:

• A linked destination spreadsheet: The script will attach to the Google Sheet where responses are logged, not the Google Form itself.
• A unique identifier field: You need a reliable data point to check against.
• Consistent data validation: The identifier field in your form should use data validation (like requiring a valid email format) to prevent bad inputs from breaking your script logic.
• Editor permissions: You must be an owner or editor of the destination spreadsheet to write the script and authorize the necessary permissions.
• An empty column for flags: If you plan to write a status message like "Duplicate" next to the data, you need to designate a specific column for the script to use.

The unique identifier is the most critical component. If your script compares user inputs that are prone to variation, it will fail to detect actual duplicates.

Identifier field prompt

• ❌ Weak: What is your name?
• ✅ Strong: What is your primary contact email address? Why it works: Names are prone to typos, nicknames, and capitalization differences, making script matching unreliable.

How to link your form to a spreadsheet for automated logging

Apps Script cannot efficiently manipulate data while it is trapped inside the native Google Forms interface. You must route the incoming data to a Google Sheet first.

If you are standardizing data collection across a department, creating forms manually gets tedious. You can use tools to generate a Google Form from a description to set up the initial fields quickly, then link them to your master sheets.

Follow these steps to establish the connection:

1. Open your Google Form in edit mode.
2. Navigate to the Responses tab at the top center of the screen.
3. Click the green Link to Sheets text or the spreadsheet icon in the top right corner of the tab.
4. Select Create a new spreadsheet.
5. Click the Create button in the top right corner of the dialog box.

Once linked, Google Forms will instantly create a new spreadsheet and open it in a new tab. Every field in your form now corresponds to a column in this sheet.

The first column will always be Timestamp, which Google Forms generates automatically. Your unique identifier field will occupy one of the subsequent columns.

Make a note of which column contains your identifier. You will need this exact column number when configuring your Apps Script in the next step.

Writing the Apps Script to find and flag duplicate entries

The script below uses an installable onFormSubmit trigger. When a new row hits the spreadsheet, the script reads the incoming identifier, checks the history above it, and highlights the row if a match exists.

Open your linked Google Sheet, click Extensions in the top menu, and select Apps Script. Delete any code in the editor and paste the following block:

function flagDuplicateResponses(e) {
  // 1. Define your settings
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const identifierColumnIndex = 2; // Column B (1-indexed for Sheets API)
  const arrayIndex = identifierColumnIndex - 1; // 0-indexed for JavaScript arrays
  
  // 2. Get the incoming data from the event object
  const newRowNumber = e.range.getRow();
  const incomingValues = e.values;
  const newIdentifier = String(incomingValues[arrayIndex]).trim().toLowerCase();
  
  if (!newIdentifier) return; // Exit if the field is blank
  
  // 3. Fetch historical data (excluding the new row)
  const historicalRange = sheet.getRange(2, identifierColumnIndex, Math.max(1, newRowNumber - 2), 1);
  const historicalData = historicalRange.getValues();
  
  // 4. Check for duplicates
  let isDuplicate = false;
  
  for (let i = 0; i < historicalData.length; i++) {
    let pastIdentifier = String(historicalData[i][0]).trim().toLowerCase();
    
    if (pastIdentifier === newIdentifier) {
      isDuplicate = true;
      break;
    }
  }
  
  // 5. Apply the flag if a duplicate is found
  if (isDuplicate) {
    // Highlight the entire row in light red
    const lastCol = sheet.getLastColumn();
    sheet.getRange(newRowNumber, 1, 1, lastCol).setBackground("#fce8e6");
    
    // Optional: Write "Duplicate" in a specific column (e.g., Column 10)
    // sheet.getRange(newRowNumber, 10).setValue("Duplicate");
  }
}

To make this script work, you must understand the variables it relies on. Apps Script bridges the gap between JavaScript logic and Google Sheets architecture.

Here is a line-by-line breakdown of the core variables:

• e (Event Object): This captures the data sent by the form at the exact moment of submission. It prevents the script from having to search the entire sheet to find out what was just added.
• identifierColumnIndex: The physical column number in your sheet where the unique ID lives. In Google Sheets, column A is 1, B is 2, and so on.
• arrayIndex: JavaScript arrays start counting at 0. We subtract 1 from the column index so the script looks at the correct position in the e.values array.
• incomingValues: A simple array containing all the answers from the form submission.
• newIdentifier: The specific piece of data we are checking. The script converts it to a string, removes extra spaces with trim(), and forces it to lowercase so "[email protected]" matches "[email protected]".
• historicalRange: The block of cells containing all previous submissions. We use newRowNumber - 2 to ensure we do not compare the new submission against itself.
• isDuplicate: A boolean variable that starts as false. The script loops through the historical data, and if it finds a match, it flips this flag to true and stops searching.

Expert tip: Never put a getValue() call inside your for loop. Fetching data from the sheet cell-by-cell is extremely slow and will cause your script to time out under heavy traffic. Always read the data in one bulk array first.

Should you flag, delete, or reject duplicate submissions?

Detecting a duplicate is only half the problem. You must also decide what the system should do with the redundant data.

Different use cases require different levels of strictness. Deleting data automatically carries risks, while flagging data creates manual work.

Flagging is generally the safest default. The concept of loss aversion applies heavily to data management - people hate losing data permanently.

If your script deletes a row because a user genuinely needed to update their previous response or submit a second valid request, that data is gone forever. By highlighting the row instead, you preserve the audit trail.

If you choose to delete, you can modify the script's final block to use sheet.deleteRow(newRowNumber). Do this only if you are absolutely certain your identifier matching is flawless.

How to troubleshoot common Apps Script execution errors

When scripts fail, they usually do so silently in the background. Unless you are actively checking the execution logs, you might not realize duplicates are slipping through.

Most errors stem from authorization issues or a misunderstanding of how event triggers operate.

The first error in that table trips up almost everyone. The event object e only exists when a form is actually submitted. If you click the Run button inside the Apps Script editor, there is no form submission happening, so e is undefined.

To test your code safely, submit a dummy response through the live form. Then, open the Executions tab on the left sidebar of the script editor to read the logs and see if it succeeded.

What are the execution limits and scale boundaries of this method?

Apps Script is powerful, but it runs on shared Google servers. It is not designed to handle massive, enterprise-level data streaming without hitting architectural walls.

Before deploying this to a form expecting thousands of rapid responses, you need to understand the constraints.

• Google Daily Quotas: Free Gmail accounts are limited to 90 minutes of total trigger execution time per day. Google Workspace accounts are capped at 6 hours per day.
• Run-time limits: A single execution of a script will time out and fail if it runs longer than six minutes. If your spreadsheet has hundreds of thousands of rows, the array processing might breach this limit.
• Lock contentions: High concurrency causes race conditions. If fifty people submit the form at the exact same second, multiple instances of the script will run simultaneously and step on each other.

To handle high concurrency safely, advanced scripts use LockService.getScriptLock(). This service forces concurrent executions to wait in line.

If script A is processing a submission, script B will wait up to a specified number of seconds before trying again. Without a lock, script B might read the historical data before script A has finished writing its row, causing script B to miss the duplicate entirely.

For basic surveys and departmental forms, the script provided earlier is entirely sufficient. If you are running a massive public event with thousands of simultaneous users, you will need to implement LockService or move to a dedicated database infrastructure.

FAQ

Can Apps Script prevent a user from clicking submit on a duplicate response?

No. Apps Script runs on the server side after the form is already submitted and the data has reached the spreadsheet. It cannot communicate back to the live form UI to disable the Submit button or show a warning message. If you must block the submission at the source, you have to use the native Google Forms setting.

How do I set up an installable onSubmit trigger instead of a simple trigger?

Open your Apps Script editor and click the clock icon on the left sidebar to open the Triggers menu. Click Add Trigger in the bottom right corner of the screen. Choose your function name, set the event source to From spreadsheet, and set the event type to On form submit, then click Save.

Does this method work if respondents are anonymous?

Yes, but only if you ask them to type a unique identifier into a standard form field, like an email address or an employee ID number. The script cannot identify a user's IP address or browser session. Without a user-provided identifier in the form data, there is nothing for the script to compare against.

When your forms are structured correctly from the start, managing the data later becomes trivial. If you are tired of building these structures and mapping fields from scratch, Doc2Form can turn your existing documents into ready-to-use Google Forms directly in your Drive. Focus your energy on writing the logic that cleans your data, rather than manually building the fields that collect it.