Form Google Script Prevent Duplicates

问题:

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?

回答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.