Send email from cell value google sheet, with other columns in email

Question

Apologises as I know this has been asked a few times, which I have learnt from other questions, however I'm still stuck and not sure how to continue.

I'm trying to make a google script to send an email notification to more then one person, based on a cell change, also sending in the email multiple cell values in the same row?

e.g. If Column 'C' changes, can I send an email with data from Column 'A' and 'B' in the same Row of the change in column 'C'?

This is the script I've been trying to play around with but not sure how to add multiple e-mails, and values from two more cells.

function sendNotifications() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell().getA1Notation();
var row = sheet.getActiveRange().getRow();
var cellvalue = ss.getActiveCell().getValue().toString();
var recipients = [email protected]";
var message = '';
var cellA = ''
if(cell.indexOf('B')!=-1){ 
message = sheet.getRange('A'+ sheet.getActiveCell().getRowIndex()).getValue()
}
var subject = 'Update to '+sheet.getName();
var body = + cellvalue + '». For message: «' + message + '»';
MailApp.sendEmail(recipients, subject, body);
};

You can send to multiple recipients by separating the addresses by a ,. Acquiring the data from another cell could be done through getRange() — Casper

Anton Dementiev Anton Dementiev · Accepted Answer · 2017-05-31T14:15:09

The code below checks if the edited cell was in column C and, if the condition is met, gets the values for columns A and B from the edited row. In Google Apps Script, onEdit() is the built-in function that fires automatically when you make changes in your spreadsheet. The 'e' parameter represents the event object that you can inspect to get more details about the context in which the event occurred

function onEdit(e){

  var editRange = e.range; // e is the event object generated by the Edit event
  var editRow = editRange.getRow();   // get row and column for the edited cell
  var editCol = editRange.getColumn();


  var sheet = SpreadsheetApp.getActiveSpreadsheet()
                            .getSheetByName("Sheet1"); // put your sheet name between the brackets

  if (editCol == 3) {   // checking if the edited cell was in column C

      var range = sheet.getRange(editRow, 1, 1, editCol - 1); // Take 1st cell in the edited row and make it span 1 row and 2 columns
      var values = range.getValues(); // get values array for the range
      Logger.log(values);

  }

}

The resulting variable ('values') is an array, so you must loop through it to get string values. As for the emails, you could store multiple emails in a cell, separated by comma. Calling split() method on a string will get you an array of values (emails) that you can then pass to the sendEmail method as the first parameter.

  var cellValue = "[email protected],[email protected],[email protected]";
  var recipients = cellValue.split(",");  
  MailApp.sendEmail(recipients, "From google-apps-script", "hello from google apps script")

Send email from cell value google sheet, with other columns in email

1 Answers