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

Question

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

Of course, but you need to show you tried something. If you want someone to write code from scratch, you should post on Upwork ;) — Dmitry Kostyuk
– Dmitry Kostyuk, Commented Sep 16, 2021 at 8:16
Change the way you do your data entry to always insert a row at the top when entering new data. Then just use the tool Menu>Data>Remove Duplicates. — MattKing
– MattKing, Commented Sep 16, 2021 at 17:48

doubleunary · Accepted Answer · 2021-09-16 09:27:43Z

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);
}

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related