Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sample code sending email when Google Sheet is updated
var admin_email='youremailaddress@gmail.com'; //<- update your email address here
function triggerOnEdit(e)
{
sendEmailOnApproval(e);
}
function showMessageOnApproval(e)
{
var edited_row = checkStatusIsApproved(e);
if(edited_row > 0)
{
SpreadsheetApp.getUi().alert("Row # "+edited_row+" approved!");
}
}
function showMessageOnUpdate(e)
{
var range = e.range;
SpreadsheetApp.getUi().alert("range updated " + range.getA1Notation());
}
function checkStatusIsApproved(e)
{
var range = e.range;
if(range.getColumn() <= 4 &&
range.getLastColumn() >=4 )
{
var edited_row = range.getRow();
var status = SpreadsheetApp.getActiveSheet().getRange(edited_row,4).getValue();
if(status == 'approved')
{
return edited_row;
}
}
return 0;
}
function sendEmailOnApproval(e)
{
var approved_row = checkStatusIsApproved(e);
if(approved_row <= 0)
{
return;
}
sendEmailByRow(approved_row);
}
function sendEmailByRow(row)
{
var values = SpreadsheetApp.getActiveSheet().getRange(row,1,row,4).getValues();
var row_values = values[0];
var mail = composeApprovedEmail(row_values);
//Uncomment this line for testing
//SpreadsheetApp.getUi().alert(" subject is "+mail.subject+"\n message "+mail.message);
MailApp.sendEmail(admin_email,mail.subject,mail.message);
var candidate_email = composeCandidateEmail(row_values);
MailApp.sendEmail(candidate_email.email,candidate_email.subject,candidate_email.message);
}
function composeApprovedEmail(row_values)
{
var first_name = row_values[0];
var last_name = row_values[1];
var email = row_values[2];
var message = "The following applicant is approved: "+first_name+" "+last_name+
" email "+email;
var subject = "Applicant approved "+first_name+" "+last_name
return({message:message,subject:subject});
}
function composeCandidateEmail(row_values)
{
var first_name = row_values[0];
var last_name = row_values[1];
var email = row_values[2];
var message = "The following applicant is approved: "+first_name+" "+last_name+
" email "+email;
var subject = first_name+ ", Your appliation is approved";
var message = "Hello "+first_name+"\n"+
"Your application is approved.\n Please follow the following instructions to proceed with the process.\n";
//... etc
return({message:message,subject:subject, email:email });
}
@TriptiHirani

This comment has been minimized.

Copy link

TriptiHirani commented Feb 4, 2020

var range = e.range;
is not working

@TriptiHirani

This comment has been minimized.

Copy link

TriptiHirani commented Feb 4, 2020

error

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.