How To Make The Google Filter Function Overwrite Cell Data

Google Sheets sort by color and filter past color are useful techniques to organize your information based on the color of text or cells within the data.

For example, you might highlight rows of data relating to an important customer. Google Sheets sort by color and filter by color let yous bring those highlighted rows to the top of your dataset, or fifty-fifty just bear witness those rows.

They're actually helpful for removing duplicates in Google Sheets too.

As a bonus, they're really easy to employ. Let's run across how:

Google Sheets Sort By Colour

Suppose you accept a dataset with highlighted rows, for example all the apartments in this dataset:

Google Sheets dataset

Add a filter (the funnel icon in the toolbar, shown in red in the above paradigm).

On any of the columns, click the filter and choose the "Sort by color" option.

Yous can filter by the groundwork colour of the cell (similar the yellow in this example) or by the color of the text.

Google Sheets Sort By Color

The effect of applying this sort is all the colored rows volition be brought to the top of your dataset.

Data sorted by color in Google Sheets

This is super helpful if you want to review all items at the same fourth dimension. Some other reason might exist if they're duplicate rows you've highlighted which you can now delete.

Google Sheets Filter By Color

The Google Sheets filter by color method is very similar to the sort past color method.

With the filters added to your dataset, click one to bring upwardly the carte du jour. Select "Filter by color" and and so select to filter on the groundwork prison cell color or the text color.

Google Sheets filter by color

In this case, I've used the Google Sheets filter by color to only brandish the yellow highlighted rows, which makes information technology really easy to review them.

Data filtered by color in Google Sheets

There's an option to remove the filter by color by setting information technology to none, found under the filter by color menu. This option is not establish for the sort by color method.

Apps Script Solution

When I originally published this article, sort by color and filter by color were non available natively in Google Sheets, so I created a small script to add this functionality to a Canvas.

They were added on 11th March 2020. Read more here in the Google Workspace update blog.

Here is my original Apps Script solution, left here for general interest.

With a few simple lines of Apps Script, nosotros tin implement our ain version.

Filter By Color in Google Sheets

This commodity will show you how to implement that aforementioned feature in Google Sheets.

Information technology's a pretty basic idea.

We demand to know the groundwork color of the cell we want to sort or filter with (user input 1). Then we need to know which column to utilise to practise the sorting or filtering (user input 2). Finally we need to practise the sort or filter.

So step i is to to prompt the user to input the cell and columns.

I've implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, nosotros store this using the Properties Service for retrieval when we come to sort or filter the information.

Apps Script Sort By Color

At a high level, our plan has the post-obit components:

Custom menu to run the Google Sheets sort by color program
Prompt to ask user for the color prison cell
Save the color cell using the Backdrop Service
Second prompt to ask the user for the sort/filter column
Save the sort/filter column using the Properties Service
Evidence the color and cavalcade choices and ostend
Retrieve the background colors of the sort/filter column
Add together helper column to data in Sheet with these groundwork colors
Sort/Filter this helper column, based on the color cell
Clear out the values in the certificate Backdrop shop

Allow's look at each of these sections in turn.

Add A Custom Card (i)

This is simply average Apps Script code to add a custom menu to your Google Sheet:

/**  * Create custom menu  */ function onOpen() {   var ui = SpreadsheetApp.getUi();   ui.createMenu('Colour Tool')         .addItem('Sort by colour...', 'sortByColorSetupUi')         .addItem('Clear Ranges','clearProperties')         .addToUi(); }

Prompt The User For Cell And Column Choices (two, four and half dozen above)

I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they desire to select.

/**  * Sort By Colour Setup Program Flow  * Cheque whether colour cell and sort columnn have been selected  * If both selected, motion to sort the data by color  */ 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   if(!colorCellRange)  {     title = 'Select Color Prison cell';     msg = '<p>Delight click on jail cell with the background colour you desire to sort on and then click OK</p>';     msg += '<input blazon="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';     dispStatus(title, msg);   }      //if colorCellRange and !sortColumnLetter   if (colorCellRange && !sortColumnLetter) {              title = 'Select Sort Column';       msg = '<p>Please highlight the column you desire to sort on, or click on a cell in that column. Click OK when you are ready.</p>';       msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';       dispStatus(title, msg);   }      // both color cell and sort column selected   if(colorCellRange && sortColumnLetter) {          title= 'Displaying Color Cell and Sort Column Ranges';     msg = '<p>Confirm ranges before sorting:</p>';     msg += 'Colour Cell Range: ' + colorCellRange + '<br />Sort Cavalcade: ' + sortColumnLetter + '<br />';     msg += '<br /><input type="button" value="Sort Past 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);        } }  /**  * brandish the modeless dialog box  */ function dispStatus(title,html) {      var title = typeof(championship) !== '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 switch betwixt dialog box 1 (to select colour jail cell) and 2 (to select sort cavalcade)  */ function sortByColorHelper(mode) {      var way = (typeof(mode) !== 'undefined')? fashion : 0;   switch(mode)   {     case i:       setColorCell();       sortByColorSetupUi();       break;     case 2:       setSortColumn();       sortByColorSetupUi();       break;     default:       clearProperties();   } }

The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.

Following this, the google.script.host.shut() is as well a client-side JavaScript API that closes the current dialog box.

Save The Cell And Column Choices In The Holding Store (three and 5)

These two functions save the cell and column ranges that the user highlights into the Sheet's property store:

/**   * saves the color prison cell range to properties  */ role setColorCell() {      var sheet = SpreadsheetApp.getActiveSheet();   var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();   var colorProperties = PropertiesService.getDocumentProperties();   colorProperties.setProperty('colorCellRange', colorCell);  }  /**  * saves the sort column range in backdrop  */ function setSortColumn() {      var canvass = SpreadsheetApp.getActiveSheet();   var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();   var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/chiliad,'').toUpperCase(); // find the column letter of the alphabet   var colorProperties = PropertiesService.getDocumentProperties();   colorProperties.setProperty('sortColumnLetter', sortColumnLetter);    }

Every bit a result of running these functions, we have the colour prison cell address (in A1 notation) and the sort/filter column letter saved in the Holding store for future access.

Sorting The Information (7, 8 and 9 above)

Once we've selected both the color cell and sort column, the program flow directs u.s.a. to actually go alee and sort the data. This is the button in the third dialog box, which, when clicked, runs this phone call google.script.run.sortData();.

The sortData function is defined as follows:

/**   * sort the information based on color prison cell and called column  */ function sortData() {      // get the properties   var colorProperties = PropertiesService.getDocumentProperties();   var colorCell = colorProperties.getProperty('colorCellRange');   var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');    // extracts cavalcade letter from whatsoever range has been highlighted for the sort column      // get the sheet   var sheet = SpreadsheetApp.getActiveSheet();   var lastRow = sail.getLastRow();   var lastCol = canvass.getLastColumn();      // get an assortment of background colors from the sort column   var sortColBackgrounds = canvass.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1      // get the background colour of the sort cell   var sortColor = sheet.getRange(colorCell).getBackground();      // map background colors to 1 if they lucifer the sort cell color, 2 otherwise   var sortCodes = sortColBackgrounds.map(part(val) {     return (val[0] === sortColor) ? [ane] : [2];   });      // add a column heading to the array of background colors   sortCodes.unshift(['Sort Column']);      // paste the background colors assortment as a helper column on correct side of data   sheet.getRange(ane,lastCol+one,lastRow,1).setValues(sortCodes);   sheet.getRange(one,lastCol+ane,1,1).setHorizontalAlignment('middle').setFontWeight('bold').setWrap(true);      // sort the data   var dataRange = sail.getRange(ii,1,lastRow,lastCol+ane);     dataRange.sort(lastCol+1);      // add new filter beyond whole information table   sail.getDataRange().createFilter();    // clear out the properties and then information technology'south prepare to run again   clearProperties(); }

And finally, we want a way to clear the backdrop shop and then we can start over.

Articulate The Property Shop (10 to a higher place)

This uncomplicated function volition delete all the central/value pairs stored in the Sheet's property store:

/**  * clear the backdrop  */ role clearProperties() {   PropertiesService.getDocumentProperties().deleteAllProperties(); }

Run The Google Sheets Sort By Color Script

If you put all these code snippets together in your Lawmaking.gs file, you should be able to run onOpen, authorize your script and so run the sort by color tool from the new custom menu.

Here'southward the sort by colour tool in activeness in Google Sheets:

Google Sheets sort by color

Yous can encounter how all of the green shaded rows are sorted to the superlative of my dataset.

Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).

Some improvements would be to get in more generalized (or prompt the user to highlight the dataset initially). I too have not included any fault handling, intentionally to proceed the script as simple equally possible to aid understanding. Withal, this is something you'd want to consider if you want to make this solution more than robust.

Apps Script Sort By Color Template

Here'due south the Google Sheet template for you to copy.

(If you're prompted for permission to open this, it'south because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You can talk to your Google Workspace administrator about that. Alternatively, if you open this link in incognito way, y'all'll be able to view the Sheet and copy the script direct from the Script Editor.)

If GitHub is your thing, here'southward the sort by color code in my Apps Script repo on GitHub.

Apps Script Filter Past Color

The programme period is virtually identical, except that nosotros filter the data rather than sort information technology. The lawmaking is almost exactly the same too, other than variable names existence different and implementing a filter instead of a sort.

Rather than sorting the information, we create and add a filter to the dataset to testify only the rows shaded with the matching colors:

Filter By Color in Google Sheets

The filter portion of the lawmaking looks like this:

// remove existing filter to the data range if (sheet.getFilter() !== null) {   sheet.getFilter().remove(); }  // add new filter beyond whole information table var newFilter = sheet.getDataRange().createFilter();  // create new filter criteria var filterCriteria = SpreadsheetApp.newFilterCriteria(); filterCriteria.whenTextEqualTo(filterColor);  // utilise the filter color as the filter value newFilter.setColumnFilterCriteria(lastCol + ane, filterCriteria);

If you lot want a challenge, run into if yous can alter the sort code to work with the filter example.

Apps Script Filter Past Color Template

Feel gratis to copy the Google Sheets filter past colour template hither.

(If you're prompted for permission to open this, it'due south because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You tin talk to your Google Workspace administrator near that. Alternatively, if you open up this link in incognito fashion, yous'll be able to view the Canvas and copy the script straight from the Script Editor.)

Or pull the code directly from the GitHub repo here.