Google Sheets script editor send email function

gSheets send email

/*
This constant is written in column C for rows for which an email
has been sent successfully.
*/

function send_software_request() {

  //MailApp.sendEmail("[email protected]", "Running",""); //- use to test if script is running

  var EMAIL_SENT = "EMAIL_SENT";
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1;  // First row of data to process
  var numRows = sheet.getMaxRows();

  //method getRange(row, column, optNumRows, optNumColumns) gets the range of the sheet to cover.  The first column is column 1.
  // optNumRows and optNumColumns are optional.  Here we specify numRows (based on the above variable that checks for the total
  // number of rows in the current sheet) and 9 which is the last column is use.
  var dataRange = sheet.getRange(startRow, 1, numRows, 27);

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];

    // test for content - if column 1 is empty the row is ignored
    if (row[1]) {
    
      // Enter address(s) email should be sent to here.  Separate multiple addresses with a comma but no space
      var emailAddress = "[email protected],[email protected],[email protected]";
      
      var requesterEmail = row[1];
      var advancedArgs = {replyTo:requesterEmail, name:requesterEmail}; 
      var emailSent = row[10];     // Last column
      
      if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      
        /* 
        Enter a line for each entry you want to display in the mail.
        To include more than one entry on a line follow pattern for "Submitted on: " line.
        \r\n means return or new line
        */
        var subject = "Workflow Feedback from " + row[2];
        var message = "Workflow Feedback details:\r\n===================\r\n\r\n"
        + "Submitted on: " + row[0] + " by " + row[2] + "\r\n\r\n"
        + "Email:  " + row[1] + "\r\n" 
        + "Name:  " + row[2] + "\r\n" 
        + "Feedback: \r\n\r\n" 
        + row[3] + "\r\n\r\n"
        + "Spreadsheet:\r\n"
        + "https://docs.google.com/a/guardian.co.uk/spreadsheets/d/1ZnPu2euiJ920VGMC30Ko-rrBtQxtNl--y8x01vfsZ-8/edit" 
        
        MailApp.sendEmail(emailAddress, subject, message, advancedArgs);
        sheet.getRange(startRow + i, 11).setValue(EMAIL_SENT);
        // Make sure the cell is updated right away in case the script is interrupted
        SpreadsheetApp.flush(); 
      }      
    }
  }
}