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.
Thank alot man, I really appreciate your hard work, it is working as you have explained.
ReplyDeletebut how can I have it automatically execute the "test_sendEmail()" function, because values in D2 will changing automatically all the time.
is there away execute it from formula or by time using google clock formula.
Thanks alot again
I guess I can do from script editor and set up a project's trigger, I added time driver and every minute...I'll update you if it works.
DeleteThanks again
Hello Mick,
ReplyDeleteI want to add different different criterion for every row under column D.And then i want that the concerned person email ID should get the same report if alert arises.
Pawan