0

I need Google Spreadsheet to send an email when there's changes to a cell.

Problem is, what appears in this cell is determined by values of another sheet (I used if and filter functions for this).

The usual,

if(cell.indexOf('F')!=-1){    
    MailApp.sendEmail(emailAddress, subject, message);
}

doesn't seem to capture the change in the cell since the change is brought about by a formula compiling data from another sheet, however, it does send me an email if I were to manually type in stuff in column F.

The following is my whole code in the script editor: 

function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  ScriptApp.newTrigger("sendNotification")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onEdit()
  .create();
};

function sendNotification(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var dataRange = sheet.getRange(2, 1, 2, 2);
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i]
    var emailAddress = row[0];
    var message = row[1];
    var subject = "Job exceeded time allocated";
    if(cell.indexOf('F')!=-1){
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}
Nathan Tuggy
  • 2,237
  • 27
  • 30
  • 38
Zachary Lim
  • 1
  • 1

1 Answers1

0
  1. There is a discussion of how to extract the value from a cell with a formula here:

get and set Value of a Cell do not work Basically - getCell() returns a Range object.. you need to add the .getValue() method to get the value of the cell in the range which you are trying to do in the script. I found that using getRange with the cell address works much better: i.e. getRange ('B1').

  1. getCell is not designed to return the value of your cell but rather to return a Range object of a cell, as discussed here:

Trying to read cell 1,1 in spreadsheet using Google Script API

The cell object is a formula so you really don't want that. Hence using getRange meaning the you get the range of the result of the formula in the one cell.

That sounds like I know more than I do. I stumbled across this problem myself and fixed it as indicated above.

Community
  • 1
  • 1
DroidA
  • 1
  • 2
  • 1
    Welcome To Stack Overflow! Instead of pasting a link to a solution, you should also add the relevant code from the link in your answer. That way if the link ever goes down the answer is still available. – Gary Storey May 28 '15 at 20:45