CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Terry Holt
Terry Holt

Reputation: 15

How do I edit loop in code to send one email with all data?

My code checks for lines on Google Sheet with today's date in a certain column. Certain cells on those lines are then emailed. My goal was to receive all lines that meet that criteria on one email daily.

As is, if I have 3 lines that meet the criteria, I receive 3 emails. The first email has one record, the second email has 2 records, and the 3rd email has all 3 records.

I'm sure it's an easy fix, but I'm not sure how to edit the loop.

  var datasheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1"); 
  var numRows = datasheet.getLastRow()-1
  var data = datasheet.getRange(2, 1, numRows, datasheet.getLastColumn()).getValues();
  var today = new Date().toLocaleDateString()
  var result = "";
  for (var i = 0; i < data.length; i++) { 
    var row = data[i];
    var enddate = new Date(row[14]).toLocaleDateString();
    if(enddate == today) { 
      result = result + row[8] + "   Dates: " + row [24] + " - "+ row[25] + '\n'
    }    
    if (result != "") {
      var body = "List of records ending today: " + '\n\n' 
      + result + ' \n' 
      GmailApp.sendEmail("[email protected]","Daily ending today email", body, {
           from: '[email protected]',}); 
    }
  } 
}

Upvotes: 0

Views: 47

Answers (1)

PySeeker
PySeeker

Reputation: 874

Simply put the email sending statement outside of the for loop:

      var datasheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1"); 
      var numRows = datasheet.getLastRow()-1
      var data = datasheet.getRange(2, 1, numRows, datasheet.getLastColumn()).getValues();
      var today = new Date().toLocaleDateString()
      var result = "";
      for (var i = 0; i < data.length; i++) { 
        var row = data[i];
        var enddate = new Date(row[14]).toLocaleDateString();
        if(enddate == today) { 
          result = result + row[8] + "   Dates: " + row [24] + " - "+ row[25] + '\n'
        }    
      } 
      if (result != "") {
          var body = "List of records ending today: " + '\n\n' 
          + result + ' \n' 
          GmailApp.sendEmail("[email protected]","Daily ending today email", body, {
               from: '[email protected]',}); 
        }
    }

Upvotes: 1

Related Questions