Google脚本可以删除电子表格中的重复行，并根据时间戳记保留最近的条目 [英] Google script to remove duplicate rows in spreadsheet and keep the most recent entry based on timestamp

问题描述

我有一个由表单填充的Google电子表格，所以时间戳会自动添加到每一行的第一列中。我有一个脚本可以删除我的电子表格中的重复行（5个特定的列必须是相同的，因为它是重复的，而其他一些列被忽略），但我想修改它，以便如果我有相同的行人员的数据但具有不同的时间戳，脚本将保留最近的一行。我该怎么做？谢谢！

I have a google spreadsheet that is populated by a form, so timestamps are automatically added in the first column for each row. I have a script that removes duplicate rows in my spreadsheet (5 specific columns must be the same for it to be a duplicate, while some other columns are ignored), but I want to modify it so that if I have multiple rows for the same person's data but with different timestamps, the script will keep the most recent row. How would I do this? Thanks!

/** removes duplicate rows in studentsheet **/
function removeDuplicates() {
  var newData = new Array();
  for(i in studentdata){
    var row = studentdata[i];
    var duplicate = false;
    for(j in newData){
      if(row[1] == newData[j][1] && row[2] == newData[j][2] && row[5] == newData[j][5] && row[9] == newData[j][9] && row[10] == newData[j][10]){
      duplicate = true; //first name, last name, grade, dad's first name, and mom's first name are the same
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  StudentSheet.clearContents();
  StudentSheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  sortSheet(); //sorts sheet by 2 columns
}

推荐答案

在完成了我以前的答案之后，我认为另一种方法会减少现有代码的中断。

After working up my previous answer, which I believe to be the better, I considered another approach that would cause less disruption to your existing code.

你推第一个非从studentdata重复到新数组，所以如果studentdata按测试之前的时间戳排序，则遇到的第一个非重复的被重新排列将是最新的。

You push the first non duplicate from studentdata to the new array so if studentdata is sorted by timestamp descending before the test the first non duplicate encountered that is pushed will be the latest.

将以下内容放在你的功能的开始应该实现

Placing the following at the very beginning of you function should achieve

for( var i = 0; i < studentdata.length; i++ ) {
  // add sortable date to beginning of rows
  studentdata[i].unshift(Utilities.formatDate(studentdata[i][0], "GMT", "yyyyMMddHHmmss"));
  }
  studentdata.sort();
  studentdata.reverse();
  // remove temp sort date from beginning of rows
  for( var i = 0; i < studentdata.length; i++ ) {
    studentdata[i].splice(0, 1);
  }

这篇关于Google脚本可以删除电子表格中的重复行，并根据时间戳记保留最近的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！