*Ugh.., I have so many duplicate rows in my spreadsheet*. Well so do many other people. With the script in this article, you can introduce a menu button that lets you delete duplicate rows in your spreadsheet.

\
This blog is the second part of the blog I published a few days ago: “[How to Highlight Duplicate Rows in Google Sheets?](https://hackernoon.com/how-to-highlight-duplicate-rows-in-google-sheets)”. There I provided a simple script to highlight duplicate rows. This script will extend that script to delete those duplicate rows.

\
## For Non-Coders

If you’re not a coder and don’t wanna be bothered with an explanation then follow this step:

\
 ![Open Apps Script Editor ](https://cdn.hackernoon.com/images/gjQkVcwbXQPVp1wM6h9gfXvD0Vp1-2023-03-15T17:00:02.013Z-clf9xh3v7000001s63jg0453z)

\

1. Open Script editor. On your spreadsheet click the extensions tab and open the apps script as shown in the image above.

   \
2. Remove all the codes in **Code.gs**. Then copy and paste the code from the **Full Code** section in this blog.

   \
3. Save and then reload the **spreadsheet**. Now, reopen the Apps script as instructed in step 1 (*Sometimes the script doesn't work without saving and reloading).*

   \


:::info
If you’re already using the previous script then you can just copy and paste this script **over** the previous one.

:::

## Full Code

Here’s the full script for this blog including the previous blog’s code.

\
```javascript
/**
 *The colorDuplicateRows() colors the duplicate rows.
 *Each unique rows and its duplicates are highlighted with different color
 */
function colorDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();

  // find lastCol
  let lastCol = Math.max(...data.map((arr) => arr.length));

  // convert nested arrays to string
  data = data.map((arr) => arr.join(""));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // create unique color for each unique arr
  let uniqueColor = {};
  uniqueRows.forEach((val) => (uniqueColor[val] = getRandomUniqueColor()));

  // console.log(uniqueColor);
  // find duplicate row for each item
  let duplicateRows = data
    .map((x, i) => (isDuplicateRow(x, data) ? i + 1 : ""))
    .filter(String);
  // console.log(duplicateRows);

  // now reset color before highlighting duplicate rows
  colorReset();

  duplicateRows.forEach((rowNum) => {
    for (let i = 0; i < uniqueRows.length; i++) {
      //console.log(range.getValues())
      // compare each item with uniqe items and assing color accordingly
      if (
        sheet.getRange(rowNum, 1, 1, lastCol).getValues().flat().join("") ===
        uniqueRows[i]
      ) {
        sheet
          .getRange(rowNum, 1, 1, lastCol)
          .setBackground(uniqueColor[uniqueRows[i]]);
      }
    }
  });
}

/**
 * Function takes two items: row and arr.
 * The parameter "row" is a string to be compared to items in array "arr".
 * Inspired from https://stackoverflow.com/a/68424642/6163929
 * @param {String} row
 * @param {Array<String>} arr
 * @returns {Boolean}
 */
function isDuplicateRow(row, arr) {
  return row === "" ? false : arr.indexOf(row) != arr.lastIndexOf(row);
}

/**
 * Menu creates menu UI in spreadsheet.
 */
function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows"); // Or DocumentApp or SlidesApp or FormApp.

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Delete Duplicate Row", "deleteDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
}

/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * ColorReset is used to reset bg color of spreadsheet to its original color.
 */
function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}

/**
 * Function creates a unique random color as hashcode.
 * @returns {String}
 */
function getRandomUniqueColor() {
  // thanks to function https://dev.to/rajnishkatharotiya/generate-unique-color-code-in-javascript-3h06
  let n = (Math.random() * 0xfffff * 1000000).toString(16);
  return "#" + n.slice(0, 6);
}


/**
 *The deleteDuplicateRows() funciton deleted the duplicate 
 *rows in current active spreadsheet.
 */
function deleteDuplicateRows() {
  // get sheet and data
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getDataRange().getValues();



  // convert nested arrays to string
  data = data.map((arr) => arr.join(";"));
  // find unique ones among arrays
  let uniqueRows = [...new Set(data)].filter(String);
  // convert the values from string to qrray again
  let newData = uniqueRows.map(itemStr => itemStr.split(";"));

  // find last row and col for new range.
  let newLastRow = newData.length;
  let newLastCol = Math.max(...newData.map((arr) => arr.length));

  // reset previous colors if the highlight function is used
  colorReset();


  // clear previous contents to save only unique values
  sheet.clearContents();
  // set new values
  sheet.getRange(1, 1, newLastRow, newLastCol).setValues(newData);

}


/**
 * Code By Nibesh Khadka.
 * I am freelance and Google Workspace Automation Expert.
 * You can find me in:
 * https://linkedin.com/in/nibesh-khadka
 * https://nibeshkhadka.com
 * me@nibeshkhadka.com
 */
```

\

:::info
**deleteDuplicateRows()** is the new function added to the script of the previous blog. Also, the menu item is extended to add the function.

:::

\
Similar to the previous blog, you can now just:

\

1. Save the code.
2. Reload the spreadsheet.
3. And execute the function.

\
You’ll able to see the menu with the title “Highlight Duplicate Rows“.

 ![Highlight and Delete Duplicate Rows Menu](https://cdn.hackernoon.com/images/gjQkVcwbXQPVp1wM6h9gfXvD0Vp1-2892pj9.jpeg)

\
Now, you can check duplicate rows  in your spreadsheet and delete them if you want with ease.


---

Also published [here.](https://kcl.hashnode.dev/how-to-highlight-duplicate-rows-in-google-sheets) 

\
\
\

***

**Thank You for Your Time**

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me [know](https://nibeshkhadka.com/contact/?ref=hackernoon.com).

\
Don’t forget to like and share this blog.

\
\


2022 - Tech Tutorial Teacher of the Year

Subscribe To Our News Letter For Free Contents

Hire Me

Newsletter

Removing Duplicates in Google Sheets: A Guide for Non-coders

Too Long; Didn't Read

@kcl

RELATED STORIES