This macro allows to check which entries on a sheet are duplicated in another, and deletes them. Instead of deleting one by one, which in GoogleSheets is extremely slow, the algorithm gets the runs of matches (e.g., starting in position K there are N matches) and then deletes them by blocks. The worst case scenario would be if the matches were intermittent, in such case the performance would be as bad as deleting one by one.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
// Checks if 'Edited Sheet' has duplicated rows from 'Original Sheet' and deletes them. function removeDuplicates() { var sheetEdited = SpreadsheetApp.getActive().getSheetByName('Edited Sheet'); if (!sheetEdited) { SpreadsheetApp.getUi().alert('No sheet found.'); return; } var sheetOriginal = SpreadsheetApp.getActive().getSheetByName('Original Sheet'); // In which row does our data start? var offset = 2; // NOTICE: change the ranges if necessary! var dataEdited = sheetEdited.getRange("A" + offset + ":W"+sheetEdited.getLastRow()).getValues(); var dataOriginal = sheetOriginal.getRange("A" + offset + ":W"+sheetOriginal.getLastRow()).getValues(); var areRowsEqual = function(a, b) { return a.sort().toString() == b.sort().toString(); }; // As deleting rows one by one is really slow, we will create an array as: // rowsToDelete = [[starting_position, how_many_to_delete], ...] // And then we will use de deleteRows() method which takes exactly those arguments. var rowsToDelete = []; var pointer = -1; var sum = 0; for (var i = 0; i < dataEdited.length; i++) { // We want to check if this entry (dataEdited[i]) is in dataOriginal. If so, we will // delete that row. var match = false; for (var j = 0; j < dataOriginal.length; j++) { if (areRowsEqual(dataEdited[i], dataOriginal[j])) { match = true; break; } } // As long as sum is 0 (i.e., previous entry was not matched), we move our pointer. if (sum == 0) { pointer = i; } else { // If sum is not 0 we are in a run of matches and the pointer keeps its position. // We check whether we are not matching anymore to stop here, store the result // and start over by reseting sum to 0. if (!match || i + 1 == dataEdited.length) { rowsToDelete.push([pointer, sum]); sum = 0; } } // If we've matched, we add 1 to sum. sum += match; } // We reverse our array because we want to start deleting from the end, as otherwise // our pointer won't be matching the right position anymore. rowsToDelete.reverse(); for (var i = 0; i < rowsToDelete.length; i++) { sheetEdited.deleteRows(rowsToDelete[i][0] + offset, rowsToDelete[i][1]); } } |