sendEmail function: How do I restrict this so the cell value has to meet a specific text value such as “Yes” or “No”

1
votes

I want google sheets to send an email when a cell is edited in a specific column, however, the cell must match a specific word. I.e the email will only send if the cell text is equal to "Yes" or "No"

I've been able to get the specific column and Onedit trigger setup in google script editor, however, I haven't been able to work out getting the cell to match specific text

function sendEmail(event) {
  // Getting the row
  const ss = event.source.getActiveSheet();
  const row = ss.getDataRange().getValues()[event.range.getRow()-1];
 const currentColumn = event.range.columnStart;
  if (ss.getName() !== 'Sheet1' || currentColumn !== 5 )  
return; 
 var emailAddress= [email protected]
var emailBody= "Test"
var subject = "Test"
MailApp.sendEmail(emailAddress, subject, emailBody);

I want google sheets to send an email when a cell is edited in a specific column, however, the cell must match a specific word. I.e the email will only send if the cell text is equal to "Yes" or "No"

1
javascriptemailgoogle-apps-scriptgoogle-sheetstriggers
@Jay instead of google-sheets-api the correct is google-apps-script because that is the "language" being used on the OP code. (The Sheets API isn't used here) - Rubén

1 Answers

0
votes

I added one line of code to your function that should do the trick. :)

function sendEmail(event) {
  // Getting the row
  const ss = event.source.getActiveSheet();
  const row = ss.getDataRange().getValues()[event.range.getRow()-1];
  const currentColumn = event.range.columnStart;
  if (ss.getName() !== 'Sheet1' || currentColumn !== 5 )  
return;

  if (!event.value.match(/^(Yes|No)$/)) return;

  var emailAddress= [email protected];
  var emailBody= "Test";
  var subject = "Test";
  MailApp.sendEmail(emailAddress, subject, emailBody);
}

References

regex101.com

Event Objects

Here's how I'd write it.

function sendEmail(event) {

 const sheet = event.source.getActiveSheet();
 const currentColumn = event.range.getColumn();
 if (sheet.getName() !== 'Sheet1' || currentColumn !== 5)  
return;

 if (!event.value.match(/^(Yes|No)$/)) return;

 var emailAddress = [email protected];
 var emailBody = "Test";
 var subject = "Test";
 MailApp.sendEmail(emailAddress, subject, emailBody);
}