CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docsgoogle-apps
Jens
Jens

Reputation: 37

Google Apps Script: email trigger only for new entries (without using Google Forms)

I am a beginner in coding and I tried many things (solutions), but no script is running. I am using an existing sendEmail script (see down below) to send emails to new spreadsheet entries.

https://docs.google.com/spreadsheet

I am not using Google Forms. I am copying the needed Information out of another spreadsheet table. With an onEdit trigger the script will send an email to the new entries.

The problem I am having is that the script sends an email to every row of the spreadsheet even though that information was already sent.

I tried some workarounds, although which are named in the forum, but nothing helped in my case. I don`t want to send an email only to the last single entry. I want to send Emails to a different amount of new entries and this should be triggert by an onEdit Event, if it is possible. I hope, someone can help me soon…

  var EMAIL_SENT = "EMAIL_SENT";

  function sendEmails(onlyLast) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var startRow = 2;
  var lastRow = datasheet.getlastRow()-1;
  if (onlyLast)
  startRow = endRow;
  var dataRange = dataSheet.getRange(startRow, 1, lastRow, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A1").getValue();

  // Create one JavaScript object per row of data.
  var objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];


    var file = DriveApp.getFileById('');
    // Generate a personalized email.
    // Given a template string, replace markers (for instance ${"First Name"}) with
    // the corresponding value in a row object (for instance rowData.firstName).
    var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = "Tutorial: Simple Mail Merge";
    var emailSent;   
    if (emailSent != EMAIL_SENT) {  
      var subject = "Tutorial: Simple Mail Merge";
      MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText, {attachments:[file.getAs(MimeType.PDF)]});
      dataSheet.getRange(startRow + i, 5).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Upvotes: 1

Views: 6050

Answers (4)

Aaron Shier
Aaron Shier

Reputation: 62

This worked amazing for me, its a modification of what Bjorn did up there. Props for the script Bjorn thank you, hope this thread helps someone as much as it helped me tonight. Thanks guys!

    function ssForward() {

    // DEFINE YOUR MAIN SPREADSHEET
    var ss = SpreadsheetApp.openById("SPREADSHEET_ID_ITS_IN_THE_SS_URL");

    // DEFINE THE DATA SHEET THAT RECIEVES THE SUBMISSIONS HERE
    var dataSheet = ss.getSheetByName("data");

    // DEFINE THE TEMPLATE SHEET THAT HAS THE EMAIL TEMPLATE INSIDE OF IT
    var templetSheet = ss.getSheetByName("template");

    //DEFINE THE CELLS THAT CONTAIN THE TEMPLATE TEXT FOR THE SUBJECT
    var templateSubject = templetSheet.getRange(1, 2).getValue();

    //DEFINE THE CELLS THAT CONTAIN THE TEMPLATE TEXT FOR THE SUBJECT
    var templateBody = templetSheet.getRange(2, 2).getValue();

    //NOW RUN A FOR LOOP THAT GOES THROUGH EACH ENTRY IN EACH ROW (var i = the row in your spreadsheet )      
    for (var i = 2; i <= dataSheet.getLastRow(); i++) {

        // ADD CURRENTLY VIEWED ENTRY TO VAR (var phone = the sheet in row i, column 3)
        // all 3 of these var work the same way    
        var phone = dataSheet.getRange(i, 3).getValue();
        var location = dataSheet.getRange(i, 4).getValue();
        var status = dataSheet.getRange(i, 10).getValue();

        // NOW USE THE templetSheet VAR DEFINED ABOVE AND REPLACE THE PLACEHOLDER TEXT WITH THE APPROPRIATE VARIABLE

        //REPLACE THE SUBJECT TEMPLATE TEXT <<phone>> WITH THE CONTENTS OF THE VAR 'phone' defined above.
        var subject = templateSubject.replace("<<phone>>", phone);

        //REPLACE THE 'SUBJECT TEMPLATE' TEXT: <<location>> - WITH THE CONTENTS OF THE VAR 'location' defined above.
        var body = templateBody.replace("<<location>>", location);

        //REPLACE THE 'SUBJECT TEMPLATE' TEXT: <<phone>> - WITH THE CONTENTS OF THE VAR 'phone' defined above.
        body = body.replace("<<phone>>", phone);

        // IF OUR STATUS CELL IS EMPTY
        if (status == "") {

            // SEND THE EMAIL FROM '[email protected]', USE THE 'subject' VAR FOR THE SUBECT, AND THE VAR 'templateBody' FOR THE MESSAGES BODY
            MailApp.sendEmail('[email protected]', subject, body);

            // MARK THE ROW AS COMPLETED
            dataSheet.getRange(i, 10).setValue("Email Notification Sent");
        }
    }
}

Upvotes: 1

Bjorn Behrendt
Bjorn Behrendt

Reputation: 1264

Here is an example spreadsheet: https://docs.google.com/spreadsheets/d/1RdwZcK6tsOr1m44ZyawWsWXGa26zhd8J1ZZs_GsRRtY/edit?usp=sharing

Put this on a timed trigger:

function sendNew(){
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
  var templetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("template");
  var templateSubject = templetSheet.getRange(1, 2).getValue();
  var templateBody = templetSheet.getRange(2, 2).getValue();

  for (var i =2; i<= dataSheet.getLastRow(); i++){
    var name = dataSheet.getRange(i, 1).getValue();
    var color = dataSheet.getRange(i, 2).getValue();
    var status = dataSheet.getRange(i, 3).getValue();

    var subject = templateSubject.replace("<<Name>>", name);
    var body = templateBody.replace("<<Name>>", name);
    body = body.replace("<<Color>>", color);

    if (status == ""){
      MailApp.sendEmail('[email protected]', subject, body);
      dataSheet.getRange(i,3).setValue("Email Sent");
    } 
  }
}

Upvotes: 1

Serge insas
Serge insas

Reputation: 46812

in your code you define var emailSent; but don't assign it any value...

so the following comparison is always returning true. if (emailSent != EMAIL_SENT) {

try simply to replace var emailSent; with this :

var emailSent = dataSheet.getRange(startRow + i, 5).getValue();

and it should work as expected.

And, finally, as mentioned in the other answer, run this function on a timer trigger instead of an onEdit to avoid partial email sending (if a user changed a cell content or mistyped something...)

edit : final note, please delete this first row var EMAIL_SENT = "EMAIL_SENT", I know it comes from a Google example but it's useless here and (IMHO) very confusing because finally you don't know if EMAIL_SENT is a variable or a value..., change you condition to

if (emailSent != 'EMAIL_SENT') { // which compares a value to a string, clearly ;-)

Upvotes: 0

Bjorn Behrendt
Bjorn Behrendt

Reputation: 1264

First, I would suggest not using onEdit but rather a time driven trigger that checks for changes. That way your script won't be constantly trying to send emails, even if you miss type something.

Second, you will need some way to indicate weather the data has been processed or not. You could have the script Bold all processed information, then email anything that is not bold.

Here is a link to a similar script I wrote for someone that would email a list of people if the script sees the word "canceled".

https://docs.google.com/a/askbj.net/spreadsheets/d/1Y82TwZWC8Nh2HQhpKWcdxgnrU0ANo8d2JLenK3gWyY4/edit?usp=sharing

-bj

Upvotes: 0

Related Questions