Google Spreadsheet Script to Set Trigger to Send Emails

Question:

( by Jerrilyn Gray )

Hello,

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];       // Third column
    var subject = "Project Update: " + row[1];       // 2nd column
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Solution:

Have a look at the screenshot of Sheet 'test email':

Try the following script code:

/////////////////////

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test email");
  var dataRange = sheet.getDataRange();
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i=1;i<data.length;i++) {
    // For loop starts from i=1 because we have to start from second row
    var emailAddress = data[i][0];  // 1st column
    var message = "Project Owner: " + data[i][3] + '\n' + "Status: " + data[i][2]; // 3rd column
    var subject = "Project Update: " + data[i][1]; // 2nd column
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

/////////////////////

the above code will take dataRange upto the last filled row and column, so if you have last filled data in row 109 then the for loop will run up to 109 times...

Instead of (first line):

var sheet = SpreadsheetApp.getActiveSheet();

Try this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your Sheet Name");

This is because at the time of trigger, when your spreadsheet is closed there may not be any Active Sheet, So its better to call getSheetByName...

And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan.

iGoogleDrive