0

I've built a sheet that generates messages for clients based on certain input data. The message appears in a cell in column B when all the required data has been entered. When a new text value is generated at column B I need this script to be activated:

    // This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */ 
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow(); // Number of rows to process
  // Fetch the range of cells 
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var emailSent = row[2]; // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Cognitive Training Software Tailored To Your Audience';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

I'm new to any sort of coding and could't figure out how to make this happen. I tried creating an installable trigger using onEdit but could no longer get anything to send.

Invalid property ID when trying to create simulated trigger event

0

On edit triggers are triggered on any changed made by an editor. The alternative is to use the edit event object and a control-flow statement, like if..else to terminate immediately the function if another column was edited.

Example

function sendEmails2(e){
  if( e.range.columnStart === 2 ) {
    // include here the code that does what you want if column 2 (B) is edited
  } 
}
  • I tried as you suggested and get this error: Cannot read property "range" from undefined. (line 2, file "Code"). – Sahelanthropus Oct 22 '18 at 20:46
  • @Sahelanthropus It's very likely that you tried to run the function from the Google Apps Script editor but the edit event object is available only when the function is triggered by an on edit trigger. If you want to debug/test this, you should create a helper function that creates an object with the same properties of the edit event object. For further details see stackoverflow.com/a/16089067/1595451 – Rubén Oct 22 '18 at 20:53
  • Correct, I did try to run using GAS editor, I also tried editing column B as I thought this should be recognized as a trigger but got no result. – Sahelanthropus Oct 24 '18 at 5:27
  • 1
    Solved! I accidentally placed the send emails function inside of itself along with the code that does what I wanted. Accepted your answer. – Sahelanthropus Oct 24 '18 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.