Automating Email Dispatch with Google Apps Script

Question

I am seeking assistance with a Google Apps Script that I’m trying to set up to automate email sending from a Google Sheet. My goal is to have the script send emails automatically whenever the Status column in my sheet is updated to Send.

Here’s what I’ve done so far:

I’ve created a Google Sheet with columns for First Name, Last Name, Email, and Status.

I’ve written a script that is supposed to send emails when the Status column is updated to ‘Send’. However, I’m encountering issues with the script not triggering as expected. I’m not a developer, so I’m finding it challenging to troubleshoot the problem on my own.

Could someone please review my script and provide guidance on what might be going wrong? Any help or pointers would be greatly appreciated.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var firstName = row[0];
    var lastName = row[1];
    var emailAddress = row[2];
    var emailSent = row[3];
    if (emailSent == 'Gửi') { // Chỉ gửi email khi cột 'Status' là 'Gửi'
      var subject = 'Chủ đề Email của Bạn';
      var message = createEmailTemplate(firstName, lastName);
      MailApp.sendEmail(emailAddress, subject, "", { htmlBody: message });
      sheet.getRange(i + 1, 4).setValue('Đã gửi');
      SpreadsheetApp.flush();
    }
  }
}

function createEmailTemplate(firstName, lastName) {
  var htmlTemplate = HtmlService.createTemplateFromFile('EmailTemplate');
  htmlTemplate.firstName = firstName;
  htmlTemplate.lastName = lastName;
  return htmlTemplate.evaluate().getContent();
}

Answer 1

SUGGESTED SOLUTION

Note: It seems like onEdit(e) won't work for this because it requires authorization, and as per Restrictions:

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

Instead, use Manage triggers manually to run the function. Click on Triggers > + Add Trigger on the left of the Google Apps Script Editor.

SAMPLE TRIGGER IMAGE

SPREADSHEET

GMAIL

Answer 2

You need to name the function onEdit in order for it to be enter code hereautomatically triggered whenever a value is edited in the spreadsheet.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  // If the value is edited is in the status column (4th column) in the the sheet which has your data, only thencontinue with further operations.
  // Change Sheet1 to whatever is the name of your sheet.
  if (sheet.getName() !== 'Sheet1' && column == 4) {
    var dataRange = sheet.getDataRange();
    var data = dataRange.getValues();
    for (var i = 1; i < data.length; i++) {
      var row = data[i];
      var firstName = row[0];
      var lastName = row[1];
      var emailAddress = row[2];
      var emailSent = row[3];
      if (emailSent == 'Gửi') { // Chỉ gửi email khi cột 'Status' là 'Gửi'
        var subject = 'Chủ đề Email của Bạn';
        var message = createEmailTemplate(firstName, lastName);
        MailApp.sendEmail(emailAddress, subject, "", { htmlBody: message });
        sheet.getRange(i + 1, 4).setValue('Đã gửi');
        SpreadsheetApp.flush();
      }
    }
  }
}

function createEmailTemplate(firstName, lastName) {
  var htmlTemplate = HtmlService.createTemplateFromFile('EmailTemplate');
  htmlTemplate.firstName = firstName;
  htmlTemplate.lastName = lastName;
  return htmlTemplate.evaluate().getContent();
}

This should work. Just in case you are still stuck, let me know the exact issue/error from logs.