0

I've been using the below script which is working fine, however I'd like to cut down on the number of times it triggers.

function sendNotification(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheetByName("Allocation Requests");
  var sheet = ss.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var emailAdd = "email@yourdomain.com";
  if(event.range.getA1Notation().indexOf("G") > -1 && sheet.getRange("G" + row).getDisplayValue() && emailAdd.length > 1)
  {
    {var confirm = Browser.msgBox('Case Quantity', 'Is this a confirmed amount?', Browser.Buttons.YES_NO); 
  if(confirm!='yes'){return};
     var rowVals = getActiveRowValues(sheet);
     MailApp.sendEmail({
       to: emailAdd,
       subject: "Allocation Request - " + rowVals.quantity + " cases on " + rowVals.date,
       htmlBody: "There has been a new allocation request from " + rowVals.name + " in the " + rowVals.team + " team.<br \> <br \> " 
       + "<table border = \"1\" cellpadding=\"10\" cellspacing=\"0\"><tr><th>Issuing Depot</th><th>Delivery Date</th><th>Case Quantity</th></tr><tr><td>"+rowVals.depot+"</td><td>"+rowVals.date+"</td><td>"+rowVals.quantity+"</td></tr></table>" 

      });
  }
}


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

Users enter more than one row of data at a time, and I would like to receive a notification when that user has finished entering data, but still send the information in the email for all the rows they've entered. At present I get an email each time they complete a row.

P.Seymour
  • 53
  • 3
  • 9
  • I can't really think of a way to see when users are done, but for fewer triggers it would be possible to more from event based to time based where you build it so it checks for updates every 30 min or so and emails you if it can find any. – Hink Feb 16 '17 at 20:16
  • Add a column have end user mark X for complete? – OblongMedulla Feb 16 '17 at 21:05

0 Answers0