Google-sheets – Send the argument of a custom function as an email

google sheetsgoogle-apps-script

I am trying to send email via script in Google Spreadsheet. I know that the argument in this case cell B4 will be integer.

In my cell is function =sendEmails(B4) and my script looks like this.

function sendEmails(result) {
  var cell = Number(result);
  Logger.log(cell);
  Logger.log(result);
  MailApp.sendEmail("info@domain.com", "Lab", cell);
  MailApp.sendEmail("info@domain.com", "Lab", result);
}

But the problem is that in the logs it shows this and the same arrives by email.

[15-09-27 20:13:01:449 CEST] NaN
[15-09-27 20:13:01:449 CEST] undefined

Where is my mistake?

Best Answer

As Rubén pointed out, custom functions cannot be used for this purpose. Sending an email requires authorization, which is not a part of custom function workflow. Quoting from documentation:

custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following:

[a list that does not include MailApp]

The same page offers a solution:

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Related Topic