google apps script - e.range.getA1Notation() unable to track changes caused by formula update

Question

I modified a script provided from this blog

How to have your spreadsheet automatically send out an email when a cell value changes

After some debugging an modifications, I can send emails by manually entering a value at position C7. That is, according to script, if the value is greater than 100, it will send a email to me. That only happens if I type the number manually into the cell.

The problem is, if the value is generated by a formula, then it doesn't work. (Say cell C7 is a formula=C4*C5 where the product value is >100)

After some trial-and-error, I think it is the code in the edit detection part causing the problem.

var rangeEdit = e.range.getA1Notation(); 
if(rangeEdit == "C7")

Since cell C7 is a formula, the formula itself doesn't change, what is changing is the values from formula calculations. So it may not think I have edited the cell.

How should I modify the script, so that the script also send email when value of C7 produced by a formula is greater than 100?

For reference, here is the code that I am using.

function checkValue(e)
{
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("sheet1");
var valueToCheck = sheet.getRange("C7").getValue();
var rangeEdit = e.range.getA1Notation();
if(rangeEdit == "C7")
{
if(valueToCheck >100)
{
MailApp.sendEmail("h********@gmail.com", "Campaign Balance", "Balance is currently at: " + valueToCheck+ ".");
}
}
}

Answer 1

onEdit(e) Trigger(Both simple and Installable) will not trigger unless a human explicitly edits the file. In your case, Your seem to be getting value from an external source (specifically, Google finance data).

Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form's submit trigger to run.

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

Also, for Google finance data,

Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

Notes:

Having said that,

• In cases where the change is made by a formula(like =IF(),=VLOOKUP()) other than auto-change formulas(like =GOOGLEFINANCE,=IMPORTRANGE,=IMPORTXML, etc), Usually a human would need to edit some other cell- In which case, You will be able to capture that event(Which caused a human edit) and make changes to the formula cell instead.

• In cases where the change is done from sheets api, a installed onChange trigger may be able to capture the event.