时间戳

首页 > 解决方案 > 使用 Apps 脚本识别未定义工作表中相同列中的重复值

javascript - 使用 Apps 脚本识别未定义工作表中相同列中的重复值

问题描述

我有一个电子表格，其工作表的数量正在增长。我想突出显示所有未定义工作表中相同列中的重复值。我可以使用条件格式来做到这一点，但是随着工作表的数量会增加很多，这种方法是无效的。

我试过类似的东西：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();

function readSheetData() {
  var rowRange = sheet.getRange(1, 1, lastRow, lastColumn);
  var rangeArray = rowRange.getValues();
  // Convert to a one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  return rangeArray;
}

// Creates an array with data from a chosen column
function readColumnData(column) {
  var columnRange = sheet.getRange(1, column, lastRow);
  var rangeArray = columnRange.getValues();
  // Convert to one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  return rangeArray;
}

// Creates an array with data from a chosen row
function readRowData(row) {
  var rowRange = sheet.getRange(row, 1, 1, lastColumn);
  var rangeArray = rowRange.getValues();
  // Convert to one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  Logger.log(rangeArray);
  return rangeArray;
}

// Sort data and find duplicates
function findDuplicates(data) {
  var sortedData = data.slice().sort();
  var duplicates = [];
  for (var i = 0; i < sortedData.length - 1; i++) {
    if (sortedData[i + 1] == sortedData[i] && sortedData[i] != "") {
      duplicates.push(sortedData[i]);
    }
  }
  return duplicates;
}

// Find locations of all duplicates
function getIndexes(data, duplicates) {
  var column = 2;
  var indexes = [];
  i = -1;
  // Loop through duplicates to find their indexes
  for (var n = 0; n < duplicates.length; n++) {
    while ((i = data.indexOf(duplicates[n], i + 1)) != -1) {
      indexes.push(i);
    }
  }
  return indexes;
}

// Highlight all instances of duplicate values in a sheet
function highlightSheetDuplicates(indexes) {
  var row;
  for (n = 0; n < indexes.length; n++) {
    row = 1;
    if (indexes[n] > lastColumn) {
      row = Math.floor(indexes[n] / lastColumn);
      indexes[n] = indexes[n] - lastColumn * row;
      row++;
    }
    sheet.getRange(row, indexes[n] + 1).setBackground("red");
  }
}


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

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

//----------- Main -------------

function sheetMain() {
  var data = readSheetData();
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightSheetDuplicates(indexes);
}

function columnMain(column) {
  var data = readColumnData(column);
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightColumnDuplicates(column, indexes);
}

function rowMain(row) {
  var data = readRowData(row);
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightRowDuplicates(row, indexes);
}

// ---------- Menu ----------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('DUPLICATED')
    .addItem('Sheet', 'sheetMain')
    .addItem('Row', 'showRowPrompt')
    .addItem('Column', 'showColumnPrompt')
    .addToUi();
}

// ---------- Prompt ----------
function showColumnPrompt() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt(
    'Find Duplicates',
    'Enter letter of column to search:',
    ui.ButtonSet.OK_CANCEL);
  // Get user response, run main
  var button = response.getSelectedButton();
  var text = response.getResponseText();
  if (button == ui.Button.OK) {
    text = sheet.getRange(text + "1");
    text = text.getColumn();
    columnMain(text);
  }
}

function showRowPrompt() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt(
    'Find Duplicates',
    'Enter number of row to search:',
    ui.ButtonSet.OK_CANCEL);
  // Get user response, run main
  var button = response.getSelectedButton();
  var text = response.getResponseText();
  if (button == ui.Button.OK) {
    rowMain(text);
  }
}

但它只适用于活动工作表的列/行。它还启用了一个非常有用的触发菜单。

预期结果：

  1. 从菜单运行脚本
  2. 突出显示 Sheet_1 A:A、Sheet_2 A:A、Sheet_3 A:A... 中具有相同值的红色背景单元格。
  3. 对 Sheet_1 D:D、Sheet_2 D:D、Sheet_3 D:D 中的值执行相同操作
  4. 对 Sheet_1 J:J、Sheet_2 J:J、Sheet_3 J:J 中的值执行相同操作
  5. 手动删除一些突出显示的行
  6. 再次运行脚本
  7. 深呼吸，喝点茶

PS我不想在第一行中寻找重复项（固定标题）

如果有人可以帮助我解决这个我已经工作了三天但没有找到正确解决方案的问题，我将非常感激，我尝试了不同的方法，但我找不到理想的解决方案。

谢谢！‍</p>

标签： javascriptfunctiongoogle-apps-scriptgoogle-sheetstriggers

解决方案

假设：
原始值来自最早的工作表，因此大多数副本可以在以后的工作表中找到，或者至少在第一张工作表的最后一行中找到。

function findDuplicateInMultipleColumnsAndMultipleSheets() {
  const names = ['Sheet1', 'Sheet2', 'Sheet3'];//sheet names included
  const colors= ['#ff0000','#ffff00','#00ffff'];//added different colors for each column
  const cols = [1, 4, 10];//columns
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets().filter(s => { return ~names.indexOf(s.getName()) });//only gets the sheets with names in the names array
  let uA = new Array(cols.length);//create unique array for all three cols
  cols.forEach((c, i) => {
    uA[i]= new Array(1);
    shts.forEach((sh, j) => {
      let vs = sh.getRange(2, c, sh.getLastRow() - 1, 1).getValues().flat();
      vs.forEach((v, k) => {
        if (!~uA[i].indexOf(v)) {
          uA[i].push(v);//unique array for each column for all three sheets
        }
      });
    })
    let obj={};//used to record first matches which are assumed to be originals
    shts.forEach((sh,j)=>{
       let vs = sh.getRange(2, c, sh.getLastRow() - 1, 1).getValues().flat();
      vs.forEach((e, k) => {
        if (~uA[i].indexOf(e)) {
          if(!obj.hasOwnProperty(e)) {
            obj[e]=1;//first one gets recorded
          } else {
            if(e) {
              sh.getRange(k+2,c).setBackground(colors[i]);//copies get background changed if they're not blank
            }
          }
        }
      });
    })
  });
}

我的数据：Sheet1：

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
字符串1 25 25 字符串1 3 2 24 0 1 字符串1
字符串2 9 22 字符串2 23 3 11 20 4 字符串2
字符串3 7 28 字符串3 19 19 22 7 3 字符串3
字符串4 10 21 字符串4 25 12 11 0 0 字符串4
字符串5 2 9 字符串5 7 6 29 15 4 字符串5
字符串6 21 26 字符串6 21 18 20 1 6 字符串6
字符串7 7 25 字符串7 3 21 5 28 29 字符串7
字符串8 17 2 字符串8 6 19 20 26 2 字符串8
字符串9 9 26 字符串9 12 21 20 19 18 字符串9
字符串4 9 26 字符串4 12 21 20 19 18 字符串4
9 26 12 21 20 19 18

在此处输入图像描述

表2：

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
字符串8 28 6 字符串8 14 28 0 9 29 字符串8
字符串9 13 24 字符串9 27 1 26 22 21 字符串9
字符串 10 20 17 字符串 10 9 26 10 24 16 字符串 10
字符串 11 12 24 字符串 11 17 28 17 29 24 字符串 11
字符串 12 18 27 字符串 12 4 23 6 12 11 字符串 12
字符串 13 8 29 字符串 13 21 18 1 24 7 字符串 13
字符串 14 8 21 字符串 14 14 29 2 7 19 字符串 14
字符串 15 23 5 字符串 15 2 20 8 8 9 字符串 15
字符串 16 1 12 字符串 16 22 23 19 5 27 字符串 16
1 12 22 23 19 5 27

在此处输入图像描述

表 3：

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
字符串 15 21 14 字符串 15 27 26 3 23 24 字符串 15
字符串 16 10 25 字符串 16 22 6 20 25 21 字符串 16
字符串 17 3 10 字符串 17 6 18 28 3 10 字符串 17
字符串 18 19 20 字符串 18 6 25 1 11 16 字符串 18
字符串 19 5 0 字符串 19 14 29 27 2 21 字符串 19
字符串20 8 8 字符串20 13 9 27 13 5 字符串20
字符串 21 18 6 字符串 21 1 24 22 3 7 字符串 21
字符串22 6 4 字符串22 26 5 25 5 4 字符串22
字符串23 5 28 字符串23 6 7 17 19 25 字符串23
5 28 6 7 17 19 25

在此处输入图像描述

我正在做其他事情，我以这个问题为例，这种方式工作得很好，你可以通过将它们添加到适当的数组来更改它工作的工作表和你想要包含的列。我

function getCopyData() {
  const ss = SpreadsheetApp.getActive();
  const names = ['Sheet1', 'Sheet2', 'Sheet3'];//sheetames to include
  const colors = ['#ffff00', '#ff00ff', '#00ffff'];//colors to use
  const cols = [1, 4, 10];columns to check you can add more
  const shts = ss.getSheets().filter(s => names.includes(s.getName()));
  const vals = shts.map(s => s.getRange(2, 1, s.getLastRow() - 1, s.getLastColumn()).getValues()).map(a => { return a.map(r => { let row = []; cols.forEach((c, i) => { row.push(r[c - 1]) }); return row; }) });//only gets values for columns that you want
  let uA = Array.from(cols, u => ({}));//create an array of independent objects
  vals.forEach((a, i) => {
    a.forEach((r, j) => {
      cols.forEach((c, k) => {
        if (r[k]) {//doesn't do anything if value is empty
          if (!uA[k].hasOwnProperty(r[k])) {//checks to see if this value has already been found
            uA[k][r[k]] = 0;//if it  hasn't been found then it's the original
          } else {
            uA[k][r[k]] += 1;//if it has then it's a copy
            shts[i].getRange(j + 2, c).setBackground(colors[k%colors.length]);//sets the background color for copies
          }
        }
      });
    })
  });
}

推荐阅读