Automate Email Based On Specific Text Entry In Cell

Question

I am having difficulty writing a script that will automatically send an email from Google Sheets. The email should be sent to the email address located in column V, but only after column AB of the same row has "N" entered into it. It would only happen the one time immediately after entry and would not reoccur.

I've tried some of the scripts like Google Apps Script - Send email based on data in cell but am not having success.

AMolina · Answer 1 · 2019-06-14T09:59:42.383

What you need is to iterate through the AB column and check for the "N" entries (assuming it's just a string), then send the email and then, to avoid the recurrence you can change the N to some other value. Using app scripts it would look something like this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Will return a 2D array with the entire's sheet values 
  var data = sheet.getDataRange().getValues(); 

  for (var i = 0; i < data.length; i++){
    if (data[i][0] == "N"){
      // Sends the email, The name section is the name people who receive the email would see ass the "from" id.
      // If there is no email in the cell this will return an error, so you can add a try/catch here to avoid that.
      GmailApp.sendEmail(data[i][1], "Subject", "Message", {name: 'N Finder Script'});

      //To avoid repetition, you can edit the N value after sending the email.
      sheet.getRange("A" + (i+1)).setValue(""); // This would be "AB" in your case.
      sheet.getRange("A" + (i+1)).setValue("S"); // S for email sent, for example.
    }
  }
}

Some small caveats; I ran this with a small test spreadsheet, which is why I search for the value in the "A" column. When I check data[i][0] I am checking the ith row of the first column, you have to change the 0 to the number that matches the column where you have the "N". The same applies to the emails, I look at them in data[i][1], you would change that 1 to match the column where you have the email list.

Lastly, to make the code run every time an N is added, you can add an onEdit trigger, you can do this by following the instructions in the following link:

https://developers.google.com/apps-script/guides/triggers/installable

Automate Email Based On Specific Text Entry In Cell

1 Answers1