I've been referencing @w0lf's answer to help me calculate the sum of cells based on their background color in Google Sheets. From the comments and some other research it appears that changing the color doesn't trigger a change in the cell. This causes my function to never update.

Does anyone know a way to force my sum function to update when just the background color of a cell in my range changes? I've tried changing the update settings under "spreadsheet settings" already, that doesn't work.


1 Answer 1


From Sum data based on cell color in Google Spreadsheets by Jeff Lockhart

@aezell In order to have the sumWhereForegroundColorIs() equation update when the cells in its range change, append the cells within the 2nd argument string as a 3rd argument range like so: sumWhereForegroundColorIs(getForegroundColor("B3"), "E3:AL3", E3:AL3). Unfortunately if you need the equation to update when the foreground color of any of the cells in that range change, this is not possible. The cell does not fire a changed event when the color changes, only when the content does.

So, you have to change the content in order to make the referred custom function to be recalculated.

  • Thanks, I find it odd that a color change wouldn't trigger a change event in the cell? I'm trying to find actual google documentation on this to see if there is a way to capture the color change and update my function. Until then I'll accept Jeff's knowledge as truth.
    – Braden
    Oct 12, 2015 at 20:43
  • @Braden: Usually there isn't "actual google documentation" for non-included features. They usually document only features that are included. For the documented stuff, see Spreadsheet Service - Apps Script Oct 12, 2015 at 20:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.