CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
swineone
swineone

Reputation: 2814

Run Google Apps Script when a formula is updated

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:

Hence my question: how can I trigger a script on a change of value in a cell containing a formula?

Upvotes: 1

Views: 952

Answers (1)

John
John

Reputation: 916

I would store the value of C2 as a property using the Property Service, then compare the property's value to C2 on any change to the spreadsheet using onEdit().

Upvotes: 1

Related Questions