如果有需要,这里还有另一种解决方案:
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();
const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);
const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);
const colors = range.getBackgrounds();
indexes.forEach(x => colors[x][0] = "yellow");
range.setBackgrounds(colors);
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();
const is_duplicate = (x,arr) => arr.indexOf(x) != arr.lastIndexOf(x);
const indexes = data.map((x,i) => is_duplicate(x,data) ? i : '').filter(String);
const colors = range.getBackgrounds();
indexes.forEach(x => colors[x][0] = "yellow");
const data_all = sheet.getDataRange().getValues();
const duplicates = indexes.map(x => data_all[x]);
return duplicates;
}
setBackgrounds()
方法一次性为范围(列)设置所有背景将更快。 - Yuri Khristich=countif(A:A,A1)>1
- pgSystemTester