How to send emails in google sheets based on the following conditions?

Question

https://docs.google.com/spreadsheets/d/1tf99kQdDSGxvj8f9B383W1NZRHRsd-b6bUcl9FJAOmQ/edit?usp=sharing

I want to send email from google sheets based on the criteria in column D and subject and messages choose as per criteria.I lost it in the line to choose the three different criteria(Yes,No,Confirm) and couldn't loop it.The email will be sent to different ids in sheet1 table.

How can i achieve this in google sheets scripts and how to send emails when new entry are made into the sheet?

Any solutions would be highly helpful for my project. Googlesheet newbie.

function checkValue(e)
{
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName(“Sheet1”);
var valueToCheck = sheet.getRange(“D2:D”).getValue();
var rangeEdit = e.range.getA1Notation();
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet1');
var n=sheet1.getLastRow();

if(rangeEdit == “D2:D”)
{
if(valueToCheck = Yes )
{
function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet1');
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,1).getValue();
var subject = sheet1.getRange(i,2).getValue();
var message = sheet1.getRange(i,3).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}

Please try this solution How to send email notifications on Google Spreadsheets if any cell value changes to a certain value — Rocky

Nikko J. Nikko J. · Accepted Answer · 2021-02-25T19:03:01

You can add Google Forms as a way to populate the data in your sheet. Using forms will give you access to new entry data which you can use to send an email.

How to add Google Forms to Google Sheet?

In your spreadsheet, click on Insert and select Form

How to determine if new entry is added and how to access the data?

You can use Installable Triggers. Installable Triggers run a function automatically when a certain event, such as submitting a form, occurs. When a trigger fires, Apps Script passes the function an event object as an argument, typically called e. The event object contains information about the context that caused the trigger to fire. You can follow the steps here on how to create Installable Trigger.

Note: For this project, make sure to use On form submit as event type.

Example:

Form:

Code:

function sendEmail(e) {
  var data = e.namedValues;
  var msgContent = {
    'Yes':{
      'Subject': 'Receipt of your goods',
      'Message': 'We acknowledge receipt of goods.',
    },
    'No':{
      'Subject': 'Non delivery',
      'Message': 'We have not received the goods.',
    },
    'Confirm':{
      'Subject': 'Confirm',
      'Message': 'Confirm your message.',
    }
  }
  var emailAddress = data['Email'][0];
  var criteria = msgContent[data['Criteria'][0]];
  var subject = criteria['Subject'];
  var body = criteria['Message'];
  MailApp.sendEmail(emailAddress, subject, body);
}

Output:

How to send emails in google sheets based on the following conditions?

1 Answers

Example: