0

I am trying to set up a script for a google spreadsheet that will email a specific person whenever a cell in Column M is modified to 'y'. I found this script, email notification if cell is changed and I am trying to modify it to suit my needs, but I am having an issue getting it to work. This is my script as it stands now.

function onEdit(e){
  Logger.log(e)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(ss)
  var sheet = ss.getActiveSheet();
  Logger.log(sheet)
  var cell = ss.getActiveCell().getA1Notation();
  Logger.log(cell)
  var row = sheet.getActiveRange().getRow();
  Logger.log(row)
  var column = sheet.getActiveRange().getColumn();
  Logger.log(column)
  var cellvalue = ss.getActiveCell().getValue().toString();
  Logger.log(cellvalue)
  var recipients = "08cylinders@gmail.com"; //email address will go here
  var message = '';
  if(cell.indexOf('M')!=-1){ 
message = sheet.getRange('M'+ sheet.getActiveCell().getRowIndex()).getValue()
} 
  Logger.log(message)
  Logger.log(cell)
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + to view the changes on row: ' + row; //New comment: «' + cellvalue + '». For   message: «' + message + '»';
  MailApp.sendEmail(recipients, subject, body);
}

If anybody has an idea of what I'm missing, I would greatly appreciate any help.

Thank you,

Victor

Community
  • 1
  • 1
Victor Octo
  • 1

2 Answers2

0

i think this may solve your event problem:

function onEdit(e) {
var ssActive = e.source;
var ssActiveRange = ssActive.getActiveRange();
var ssActiveRangeVal = ssActiveRange.getValue();
var ActiveRow = ssActiveRange.getRow(); // if needed to put on body's message
var ActiveColumn = ssActiveRange.getColumn();

if((ssActiveRangeVal=='y' || ssActiveRangeVal=='Y') && ActiveColumn==13){ // ActiveColumn==13 for M
  // write down your mail code here
}

}

LeandroP
  • 337
  • 2
  • 8
  • Hi. Thank you for this. I gave it a try, and while I can get it to run manually, if I set up a trigger, it doesn't work. Do you have any thoughts? – Victor Octo Mar 04 '15 at 20:40
0

You cannot send an email from the onEdit trigger. What you need to do is save the edits as a Document Property and then setup a 1-minute time based trigger that sends the content of the property in an email and flushes the queue.

Amit Agarwal
  • 10,910
  • 1
  • 32
  • 43