onChange Trigger not working as expected

Question

I have a google sheet that has an auto refreshing IMPORTXML function on it based on a timed trigger and am trying to write a script that will auto email a notification alert when it retrieves a row with a "HELP" message type.

I compiled this script based on some examples I found. I have tested it and it works fine with manually entered data with an onEdit installable trigger but from my testing (and based on research) I need to use the onChange trigger to have the IMPORTXML trigger the script.

However, when I set the script to a onChange installable trigger, the script doesn't seem to execute either automatically with the IMPORTXML loading data or by me manually entering it on the sheet.

Am I hitting some limitation of Good Apps Script? This is my first time using it (and JavaScript)

function helpAlertEmail(e) 
{
  if (e.range.columnStart !== 5 || e.value !== 'HELP' && e.value !== 'HELP-CANCEL') return;
  var ss = e.source.getActiveSheet()
  var details = ss.getRange(e.range.rowStart, 1, 1,11).getValues()[0];
  var headers = ss.getRange(1, 1, 1, 11).getValues()[0];
  var subject = "SPOT BEACON ALERT:  " + details[2] + " Sent a " + details[4] + " Message at " + details[9];
  var body = "SPOT Beacon " + details[2] + " (" + details[1] + ")  Sent a " + details[4] + " Message at " + details[9] +  "\n\n";
  var email = "[email protected]";
  var cols = [0, 4, 5, 6, 9, 10];

  for (var i = 0; i < details.length; i++) 
  {
    if (cols.indexOf(i) === -1) continue;
    body += headers[i] + ": " + details[i] + "\n"
  }

  body += "\n\n\n Please do not respond to this email as it is automatically generated by an account that is not checked.";
  MailApp.sendEmail(email, subject, body, {noReply:true});
}

Suyash Gandhi Suyash Gandhi · Accepted Answer · 2017-01-11T13:01:55

By what I understand from your explanation about the use case, I would like you to know this:

1) onEdit - Specifies a trigger that will fire when the spreadsheet is edited.

2) onChange - Specifies a trigger that will fire when the spreadsheet's content or structure is changed.

These are the conditions stated by Google.

Now what it does not tell us is:

1) The edit has to be manual

Now you said you tried manually as well but the trigger did not fire. A possible reason for that is that you might have copied the data from somewhere and then pasted it where you want it. So, you cannot expect the trigger to fire in that case.

2) The edit cannot be any form of automation (it cannot be done using a spreadsheet formula or using any script). In this case as well, the trigger would not fire.

So, in all, what you are facing is not a consequence of any of the limitations stated by Google. Sadly, It is what it is.

What you will have to do is, think of another way to go about what you are trying to achieve, which is, use something else except the onChange and the onEdit triggers.

onChange Trigger not working as expected

1 Answers