How to Highlight Groups of Identical Text with Unique Colors Using JavaScript in Google Sheets
Nitish Kumar Singh
Sep 23, 2024Hi Everyone! In this post, we are going to add a feature in Google Sheets that highlights groups of cells that have the same text with unique colors by creating a custom menu using Google Apps Script.
Let's first clearly understand what we want and what we have. We have a range of data with groups of identical text. For example, below is an image of sample data.
Here, we want to select the range where we want to highlight the group of identical text's cells with a unique color for each group, then click on the menu item, and the cells get highlighted. Colors are generated and used randomly, ensuring each group has a unique color, and the text remains black for visibility.
Additionally, if we select a cell in an unused area of the sheet, all groups of identical text must get listed in the selected cell's column, starting from that cell, with their respective counts listed in the next column in descending order. The text should also be highlighted with the same unique color as used in the main range. For example, a sample listing is shown in the image below.
You might be wondering when we would need this type of feature. Let me explain when I needed it and how this feature can be useful in many situations. The sample data I have provided is a list of inventory in a warehouse, where column D shows the WID—a unique identifier for a product—and column E shows their locations within the warehouse.
So, when we need to move products to different locations, we create a system task called GTL. When we want to know if a product is available in multiple locations and can fit in one location, we create a GTL that includes all locations with the same WID. I used to look through the data, find a WID that was in many locations, highlight it using the "Find" option, copy the locations, and paste them into the task creation system.
This process is fine for one product, but what if we have 100 products of that type? That's when I created this feature with the help of my coding knowledge, and now I want to share it with others, especially coders. You can use this feature in many situations, whether you are a coder or not.
Steps to Add And Use This Feature in Google Sheets
Click on Extensions > Apps Script : This will open the Apps Script editor in a new tab, where you will see an opened file named code.gs. You can see the editor page in the image below the code block.
Use Below Code: Copy the code below and replace the existing code in the code.gs file by pasting it.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Highlight Groups', 'colorAndListValues')
.addToUi();
}
function colorAndListValues() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList = sheet.getActiveRangeList();
// Validation: Check if valid ranges are selected
if (!rangeList || rangeList.getRanges().length <= 0) {
SpreadsheetApp.getUi().alert('Please select a valid range.');
return;
}
var ranges = rangeList.getRanges();
var rangeCells = ranges[0];
var rangeData = rangeCells.getValues();
// Flatten the 2D array into 1D and count occurrences
var occurrences = {};
rangeData.flat().forEach(function(value) {
occurrences[value] = (occurrences[value] || 0) + 1;
});
// Get values that occur more than once
var repeatedValues = Object.keys(occurrences).filter(function(value) {
return occurrences[value] > 1;
});
var colors = [];
// Random color generator with uniqueness check
repeatedValues.map(function() {
var color;
do {
var r = Math.floor(Math.random() * 155 + 100);
var g = Math.floor(Math.random() * 155 + 100);
var b = Math.floor(Math.random() * 155 + 100);
color = `rgb(${r},${g},${b})`;
} while (colors.includes(color)); // Ensure uniqueness
colors.push(color); // Add the unique color to the colors array
return color;
});
// Apply background colors for repeated values
var bgColors = rangeCells.getBackgrounds();
rangeCells.getValues().forEach(function(row, rowIndex) {
row.forEach(function(cellValue, colIndex) {
var colorIndex = repeatedValues.indexOf(cellValue);
if (colorIndex > -1) {
bgColors[rowIndex][colIndex] = colors[colorIndex];
}
});
});
rangeCells.setBackgrounds(bgColors); // Apply colors in one batch
// Function to list repeated values and apply corresponding colors
function listGroups() {
var cell = ranges[1]; // Second range (a single cell)
if (cell.getNumRows() !== 1 || cell.getNumColumns() !== 1) {
SpreadsheetApp.getUi().alert('Please select a single cell for the listing range.');
return;
}
// Sort values by occurrence
var sortedValues = Object.entries(occurrences).sort(function(a, b) {
return b[1] - a[1];
});
// Apply values and colors to the listing
var listingRange = sheet.getRange(cell.getRow(), cell.getColumn(), sortedValues.length, 2);
var listingValues = [];
var listingColors = [];
sortedValues.forEach(function([value, count], i) {
listingValues.push([value, count]);
var colorIndex = repeatedValues.indexOf(value);
var color = colorIndex > -1 ? colors[colorIndex] : null; // Use 'null' to clear background
listingColors.push([color, color]);
});
listingRange.setValues(listingValues);
listingRange.setBackgrounds(listingColors);
}
if (ranges.length > 1) listGroups(); // If a second range (listing cell) is selected
}The image showing Google Apps Script editor page with above code pasted in code.gs file.
Save and Use: After pasting the code, save the project by clicking on the save button, then refresh the Google Sheets page. After refreshing, you will see an extra custom menu with one item, Highlight Groups, like the one shown in the image below in your sheet's menu bar.
The first time you use this option, you will need to give permission to run the Apps Script code.
To use this feature, simply select the range where you want to highlight the text, optionally select a cell where you want to list all texts with their occurrence counts, and click on the custom menu item.
Explanation of Given Code
If you're a coder and want to understand how the given code works, this section is for you. If you're not a coder, you can still read this section, but understanding it requires some coding knowledge.
The code below creates a custom menu with one option, Highlight Groups. When we click on that option, the specified function is called. The onOpen function is executed every time the sheet is opened.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Highlight Groups', 'colorAndListValues')
.addToUi();
}
function colorAndListValues() {
/*
.....
.....
*/
}
The colorAndListValues function gets the open sheet, retrieves the selected range, validates it, gets the main range and data in a 2D array, flattens it into a 1D array, checks for occurrences, and filters the texts that appear more than once.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList = sheet.getActiveRangeList();
// Validation: Check if valid ranges are selected
if (!rangeList || rangeList.getRanges().length <= 0) {
SpreadsheetApp.getUi().alert('Please select a valid range.');
return;
}
var ranges = rangeList.getRanges();
var rangeCells = ranges[0];
var rangeData = rangeCells.getValues();
// Flatten the 2D array into 1D and count occurrences
var occurrences = {};
rangeData.flat().forEach(function(value) {
occurrences[value] = (occurrences[value] || 0) + 1;
});
// Get values that occur more than once
var repeatedValues = Object.keys(occurrences).filter(function(value) {
return occurrences[value] > 1;
});In the next section of the code, unique colors are generated for each group of identical text. A 2D array of backgrounds for the selected range's cells is created, and then the generated colors are applied to each cell that contains text that occurs more than once, leaving the other cells unchanged. The 2D array allows for applying all backgrounds in a batch.
var colors = [];
// Random color generator with uniqueness check
repeatedValues.map(function() {
var color;
do {
var r = Math.floor(Math.random() * 155 + 100);
var g = Math.floor(Math.random() * 155 + 100);
var b = Math.floor(Math.random() * 155 + 100);
color = `rgb(${r},${g},${b})`;
} while (colors.includes(color)); // Ensure uniqueness
colors.push(color); // Add the unique color to the colors array
return color;
});
// Apply background colors for repeated values
var bgColors = rangeCells.getBackgrounds();
rangeCells.getValues().forEach(function(row, rowIndex) {
row.forEach(function(cellValue, colIndex) {
var colorIndex = repeatedValues.indexOf(cellValue);
if (colorIndex > -1) {
bgColors[rowIndex][colIndex] = colors[colorIndex];
}
});
});
rangeCells.setBackgrounds(bgColors); // Apply colors in one batchFinally, the code handles listing all text in a column based on their occurrence count. The code ensures the second selected range is a single cell, sorts the text, and applies the same backgrounds to the listed texts that were used for highlighting in the main range, again in a batch.
// Function to list repeated values and apply corresponding colors
function listGroups() {
var cell = ranges[1]; // Second range (a single cell)
if (cell.getNumRows() !== 1 || cell.getNumColumns() !== 1) {
SpreadsheetApp.getUi().alert('Please select a single cell for the listing range.');
return;
}
// Sort values by occurrence
var sortedValues = Object.entries(occurrences).sort(function(a, b) {
return b[1] - a[1];
});
// Apply values and colors to the listing
var listingRange = sheet.getRange(cell.getRow(), cell.getColumn(), sortedValues.length, 2);
var listingValues = [];
var listingColors = [];
sortedValues.forEach(function([value, count], i) {
listingValues.push([value, count]);
var colorIndex = repeatedValues.indexOf(value);
var color = colorIndex > -1 ? colors[colorIndex] : null; // Use 'null' to clear background
listingColors.push([color, color]);
});
listingRange.setValues(listingValues);
listingRange.setBackgrounds(listingColors);
}
if (ranges.length > 1) listGroups(); // If a second range (listing cell) is selectedThe current code does not check if the listing area is empty, but this can be easily added to the listGroups() function to prevent overwriting existing data.
I hope you found this post informative and valuable. You can now use this feature or build your own custom menu options to suit your needs. Happy coding!
