Google Sheet script - Automatically send an email with values when they are added to a Google Sheet

Question

I've been trying to follow the document below but have got a bit stuck. In short, I am trying to have a Google Sheet send out an email automatically when two values are added to a Google Sheet (Name, Telephone)

The function below works great when I run it from App script but I am looking for it to grab the values that are inserted at the time and send the email automatically rather than having to run the script each time in App Script.

// Fetch the email address
var emailRange = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2");
var emailAddress = emailRange.getValue();
// Send Alert Email.
var message = 'This is your Alert email!'; // Second column
var subject = 'Your Google Spreadsheet Alert';
MailApp.sendEmail(emailAddress, subject, message);

https://www.groovypost.com/howto/google-sheets-send-email-based-on-cell-value/

Andre Bradshaw · Accepted Answer · 2022-08-25 15:37:01Z

0

You need to use the onEdit feature. https://developers.google.com/apps-script/guides/triggers/events

function onEdit(e){
  sendEmailToUpdatedValue(e)
}
function sendEmailToUpdatedValue(e){
  let email_x = /\b[\w\.\-\+]+@[\w\-]+\.[a-zA-Z]{2,13}(\.[a-zA-Z]{2,13}|\b)/;
  let email = e.range.getValue();
  let is_email = email_x.test(email);
  if(is_email){
    //your email function here
  }
}

Be sure to run the onEdit function once within Apps Script in order to ensure the trigger is set up. That first time will get an error.

Keep in mind that onEdit only fires from a user action, so if the emails are being added from a form or some other script, this will not work. In those scenarios you would need a time based trigger.

answered Aug 25, 2022 at 15:37

Andre Bradshaw

1191 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheet script - Automatically send an email with values when they are added to a Google Sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related