Trouble sending email from edited spreadsheet

Question

I am trying to write a script that will send an email to a particular person based on an input on a spreadsheet. I am having trouble getting an email to be sent out. I would like this script to do X things:

Monitor column 4 for the Task Leads name.
Send an email to the appropriate Lead based on the name input.
Not send out duplicate emails. The script needs to check column 8 and verify that an email has been sent out and if not, send the email out.
Insert the spreadsheet name into the email subject and use column 2 as the body/message of the email.

Here is my current script code:

function sendEmail() {
  var emailColumn = 8;
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = sh.getActiveCell().getRowIndex();
  var emailSent = "Yes. Email Sent";
  var range = sh.getActiveCell();
  var taskLeads = ["noah", "tony", "larry"];
  var leadsColumn = 4;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = SpreadsheetApp.getActiveSheet();

  if (range.getColumn() == leadsColumn && range.getValue().toLowerCase() == taskLeads[0]) {
    var recipient = "[email protected]"; // email address
    var body = sh.getRange(row, 2); // Message contained in second column
    var subject = "New to do item in " + ss.getName(); //Pulls Spreadsheet name
    sh.getRange(row, 10).setValue(body);  //Testing
    sh.getRange(row, 11).setValue(subject); //Testing
    sh.getRange(row, 9).setValue(recipient); //Testing
    MailApp.sendEmail(recipient, subject, body);

    if (range.getColumn() == emailColumn && range.getValue().toLowerCase() == valueToWatch) {
      sh.getRange(row, 8).setValue(emailSent);
    }
  }
}

I understand that there are particular functions that work for sending emails from spreadsheets and other functions for having cells update live in spreadsheets. My question ultimately is in regards to the correct function to call and how to get the code inside that function to work correctly. — Noah

Gabriel Crivelli Gabriel Crivelli · Accepted Answer · 2013-10-29T11:52:13

I use this script to send emails to users upon form submit. You can run it from the Google Spreadsheet that handles the webform.

  var EMAIL_SENT = "Email_Sent";

function sendEmails2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var startRow = 2; //starts checking from row 2 
  var numRows = 1000; //to handle at least the first 1000 rows
  var dataRange = sheet.getRange(startRow, 1, numRows, 1000)
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  //gets the email address of the user in Col 1
    var message = row[1];   // gets the message from Col 2    
    var emailSent = row[2]; // writes "Email_Sent" in Col 3     
    if (emailSent != EMAIL_SENT) {  
      var subject = "Place your default subject";
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Trouble sending email from edited spreadsheet

1 Answers