Why does my Google Script code start at the wrong row?

Question

So, I've been working on a project and that involves a code that sends emails automatically. The code is supposed to write "Email Sent" on the far-right column, besides each balance.

So, here's my auto-email function and a link to the spreadsheet: https://docs.google.com/spreadsheets/d/14ukVvpMh0dJr5_HLUYxK1n4m6HXd2tvAj-bMrixAQFg/edit?usp=sharing

    function sendEmails() { 
  var EMAIL_SENT = "EMAIL SENT"
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Control_Mens")  
  var dataRange = sheet.getRange(2, 2, 61, 16) 
  var data = dataRange.getValues();
  for (i in data) { 
    var row = data[i]; 
    var emailAddress = row[0];
    var message = row[1]; 
    var emailSent = row[15]
    if (emailSent != EMAIL_SENT){
      var subject = "Subject"; 
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(2 + i, 17).setValue(EMAIL_SENT);
    }
  } 
}

While I think I may be counting something wrong (like one more row and vice-versa), I've ran it multiple times, it sends the email to everyone in the sheet, but it only writes "EMAIL SENT" beside specific people.

You are aware that sheet column `17`, to which your code writes `"EMAIL SENT"`, is read into the javascript array index numbered `16`, correct? What is in the sheet's column 16? — tehhowch, Mar 18 '19 at 02:44
Column 17 is correct, but the code starts writing "EMAIL SENT" on row 20 instead of row 2 — LFSS, Mar 18 '19 at 02:53
Then why are you checking for it in column 16, at array index 15? Also, you use a very inappropriate for loop scheme for arrays. [Why are you enumerating properties? You should iterate the indices explicitly, or with an array class method such as `Array#forEach`](https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea) — tehhowch, Mar 18 '19 at 03:01
thanks for all the insights, do you guys know any specific reason why people are downvoting me? — LFSS, Mar 18 '19 at 03:26
That's a whoops on my part. It is very uncommon to see ranges skipping column A, and I should have verified my assumption first. — tehhowch, Mar 18 '19 at 03:43

score 3 · Answer 1 · answered Mar 18 '19 at 07:03

Problem

I guess problem you said in title is i is string. See below script.

var data = ["data1", "data2", "data3"];
for (i in data){
Logger.log(typeof i);
}

All i are string. When i (1, 2, 3) are string, sheet.getRange(2 + i, 17) is

sheet.getRange("21", 17).getA1Notation(); This is Q21.
sheet.getRange("22", 17).getA1Notation(); This is Q22.
sheet.getRange("23", 17).getA1Notation(); This is Q23.

This is cause of problem. This problem doesn't write "EMAIL SENT" to row sent email.

Solution

Use i of number type.

Put i = Number(i); below for (i in data) {.
Replace for (i in data) with for (var i=0; i<data.length; i++). This is Cooper's answer.

Cooper · Answer 2 · 2019-03-18T03:07:04.633

I tested your code like this and it works perfectly:

I have a couple tweaks in it.

function sendEmails() { 
  var EMAIL_SENT="EMAIL SENT";
  var sh=SpreadsheetApp.getActive().getSheetByName("Sheet95");  
  var rg=sh.getRange(2, 2, 61, 16); 
  var data=rg.getValues();
  var s='';
  for (var i=0;i<data.length;i++) { 
    var row=data[i]; 
    var emailAddress=row[0];
    var message=row[1]; 
    var emailSent=row[15];
    if (emailSent!=EMAIL_SENT){
      var subject=row[2];//added this just to test the data position 
      //MailApp.sendEmail(emailAddress, subject, message);
       s+=Utilities.formatString('<br />EmailAddress:%s<br />Subject: %s<br />Message:%s<br /><hr width="100%">',emailAddress,subject,message);
      sh.getRange(2 + i, 17).setValue(EMAIL_SENT);
    }
  } 
  var userInterface=HtmlService.createHtmlOutput(s);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Emails')
}

I wonder if your using the variable i somewhere else in the project and since you didn't actually declare i it could be picking a global value from somewhere else in the project. Just guessing.

I'll run your code later when my email quota comes back! – LFSS Mar 18 '19 at 03:25 — LFSS, Mar 18 '19 at 03:25

Why does my Google Script code start at the wrong row?

2 Answers2

Problem

Solution