CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Excelsson
Excelsson

Reputation: 195

Appscript onEdit Get Row Number and Send Emails

I'm trying to come up with a script that onEdit will get the Row Number for the Checkbox Selected, copy that row number and Set the Value on another sheet on Cell M1 and then send an email automatically, however the Trigger is not occurring on Edit, below my code:

function onEdit(e) {
  
  if(e.range.getSheet().getName() != 'Example') { return; }
  if(e.range.columnStart==22 && e.value=="TRUE") {

    SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getRow().getSheetByName("Email").range("M1").setValue(row);

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").activate();

var details = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("M1").getValues();
 var recipient = "[email protected]";
  var subject = "Example for the Number" && details;
  var body = "This is an example test";
  MailApp.sendEmail(recipient, subject, body);
  
    }
    
}

Upvotes: 0

Views: 1707

Answers (1)

Tanaike
Tanaike

Reputation: 201408

I believe your current situation and your goal as follows.

  • Checkboxes are put in the column "V" on "Example" sheet.
  • When the checkbox is checked, you want to retrieve the row number of the checked checkbox and want to put the row number to the cell "M1" on "Email" sheet.
  • And, you want to send an email including the row number.

Modification points:

  • In your script, at SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getRow().getSheetByName("Email").range("M1").setValue(row);, getRow() returns the row number. So I think that an error occurs at getSheetByName("Email"). I thought that this might be the reason of your issue.
  • Unfortunately, I'm not sure about your trigger from your question. But, in your script, MailApp.sendEmail is used. In this case, MailApp.sendEmail cannot be used with the simple trigger. By this, I thought that an error also occurs.
    • So in this case, please use the installable OnEdit trigger.
    • And also, when the installable trigger is used, please rename the function name from onEdit from others. Because when onEdit is installed as the installable trigger, 2 times both the simple trigger and the installable trigger are executing. Please be careful this.
  • By the way, about the checked checkboxes, you can use isChecked() method.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please copy and paste the following script and install the OnEdit trigger to installedOnEdit(). When you use this script, please check the checkbox of the column "V" on "Example" sheet.

function installedOnEdit(e) {
  var range = e.range;
  if (range.getSheet().getName() != 'Example') { return; }
  if (range.columnStart == 22 && range.isChecked()) {
    var ss = e.source;
    var row = range.getRow();
    ss.getSheetByName("Email").getRange("M1").setValue(row);
    var details = row;
    var recipient = "[email protected]";
    var subject = "Example for the Number" && details;
    var body = "This is an example test";
    MailApp.sendEmail(recipient, subject, body);
  }
}

References:

Upvotes: 2

Related Questions