Can you Sendnotification in Google Script for multiple sheets?

Question

I'd like to send a notification to someone when a particular cell (ie document) is updated. Specifically, when a cell in one row is updated, it would send a notification to the owner listed in the same row. For example B47 gets updated, it send an email to B3.

I've gotten the script to work when it's pointed to one sheet. But when I try to ask the script to run over multiple sheets/tabs, I get error 'Script function not found: sendNotification' Is it possible to have this script work for multiple sheets/tabs in the same google doc? Updates are pulling from a different source so I'd like to keep using getSheetbyName instead of getActiveSheet.

Any help would be much appreciated. Here's my code where it stands:

function shellFunction() {
  var sheets = ['Arabic', 'Portuguese'];
  for (var s in sheets) {
    toTrigger(sheets[s]);
  }

  function toTrigger(sheetName) {
    function sendNotification() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheets[s]);
      var cell = ss.getActiveCell().getA1Notation();
      var row = sheet.getActiveRange().getRow();
      var cellvalue = ss.getActiveCell().getValue().toString();
      var recipients = sheet.getRange('J' + sheet.getActiveCell().getRowIndex()).getValue();
      var message = '';

      if (cell.indexOf('B') != -1) {
        message = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue()
      }
      var subject = 'The ' + sheet.getRange('F' + sheet.getActiveCell().getRowIndex()).getValue() + ' ' + sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' needs your Translation';
      var body = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' has been updated. Can you please update ' + sheet.getRange('G' + sheet.getActiveCell().getRowIndex()).getValue() + '? Please remember to update the date column in the Resource Document when the translation is complete:' + ss.getUrl();
      MailApp.sendEmail(recipients, subject, body);
    }
  }
}

Karl_S · Answer 1 · 2016-12-01T05:16:29.963

Should this line

var sheet = ss.getSheetByName(sheets[s]);

be changed to

var sheet = ss.getSheetByName(sheetName);

since that is the name of the passed sheet?

EDIT

There were a couple other items which may have caused issue. I made edits for this result:

function shellFunction() {
  var sheets = ['Arabic', 'Portuguese'];
  for (var s in sheets) {
    toTrigger(sheets[s]);
  }
}

  function toTrigger(sheetName) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheetName);
      var cell = ss.getActiveCell().getA1Notation();
      var row = sheet.getActiveRange().getRow();
      var cellvalue = ss.getActiveCell().getValue().toString();
      var recipients = sheet.getRange('J' + sheet.getActiveCell().getRowIndex()).getValue();
      var message = '';

      if (cell.indexOf('B') != -1) {
        message = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue()
      }
      var subject = 'The ' + sheet.getRange('F' + sheet.getActiveCell().getRowIndex()).getValue() + ' ' + sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' needs your Translation';
      var body = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' has been updated. Can you please update ' + sheet.getRange('G' + sheet.getActiveCell().getRowIndex()).getValue() + '? Please remember to update the date column in the Resource Document when the translation is complete:' + ss.getUrl();
      MailApp.sendEmail(recipients, subject, body);
  }

My first change was to not nest the functions. To do this I added a closing curly bracket before the toTrigger function and removed one of the last in your code. After that there was another nested function which actually made up all of the toTrigger function. It was called sendNotifocation which matched your error. I removed that by removing the line after the toTrigger function declaration and another curly bracket at the end of your code.

Last, I made the above edit. In my test it seemed to work fine, except that I commented out the call to actually send an email and only debugged to the line just prior to that point.

I still get a 'Script function not found: sendNotification' with that update. Does sendNotification only work when the script points to one sheet? — Wildsea, Nov 30 '16 at 22:48
Thanks @Karl_S looks like that about did it. One remaining problem Im running into - when I add new sheets, I receive a 'Failed to send email: no recipient' error. is there a way to get the who script to run if doesnt have an one sheet is missing an email address? — Wildsea, Dec 01 '16 at 20:48
You should probably do a test once you set the recipients variable to confirm you have a valid email address. If not, exit the function with a return; See [this post](http://stackoverflow.com/questions/46155/validate-email-address-in-javascript) for ways to validate an email address. Once you try some things, if you still need help, open a new post. — Karl_S, Dec 01 '16 at 21:05

Can you Sendnotification in Google Script for multiple sheets?

1 Answers1