3

This question already has an answer here:

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.

marked as duplicate by serenesat, user0, jonsca Jun 4 at 18:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

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 '15 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 – Rubén Oct 12 '15 at 20:49

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