google-apps-script - 基于一列删除重复项并在谷歌表格中保留最新条目(同时忽略该列中的空白条目)

Question

我正在处理一个电子表格，其中的数据通过 API 实时添加。 API 中的数据来自注册时事通讯的用户，其中包含基本数据。从 de API 发送数据时，它会作为新行添加到电子表格中。

用户还可以选择稍后回答其他时事通讯问题，这也会导致 API 添加一个新行，将额外的数据放在不同的列中，但仍会显示以前已知的现有数据。

为了避免困惑，我想根据一列删除重复项并保留 Google 表格中的最后一个条目。这会导致删除旧的基本数据行，只保留包含其他数据的行。为了强调这是由用户“更新”的数据，我还突出显示了这一行。用于将提交标记为重复的数据将基于用户的电子邮件地址。因为这在两种情况下都将保持不变。 [我可能必须小心大写和小写字母，脚本不会将两封电子邮件视为重复，我还没有答案]

除此之外，我已经有一个脚本可以将当前时间和日期添加到添加的行并将其放在第一列中。

对于重复的问题，我已经找到了一个类似的问题Remove duplicates based on one column and keep latest entry in google sheets Tanaike 的解决方案非常有帮助。总的来说这段代码对我有用，但有时脚本似乎在不应该运行的时候运行。

我当前的脚本如下所示:

function onChange(e){
  if (e.changeType == 'INSERT_ROW'){
    const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sh.getRange(sh.getActiveRange().getRow(), 1).setValue(new Date());
  } 
}

function removeDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Inzendingen');  
  var dt = sh.getDataRange().getValues();
  var uA = [];
  for (var i = dt.length - 1; i >= 0; i--) {
    if (uA.indexOf(dt[i][4]) == -1) {
      uA.push(dt[i][4]);
    } else {
      sh.deleteRow(i + 1);
      Utilities.sleep(500);
      sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).setBackground('lightblue');
    }
  }
}

我添加了 Utilities.sleep(500); 以防止被删除的行快于突出显示的情况。因此，为了防止在最新输入的行下方的底部有一个空的突出显示行。

两个脚本都设置了触发器:来自电子表格 - 更改

如果一切按计划进行，它应该像这样工作(所有假数据，不用担心):

我的问题如下:

目前API首次添加的一些新用户也被高亮显示。我怀疑这与以下事实有关:当电子邮件列的值为空时，重复删除也有效。 但是，鉴于我对这些问题的了解有限，这只是一个假设。

看到的是这个例子:

长话短说

我希望这个脚本能够按照我的预期运行，它只删除基于 E 列中重复电子邮件地址的重复项。如果重复删除脚本也忽略大写，那就更好了。最后，它还会忽略 E 列中的空白条目。

我尝试使用脚本 Remove duplicates based on one column and keep latest entry in google sheets

并在此脚本中添加一些内容。像这样的东西:

function removeDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Inzendingen');  
  var dt = sh.getDataRange().getValues();
  var uA = [];
  for (var i = dt.length - 1; i >= 0; i--) {
    if (uA.indexOf(dt[i][4]) == -1 && dt.length !=0 ) {
      uA.push(dt[i][4]);
    } else {
      sh.deleteRow(i + 1);
      sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).setBackground('lightblue');
    }
  }
}

我认为添加 && dt.length !=0 会向“if”发出信号，仅在存在重复且值/长度不为 0 时触发。

Answer 1

如果我没理解错的话，唯一的问题是这些新人没有突出显示电子邮件。我相信您走在正确的轨道上，但是您有 dt.length != 0，它正在查看整个数组。相反，您只想检查电子邮件。

因此，您可以使用它:

dt[i][4].length != 0或

dt[i][4] != ""

编辑:我相信这会给你想要的结果。空白电子邮件将被忽略，重复电子邮件将忽略大小写。

function removeDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Inzendingen');  
  var dtAll = sh.getDataRange().getValues();
  dt = dtAll.map(function(f){return [f[4].toLowerCase()]});
  var uA = [];
  for (var i = dt.length - 1; i >= 0; i--) {
    if (uA.indexOf(dt[i][0]) == -1) {
      uA.push(dt[i][0]);
      Logger.log(uA[i]);
    } else if (dt[i][0] != ""){
      sh.deleteRow(i + 1);
      sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).setBackground('lightblue');
    }
  }
}