Getting error while triggering auto e-mail using script

Question

I am using a google spreadsheet which looks like:

   A             B                    C             D
1 Name        e-mail              Identifer       Status
2 Alex       ax@gmail.com         ERT ER          A
3 Micke      miike477@gmail.com   Ejyu er w       
4 John       john7788@tri.com     Arb Ed          C

I have a drop down list in column D (let say A,B & C for example), now i want that whenever the value changes (Initially the column D would be blank) in column D against a particular Name than an automatic e-mail trigger to e-mail id mentioned in column B by below mentioned sender id and content.

The email should be trigger whenever value changes in column D except for the blank, and if there were previously value was "B" and now it change to "C" than mail should be trigger.

Sender-example@gmail.com
CC-test1@gmail.com,test2@gmail.com

E-mail Body:

Hi Alex (Should be picked from column A depending against which name e-mail trigger)

some sentence here. some sentence here with your ERT ER (Should be pick from column C) has status A (should be pick from column D).

Regards,
example
123456789

I am trying using below mentioned script:

function onEdit(event){
    if(event.range.getColumn() == 4){ //A -> 1, B -> 2, etc
        function sendMyEmail(line){
    var sendTo = spreadsheet.getRange(row, 2).getValue();
    var cc     = 'test1@gmail.com'+","+'test2@gmail.com';
    var subject = "What is the: "+ spreadsheet.getRange(row, 3).getValue();
    var content = "Hi "+spreadsheet.getRange(row, 1).getValue();+","

                 +"what is the vlaue "+spreadsheet.getRange(row, 3).getValue();+ "with the status"+spreadsheet.getRange(row, 4).getValue();+ "."
    MailApp.sendEmail(sendTo,
        cc,
        subject,
        content);
}
    }
}

Take a look at [this](https://www.w3schools.com/js/js_operators.asp) and review how you're doing the string concatenation for `cc`, `subject`, and `content. — Diego, Apr 07 '18 at 04:37
@Diego Thanks...Now i'm getting error:`TypeError: Cannot read property "range" from undefined. (line 2, file "Code")` — Vector JX, Apr 07 '18 at 04:41
@Diego I think i am getting that error because running the script manually in script editor, but i have tried to change the value in column D but didn't receive the e-mail. — Vector JX, Apr 07 '18 at 05:06

score 2 · Accepted Answer · answered Apr 07 '18 at 05:31

2

You have two major issues.

Simple triggers cannot access services that require authorization (such as MailApp).
Your usage of MailApp.sendEmail() is incorrect as you're passing cc to where should be passed either the subject or the replyTo address (docs). Argument order is important.

To address the issue of simple triggers, all you need to do is install a trigger manually that calls your function on edit.

All other issues are addressed in the code below.

function sendEmailToUser(event){
  var eventRange = event.range;
  var sheet = eventRange.getSheet();
  var sheetName = sheet.getName();
  var column = eventRange.getColumn();
  if (sheetName == "Sheet1" && column == 4){ // Make sure the edited column is in the correct sheet, otherwise editing Column D in Sheet3 might trigger this
    var row = eventRange.getRow(); // You need to know which row so you can send the email to the correct person
    var rowValues = sheet.getRange(row, 1, 1, 4).getValues();
    var name = rowValues[0][0];
    var sendTo = rowValues[0][1];
    var identifier = rowValues[0][2];
    var status = rowValues[0][3];
    if (status != "") { // Don't send the email if the status is blank
      var cc = "test1@example.com, test2@example.com";
      var subject = "What is the: " + identifier;
      var content = "Hi " + name + "\nWhat is the value " + identifier + " with the status " + status + "?";
      MailApp.sendEmail(sendTo, subject, content, {
        cc: cc
      });
    }
  }
}

answered Apr 07 '18 at 05:31

Diego

9,261
2
19
33

Thank you very much...it worked very well. Just a little help require i just want to leave space of one line between `Hi` and Next line and again between `Next` line and Regards Line. and want to bold the +identifier+ and +status+. – Vector JX Apr 07 '18 at 06:07
@VectorJX Do you see the "\n"? That's a new line character. Add another `\n` and you'll get a blank line. – Diego Apr 07 '18 at 06:08
Thanks...bold option is not possible?? – Vector JX Apr 07 '18 at 06:22
It is possible @VectorJX. You'll need to use the `htmlBody` advanced parameter of `sendMail`. – Diego Apr 07 '18 at 06:31
Means i have to create the whole e-mail body in html format?? – Vector JX Apr 07 '18 at 06:38
Can you please suggest me how can i bold those two values (identifier & status) in email body. I have tried many things but couldn't succeed. – Vector JX Apr 08 '18 at 04:25
https://webapps.stackexchange.com/questions/54670/send-html-email-from-a-spreadsheet-using-a-different-alias – Diego Apr 08 '18 at 04:28
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/168478/discussion-between-vector-jx-and-diego). – Vector JX Apr 08 '18 at 04:37
@VectorJX This is a new topic. You should post a new question so that it and the answer can be available for reference to anyone else with a similar problem. – Diego Apr 08 '18 at 05:03
Thanks...posted a new question: https://stackoverflow.com/questions/49714667/bold-specific-word-in-email-body-while-sending-it-through-googlescript – Vector JX Apr 08 '18 at 05:19

Getting error while triggering auto e-mail using script

1 Answers1