Google表格 - 仅当它们在同一天提交时才能删除重复项 [英] Google Sheets - Remove Duplicates Only if They Were Submitted on the Same Day

问题描述

我正在运行一个表单，该表单已加入Google表单中。我们遇到了用户点击提交多次的问题，导致在快速连续的多次提交相同的信息。但是，我们也有用户在以后回来并填写新表单。

我发现如何删除重复的条目 - 但是是否也可以定义脚本只在同一天提交脚本才能删除它们？
表单自动添加此格式的时间戳：8/15/2016 16:39:27

我的脚本如下所示：

  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）;它检查列D（3）和E（4）（其对应于用户的邮政编码和电子邮件地址）复制。
 
 
 
如果需要，我可以共享工作表 - 它包含用户的电子邮件地址和家庭地址，所以我会需要重新填充虚拟数据！
解决方案 
您可以在 if（row [3] == ...  block。假设时间戳在第一列： 
 
 
  var oldTime = Date.parse（row [0]）; 
 if（newTime-oldTime <（1000 * 60 * 60 * 24）&& oldTime！= newTime）duplicate = true; //数字在24小时内为毫秒
  
这样，一旦您检查了相同的邮政编码和电子邮件地址（您是否真的需要邮政编码，因为电子邮件地址是唯一的？），如果在24小时内最后的回应se。&& oldTime！= newTime 确保在事件到达嵌套循环时不会删除事件。
 
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!
解决方案
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.

                        
这篇关于Google表格 - 仅当它们在同一天提交时才能删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！