Google-sheets – Custom actions in Google Spreadsheets

Please see if my answer to a similar question helps.

Update:

My submission for the Google Docs Script gallery was accepted and you can install it to get the functionality you wish.

The steps:

• Open your spreadsheet
• In the menu go to Tools -> Script gallery...
• Search for Sum by color
• Click Install
• Click the Authorize button if you trust the script and then Close
• go back to the spreadsheet

Now you have three additional functions you can use in your spreadsheet formula:

• sumWhereBackgroundColorIs(color, rangeSpecification)
• sumWhereBackgroundColorIsNot(color, rangeSpecification)
• getBackgroundColor(rangeSpecification)

Please from that the rangeSpecification parameters require quotes (") around them.

For example:

=sumWhereBackgroundColorIs("white", "A1:C4")

sums the values of all cells in the A1:C4 range that have a white background.

Please note that if you don't know the color of a certain cell you can use the provided getBackgroundColor function to find out what the color is. This function is necessary, because some colors are expressed as RGB codes (for example, #00ff00 instead of green).

Automatic if within the existing formatted range (ie added rows are above and added columns are to the left) would be conditional formatting with Custom formula is

=row()=column()  

and Text Colour: white (assuming this is what you mean by foreground) and Background Colour: black applied to Range: to suit.