0

I'm working on a project where I need to prevent duplicate entries in a Google Form based on a barcode number field. When a duplicate barcode number is detected upon form submission, the submission should be rejected, and both the submitter and a designated recipient should be notified via email.

Requirements:

Prevent duplicate entries based on the barcode number. Notify both the submitter and a designated recipient if a duplicate is detected. Automate the process without manual intervention. What I’ve Done:

Created a Google Form with fields for Barcode Number and Submitter's Email. Linked the form to a Google Sheets document. Wrote the following Google Apps Script to check for duplicates and send emails:

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var barcodeIndex = 1; // Adjust this index based on the position of the Barcode Number column in your form responses
  var emailIndex = 2; // Adjust this index based on the position of the Submitter's Email column in your form responses

  if (lastRow > 1) {
    var newBarcode = e.values[barcodeIndex];
    var submitterEmail = e.values[emailIndex];
    var existingBarcodes = sheet.getRange(2, barcodeIndex + 1, lastRow - 1, 1).getValues().flat();

    if (existingBarcodes.includes(newBarcode)) {
      // If a duplicate is found, delete the latest row (the current submission)
      sheet.deleteRow(lastRow);

      // Send email notification about the duplicate
      var recipientEmail = "[email protected]"; // Replace with your email
      var subject = "Duplicate Entry Detected";
      var message = "A duplicate entry for Barcode Number " + newBarcode + " was detected and not accepted.";

      // Send email to the designated recipient
      MailApp.sendEmail({
        to: recipientEmail,
        subject: subject,
        body: message
      });

      // Send email to the submitter
      MailApp.sendEmail({
        to: submitterEmail,
        subject: subject,
        body: message
      });

      return;
    }
  }
}

function createTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('onFormSubmit')
           .forSpreadsheet(ss)
           .onFormSubmit()
           .create();
}

Issues:

The script doesn't seem to detect duplicates properly. Fails to delete the row and send the emails when a duplicate is found. Could anyone provide guidance on how to properly set this up? Any help would be greatly appreciated!

Thanks in advance!

1 Answer 1

0

In the event the barcodes are numeric, you cannot assume that e.values[barcodeIndex] will match a barcode number in the spreadsheet.

The items in e.values are strings, while the values stored in the spreadsheet are numbers. To make it work, try this:

    var newBarcode = Number(e.values[barcodeIndex]);

...or, alternatively:

    var existingBarcodes = sheet.getRange(2, barcodeIndex + 1, lastRow - 1, 1).getDisplayValues().flat();

Choose one approach, since you cannot use both.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.