guhsd-rblair
7/21/2017 - 3:29 PM

+Paul Seymore - Auto emailer, sheet specific.

+Paul Seymore - Auto emailer, sheet specific.

/*
Majority code taken from Paul's existing project. Updated to utilize the event object as an event object (requires on edit trigger).
Also replaced getActiveRowValues with getRowValues to use the edited range.
Note - does not check for multiple row edits!
*/

function Current (event) {
  var ss = event.source;
  var range = event.range;
  var sheet = range.getSheet();
  var sheetName = sheet.getName();
  var row = range.getRow();
  var column = range.getColumn();
  var cellvalue = event.value.toString(); // only is set if the range is a single cell - also, this doesn't ever get used?
  if (sheetName === "Current or Next Week" && column == 8 && sheet.getRange("X" + row).getDisplayValue() == "Yes")
  {
    var rowVals = getRowValues(sheet, row);
    var aliases = GmailApp.getAliases();
    Logger.log(aliases);
    var bodyHTML,o,sendTO,subject;
    o = {};
    bodyHTML = 'An approved allocation has been increased by more than 500 cases.<br \> <br \> '
      + '<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr bgcolor=#7bb134><th>Sheet Name</th><th>Row</th><th>Timestamp of change</th><th>New Case Quantity</th><th>Original Quantity</th><th>Delivery Date</th><th>Depot</th><th>Allocation Details</th></th></tr><tr><td align = center bgcolor = #F8A950>'
      + sheetName + '</td><td align = center bgcolor = #F8A950>'
      + rowVals.row + '</td><td align = center>'
      + rowVals.timestamp + '</td><td align = center>'
      + rowVals.quantity + '</td><td align = center>'
      + rowVals.origquant + '</td><td align = center>'
      + rowVals.date + '</td><td align = center>'
      + rowVals.depot + '</td><td align = center>'
      + rowVals.details + '</td><td align = center>'
      + rowVals.info + '</td></tr></table>' // Added info to your table.
      + '<br \>This allocation may need to be reviewed. Use the link below to go to the Allocation Requests sheet.<br \> <br \>'
      + '<a href=\"https://docs.google.com/spreadsheets">Allocation Requests</a>'
      + '<br \> <br \><i><b>This is an automated email. Please do not reply to it.<\i>'
    ;
    o.htmlBody = bodyHTML;
    o.from = aliases[0];
    sendTO = "email@domain.co.uk";
    subject = "Increase in an allocation - " + rowVals.quantity + " cases on " + rowVals.date;

    GmailApp.sendEmail(sendTO,subject,"",o);
    SpreadsheetApp.flush();
  };
}

function getRowValues (sheet, row){
  var values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();
  var result = {
    "details":4, // 4 or 5 - Details or Info? Column D or E.
    "depot":6, // Column F
    "date":7, // Column G
    "info":5, // Column E - Added info property.
    "quantity":8, // Column H
    "name":2, // Column B
    "timestamp":15, // Column O
    "row":row,
    "sheet":sheet,
    "origquant":23, // Column W
  }
  for (var prop in result) {
    switch (prop) {
      case "row":
      case "sheet":
        break;
      default:
        result[prop] = values[result[prop] -1];
    }
  }

  return result;
}