CODEWITHSUNDEEP
google-apps-script
Brett
Brett

Reputation: 1

How to prevent trigger from activating multiple times?

'On Edit' triggering multiple email responses

I have a code that sends an email once column W is filled with an email address (which occurs when somebody edits column U in that same row). It is an 'On Edit' trigger. However, when I enter something in column Y it sends another identical email. I understand it is probably because the trigger is 'On Edit' and it is responding to an edit in that row, but how do I make it only send an email when column W is the one that changes?

  function confirmation() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var responses = spreadsheet.getActiveSheet();
  var range = responses.getActiveCell();
  var relevantRow = range.getRow()

  responses.getRange(relevantRow,22).setFormula('=if(U'+relevantRow+'="","","Changes Made")');
  responses.getRange(relevantRow,23).setFormula('=if(U'+relevantRow+'="","",C' + relevantRow + ')'); 

  var email = responses.getRange(relevantRow,23).getValue();
  var user = responses.getRange(relevantRow,2).getValue();

  var emailBody = 'Hi' + ' ' + user + '\n\nThe online banking request you have submitted has been approved, and all necessary changes have been made. Please check and make sure that you are having no issues. If you are still having issues, please reach out to the appropriate bank admin email box. \n\nAPAC: [email protected] \nEMEA: [email protected] \nNAM/LATAM: [email protected]. \n\nThank you, \nNielsen Corporate Treasury';

  var subject =  'Online Banking Request Completed'

  MailApp.sendEmail(email, subject, emailBody) ;
  }

Upvotes: 0

Views: 279

Answers (1)

Александр Ермолин
Александр Ермолин

Reputation: 1175

function onEdit(e) {} has e (event) argument. You can consider e.range member to check where did the edit operation take place.

if (e.range.getColumn() == 23) {  // W column is 23-th
  // do something
}

Any code in place "do something" will be triggered only if a cell in column W was edited.

Upvotes: 1

Related Questions