I need a script which will automatically trigger sendEmail function when cell value changes

user_13927083 0
Viewed 64

I have written a code in which the function sendEmail sends an email which is present in cell E9 in my google sheet which is linked to a form in my website. As a new user enters his/her email in the form that email is reflected in cell E9 in my google sheet. After which i have to manually run the app script to successfully send the email to the mail id present in cell E9. I want this to be automatic so that i don't have to run the app script manually every time a user enters a new email. Please suggest an app script i have little knowledge in these codes so a full executable code will be a great help. Thanks

 function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  var emailAddress = sheet1.getRange(9,5).getValue();
  var subject = "Checking the app script" 
  var message = "Checking the app script working from indigenious id and drive link https://drive.google.com/file/d/1MdoADqSeudMwMruLJUU32OA5bdtyweiv/view?usp=sharing "
  MailApp.sendEmail(emailAddress, subject, message);

  
}
google-apps-script google-sheets-api google-sheets
1 Answers

You need to set your function on an installable onEdit trigger

  • onEdit triggers fire when an edit is taking place in the spreadsheet (e.g. a user enter a vlaue in a cell)
  • they run your funciton automatically when an event fires the trigger
  • They allow you to retrieve event objects related to the edit, e.g. the cell that has been edited and the value that has been entered - see here for available Google Sheets events for Edit
  • Due to restrictions of simple triggers, you need to use the installable version in order to be able to send emails
  • Bind the trigger to your function as described here

Modification of your code to run on trigger (please note that due to usage of event objects this code will error if you try to to run it manually by pressing the "play" button):

function sendEmail(e) {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  if(e.range.getA1Notation() == "E9" && e.range.getSheet() == sheet1){   
    var emailAddress = e.value;
    var subject = "Checking the app script" 
    var message = "Checking the app script working from indigenious id and drive link https://drive.google.com/file/d/1MdoADqSeudMwMruLJUU32OA5bdtyweiv/view?usp=sharing "
    MailApp.sendEmail(emailAddress, subject, message);
  } 
}

This code uses the event objects range and value.

user_115997890
Related