Google Spreadsheet time-driven event doesn't fire

Question

Hello you fine volks on Stack Overflow.

I've written a little script that pulls a quote from a spreadsheet and sends it via email to a user. The idea is, that the script runs once a day, pulling the next quote in the spreadsheet and sends it to the email adress.

The script looks like this:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1000;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 2, numRows, 3);
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    if(row[2]=="yes") { 
      continue;
    }
    var emailAddress = "[email protected]";     // email to send to 
    var message = '"'+row[0]+'"'+"\n\n"+"- "+row[1];       // Second column
    startRow = parseInt(startRow)+parseInt(i);
    var subject = "";
    MailApp.sendEmail(emailAddress, subject, message);  
    sheet.getRange(startRow,4).setValue("yes");
    SpreadsheetApp.flush();
    break;
  }
}

The script works fine when I run it manually.

Now I added a script trigger that looks like this:

sendEmails | Time-Driven | Day Timer | 7pm to 8pm

Unfortunately, the function is not executed and the quote is not being sent.

I also have a bit of a problem understanding the Time-Driven trigger. What does "7pm to 8pm" mean? Once between those two hours at a random time?

The script only makes sense if I don't have to be logged into Google Docs. Maybe I'm totally misunderstanding something here... Do scripts only work if I'm logged into Google Docs? (But even if I am, as of now, the script doesn't execute with the Time-Driven trigger).

I very much appreciate your thoughts on this. Moritz

mzimmerman mzimmerman · Accepted Answer · 2012-05-11T17:45:16

When you run a script from a Timer/Event, there is no "ActiveSpreadsheet". Instead of using .getActiveSpreadsheet() use .openById()

You don't need to be in Google Docs (or even authenticated at all) for your script triggers to run successfully.

Also, it would help (especially when debugging) to use an immediate notification for script failure. Where you setup the timer, click the notification link to set that up.

Google Spreadsheet time-driven event doesn't fire

2 Answers