Skip to content

Instantly share code, notes, and snippets.

@prasanthmj
Created May 2, 2019 07:42
Show Gist options
  • Save prasanthmj/f003df8ee22e37bda45032cdaa7dd690 to your computer and use it in GitHub Desktop.
Save prasanthmj/f003df8ee22e37bda45032cdaa7dd690 to your computer and use it in GitHub Desktop.
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
Copy link

var range = e.range;
is not working

@TriptiHirani
Copy link

error

@redlightgo
Copy link

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.

@drc-dmu-rims
Copy link

I get the same error message on line 26

@Kashif03
Copy link

Hi, was anyone able to solve the 'range not defined' error?

@TSiwale
Copy link

TSiwale commented Jun 9, 2022

I experienced a similar challenge with the "range not defined error. I went off to tweak the code and combined it with some other code snippets. I came up with this version that worked for me. https://github.com/TSiwale/AppsScripts/blob/main/A%20script%20to%20send%20email%20from%20Google%20Sheets%20based%20on%20a%20cell%20value

@skillza-quest
Copy link

It will throw the range error if you test it directly by clicking the Run button. Go to the spreadsheet nad make an edit to see if it works.

@ElPaso8
Copy link

ElPaso8 commented Jun 14, 2022

I also experienced this error from another function. but the code still works if you save it and run it.

@Robisy
Copy link

Robisy commented Sep 9, 2023

The code only works when we manually change the cell value. The email does not come when the cell changes its value automatically by, for example, a formula. Does anyone know how to fix this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment