Form Google Script Prevent Duplicates

Question

I'm making a google form and I have a field called name with other fields like title, company, and email address. If there is already a specific person in the database, I want the other information to replace the old information with the new info (i.e. an update function), but I'm having trouble doing with that with Google Apps Script since I find the documentation rather pathetic. Would anyone mind giving me a hand?

Answer 1

This won't prevent a Google Form from getting submitted with duplicate values in the first place, but I think what you want will look some thing like...

function updateExisting() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      s = ss.getSheetByName('Sheet1'),
      lastRow = s.getLastRow(),
      lastValues = s.getRange('A'+lastRow+':E'+lastRow).getValues(),
      name = lastValues[0][0],
      allNames = s.getRange('A2:A').getValues(), 
      row, len;

  // TRY AND FIND EXISTING NAME
  for (row = 0, len = allNames.length; row < len - 1; row++)
    if (allNames[row][0] == name) {
      // OVERWRITE OLD DATA
      s.getRange('A2').offset(0, 0, row, lastValues.length).setValues([lastValues]);
      // DELETE THE LAST ROW
      s.deleteRow(lastRow);
      break;}
}

This has to be triggered by the on Form Submit trigger inside your Sheet.

Docs can be overwhelming. They typically just do a 1 or 2 line examples, although if you run through all the tutorials there's a lot more finished examples. It's more on the developers to make these types of scripts.

Answer 2

Here's how I solved it...

I used their email address to check for duplicates but you can use anything you want:

function SendConfirmationMail(e) {
    
        // Fetch data from latest submission on spreadsheet
        var ss = SpreadsheetApp.getActiveSheet();
                   
        // Access the workbook
        var wrkBk = SpreadsheetApp.getActiveSpreadsheet();
        
        // Fetch the necessary sheets
        var ssResponses = wrkBk.getSheetByName("Form Responses");
        var ssAutomailer = wrkBk.getSheetByName("Automailer"); // <---this sheet is in another tab that has the email's subject and body so this script can be dynamically updated.
      
        // Fetch & store data from form submissions
        var numRows = ssResponses.getLastRow();  // <--- store # of total rows
        var lastfNameCell = "B" + numRows;  // <--- store the range of last cell containing fName data
        var lastEmailCell = "C" + numRows;  // <--- store the range of last cell containing email data
        var numPrevRows = numRows -1;  // <--- store the # of previous rows
        var lastPrevEmailCell = "C" + numPrevRows; // <--- store the range of last "previous" cell that contains email data in A1 notation
        var lastPrevEmailRange = "C2:" + lastPrevEmailCell; // <--- store range of ALL previous cells containing email data in A1 notation
        var fName = ssResponses.getRange(lastfNameCell).getValue();  // <--- store the fName from latest submission 
        var email = ssResponses.getRange(lastEmailCell).getValue();  // <--- store the email address from latest submission
        var prevEmails = ssResponses.getRange(lastPrevEmailRange).getValues(); // <--- store range of all previous email addresses into an array  
      
        // Convert email list to string for search functionality
        prevEmails = prevEmails.toString();
      
        // Run an index search to see if the email address already exists
        // If no match is found, -1 will be the result and we can continue on...
        if (prevEmails.indexOf(email) == "-1") {
      
            // Fetch own email address for cc functionality
            var cc = Session.getActiveUser().getEmail();
        
            // Set sender's name
            var sendername = "Your Site/Business Name Goes Here"
          
            // Store data from Automailer cells
            var subject = ssAutomailer.getRange('A3').getValue();
            var body = ssAutomailer.getRange('B3').getValue();
      
            // Store HTML template and it's contents
            var htmlFile = HtmlService.createTemplateFromFile("new-subscriber-template.html");
            var htmlContent = htmlFile.evaluate().getContent();
      
            // Convert spreadsheet body content to HTML
            var htmlBody = body.replace(/\n/g, '<br>'); //<----- converts newlines to HTML format

            // Replace placeholder data in htmlContent
            htmlContent = htmlContent.replace("[fName]", fName);
            htmlContent = htmlContent.replace("[body]", htmlBody);
    
            // Add a personalized greeting to plain text body and store to new variable
            var textbody = "Hi " + fName + ",\n\n" + body;
                
            // Send the email!
            GmailApp.sendEmail(email, subject, textbody,                           
            // Extra email paramaters: un-comment the first section of this line to send yourself a carbon copy.
            {/*cc: cc, */name: sendername, htmlBody: htmlContent});
                                               
        }
        // If the index search found a duplicate, do this instead
        else { 
                        
            // Send error notification email
            GmailApp.sendEmail(email, "There was an error with your request...", "Looks like you're already a subscriber!",                          
            // Extra email paramaters: un-comment the first section of this line to send yourself a carbon copy.
            {/*cc: cc, */name: sendername, htmlBody: "Looks like you're already a subscriber!" });
          
            // Be gone duplicate!
            ssResponses.deleteRow(numRows);
        } 
 
}

Keep in mind that if you're searching against pretty much anything other than emails, you may want to use some extra search parameters to rule out false positives. I was going to use the following, but I decided that emails are unique enough to avoid having this extra line in the code:

var emailSearch = ',' + email + ',' <--- commas on either end added to match the entire cell on the index search