. 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. Ugh.., I have so many duplicate rows in my spreadsheet This blog is the second part of the blog I published a few days ago: “ ”. There I provided a simple script to highlight duplicate rows. This script will extend that script to delete those duplicate rows. How to Highlight Duplicate Rows in Google Sheets? For Non-Coders If you’re not a coder and don’t wanna be bothered with an explanation then follow this step: Open Script editor. On your spreadsheet click the extensions tab and open the apps script as shown in the image above. Remove all the codes in . Then copy and paste the code from the section in this blog. Code.gs Full Code Save and then reload the . Now, reopen the Apps script as instructed in step 1 ( spreadsheet Sometimes the script doesn't work without saving and reloading). If you’re already using the previous script then you can just copy and paste this script the previous one. over Full Code Here’s the full script for this blog including the previous blog’s code. /**
 *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
 */ is the new function added to the script of the previous blog. Also, the menu item is extended to add the function. deleteDuplicateRows() Similar to the previous blog, you can now just: Save the code. Reload the spreadsheet. And execute the function. You’ll able to see the menu with the title “Highlight Duplicate Rows“. Now, you can check duplicate rows  in your spreadsheet and delete them if you want with ease. Also published here. 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 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

Buy Me a Coffee

Nominated for 2022 - Tech Tutorial Teacher of the Year

Developer 

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

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

Too Long; Didn't Read

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

Khadka's Coding Lounge.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES