I am trying to figure out which trigger to use to send an email (from a template) per row:

  function sendConfirmationEmail(e) {
 
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = e.range;
  var row = range.getRow();
  var dataRange = sheet.getRange(row, 1, 1, 5).getValues();
// Test
  
  console.log('Data: '   e   'range '   dataRange);

}

A/ I am trying to add a column F with sendEmail and somehow trigger the function sendConfirmationEmail(e) in the script

B/ Trying to figure out if I can use the onhover email (column E) and when clicking Send email my emailTemplate.doc will be populated and replace the {{placeholder}} default message.

Any tips, suggestions?

CodePudding user response：

To trigger a function on editing a cell content - use the onEdit trigger

• This trigger contains the event object e.range that allows you to access the cell that has been edited.
• Given that sending an email is an action that requires authorization, youu need to use an installable instead of simple onEdit trigger
• This you can do by calling your funcito something different from the keyy word onEdit (this is already the case in your funciton) and bind a trigger to it as explained here

Sample funciton to bind on installable onEdit trigger:

  function sendConfirmationEmail(e) {

  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  // for column F
  if(column ==6 && e.value == "sendEmail"){
    var dataRange = sheet.getRange(row, 1, 1, 5).getValues().flat();
    var email = dataRange[4];
    GmailApp.sendEmail(email, "subject", "body");
  }
}