Script to sent email from Google Sheet is sending multiple times

0
votes

I used the tutorial here: https://developers.google.com/apps-script/articles/sending_emails

The problem I am having is that it sends the notifications many times (17, 25, 30 times.) I tried the "Improved" version as well, where it should only send an email if the column "Email_sent" isn't populated. The script populates the "email_sent" but that didn't prevent it from sending a bunch of times, even though with the "if" that shouldn't be possible.

I don't know what I could possibly be doing wrong that causes it to send so many times.

I had to modify the code slightly because when I use var row = data[i] and var emailAddress = row[0] it was not able to retrieve the email addresses.

If I take out the [i] and just use var row = data then it works. I don't know what difference that is causing or if it is related to the problem I am having.

I also changed numrow to sheet.getlastrow() instead of a static number because the number of rows in my data could change.

Here is my code, written as in the tutorial with the i

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var numRows = sheet.getLastRow()

var dataRange = sheet.getRange(3, 2, numRows);

var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0];
var message = 'You have unreleased time in LETS. Please review and release.';
var subject = 'Please review LETS time';
MailApp.sendEmail(emailAddress, subject, message);
} }

This way fails to send, says it cannot find a recipient, and the debugger shows the emailAddress line returning blank. If I remove the [i] it runs, but every time I run it I get a ton of emails, and it should only send one per email address.

1
google-apps-scriptgoogle-sheets
I think that providing your current script for replicating your issue will help users think of the solution. Of course, please remove your personal information. – Tanaike
The [i] part of var row = data[i]; refers to the position of an element in the data. The variable i is a counter in the second example, which starts at zero. The data is in an array. The array has elements in it. Each element is at a certain position. The first data element is at position zero. The data is in a 2D array, which is an array with inner arrays. The inner arrays are each row in the spreadsheet. You could try using the debugger and step through the code to see what it is doing line by line. You can run each line individually. See Help - Documentation - troubleshooting – Alan Wells
I have run the debugger. When I use data[i] It fails to send because it cannot find a recipient, and the row that has the emailAddress variable in the debugging section turns up blank. I was going to attach an image of the debug section, but I guess I can't? – Kristyn Davis

1 Answers

0
votes

The syntax for getting ranges is getRange(row, column, numRows)

It expects the number of rows, rather than the last row as the third parameter.

Assuming that your last row is 6,

var dataRange = sheet.getRange(3, 2, numRows);

would mean "get 6 rows starting with number 3", or "get rows 3 to 8". But you won't have any data in row 7 and 8 - this throws you the error.

Just change your code to:

var startRow=3;
var dataRange = sheet.getRange(startRow, 2, numRows-startRow+1);