Prevent data from populating into a Google Spreadsheet from Google Forms when it's a duplicate

user_5234642 0
Viewed 220

I have a simple Google Form, that is generate through a Google Apps Script and it stores data into a script created Spreadsheet. When a user submits data through the Google Form, I want to check if the email is a duplicate in the Spreadsheet, and if it is, then I want to prevent the insertion. Any guidance on how would I be able to that would be highly appreciated. I believe that onFormSubmit on the FormsApp runs after the data has been inserted into the Spreadsheet. If that's the case, how would I deal with a duplicate?

google-forms google-apps-script
1 Answers

  • If you have set-up a destination spreadsheet where all responses are stored, there is no way to prevent insertions.

  • What you can do is to create a custom spreadsheet where you manually insert the form responses of your choice - based on the respondent email

  • For this, yoou need to create script bound to the Google Form that will retrieve on onFormSubmit trigger the latest response, compare the respondent email against the ones from previous responses and based on the result do or do not insert the response into a spreadsheet of your choice.

Sample code:

function myFunction() {
  var form=FormApp.getActiveForm();
  //set up the collection of emails
  form.setCollectEmail(true);
  var responses=form.getResponses();
  var lastResponse=responses[responses.length-1];
  var lastRespondent=lastResponse.getRespondentEmail();
  var duplicate=false;
  for(var i=0;i<responses.length-1;i++){
    if(responses[i].getRespondentEmail()==lastRespondent){
      duplicate=true;
    }
  }
  if(duplicate==false){
    var mySheet=SpreadsheetApp.openById('SPREADSHEET_OF_YOUR_CHOICE').getSheetByName('Sheet1');
    var items=[];
    for(var j=0;j<lastResponse.getItemResponses().length;j++){
      items.push(lastResponse.getItemResponses()[j].getResponse())
    }
    mySheet.appendRow(items)   
  }
}
user_115997890
Related