You use sheets a lot and sometimes have difficulty identifying duplicate rows. Then after this blog, you’ll be equipped with a script that’ll highlight duplicate rows in your sheet. Namaste! This is Nibesh Khadka. I am a freelancer that develops scripts to automate Google Workspace Apps like Gmail, Google Sheets, Google Docs, etc. 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 ). Full Code Here’s the full script for this blog. /**
 *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"); 

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  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);
}

/**
 * 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 Row Duplicate? To find if a row is duplicate or not we’re using function in our script. isDuplicateRow() function isDuplicateRow(x, arr) {
  return x === "" ? false : arr.indexOf(x) != arr.lastIndexOf(x);
} This function takes two parameters: a string and an array. The string is a row converted to a string and an array is a list of rows each converted to a string. Reset Color and Random Unique Color To highlight duplicate rows we’ll have to do two things. The first is to reset previous highlights(if they exist) with the function. colorReset() Then , with the function. color them with colors that are unique to each set of duplicate rows getRandomUniqueColor() function colorReset() {
  let sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground("");
}


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);
} You should know that the colors will be different each time you run the function since they are random. In real-world use cases, it is difficult to hardcode colors because the number of sets of duplicate rows can vary. Lets Color Duplicate Rows Now that all the helper functions are ready let’s put them all in a proper order to color the duplicate rows in our sheet with our function. colorDuplicateRows() 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]]);
      }
    }
  });
} Ya.. Ok, But where’s the menu? Our function is ready it will work every time you run the function from the script( ) but we want to run this function from our sheet. if not follow step 3 from the non-coders section So, let’s create our custom menu with the function. createCustomMenu() function createCustomMenu() {
  let menu = SpreadsheetApp.getUi().createMenu("Highlight Duplicate Rows");

  menu.addItem("Highlight Duplicate Row", "colorDuplicateRows");
  menu.addItem("Reset Colors", "colorReset");
  menu.addToUi();
} Then, render the custom menu to the spreadsheet with trigger. OnOpen() function onOpen(e) {
  createCustomMenu();
} Save the code and reload the spreadsheet then you’ll able to see the menu with the title “ “. Highlight Duplicate Rows 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

2022 - Tech Tutorial Teacher of the Year

Subscribe To Our News Letter For Free Contents

Hire Me

Newsletter

How to Highlight Duplicate Rows in Google Sheets?

Too Long; Didn't Read

@kcl

RELATED STORIES