Google Spreadsheets: Sort/filter by background fill

Question

Microsoft Excel has the handy ability to sort/filter rows/cells by background color. Is it possible on Google Spreadsheets?

Answer 1

-- Unfortunately, it's not possible to sort/filter rows/cells by their formatting using a built-in function. However, it is possible using a custom Google Apps Script like the one Shannon Chan lays out here:

First, you need a way to get the color of the cell. Go to Tools > Script Editor > Create Custom function.

Paste the following code inside a script file and save it:

function getHex(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange(input);
  var result = cell.getBackground();
  return result
}

This creates a function 'getHex()' within your spreadsheet, which takes in a cell address and returns the hex code of the color of the cell.

Now that we have the function, let's see how it works. To illustrate the function's usage, let's say we have cell 'A1' colored in yellow.

Now go to cell 'B1' and type:

=getHex(cell("address",A1))

This should give you the value "#ffff00" which is the hex code for the color yellow. (FYI: I used cell("address",A1) so that we can get the string form of the cell reference "A1", which the gethex() function accepts as an argument.)

You can then drag cell B1 down the row, the same way you would drag any other function. This will return the hex code values of the color of all the cells in column A.

From here, you can proceed to filter the cells by color denoted by hex code!

Note: The function getHex() does not dynamically update when the color of the cell changes. To refresh the values, you can delete the cells with the formula equivalent of '=getHex(cell("address",A1)' , and then undo the action with 'cmd + z' or 'ctrl + z'. This should refresh the formulas and the hex results.