Send an email using email addresses from a column in Google sheets-CodePudding

I'm trying to create code that will send an email using addresses from a specific column in google sheets. I want the code to send an email after the sheet is edited by other users. For example, someone enters a request on a row in the sheet - then an email is sent to the manager of the request. Here's what I have so far...

function SendEmail(){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HLP REQUESTS").getRange("K:K");
var emailAddress = emailRange.getValues()[0][0];
// Send Alert Email.
var message = 'A request has been submitted for professional learning related to an HLP you champion.  Please check the Design Team Notes document in case follow-up is required.'; // Second column
var subject = 'HLP Request for Professional Learning';
MailApp.sendEmail(emailAddress, subject, message);
}

When I run the code above I get an error - Exception: Failed to send email: no recipient. There is a valid email address in column K, so I'm a little confused.

CodePudding user response：

Sending Emails

function SendEmail() {
  const ss = SpreadsheetApp.getActive();
  const rsh = ss.getSheetByName("HLP REQUESTS");
  const emails = rsh.getRange("K1:K"   rsh.getLastRow()).getDisplayValues().flat();
  var message = 'A request has been submitted for professional learning related to an HLP you champion.  Please check the Design Team Notes document in case follow-up is required.';
  var subject = 'HLP Request for Professional Learning';
  emails.forEach(e => {
     MailApp.sendEmail(e, subject, message);
  });
}

If you wish to attach this to an onEdit you will have to rethink the process because the onEdit trigger fires on every edit to any sheet and most likely you will be require to use an installable onEdit so that you can perform operations that require permission. I'd recommend you play around with the onEdit simple trigger for a while. Look at the event object and see what's available at low overhead cost.

CodePudding user response：

If you want get email address from the last cell of the column K it can be done this way:

function SendEmail(){
  var emailRange = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("HLP REQUESTS").getRange("K:K");

  var emailAddress = emailRange.getValues()
    .flat()           // convert a 2d array into a flat array
    .filter(String)   // remove empty elements from the array
    .pop();           // get the last element from the array

  var message = 'A request has been submitted for professional learning related to an HLP you champion.  Please check the Design Team Notes document in case follow-up is required.';
  var subject = 'HLP Request for Professional Learning';

  MailApp.sendEmail(emailAddress, subject, message);
}

Update

Here is the full implementation with installable trigger onEdit that sends email as soon as the checkbox (in column L) was checked in:

// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet(); // get the srpreadsheet
var checkbox_col = 12;                          // column with checkboxes (L in this case)

// the main function
function sendEmail(e) {
  try {
    var {rowStart, columnStart} = e.range;   // get row and column of the cell that was edited
    if (columnStart != checkbox_col) return; // do nothing if it was not column with checkboxes
    if (e.value != 'TRUE') return;           // do nothing if the checkboxs was unchecked
    e.range.setValue(false);                 // else uncheck the chekbox
        
    var sheet = SS.getSheetByName('HLP REQUESTS');                // get the sheet
    var emailAddress = sheet.getRange('K'   rowStart).getValue(); // get email addres from current row, column K

    var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.';
    var subject = 'HLP Request for Professional Learning';

    MailApp.sendEmail(emailAddress, subject, message);       // send the message
    SS.toast('Email to '   emailAddress   ' has been sent');
  } 
  catch(e) { SpreadsheetApp.getUi().alert(e) }
}


// additional functions -------------------------------------------------------------------------

// insatll the trigger
function install_onEidt_trigger() {
  ScriptApp.newTrigger('sendEmail').forSpreadsheet(SS).onEdit().create();
  SS.toast('Trigger was installed');
}

// remove all triggers
function remove_all_triggers() {
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  SS.toast('All triggers were remoded');
}

// custom menu to install and remove triggers
function onOpen() {
  SpreadsheetApp.getUi().createMenu('⚙️ Scripts')
  .addItem('Install trigger', 'install_onEidt_trigger')
  .addItem('Remove all triggers', 'remove_all_triggers')
  .addToUi();
}

To make it work you have:

to reload the spreadsheet (or to run the function onEdit() manually) to get the custom menu Scripts
in the custom menu run the item Install trigger
after that it will try to send the message to the address from column K of current row whenever user clicks on checkbox in column L

My test sheet looks like this: