google-apps-script - 突出显示重复项并复制到新工作表

Question

我有一大张服务器名称表，大约 12,000 行。我需要突出显示任何重复的服务器名称，然后将它们复制到新工作表中。我有一个脚本来突出显示单元格，效果很好(谢谢 Kurt Kaiser)。但是，我无法弄清楚如何将重复项(所有实例)写入新工作表。以下是突出显示重复项的代码片段。

// Highlight all instances of duplicate values in a column
function highlightColumnDuplicates(indexes) {
  var column = 1;
  for (n = 0; n < indexes.length; n++) {
    sheet.getRange(indexes[n] + 1, column).setBackground("yellow");
  }
}

如有任何帮助，我们将不胜感激。

Answer 1

以防万一这里是另一种解决方案:

function main() {
  const duplicates = highlightColumnDuplicates();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dest_sheet = ss.getSheetByName('Duplicates') || ss.insertSheet('Duplicates');
  const dest_range = dest_sheet.getRange(1,1,duplicates.length,1);
  dest_range.setValues(duplicates.map(x=>[x]));
}

function highlightColumnDuplicates() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A1:A" + sheet.getLastRow());
  const data  = range.getValues().flat();  

  // returns true if 'x' is repeated in 'arr'
  const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);

  // get indexes (row numbers) of duplicates in data (in the column)
  const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);

  // change backgrounds like a sir
  const colors = range.getBackgrounds();
  indexes.forEach(x => colors[x][0] = "yellow");
  range.setBackgrounds(colors);

  // get duplicates from the data via indexes
  const duplicates = Array.from(new Set(indexes.map(x => data[x])));  

  return duplicates;
  
}

它将使当前工作表中 A 列中具有重复项的所有单元格成为黄色背景，并将重复项复制到名称为 Duplicates 的工作表上。

---

更新

如果你需要得到完整的行和重复的所有实例，这里是修改后的代码:

function main() {
  const duplicates = highlightColumnDuplicates();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dest_sheet = ss.getSheetByName('Duplicates') || ss.insertSheet('Duplicates');
  const dest_range = dest_sheet.getRange(1,1,duplicates.length,duplicates[0].length);
  dest_range.setValues(duplicates);
}

function highlightColumnDuplicates() {

  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A1:A' + sheet.getLastRow());
  const data  = range.getValues().flat();  

  // returns true if 'x' is repeated in 'arr'
  const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);

  // get indexes (row numbers) of duplicates in data (in the column)
  const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);

  // change backgrounds like a sir
  const colors = range.getBackgrounds();
  indexes.forEach(x => colors[x][0] = "yellow");
  // range.setBackgrounds(colors);

  // get duplicates from the data via indexes
  const data_all  = sheet.getDataRange().getValues();
  const duplicates = indexes.map(x => data_all[x]);

  return duplicates;  
}