I'm trying to loop through an spreadsheet, and for each row send an email. Once the email is sent, i'd like to delete that row.

That however is not working.

For some reason, it starts sending emails like crazy, and at some point it reaches the limit and quits.

It actually only deletes one row.

See the code below:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var startRow = 2;  // First row of data to process
  var maxRows = sheet.getMaxRows();

  var range = sheet.getRange(startRow, 1, maxRows, 50)
  var values = range.getValues();

 for (var row in values) {
     Logger.log('ID=' + values[row][0]);
     var theID = values[row][0];
     var message = "";
     var sendto = "";
     var emailAddress = values[row][2];
     if (emailAddress=="Autre"){sendto="[email protected]"}
     if (emailAddress=="Autre1"){sendto="[email protected]"}
     if (emailAddress=="Autre2"){sendto="[email protected]"}
    message+="\n ID: " + values[row][1];
    message+="\n Project Number: " + values[row][2];

    var subject = "Project ID: " + values[row][1];

   if (sendto!=''){
     MailApp.sendEmail(sendto, subject, message);
     sheet.deleteRow(row+2)
    }
 }

Logic is, if there's a valid email, send the email, delete the row.

But once it executes, it only deletes one row, sends emails like crazy, and get the email max error.

Thoughts?

I'm trying to loop through an spreadsheet, and for each row send an email. Once the email is sent, i'd like to delete that row.

That however is not working.

For some reason, it starts sending emails like crazy, and at some point it reaches the limit and quits.

It actually only deletes one row.

See the code below:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var startRow = 2;  // First row of data to process
  var maxRows = sheet.getMaxRows();

  var range = sheet.getRange(startRow, 1, maxRows, 50)
  var values = range.getValues();

 for (var row in values) {
     Logger.log('ID=' + values[row][0]);
     var theID = values[row][0];
     var message = "";
     var sendto = "";
     var emailAddress = values[row][2];
     if (emailAddress=="Autre"){sendto="[email protected]"}
     if (emailAddress=="Autre1"){sendto="[email protected]"}
     if (emailAddress=="Autre2"){sendto="[email protected]"}
    message+="\n ID: " + values[row][1];
    message+="\n Project Number: " + values[row][2];

    var subject = "Project ID: " + values[row][1];

   if (sendto!=''){
     MailApp.sendEmail(sendto, subject, message);
     sheet.deleteRow(row+2)
    }
 }

Logic is, if there's a valid email, send the email, delete the row.

But once it executes, it only deletes one row, sends emails like crazy, and get the email max error.

Thoughts?

• javascript
• for-loop
• debugging
• google-apps-script
• google-sheets

• 1 Testing it in debug mode. Seems to be stuck on the line: MailApp.sendEmail(sendto, subject, message); Not sure if that's due to the fact that the daily limit is up? – R0b0tn1k Commented Feb 6, 2017 at 13:47
• 1 try getLastRow instead of getMaxRow. Your could be getting lots of blank data back and getting truthy conversion errors with (sendto!='') – Spencer Easton Commented Feb 6, 2017 at 13:52
• Thanks, will take 24 hours to test. If it works, ill let you know so you can write the answer and score the points. – R0b0tn1k Commented Feb 6, 2017 at 14:23
• use something like: var getQuota = MailApp.getRemainingDailyQuota(); to see how many emails you have left. – James D Commented Feb 6, 2017 at 15:42

1 Answer 1

You can look up the daily limits here.

Regarding your code, looking at the execution transcript it looks like the script isn't recognizing row as an integer but as a string, it attaches the 2 and only then converts it. So you're deleting row 2 (or 02), then row 12, then row 22 and so on.

This however is irrelevant because there's a problem in your logic. I'll try my best to explain it and hope that it is understandable.

If you delete a row after every iteration, then in the first iteration it would delete row 2 (because row = 0 and you add 2), in the second iteration it would delete row 3 (because row = 1 and you add 2), but since in your first iteration you already deleted a row the data that was in row 3 at the beginning is now actually in row 2.

One way to tackle this problem is by reversing the loop, start at the max and count down. This way when you delete a row it has no effect on the next one. I've rewritten your code a bit, so that it should work like intended.

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var startRow = 2;  // First row of data to process
  var lastRow = sheet.getLastRow(); // getLastRow() gives you the last row that has content, while getMaxRows gives you the maximum number of rows in your sheet
  var range = sheet.getRange(startRow, 1, lastRow-1, 50)  // -1 because you want the number of rows from your starting position and not the index of the last row
  var values = range.getValues();

 for (row = values.length-1; row >= 0; row--) { 
     var theID = values[row][0];
     var message = "";
     var sendto = "";
     var emailAddress = values[row][2];
     if (emailAddress=="Autre"){sendto="[email protected]"}
     if (emailAddress=="Autre1"){sendto="[email protected]"}
     if (emailAddress=="Autre2"){sendto="[email protected]"}
    message+="\n ID: " + values[row][1];
    message+="\n Project Number: " + values[row][2];

    var subject = "Project ID: " + values[row][1];

   if (sendto!=''){
     MailApp.sendEmail(sendto, subject, message);
     sheet.deleteRow(row+2)
    }
 }
}

I hope this is somewhat understandable, I'm not very good at explaining things.