Google脚本应用删除Google表格中特定列中具有特定值的重复行 [英] Google Script App Delete Duplicate Rows with a Specific Value in Specific Column in Google Sheet

问题描述

我有一个Google工作表，人们在其中提交他们正在处理的任务，他们将提交状态更新，对于任务A，状态更新为进行中"或完成".任务A在D列中[4]状态在列E [5]中.我正在尝试使此代码仅在重复的任务A的状态为已完成"时才删除任务A的进行中"行.这可以识别重复项并按顺序删除重复的行，但是我不确定如何在存在任务A"完成"行时仅删除重复的任务A"进行中"行.任何帮助将非常感激！

I have a google sheet where people submit tasks they are working on and they'll submit a status update which is either 'in progress' or 'complete' for let's say task A. Task A is in column D[4] and Status is in column E[5]. I'm trying to get this code to only delete the 'in progress' row for Task A when there is duplicate Task A with the status being 'Complete'. This works for identifying the duplicate and removes the duplicate rows in order, but I'm not sure how to get it to only delete duplicate 'Task A' 'In Progress' rows when there is a 'Task A' 'Complete' row. Any help would be much appreciated!

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

推荐答案

我会尝试

function removeDuplicates()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var indexOfStatusColumn = 4;
  var newData = [];
  data.forEach(function (row, rowI){
    var isDuplicate = false
    if (newData.length)
    newData.forEach(function (dup, dupI)
    {
      if (dup[3] == row[3])
      {
        if (row[indexOfStatusColumn] == "Complete" && dup[indexOfStatusColumn] != "Complete")
          newData[dupI][indexOfStatusColumn] = "Complete";
        isDuplicate = true;
        return (false);
      }
    })
    if (!isDuplicate)
      newData.push(row);
  });
  dataRange = sheet.getRange(2, 1, dataRange.getLastRow() - 1, dataRange.getLastColumn());
  dataRange.clearContent();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

几件事

1. 我使用forEach()循环更有效
2. 通过返回false来中断循环，以避免无用地解析所有newData谷底
3. 当我找到一个副本时，我将执行某些操作

1. I use forEach()loop it's more efficient
2. break loop by returning falseto avoid parsing trough all your newDatauselessly
3. when I find a duplicate I perform some of this actions

1. 检查是否在data上重复了Complete，而不是在newData上
2. 以这种方式将newData中的值更改为"Complete"(完整)(请注意，如果两行中都有不同的数据，可能会扭曲数据).

1. Check if duplicate is Complete on data and not on newData if so
2. change the value in newDatato "Complete" this way it will keep the complete status (be careful if there's different datas on both rows it will probably twist datas).

还要在一定范围内使用clearContent()以避免除去工作表的所有内容，而仅除去工作表的特定部分.在这里，我重建了它以保留标题

also use clearContent() from a range to avoid removing all the content of the sheet but only a specific portion of it. Here I've rebuilded it to keep the header

参考

forEach()

Tanaike基准

clearContent()

这篇关于Google脚本应用删除Google表格中特定列中具有特定值的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！