I have a Google Sheets spreadsheet where different people list a series of transactions performed on a shared account: typing a date on column A of the next available row (say all rows until row 9 are already filled, so the date would be typed on A10), then a name on column B of the same row (B10 in the example), and a value on column D of the same row (D10 in the example).
When a new transaction is entered as described, a fixed cell (C2) runs a formula which depends on the dates and values of all previous transaction plus the newly inserted one. Hence a recalculation occurs, but the value of C2 may or may not be updated, depending on certain details of this new transaction.
When C2 is indeed updated after a recalculation, I'd like to send an email to all users of the spreadsheet warning of the update. I've started developing a Google Apps Script for this, but I haven't found a trigger that works in my situation. I've tried using an onEdit() function but it is never called for changes in C2 since it's not actually edited, merely recalculated.
For now I'm thinking of looking for edits in column D, since I'd expect the users to first fill the date in column A, then the name in column B, and only then the value in column D. However, this has a few problems I'd like to avoid:
- if a user first fills column D and then fills column A, then the value in C2 wouldn't be recalculated until column A is filled, and so an email would be sent for a value that wasn't changed; then, when column A is edited and the value in C2 is actually changed, no email is sent.
- also, if the user filled in the columns in the expected order, and the formula was recalculated but the value didn't change, an email would be unnecessarily sent.
Hence my question: how can I trigger a script on a change of value in a cell containing a formula?
