0

I am trying to figure out what's went wrong with my Google Apps Script. I am trying to send the email when any of cells in column 2 is no longer have "-" in it while the next cell is "No". Apparently, sendEmail function is not working for some reason.

I make little example of small spreadsheet below. I want to send the email when third row is matched.

   1  2  3
1  00 - Yes
2  00 - No
3  00 x No

Here is my code:

function onEdit() {
     var s = SpreadsheetApp.getActiveSheet();
     if( s.getName() == "Sheet4" ) { //checks that we're on the correct sheet
       var r = s.getActiveCell();
       var nextCell = r.offset(0, 1);
       if(( r.getColumn() == 2 ) && ( r.getValue() !== '-' ) && ( nextCell.getValue() === 'No' )){ //checks the cell
           MailApp.sendEmail('example@gmail.com', 'test email from Google Spreadsheet', 'Let me know if this came through ok');    
       }
     }
    }
Darthvazor
  • 53
  • 1
  • 1
  • 6
  • Your script does work - when you edit a value in column 2 so that it is something other than `'-'`, it sends an email. An edit in any other column will do nothing. Can you be more clear about what you expect the script to do? – Mogsdad Jun 22 '13 at 15:46
  • It work for you? When I replace example@gmail.com with my email address, and I edit a value something other than '-' in column 2 and it won't send an email. Are you sure you did test run it? – Darthvazor Jun 22 '13 at 18:26
  • We've been focused on the wrong part of this script - the issue isn't about the logic that takes you to sending a mail; the problem is that you are using a service that isn't available to simple triggers. (When I was testing, I replaced `MailApp.sendEmail` with a log, just to test whether the logic worked.) – Mogsdad Jun 23 '13 at 15:57
  • A reasonable work-around would be to use a time-based trigger to send your emails. You could either scan the sheet for rows that satisfy the send-email conditions. Another approach would have your onEdit write a value to a cell in the row to indicate a mail should be sent, and then the timed function would just look for those values, act on them, then change the value. A third option might be to change to using a form - see [this answer](http://stackoverflow.com/a/13871541/1677912) to a question that was very similar to yours. – Mogsdad Jun 24 '13 at 00:52
  • Thank you for advice. I would use time-based trigger for my script. – Darthvazor Jun 24 '13 at 00:59

2 Answers2

1

First, the condition to check inequality is !=. So try the modified code below. Second, you are sending the email to example@gmail.com. I assume the actual code sends the email to a real address.

function onEdit() {
     var s = SpreadsheetApp.getActiveSheet();
     if( s.getName() == "Sheet4" ) { //checks that we're on the correct sheet
       var r = s.getActiveCell();
       var nextCell = r.offset(0, 1);
       if(( r.getColumn() == 2 ) && ( r.getValue() != '-' ) && ( nextCell.getValue() == 'No' )){ //checks the cell
           MailApp.sendEmail('example@gmail.com', 'test email from Google Spreadsheet', 'Let me know if this came through ok');    
       }
     }
    }
Srik
  • 7,907
  • 2
  • 20
  • 29
  • In fact, `!==` is a valid comparison operator, "strict not equal", it compares value & type. For example, `5 != '5'` would evaluate false (they have same value) but `5 !== '5'` would evaluate true (same value, but different type). – Mogsdad Jun 22 '13 at 15:34
  • Mogsdad is correct. Everything is working as expected but the command "MailApp.sendEmail('example@gmail.com', 'test email from Google Spreadsheet', 'Let me know if this came through ok');" seems not execute for some reason. – Darthvazor Jun 22 '13 at 18:30
1

The onEdit() function is an example of a Simple Trigger Function. As described in Understanding Triggers, simple triggers cannot access services that require authentication. That is why MailApp and GmailApp are not available for sending mail.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Aw, ok. Thank you for post the info. I appreciate it. So for this script, how do we work around it since OnEdit function is useless with MailApp? I am not expert with GAS, can you help me to figure it out? – Darthvazor Jun 24 '13 at 00:40