prevent duplicate entries from being submitted to a Google Sheet from a Google Form

Question

I have been trying to create a script that will prevent duplicate entries from being submitted to a Google Sheet from a Google Form. However, I am encountering an error that says "No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it." I have checked the sharing settings and confirmed that I am the owner of the form and the sheet. I have also tried creating a copy of the form and using the new ID, but the problem persists. I have also tried modifying the script by changing the ID of the form and sheet, but I keep getting syntax errors. I am not sure what is causing the problem, and I am looking for a solution. This is the code I have been using.


function onFormSubmit(event) {
var form = FormApp.openById("1FAIpQLScvWCoM3uospk3rd1JVfcecA3mU32oNtReow71j_2lL9ZjaMg");
  var formResponses = form.getResponses([formResponses.length-1]);
  var formResponse = formResponses[formResponses.length-1];
  var itemResponses = formResponse.getItemResponses();
  var name = itemResponses[0].getResponse();
  
  var spreadsheet = SpreadsheetApp.openById("1JtVDBs4vXP3sirpc5eN6FsLBierR4ytZh9q-LLIWTJg").getSheetByName("Sheet1");
  var data = spreadsheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == name) {
      SpreadsheetApp.getUi().alert("already registered");
      return;
    }
  }
}

I have checked the sharing settings and confirmed that I am the owner of the form and the sheet. I have also tried creating a copy of the form and using the new ID, but the problem persists. I have also tried modifying the script by changing the ID of the form and sheet, but I keep getting syntax errors. I am looking to have the form show the message already registered when a name already exists on the sheet.

I'm not sure about this statement `var formResponses = form.getResponses([formResponses.length-1]);`. Try this instead `var formResponses = form.getResponses();` — TheWizEd, Mar 20 '23 at 13:24
Why not use `indexOf`? Get the name (and row) from the latest submission, lookup the name in the relevant column using `indexOf` and if you find a match and the rows do not match, then you've got a dupe. — Tedinoz, Mar 20 '23 at 13:25
Related question: [How to check if current submission is editing response or a new response](https://stackoverflow.com/a/39478935/1330560) — Tedinoz, Mar 20 '23 at 13:26
I believe `formResponses` is empty. `getResponses()` has 2 forms, an empty parameter list or a Date object. The integer [formResponse.length-1] is not a recent date. — TheWizEd, Mar 20 '23 at 13:29

score 0 · Answer 1 · answered Mar 20 '23 at 16:46

I would use the Spreadsheet trigger:

function onFormSubmit(e) {
  const ss = SpreadsheetApp.getActive();
  const sh = e.range.getSheet();
  const osh = ss.getSheetByName("Sheet1")
  const names = e.range.getRange(2, 2, e.range.rowStart - 1).getValues().flat();
  if (~names.indexOf(e.values[1])) {
    SpreadsheetApp.getUi().alert(`${e.values[1]} already registered`);
    return;
  } else {
    let vs = sh.getDataRange().getValues()
    osh.clearContents();
    osh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
  }
}

prevent duplicate entries from being submitted to a Google Sheet from a Google Form

1 Answers1