Mohamed Hassan Allam
Mohamed Hassan Allam

Reputation: 11

google sheets emails onEdit trigger

The case is I have a google sheet, that has a column that gets edited and 3 columns next to it that include a check, an email body and an email subject. I made the following code so that when a certain cell is edited in the edit column, an email is sent for notification. I put the email in a column that is referred to in the code.

function onEdit(e){

//Detecting the edited cell and fetching the values from the other columns
  var range = e.range;
  var check = range.offset(0,2).getValue()
  var serial = range.offset(0,-1).getValue()
  var email = range.offset(0,-8).getValue()
  var message = range.offset(0,3).getValue()
  var subject = range.offset(0,4).getValue()
  if (check == "SendEmail") { var email2 = email; }

//Checks to see if the code is running
      SpreadsheetApp.getActiveSpreadsheet().getRange('R1').setValue(email2)
      SpreadsheetApp.getActiveSpreadsheet().getRange('S1').setValue(check)

//Email part  
  var emailAddress = email2;
  MailApp.sendEmail(emailAddress, subject, message)


}

When I try using the function without the on edit feat, the email is sent. when I, however, put the onEdit back on, it works perfectly still but no emails are sent.

Upvotes: 1

Views: 3263

Answers (2)

tehhowch
tehhowch

Reputation: 9872

You are experiencing issues because your function name overlaps with the expected name of a simple trigger function - merely naming a function onOpen or onEdit is sufficient to make that function run under "simple trigger" environment.

If you rename your function to something more pertinent - such as sendNotification(e), then the only way it will run after cells are edited is if it is called via an installed trigger - one created manually by a user, or programmatically.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201703

Please confirm whether onEdit(e) is installed as a trigger. When you use MailApp.sendEmail(), it is required authorization. So onEdit(e) without Installable Triggers cannot run MailApp.sendEmail(). How to install onEdit(e) as a trigger is as follows.

  • On script editor
  • Edit -> Current project's triggers -> Click here to add one now.
  • For "Run", set "onEdit"
  • For "Events", set "From spreadsheet" and "On edit"
  • Click Save button

After this, please try again.

The detail information of Installable Triggers is here.

If this was not useful, I'm sorry.

Edit :

This is a sample for confirming running MailApp.sendEmail(). When you use this, please install onEdit() as a trigger.

function onEdit() {
  MailApp.sendEmail("### your e-mail address ###", "Sample subject", "Sample body");
}

Upvotes: 2

Related Questions