Delete row based of duplicate cells in a column using Google Apps Script

user_11581003 0
Viewed 654

I want a google app script that can delete rows in a spreadsheet which have duplicate values in a specific column (say the very first column being the unique ID). Now, I want it to specifically keep the bottom most duplicate row and delete all the duplicate rows above it.

In the below image, you can see the highlighted ID's are duplicated because their postcode have been changed. I want to keep the very bottom row of the duplicate and delete the top rows. For example, for Lydia and Lizzy, I want to keep rows 9 & 10, which have the most up-to-date postcode and I want to delete rows 2, 5 & 8.

Is there a google app script that can do that?

Here is an example image of what I want to delete:

Here is an example image of what I want to delete

google-apps-script google-sheets
1 Answers

It is unclear why you would want to use a script here, given that a range can be filtered as requested with a plain vanilla spreadsheet formula, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      unique(A2:A), 
      sort(A2:D, row(A2:D), false), 
      column(A2:D), 
      false 
    ) 
  ) 
)

To replace your original data with the unique results, use ControlC to copy and ControlShiftV to paste values only. On a Mac, use āŒ˜C and āŒ˜ShiftV.

The question specifies Apps Script, so here's a custom function to do the same, together with a simple function that uses it to filter a hard-coded range in a spreadsheet.

/**
* Removes duplicates in 'Sheet1!A2:D'.
* Keeps each last row where a key appears.
* Other rows are cleared.
*/
function removePriorDuplicates() {
  const range = SpreadsheetApp.getActive().getRange('Sheet1!A2:D');
  const uniques = getLastUniques(range.getValues(), 0);
  range
    .clearContent()
    .offset(0, 0, uniques.length, uniques[0].length)
    .setValues(uniques);
}

/**
* Gets exactly one row per each unique key in a key column.
* Keeps each last row where a key appears.
* Other rows are filtered out.
*
* @param {Object[][]} array2D The array to filter.
* @param {Number} columnIndex The zero-indexed column number where keys appear.
* @return {Object[][]} A 2D array where there is just one copy of each key.
* @customfunction
*/
function getLastUniques(array2D, columnIndex) {
  // version 1.0, written by --Hyde, 16 September 2021
  //  - see https://stackoverflow.com/a/69204946/13045193
  const keys = array2D.map(row => row[columnIndex]);
  return array2D.filter((row, index) => keys.lastIndexOf(row[columnIndex]) === index);
}
user_130451930
Related