Event object for installable trigger

Question

So, I'm trying to write a code that will send an email when a particular value appears in a cell. For this I'm using an installable OnEdit trigger as simple ones can't access mailApp.sendEmail. The installable trigger is for the following function:

function myOnEdit(e){
  var ss = SpreadsheetApp.getActiveSheet();
  var email_finished = "abc@email.com"
  var r = e.range;
  var heading = ss.getrange(1,range.getcolumn()).value

  if (heading == "FINISHED" && r.getvlaues() == "√") {
    MailApp.sendEmail(email_finished, "SOP Review", "SOP has been finished");
  }
}

I'm getting an error that "e" is undefined. I'm wondering how I can reference the event object for the installable onEdit() trigger.

I'm an absolute Javascript noob, so please do enlighten me as much as you can my coding gurus!! Thanks in advance.

EDIT: As Serge said there were a bunch of typos. A side-effect of VBA coding! But I corrected the typos and changed how I was doing it:

function myonEdit(){
  var ss = SpreadsheetApp.getActiveSheet();
  var e = ss.getActiveCell();
  var heading = ss.getRange(1, e.getColumn()).getValues();
  var rvalue = e.getValue(); 
  var email_finished = "abc@xyz.com";

  var ui = SpreadsheetApp.getUi()
  ui.alert("Are you sure?", ui.ButtonSet.OK)

  if (heading == "FINISHED" && rvalue == "√") {
    MailApp.sendEmail(email_finished, "SOP Review", "SOP has been finished");
  }
}

The I added this on a manual trigger from Edit>>Current project's triggers. It works fine when debugging, sends email and all. However, when I edit the sheet, the code isn't triggered! I'm not sure what I'm doing wrong this time.

If you run the code from the code editor, then `e` is undefined. If that is the issue, people do that all the time. — Alan Wells, Mar 10 '17 at 21:06

score 0 · Answer 1 · answered Mar 10 '17 at 22:02

0

Yeah (e) only has a value when it's triggered inside the sheet. You can't just test it in the script editor. I had to take time to figure this out too.

answered Mar 10 '17 at 22:02

Kris

11
1

score 0 · Accepted Answer · edited May 23 '17 at 11:54

0

Both the other answer and Sandy's comment are right, e is indeed undefined if the function is not called from an "edit" event...

But there is of course a clever workaround presented by my friend Mogsdad in this excellent post : How can I test a trigger function in GAS?

EDIT

There are too many typos in your code and you approach it the wrong way.

Below is a simplified version to show the way it works.You only need to define the e parameters you really need

function test_myOnEdit() {
  var e = {};
  e.range = SpreadsheetApp.getActiveSheet().getActiveRange();
  myOnEdit(e)
}

function myOnEdit(e){
  var email_finished = "abc@xyz.com";
  var range = e.range;
  var heading = range.getValue();

  Logger.log("heading = "+heading);
}

edited May 23 '17 at 11:54

Community

1
1

answered Mar 10 '17 at 22:16

Serge insas

45,904
7
105
131

I added the code for testing. Please check the edit in the question. Turns out I can't add the full code here! Thanks in advance for your help. – gov1991 Mar 16 '17 at 20:29
Yea I realized the typos!! I changed the way I was doing it though and got the value from the active cell and added a manual trigger to run the function every time it edits. Now the problem is that the trigger doesn't fire, no idea why. It runs fine when I debug it though. – gov1991 Mar 17 '17 at 16:50
I think i figured out the problem. I believe it's because I'm not the owner of the file. I'll have to take to my boss about that! – gov1991 Mar 19 '17 at 17:49

Event object for installable trigger

2 Answers2

EDIT