Using script editor to send an email from button in Google Sheets

Question

I am trying to make the button that I have on a sheet send an email using scripts but, I have not been able to get it to send the correct sheet or it sends a blank email.

I have tried a few different version of the Script but still can't get it to do anything I need it to.

I have 3 total sheets Sheet1 collects data for Sheet2. Sheet2 contains the Data I need to email and the button with the script to send the email. The third sheet is UserData which contains a list of emails that the data needs to be sent to.

...
function CustomEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getLastRow();
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var email = row[1];
MailApp.sendEmail(row[1], "Shift Report", "Have a great Shift");
}
}
...

I hope to be able to input my data into Sheet1 and then review sheet two and hit the sendEmail button.

There are many email examples on this site do a little research and you’ll find a lot of answers on your own. You can start by debugging the script in the script editor because it contains errors. — Cooper
been doing tons of digging and just cant find the specifics i need to make it work. I realize that there are errors in the code which is why Im reaching out, Im very new to both sheets and excel not sure what i need to make it work so even if it is just letting me know why my code isnt working would help — Scott
Maybe Im going about this all wrong. Could i just copy an active sheet and ref another sheet to pull emails and send it? — Scott
Take a look at the trouble shooting section of the Apps Script Documentation. — Cooper

Cooper Cooper · Accepted Answer · 2019-04-08T22:29:27

 function CustomEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getLastRow();

getLastRow() returns an integer not a range.

getLastRow

  var UserData = range.getValues();

Since range is not actually a range the above line will not produce any values.

  for (i in UserData) {

This sort of procedure should be reserved for objects with key/value pairs. And you should always declare your variables with var.

    var row = UserData[i];
    var email = row[1];
    MailApp.sendEmail(row[1], "Shift Report", "Have a great Shift");
  }
}

Using script editor to send an email from button in Google Sheets

1 Answers