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 Karl_S · Accepted Answer · 2016-11-30T22:21:50

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.

Can you Sendnotification in Google Script for multiple sheets?

1 Answers