How Can I Run Through Rows On Google Spreadsheet?

Question

I'm looking for a way to iterate through a spreadsheet of responses using Google Script (basically same thing as JavaScript). It worked okay, but the problem is this code keeps sending emails for data that has already been sent in an email. I know very little JavaScript, but I think it would make sense to delete the previous row and only iterate through one row. Here is the code so far:

function autoEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = 2;
var dataRange = sheet.getRange(startRow, 1, numRows, 7)
var data = dataRange.getValues();
for (i in data) {
var column = data[i];
var emailAddress = "OMITTED FOR PRIVACY";
var message = column[3] + ", who is a " + column[4] + " Scout, wants to have a " + column[6] + " on " + column[5] + ". You can contact him at " + column[1] + ". This is an automated email sent by OMITTED FOR PRIVACY.";
var subject = column[3] + " Wants To Rank Up!";
MailApp.sendEmail(emailAddress, subject, message);
}
}

Any suggestions?

score 0 · Answer 1 · edited May 23 '17 at 12:32

So you're saying process the current row, then delete it so you don't accidentally re-process it? Seems reasonable, but it would be nice to not delete your data, in case something goes wrong. Maybe you could write "done" in an empty column at the end of each row, and check for that before sending a new email?

Now some questions, so I can help more --

Why are you fetching two rows (numRows = 2) ? It would seem like you want to either fetch just one row, or N (the number of entries) rows.

Also, I've always heard that in Javascript, it's better to use a normal for loop...

for (var colNum = 0; colNum < data.length; colNum++) {
    var column = data[colNum];

...instead of a for-in loop...

for (i in data) {
    var column = data[i];

on array-like objects because "Inherited properties are also enumerated" (more info here).

EDIT: Speaking of which, when you do this

for (i in data) {
    var column = data[i];

try logging i in the loop Logger.log(i); because I think that i might be a piece of data, not an index in the two dimensional array.

END EDIT

Disclaimer: I know Stack Overflow doesn't recommend asking for clarification in an answer, but I don't have enough reputation yet to add a "comment". :-)

How Can I Run Through Rows On Google Spreadsheet?

1 Answers1