How can I trigger email script by cell value change?

2
votes

I am using Google Forms to run a customer database. When the costumer called me, I am editing the same Google Form entry. So I would like to find out how this change or entry of a particular cell can trigger the mail script which I will post here. It should only trigger once, in the moment of change.

Edit:

I have add the "if" now, and set the trigger to Form Submit. Now, if I am changing in the Google Form the value to "test" and sent the form a email is sent to me. But how can I specify this for each entry if I have several email address in the form. Also that it does not send double?

function sendArticleCountEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Test"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("A2:F4");
  var data = dataRange.getValues();
  var currentValue = sheet.getRange("J2").getValue();
  if (currentValue == ("test")) 
  for (i in data) {
    var rowData = data[i];
    var emailAddress = rowData[1];
    var recipient = rowData[0];
    var message1 = rowData[2];
    var message2 = rowData[3];
    var parameter2 = rowData[4];
    var message3 = rowData[5];
    var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;
    var subject = 'Your article count for this month';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}
1
javascriptgoogle-sheetsgoogle-sheets-formula
When you say I have several email address in the form what do you mean? Where are the email addresses stored? Also, when you say not send double, under what circumstances would the script send double? - I hope this is helpful to you
Have a look to this sheet: docs.google.com/spreadsheets/d/… - Seb
Maybe it would be enough when the if works on change and trigger the send, but how can I specific it on for each row? - Seb

1 Answers

2
votes

Answer:

It is not possible to send an email on the change of cell in a Google Sheet. You can however send emails on form submission.

Simple Triggers:

As per the Google Apps Script documentation on Simple Triggers:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.

However, triggers also have certain 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.

Submitting a Form:

You can still use the GmailApp library in Apps Script to send emails, and specify multiple to send to. If, as in your example sheet, the email addresses are all in column B:

var emails = []
var addresses = SpreadsheetApp.getActiveSpreadsheet().getRange('B2:B').getValues();
var list = [];
for (var i = 0; i < addresses.length; i++) {
  if (addresses[i] == "") {
    break;
  }
  if (list.indexOf(addresses[i][0]) > -1) {
  }
  else {
    list.push(addresses[i][0]);
  }
}

and then edit:

var emailAddress = rowData[1];

to be:

var emailAddress = list[0];

This will send the email to all the email addresses listed, though skip duplicates that are in the Sheet.

References: