Sending E-mail From a Google Spreadsheet

 Update August 8th 2013: There is chapter 9 in this book devoted to email and calendar manipulation using Google Apps Script. This chapter covers the GmailApp object in addition to the more limited MailApp object that is used here.

It’s been a while since the last real post so here is one that answers a reader’s query but also gets me back into writing some JavaScript after a liong layoff that’s left me rusty.

I've had a question recently, in italics, as follows:
do you have any fucntion where when it is called like

=if(A2>"5",CallEmailFunction()
,"Don't Call")

so if CallEmailFunction is called it will send email to user with alert notification

Thanks

Firstly, Google provide a tutorial entitled Tutorial: Sending emails from a Spreadsheet
This will be our starting point but I’ll break it down into smaller functions.  
This is not going to work because we are trying to call a function that flouts the stricture that a user-defined function must return a value but cannot have side-effects where the side-effect here is sending an email.  See the earlier posting on user-defined functions.  Excel is similar in this respect in that we cannot call subroutines as user-defined functions.

Anyway, from the editor, let’s write some code
First up, we need a function that when given the arguments, “email_address, “email_subject”, and “email_text” will do as we ask, let’s call it “sendEmail()”.

// We should do some checks in here such as error handling and argument checking
function sendEmail(email_address, email_subject, email_message) {
 MailApp.sendEmail(email_address, email_subject, email_message);
}

// Write a function to call the one defined above
function test_sendEmail() {
 var addr = whoever@gmail.com';
 var subj = 'test email from Google Spreadsheet';
 var msg = 'Let me know if this came through ok!';

 sendEmail(addr, subj, msg);
}

Try running the function “test_sendEmail()”, after asking for authorisation, it should send the email using the arguments given.



Let’s see if we can call the function  “sendEmail()” from a spreadsheet where the arguments are given in cells:

Address Subject   Message Criterion
email1@gmail.com    testing    email message for you     5
email2@gmail.com    testing    email message for you too     3

Attempting to call the spreadsheet from a cell using this formula:
“=if(D2>=5, sendEmail(A2,B2,C2))”
results in a permissions error:
error: You do not have permission to call sendEmail (line 2)

However, we can achieve the desired functionality by running a calling script from the script editor.

Here are some values in a spreadsheet:

The following code starts at cell A2 and based on the criterion supplied in column D, sends an email to the address in column A.


function sendEmail(email_address, email_subject, email_message) {
  MailApp.sendEmail(email_address, email_subject, email_message);
}


function test_sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.setActiveCell('A2');
  var criterion_cutoff = 5;
  var i = 0;
var addr;
var subj;
var msg;
  do {
    addr = cell.offset(i,0).getValue();
    subj = cell.offset(i,1).getValue();
    msg = cell.offset(i,2).getValue();
    criterion = cell.offset(i,3).getValue();
  
    if(criterion > criterion_cutoff) {
      sendEmail(addr,subj,msg);
      Browser.msgBox('Sending email to: ' + addr);
    }
    i++;
  } while( cell.offset(i, 0).getValue().length > 0 )
    
  Browser.msgBox('Done!');
}

To use just create a spreadsheet like the one shown above, open its script editor and paste in the code for the two functions given above. Save the code and execute the "test_sendEmail()" function.

Some Notes On The Code

• Declare variables and set a starting cell (var cell = line)
• Set a counter for the do loop
• Use the "offset" method of the Range object to retrieve cell values
• Check the criterion variable and based on its value, send emails by calling the "sendEmail()" function with arguments taken from cell values
• Increment i so that the code goes to the next row
• Continue until the "offset" returns an empty cell
• After not having written any JavaScript for a while, I'd forgotten that "length" is a property and not a method so it took me some time and head-scratching to determine why "length()" calls in the "while" condition were not working!

The above code could be executed from a form and I'll try to cover this in a future post. Meanwhile, check out what looks like some very useful material at Henrique Abreu's formmailer website

Planning a post on regular expressions very soon.