How can I filter my rows by background color in Google Sheets?

Question

I'm trying to filter the rows in my sheet based on background color.

I used the following code to write a function that displays the hex code of the background color of the rows selected in a helper column and then filtered by these values.

function getHex(input) {
  return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getBackgrounds();}

However, I find this to be a point in time thing. If I change the background color of a row, the hex code in the helper column does not change automatically. Thus, when I filter it, the wrong rows are displayed. Any ideas on how to move forward?

Answer 1

You can use triggers for automatically running the the script, unfortunately there is no way to do this by changing color of cell, as the cell does not fire a changed event when the color changes, only when the content does.

Look for google sheet triggers, onEdit() or onChange().

https://developers.google.com/apps-script/guides/triggers/

The only working way is to set an onEdit() or onChange() event and edit a single cell value anywhere after changing the color.

Example Steps :

• Change the desired cell background color or multiple cell color.

• Edit a cell value - better to add some random number on empty/unused cell
  and delete it.

Example of onChange() :

function onChangeColor(event) 
{
  if (event.changeType == "EDIT")
  {
    //your code.
  }
}