Google Sheets - Remove Duplicates Only if They Wer

问题:

I'm running a form, which feeds into a Google Sheet. We're running into the issue of users clicking submit multiple times resulting in multiple submissions of the same information in rapid-fire succession. However, we also have user come back at a later date and fill out a new form.

I've found how to remove duplicate entries - but is it possible to also define the script to only remove them if they were submitted on the same day? The form automatically adds a timestamp in this format: 8/15/2016 16:39:27

My script is as follows:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newData){
      if(row[3] == newData[j][3] && row[4] == newData[j][4]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

It checks columns D (3) and E (4) (which correspond to the user's zip code and email address) for duplicates.

I can share the sheet if needed - it has both email addresses and home addresses of our users, so I'd need to re-populate with dummy data!

回答1:

You could add something like the following lines inside your if(row[3]==... block. Assuming the timestamp is in the first column:

var newTime = Date.parse(newData[j][0]);
var oldTime = Date.parse(row[0]);
if (newTime-oldTime<(1000*60*60*24) && oldTime != newTime) duplicate=true; // number is milliseconds in 24 hours

This way, once you check for the same zip code and email address (do you really need the zip code since email addresses are unique?), you mark the response as duplicate if it was submitted within 24 hours of the last response. The && oldTime != newTime makes sure you don't delete the event when it reaches itself in the nested loop.