Send an automated email from google spreadsheet?

Question

This code can only work when the run button is clicked.

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 2, numRows, sheet.getLastColumn());
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

I expect is how to be able to send an email automatically whenever new messages without having to click the run button, if it can be done?

Take a look at the [Simple Triggers](https://developers.google.com/apps-script/guides/triggers/) or the [Time-driven triggers](https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers) you can manage them [manually](https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually) or [programmatically](https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_programmatically) — ocordova, Aug 19 '16 at 15:03
[This](http://stackoverflow.com/questions/34656837/set-a-trigger-to-run-function-the-last-hour-of-each-month/34684370#34684370) will help you too — iJay, Aug 19 '16 at 15:23

score 0 · Answer 1 · answered Aug 21 '16 at 22:00

@cordova is correct, you can send an email automatically whenevernew messages without having to click the run button.

You can trigger it programmatically with the Script service. Call ScriptApp.newTrigger(functionName) which returns a TriggerBuilder

The following example shows how to create two time-driven triggers—one that fires every 6 hours and one that fires every Monday at 9 a.m. (in the time zone that your script is set to).

function createTimeDrivenTriggers() {
  // Trigger every 6 hours.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyHours(6)
      .create();

  // Trigger every Monday at 09:00.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(9)
      .create();
}

Send an automated email from google spreadsheet?

1 Answers1