Do you know how to sort and filter your data based on color in Google Sheets? This incredibly useful feature allows you to organize your data based on the color of the text or cells.
Imagine you have highlighted certain data rows related to an important client. With color sorting and filtering in Google Sheets, you can easily bring these rows to the top of your data set or even show only those rows.
These methods are also very helpful for removing duplicates in Google Sheets.
And the best part is, they are really easy to use. Let’s see how:
Sorting by Color in Google Sheets
Suppose you have a data set with highlighted rows, such as all the apartments in this data set:
Add a filter (the funnel-shaped icon in the toolbar, marked in red in the image above).
On any column, click on the filter and choose the “Sort by color” option.
You can filter by the background color of the cell (like the yellow in this example) or by the color of the text.
The result of applying this sorting is that all the colored rows will be placed at the top of your data set.
This is extremely useful if you want to review all the items at once. Another reason could be if they are duplicates that you have highlighted and can now remove.
Filtering by Color in Google Sheets
The method of filtering by color in Google Sheets is very similar to sorting by color.
Once you have added filters to your data set, click on them to bring up the menu. Select “Filter by color” and then choose to filter based on the background color of the cell or the color of the text.
In this example, I used color filtering in Google Sheets to only display the highlighted rows in yellow, making it easier to review them.
There is an option to remove the color filter by setting it to “none,” which can be found in the “Filter by color” menu. This option is not available for the sorting by color method.
Solution with Apps Script
When I initially published this article, sorting and filtering by color were not available natively in Google Sheets, so I created a small script to add this functionality to a sheet.
These were added on March 11, 2020. You can read more about it in the Google Workspace update blog.
Here is my original solution with Apps Script, which I will leave here for general interest.
With a few simple lines of Apps Script code, we can implement our own version.
This article will show you how to implement this same functionality in Google Sheets.
It’s a fairly basic idea.
We need to know the background color of the cell we want to sort or filter (user input 1). Then we need to know which column to use for sorting or filtering (user input 2). Finally, we need to perform the sorting or filtering.
So, the first step is to ask the user to input the cell and column.
I have implemented this color sorting feature in Google Sheets using a non-modal dialog, which allows the user to click on cells in the Google sheet independently of the dialog. When the user has selected the cell or column, we store it using the Properties service to retrieve it when we want to sort or filter the data.
Sorting by Color with Apps Script
Overall flow of our program:
- Custom menu to run the color sorting program in Google Sheets
- Dialog to ask the user for the color cell
- Save the color cell using the Properties service
- Second dialog to ask the user for the sort column
- Save the sort column using the Properties service
- Display color and column choices and confirm
- Retrieve background colors of the sort column
- Add a helper column to the sheet data with these background colors
- Sort/filter this helper column, based on the color cell
- Clear the values in the Document Properties memory
Let’s look at each of these sections in more detail.
Add a custom menu (step 1)
This is just standard Apps Script code to add a custom menu to your Google sheet:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Color Tool')
.addItem('Sort by Color...', 'sortByColorSetupUi')
.addItem('Clear Ranges','clearProperties')
.addToUi();
}Ask the user to choose the cell and column (steps 2, 4, and 6)
I am using non-modal dialogs for the prompts, which allows the user to interact with the sheet and click directly on cells they want to select.
function sortByColorSetupUi() {
var colorProperties = PropertiesService.getDocumentProperties();
var colorCellRange = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
var title='No Title';
var msg = 'No Text';
// if colorCellRange does not exist
if(!colorCellRange) {
title = 'Select the Color Cell';
msg = '<p>Please click on the cell with the background color you wish to sort on, then click OK.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
dispStatus(title, msg);
}
// if colorCellRange exists and sortColumnLetter does not exist
if (colorCellRange && !sortColumnLetter) {
title = 'Select the Sort Column';
msg = '<p>Please select the column you wish to sort on, or click on a cell in that column. Click OK when ready.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
dispStatus(title, msg);
}
// if colorCellRange and sortColumnLetter exist
if(colorCellRange && sortColumnLetter) {
title= 'Displaying Color and Sort Ranges';
msg = '<p>Confirm ranges before sorting:</p>';
msg += 'Color Cell Range: ' + colorCellRange + '<br />';
msg += 'Sort Column: ' + sortColumnLetter + '<br />';
msg += '<br /><input type="button" value="Sort by Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
dispStatus(title,msg);
}
}
// Display the non-modal dialog
function dispStatus(title,html) {
var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(350)
.setHeight(200);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
// Helper function to transition from dialog 1 (selecting the color cell) to dialog 2 (selecting the sort column)
function sortByColorHelper(mode) {
var mode = (typeof(mode) !== 'undefined')? mode : 0;
switch(mode) {
case 1:
setColorCell();
sortByColorSetupUi();
break;
case 2:
setSortColumn();
sortByColorSetupUi();
break;
default:
clearProperties();
}
}The buttons on the dialogs use the client-side google.script.run API to call server-side Apps Script functions.
And then google.script.host.close() is also a client-side JavaScript API that closes the current dialog.
Save the cell and column choices to Properties storage (steps 3 and 5)
These two functions save the cell ranges that the user selects to the sheet’s Properties storage:
// save the color cell range to properties
function setColorCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('colorCellRange', colorCell);
}
// save the sort column range to properties
function setSortColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
var sortColumnLetter = sortColumn.split(':')[0].replace(/(d)/g,'').toUpperCase(); // find the column letter
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
}By running these functions, we have the cell address (in A1 format) of the color cell and the letter of the sort/filter column saved in the sheet’s Properties storage for future use.
Sort the data (steps 7, 8, and 9)
Once we have selected both the color cell and the sort/filter column, the program flow will lead us to the actual sorting/filtering of the data. This is the button in the third dialog, which when clicked, executes the google.script.run.sortData() call.
The sortData function is defined as follows:
// sort the data based on the color cell and chosen column
function sortData() {
// get the properties
var colorProperties = PropertiesService.getDocumentProperties();
var colorCell = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
// extract the column letter from the selected range for the sort column
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
// get an array of background colors from the sort column
var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds();
// assume header in row 1
// get background color of sort cell
var sortColor = sheet.getRange(colorCell).getBackground();
// map background colors to 1 if they match the sort cell color, 2 otherwise
var sortCodes = sortColBackgrounds.map(function(val) {
return (val[0] === sortColor) ? [1] : [2];
});
// add a column header to the background color array
sortCodes.unshift(['Sort Column']);
// paste the background color array as a helper column to the right of the data sheet
sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
// sort the data
var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);
dataRange.sort(lastCol+1);
// add a new filter over the whole data table
sheet.getDataRange().createFilter();
// clear the values in the Document Properties memory
clearProperties();
}And finally, we want to have a way to reset the Properties storage so we can start over.
Clear the Properties storage (step 10)
This simple function will delete all key/value pairs stored in the sheet’s Properties storage:
// clear the properties
function clearProperties() {
PropertiesService.getDocumentProperties().deleteAllProperties();
}Running the Color Sorting Script in Google Sheets
If you bundle all these code snippets into your Code.gs file, you should be able to run onOpen, authorize your script, and then run the color sorting tool from the new custom menu.
Here is the color sorting tool in action in Google Sheets:
You can see how all the green rows are sorted to the top of my data set.
Note that this color sorting feature is set up to work with data sets that start in cell A1 (because it relies on the getDataRange() method, which does the same). Some improvements might include making it more generalized (or asking the user to highlight the data set initially). I also didn’t include any error handling, intentionally to keep the script as simple as possible for easier understanding. However, this is something you should consider if you want to make this solution more robust.
Filtering by Color with Apps Script
The program flow is almost identical, except we filter the data instead of sorting it. The code is almost identical as well, except for the variable names being different and the implementation of a filter instead of a sort.
Instead of sorting the data, we create and add a filter to the data set to only show the rows tinted with the corresponding colors:
The part of the code relating to filtering looks like this:
// remove existing filter from data range if necessary
if (sheet.getFilter() !== null) {
sheet.getFilter().remove();
}
// add a new filter over the whole data table
var newFilter = sheet.getDataRange().createFilter();
// create new filtering criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor); // apply filter color value as filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);If you like challenges, try modifying the sorting code to work with the example filtering.
Color Filtering Template with Apps Script
Feel free to copy the color filtering template in Google Sheets here.
Or check out this GitHub page to get the code directly from the GitHub repository.
Now you know how to sort and filter your data by color in Google Sheets. Have fun!
