Sunday, June 2, 2024
 Popular · Latest · Hot · Upcoming
115
rated 0 times [  118] [ 3]  / answers: 1 / hits: 19096  / 7 Years ago, mon, february 6, 2017, 12:00:00

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){[email protected]}
if (emailAddress==Autre1){[email protected]}
if (emailAddress==Autre2){[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?


More From » for-loop

 Answers
61

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){[email protected]}
if (emailAddress==Autre1){[email protected]}
if (emailAddress==Autre2){[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.


[#59056] Friday, February 3, 2017, 7 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
shylaelisan

Total Points: 37
Total Questions: 94
Total Answers: 110

Location: Angola
Member since Tue, May 5, 2020
4 Years ago
;