e.range.getA1Notation() unable to track changes ca

问题:

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+ ".");
}
}
}

回答1:

To avoid repeated notifications due to edits in other places, send an email only when the value you are tracking is different from the previous one. Store the previous value in script properties.

function checkValue(e) {
  var sp = PropertiesService.getScriptProperties();
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("sheet1");
  var valueToCheck = sheet.getRange("C7").getValue();
  var oldValue = sp.getProperty("C7") || 0;
  if (valueToCheck > 100 && valueToCheck != oldValue) {
    MailApp.sendEmail("***[e-mail]", "Campaign Balance", "Balance is currently at: " + valueToCheck+ ".");
    sp.setProperty("C7", valueToCheck);
  }
}

Here the email is sent only when valueToCheck differs from the stored value. If this happens, the stored value is updated by setProperty.

回答2:

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).

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.

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.