Cannot execute sendEmail from trigger function

Question

I'm looking to see if this is possible, I have developed a google form, where there is a date picker, In the spreadsheet output, Im looking to run a check to see if the user is under 18 or not. If the user is under 18 I wish to trigger an email.

I have got the email part working, but only when I execute the code from within the google scripts window. (I still need to get the trigger working, it gives me You do not have permission to call sendEmail) But I can run it from within the google script environment no problem

I know how I would calculate the date, IE getTheYear() - getCurrentYear(), and wrap this within an IF else block that contains the email execution script.

Here is my attempt I don't think I'm to far off

function ageChecker() {

var sheet = SpreadsheetApp.getActiveSheet();
var valTEST = sheet.getRange("I2").getValue(); 
var cell = sheet.getActiveCell();
var msg="Warning Student X is under 18" 
var ui = SpreadsheetApp.getUi();

// Get the actual cell value
// var response = ui.alert(valTEST, ui.ButtonSet.YES_NO);
// valTest output TUE SEPT 17 1991 00:00:00 GMT+0100 (BST)

var d = new Date();
var timeStamp = d.getTime();  // Number of ms since Jan 1, 1970
//var response = ui.alert(d, ui.ButtonSet.YES_NO);

var age = valTEST-d;

var response = ui.alert(age, ui.ButtonSet.YES_NO);

 if(valTEST==18) {   
    MailApp.sendEmail("email@domain.ie","Student Error", msg);
  }

}

Possible duplicate of [Not allowed to execute code from Google sheets but it runs from the script editor](http://stackoverflow.com/questions/19840894/not-allowed-to-execute-code-from-google-sheets-but-it-runs-from-the-script-edito) — Mogsdad, Jan 13 '16 at 17:17

score 1 · Answer 1 · answered Oct 18 '14 at 08:29

Here is the exact same problem.

Not allowed to execute code from Google sheets but it runs from the script editor

Mogsdad said:

Read over Permissions and Custom Functions. Since custom functions (scripts called from > spreadsheet functions) are open to any user of a spreadsheet, they aren't allowed to use any service that requires authentication. That's why you can't send mail from one.

This is different than the process of Authorizing a script to access your services. That does make the error message confusing, but rest assured that was just about the way you were invoking the script.

No problem though, because a custom function is a bad way to perform this type of action anyway, because the function will be re-evaluated every time there is a change in the spreadsheet, sending many more emails than you want.

I recommend that you create a menu item for this operation instead. (See the sample code provided in the editor if you create a new Spreadsheet script.) The workflow would be to move the cursor to the row you want processed, then use the menu to "Make It So", which would invoke your script.

Not really helpfull to me... I need help getting my logic to work, I can sort this issue at a later point... — Nemothefish, Oct 18 '14 at 17:23

score 0 · Accepted Answer · answered Oct 19 '14 at 22:49

I have found a workaround / solution for this.

1) Im getting the spread sheet to do the Age calucation

=IF(( IF((DAY(NOW())-DAY(I2))<0 , -1,0)+(MONTH(NOW())-MONTH(I2)))< 0 , -1 , 0)+(YEAR(NOW())-YEAR(I2))

2) I created a button that can execute the script for me

3) I added in a nice email / cell colour option if the user is under 18

I hope this helps someone

function ageChecker() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 var range = sheet.getDataRange();
 var values = range.getValues();
 var rangeColour = sheet.getRange("J:J");

 for (var r=0; r<values.length; r++) {

 var row = values[r],studentName = row[3],studentID = row[2],studentAge = row[9];
 var msg="Warning Student "+studentName+" with "+" Student Number ("+studentID+")"+" is under 18 with an age of "+ studentAge;   
 var cell = range.getCell(r+1, 10);

  if(studentAge <= 18) {   
    MailApp.sendEmail("test@user.com","Under Age Student Alert", msg); 
    cell.setBackgroundColor("#FF3300");     
  }
 } 
}
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menubuttons = [ {name: "Age Checker", functionName: "ageChecker"}];
ss.addMenu("HelperButton", menubuttons);
}

no need for a cell formula. You can read the cell as date from javascript. also note that its not enough to substract years. That could skip over some people that are almost 18. Instead you need to perform more date math. — Zig Mandel, Nov 07 '14 at 02:48

Cannot execute sendEmail from trigger function

2 Answers2