getting error message when using sendemail function

Question

I've written a script to send auto-emails from a google spreadsheet using a weekly time-driven trigger. I've tested it and it seems to only work when I hit "run". The time-driven trigger returns an error message of "Invalid email: #VALUE! (line 14, file "Code")". Where have I gone wrong? Any help would be greatly appreciated!

Here is what I have so far:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 20;   // Number of rows to process
  // Fetch the range of cells A2:c20
  var dataRange = sheet.getRange(startRow, 1, numRows, 20)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = "Project Owner: " + row[3] + '\n' + "Status: " + row[2]; //3rd column
    var subject = "Project Update: " + row[1];                             //2nd column
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Mogsdad Mogsdad · Accepted Answer · 2013-09-07T15:37:04

When run as a time-based trigger function, there is no active spreadsheet for SpreadsheetApp.getActiveSheet(). Use openById().getSheetByName() instead.

Iterating an array using for (i in data) might give you issues. Use for (var i=0; i<data.length; i++) instead.

getting error message when using sendemail function

1 Answers