Deny duplicate entry on a Google form via apps script-CodePudding

The Form will have a Field Select Name and Select Date and I want to prevent a submission if there is already a matching name and date in the responses.

For now I am just removing duplicated but I want to make it impossible to enter them in the first place.

CodePudding user response：

a simple way to go around it is to perhaps have a list of names to submit, then a filter() list of names that have submitted based on today() or a dropdown date filtered from the G form responses. Then finally one more filter() to list down the names that have not submitted.

Using the List of Names Not Submitted, you can use the code below to actively update the dropdown in your google form to only show the names that are pending so you wont get repeats. You want to also set a time-trigger (minute)/on form submission to update the dropdown automatically.

function updateInfo() {
// Identify the dropdown in the form to push data in
  var form = FormApp.openById("10HlSN9qxWHZtV4bYGHJGiIEoZaGocmyQeLLQjVkD2Is");//Put form ID here
  var brandlist = form.getItemById("617126825").asListItem(); // Select the dropdown ID by right clicking and selecting "inspect"

// Identify the sheet to pull data from
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1x2hTWJHoWUh62bWTRoaslzM2L1jlMgzZdLAEYo6BHig/edit?resourcekey=null&pli=1#gid=0');
  var tab = ss.getSheetByName("Dropdown");

//Updates for Names that can be selected
  var namesValues = tab.getRange(2, 1,tab.getMaxRows() - 1).getValues(); // Where to start grabbing values? use 2 to skip the header row and identify the column
  var name = []; // Set an empty array
  for(var i = 0; i < namesValues.length; i  ) // Set values into the array, ignore empty cells
    if(namesValues[i][0] != "")
      name[i] = namesValues[i][0];

  namelist.setChoiceValues(name); // populate the drop-down with the array data

CodePudding user response：

I found simple solution for this.

First just create one new column.

For example, if you have Name in column A, Date in Column B, created new column is C

Now suppose if you have "Alex" in A2 cell and "11-11-2022" in B2 cell So in C column write below function

=textjoint(,,A2,B2)

which will give you output like

"Alex11-11-2022"

now in the sheet write below function in the cell you are not using. For example I am using Z2 cell

=COUNTIF(sheet1!C2:C,Z2)

I used above formula in cell and it returns "0" if no duplicate found.

It returns >0 values i.e 1,2,3,.. if found duplicates.

after this just go into appscript and paste below code at the beginning of your script

var dupentry=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("yoursheetname").getRange("A17").getValue(); // this is the cell in which we have entered "=COUNTIF(master!T3:T15,C3)" formula
if(dupentry=='0'){
//write the script here for whatever you want to do if no duplicates found
}
else SpreadsheetApp.getUi().alert("Error duplicate entry found");
return;}