0
votes

really feel like I'm missing something with the Spreadsheet object scripts.

I'm trying to automatically email collaborators onEdit. I successfully emailed when explicitly runnign the script in test, but the onEdit event never seems to get fired (not seeing log messages even). Script seems pretty straightforward.

function onEdit(e) {
  var sheet = e.source;
  var viewers = sheet.getViewers();
  var ct = viewers.length;
  var recipients = [];
  for(var i=0;i<ct;i++){
    recipients.push(viewers[i].getEmail());
  };
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated.  Visit ' + sheet.getUrl() + ' to view the changes ' + e.range;

  Logger.log('Running onedit');

  MailApp.sendEmail(recipients, subject, body);
};
1
one correction; the version that worked when hitting Run from the script window had SpreadsheetApp.getActiveSpreadsheet() instead of e.source, but I changed it to e.source when it didn't work, on the assumption that that's hwy the event wasn't working.Paul

1 Answers

6
votes

the simple onEdit function has a very limited set of possible actions since it runs without the authorization of the potential user. You have to create another function and set a specific trigger on this function.(installable trigger)

See this post as an example. It shows examples of simple onEdit and installable edit (for email send) This is explained in the documentation here.

EDIT : here is your code working :

function sendAlert() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation()
  var viewers = ss.getViewers();
  var ct = viewers.length;
  var recipients = [];
  for(var i=0;i<ct;i++){
    recipients.push(viewers[i].getEmail());
  };
  var subject = 'Update to '+sheet.getName();
  var body = sheet.getName() + ' has been updated.  Visit ' + ss.getUrl() + ' to view the changes on cell ' + cell;
  MailApp.sendEmail(recipients, subject, body);
};