0

I have searched and troubleshooted for many hours, but I can't get this to work.

I am trying send an email when a cell in Column C is marked Complete. The email will contain info from Column D as well as the sheet name. The code below works, but if Column C has more than one item marked Complete, it will send multiple emails.

How can I modify this code to only send an email once for newly marked items?

I'd rather avoid adding a column labeling the item as 'email sent'.

EDIT: For the avoidance of doubt, I wanted to note that I have this set up as an OnEdit Trigger, so that's not the issue.

function sendNotification() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

// figure out what the last row is
var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 2
var startRow = 2;

// grab column 3 (the Project Status column) 
var range = sheet.getRange(2,3,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var projectStatus_values = range.getValues();

// grab column 4 (the Project Name column) 
var namerange = sheet.getRange(2,4,lastRow-startRow+1,1 );
var nameNumRows = namerange.getNumRows();
var projectName_values = namerange.getValues();

// Get Active Sheet Name
var sheetname = SpreadsheetApp.getActiveSheet().getName();

// Loop over the Project Status values
for (var i = 0; i <= numRows - 1; i++) {
var projectStatus = projectStatus_values[i][0];
if(projectStatus == "Complete")

  {var projectName = projectName_values[i][0];

   //Define Notification Details
   var recipients = "EMAIL@EMAIL.com";
   var subject = sheetname + " has a new project marked Completed.";
   var body = sheetname + " has a new project marked Completed:\n\n" + projectName + ".\n\nVisit " + ss.getUrl() + " to view the changes.";

//Send the Email
  MailApp.sendEmail(recipients, subject, body);
  } 
}
//End sendNotification

Of the many threads I perused I found two approaches that seemed like they should work, but both didn't workfor me.

First, I tried Hyde's suggestions here---but I ran into an error ("TypeError: Cannot find function getvalue in object Range.") which I understand because I am using a custom function and not passing a range, but rather an object (probably butchered that).

I also tried this thread's suggestion to incorporate a background color check but haven't had any luck (script is just not sending emails at all despite complete lack of debug errors).

Dan BB
  • 1
  • 1
  • You have 2 states - completeEmailPending and complete(EmailSent). You represent these 2 states with one status "complete". You should make the cell completeEmailPending and when email is sent change it to complete. If that does not help, post a link and someone will fix the code. – bcperth Oct 30 '18 at 14:53
  • @bcperth thank you, clearly sometimes the solution is just to take a simpler approach to get to the same place. It is up and running now! – Dan BB Oct 30 '18 at 18:12

0 Answers0