1
votes

I referenced this post most recently to make a launch calendar with dates and names:

The two columns I have are one for a date (A) and the other for the launch name/description (B). In the ideal state, I wanted to send notifications when:

  • There was a new launch entry name (B); but not necessarily a date entered yet (A)
  • Separately, sending an update for date changes (A) and referencing the launch name (B); there probably won't be any case where I have a date but no launch name (unless I'm in the process of entering one after the other), so there should not be notifications sent if there's just a date entered only and no corresponding B cell yet.

I'm seeing some false positives currently with this code in the following ways:

1.) If I don't enter a date (A) but put text in column (B), I'm getting a date email that is empty but referencing (B); I'd like to restrict it to not send the notification if the corresponding cell in column A is empty

2.) I'm sometimes getting duplicate date (A) emails; this has something to do with adding a date when the second 'if' condition is present to check for column B (launch name); it's then that when I add a new date, I receive two emails about adding the date.

3.) If I enter a launch name (B) but no date (A), I'm getting both emails; this is happening when the second if condition is present. Preference is to not receive a date email if date cell (A) is empty.

I also just noticed that deletion and empty cells also seems to send notification emails.

    function sendNotification() {
  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 mycell = ss.getActiveSelection();
  var cellcol = mycell.getColumn(); 
  var recipients = "[email protected]";
  var message = '';
  if(cell.indexOf('A')!=-1){ 
    message = sheet.getRange('B'+ sheet.getActiveCell().getRowIndex()).getValue()
  }
  var subject = ':Date modification for  «' + message + '»';
  var body = 'The launch tracker has been updated. \n\n\nA date modification of: «' + cellvalue + '» has occured for the following user-facing change: «' + message + '» \n\n\nThis modification should also be reflected on the Launch Calendar. \n\n\nVisit: ' + ss.getUrl() + ' to view the modification, found in row: «' + row + '»';
  MailApp.sendEmail(recipients, subject, body);
  if (cellcol == 2)
  var subject = 'New addition of «' + cellvalue + '»';
  var body = 'The waypoint tracker has been updated. \n\n\nA new user-facing change of: «' + cellvalue + '» has been added. \n\n\nThis modification should also be reflected on the Launch Calendar when date information is added. \n\n\nVisit: ' + ss.getUrl() + ' to view the modification, found in cell: «' + cell + '»';
  MailApp.sendEmail(recipients, subject, body);
}