Google Apps Script personalised Email notifications

Question

I want a script that will send out an email from a spreadsheet when a new row of data is entered. I'd like some of this data to be included in the subject, and some more data and a link to the spreadsheet in the main body.

I'm currently using a script that sends an email when a certain column in the spreadsheet is updated, but I'd now like to personalise it a bit more and make it quicker for the recipient to review without having to always open the spreadsheet.

This is my spreadsheet:

And this is my current script:

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Requests");
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();

  if (cell.indexOf('F')!=-1)
  {
    MailApp.sendEmail({
      to: "[email protected]",
      subject: "Request",
      htmlBody: "There has been a new request. <br \> <br \> To review it, use the link below. <br \> <br \> " +
           "<a href=\"https://docs.google.com/spreadsheets/d/1Eq8Kpn0oOBItaGaKaOOLa1qLZZ2_l2gg6wOkLa6GMWo/edit#gid=872382993\">Requests</a>"
    });
  }
}

I'd like the quantity (column F) and date (column E) to be included in the email subject, and the quantity (F), date (E) and depot (D) included in the main body, with the link to the spreadsheet.

EDIT: Columns G, H & I would be filled in at a later date by the recipient of the email.

Thanks in advance

EDIT:

My script is now looking like this:

function getActiveRowValues(sheet){
  var cellRow = sheet.getActiveRange().getRow();
  // get depot value
  var depotCell = sheet.getRange("D" + cellRow);
  var depot = depotCell.getDisplayValue();
  // get date value
  var dateCell = sheet.getRange("E" + cellRow);
  var date = dateCell.getDisplayValue();
  // get quantity value
  var quantCell = sheet.getRange("F" + cellRow);
  var quant = quantCell.getDisplayValue();
  // return an object with your values
  return {
    depot: depot,
    date: date,
    quantity: quant
  } 

if (cell.indexOf('F')!=-1)
{
  var rowVals = getActiveRowValues(sheet);
  MailApp.sendEmail({
   to: "[email protected]",
   subject: "Request" + " date " + rowVals.date + " quant " + rowVals.quant,
   htmlBody: "There has been a new request. <br \> <br \> To review it, use the link below. <br \> <br \> " + "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr><th>Depot</th><th>Date</th><th>Quantity</th></tr><tr><td>"+rowVals.depot+"</td><td>"+rowVals.date+"</td><td>"+rowVals.quant+"</td></tr></table>" + 
       "<a href=\"https://docs.google.com/spreadsheets/d/1Eq8Kpn0oOBItaGaKaOOLa1qLZZ2_l2gg6wOkLa6GMWo/edit#gid=872382993\">Requests</a>"
  });
}}

But getting the error:

TypeError: Cannot call method "getActiveRange" of undefined. (line 2, file "Code")

I'd like the date and quantity to be included in the email subject, and in the email body the date, quantity and depot, alongside a link to the spreadsheet. — P.Seymour

m5khan m5khan · Accepted Answer · 2017-01-24T14:01:16

Add this function in your script that will extract your required details from the respective column:

/**
* get values of depot, date and quantity from their respective cells.
* @returns    {Object.<string, string>}
*/
function getActiveRowValues(sheet){
  var cellRow = sheet.getActiveRange().getRow();
  // get depot value
  var depotCell = sheet.getRange("D" + cellRow);
  var depot = depotCell.getDisplayValue();
  // get date value
  var dateCell = sheet.getRange("E" + cellRow);
  var date = dateCell.getDisplayValue();
  // get quantity value
  var quantCell = sheet.getRange("F" + cellRow);
  var quant = quantCell.getDisplayValue();
  // return an object with your values
  return {
    depot: depot,
    date: date,
    quantity: quant
  } 
}

Then call getActiveRowValues inside your if condition and also update your subject and htmlBody variables:

if (cell.indexOf('F')!=-1)
{
  var rowVals = getActiveRowValues(sheet);
  MailApp.sendEmail({
   to: "[email protected]",
   subject: "Request" + " date " + rowVals.date + " quant " + rowVals.quantity,
   htmlBody: "There has been a new request. <br \> <br \> To review it, use the link below. <br \> <br \> " + "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr><th>Depot</th><th>Date</th><th>Quantity</th></tr><tr><td>"+rowVals.depot+"</td><td>"+rowVals.date+"</td><td>"+rowVals.quantity+"</td></tr></table>" + 
       "<a href=\"https://docs.google.com/spreadsheets/d/1Eq8Kpn0oOBItaGaKaOOLa1qLZZ2_l2gg6wOkLa6GMWo/edit#gid=872382993\">Requests</a>"
  });
}

And your overall script should look like this:

function sendNotification(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheetByName("Requests");
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var emailAdd = "[email protected]";
  if(event.range.getA1Notation().indexOf("F") > -1 && sheet.getRange("F" + row).getDisplayValue() && emailAdd.length > 1)
  {
     var rowVals = getActiveRowValues(sheet);
     MailApp.sendEmail({
       to: emailAdd,
       subject: "Request" + " date " + rowVals.date + " quant " + rowVals.quantity,
       htmlBody: "There has been a new request. <br \> <br \> To review it, use the link below. <br \> <br \> " + "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr><th>Depot</th><th>Date</th><th>Quantity</th></tr><tr><td>"+rowVals.depot+"</td><td>"+rowVals.date+"</td><td>"+rowVals.quantity+"</td></tr></table>" + 
       "<a href=\"https://docs.google.com/spreadsheets/d/1Eq8Kpn0oOBItaGaKaOOLa1qLZZ2_l2gg6wOkLa6GMWo/edit#gid=872382993\">Requests</a>"
      });
  }
}

/**
* get values of depot, date and quantity from their respective cells.
* @returns    {Object.<string, string>}
*/
function getActiveRowValues(sheet){
  var cellRow = sheet.getActiveRange().getRow();
  // get depot value
  var depotCell = sheet.getRange("D" + cellRow);
  var depot = depotCell.getDisplayValue();
  // get date value
  var dateCell = sheet.getRange("E" + cellRow);
  var date = dateCell.getDisplayValue();
  // get quantity value
  var quantCell = sheet.getRange("F" + cellRow);
  var quant = quantCell.getDisplayValue();
  // return an object with your values
  return {
    depot: depot,
    date: date,
    quantity: quant
  } 
}

Google Apps Script personalised Email notifications

2 Answers