Google Apps Script - Time Based Trigger

Question

I'm a complete novice, trying to set up a timed trigger. I have a spreadsheet with a list of emails, created a script that would send an email to each of them. If I run the script manually, it works just fine. I want a trigger that will automatically send the emails every 3 months.

function sendEmails() {
  var ss = SpreadsheetApp.openById("1234ID-here"); SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange(); // Fetch values for each row in the Range.  var data = dataRange.getValues();
  for (var i = 1; i < data.length; i++) {
    (function (val) {
      var rowData = data[i];
      var emailAddress = rowData[1];
      var recipient = rowData[0];
      var phone = rowData[2];
      var skill = rowData[3];
      var address = rowData[4];
      var moreinfo = rowData[5];
      var message = 'Dear ' + recipient + ',\n\n' + "You signed up for the following skillshare:" + skill + "." +
        "If you would like to be removed from this list, or if the following contact info has changed, please reply to this email. Otherwise we will assume no change." +
        '\n\n' + address + '\n\n' + phone + '\n\n' + moreinfo + '\n\n' + 'Thanks!';
      var subject = 'Skill sharing list';
      MailApp.sendEmail(emailAddress, subject, message);
    })(i);
  }
}

If I run that, it works fine. Here's the other script I created for the trigger:

function createTimeTriggers() {
  ScriptApp.newTrigger('sendEmails')
    .timeBased()
    .everyMinutes(1) //(to test it)
    .create();
}

Running this did nothing for me, looks like it had a 90% error rate with 119 executions as soon as I ran it. Help please?

*Edit - looks like it logged the error "Script function not found: sendEmails"

Answer 1

Like I said, novice (as in, I just started learning this last night). I figured it out! I had created the scripts as two separate projects, but just realized that I can add a script to my sendEmails project....and now that both scripts are contained in one project it works beautifully! Haha thanks for the help and patience!

Answer 2

Your code to create a time based trigger looks correct. If it worked, you should be able to see your trigger(s) at: https://script.google.com/home/triggers

I'm guessing that the problem may be linked to your use of SetActiveSheet/getActiveSheet, which seems unneeded and might be leading to unintended issues or a crash when run from the context of a trigger.

Sheets do not need to be active for you to access them from your code, "active" in this context is relevant to the user interface.

Instead of that code, try accessing both the sheet and spreadsheet explicitly:

var sheet = SpreadsheetApp.openById("1234ID-here").getSheetByName("Sheet1");
var dataRange = sheet.getDataRange(); // Fetch values for each row in the Range. 
...

Answer 3

Why it runs differently with a timebased trigger

function sendEmails() {
  var ss = SpreadsheetApp.openById("1234ID-here");  
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = SpreadsheetApp.getActiveSheet();

When you run above function while a user interface is open it runs okay from whatever spreadsheet is open but when this is run from a timebased trigger it always runs on the server with no user interface open and it selects the active sheet as ss.getSheets()[0] which is the left most sheet in the spreadsheet.

So @Cameron Roberts solution will work just fine I just wanted let you know why it wasn't working for you.