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 TriptiHirani commented Feb 4, 2020

var range = e.range;
is not working

@TriptiHirani

This comment has been minimized.

Copy link

@TriptiHirani TriptiHirani commented Feb 4, 2020

error

@redlightgo

This comment has been minimized.

Copy link

@redlightgo redlightgo commented Jun 10, 2020

Hello, I am new to programming and honestly do not have a clue what I am doing. I would like to adapt the above script to a google sheet that originates from a google form. I would like to get an email only when a certain question, i.e. a cell in the google sheet is modified with the word "Yes". I presume I could modify this script to do so after I learned how to implement it using the examples in the blog post. at https://blog.gsmart.in/alert-by-email-when-input-data-on-google-spreadsheet-meets-given-condition/

Thank you for creating the post and sharing this wonderful script.

I too get the same 'Range' errors TriptiHirani experiences but on line 26 of the above code. My google sheet has the same column headings as those in the blog post and I did update the email address on line 1

Here's to hoping someone sees this request. Cheers.

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.