Need to sendNotification for multiple columns and sheets

Question

In an attempt to automate some of my work, I am beginning to learn basics of google scripts.

I have a spreadsheet in which I want to send an email notification when data is input into one column or another. There are also two tabs within this spreadsheet in which I would like this to occur.

The current result from the script is an email on the second 'sendnotification' function.

Question: How do I get the script to consider both functions?

I know this code can be condensed by likely using an IF fucntion in a better way but I am at a loss.

For some context: This is used in a manufacturing operation. The production is done by an offsite company and when they enter quantity into column 10 on either sheet, I want it to send a group of people an email that I work with. Similarly, when the product quality testing is done I want to be able to input into Column 12 and it send the offsite company an email.

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
//Get Active cell
  var mycell = ss.getActiveSelection();
  var cellcol = mycell.getColumn();
  var cellrow = mycell.getRow();
//Define Notification Details
  var recipients = "ryan.helms@company.com";
  var subject = "Disc production was entered on the "+ss.getName();
  var body = ss.getName() + " has been updated with an amount produced.  Visit " + ss.getUrl() + " to view the quantities entered.";
//Check to see if column is A or B to trigger
  if (cellcol == 10)
  {
//Send the Email
  MailApp.sendEmail(recipients, subject, body);
  }
//End sendNotification
}
function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
//Get Active cell
  var mycell = ss.getActiveSelection();
  var cellcol = mycell.getColumn();
  var cellrow = mycell.getRow();
//Define Notification Details
  var recipients = "ryan.helms@company.com";
  var subject = "A lot of disc has been RELEASED by XYZ Company";
  var body = ss.getName() + " has been updated with a lot of disc that were released by XYZ Company.  Visit " + ss.getUrl() + " to view this updated information.";
//Check to see if column is A or B to trigger
  if (cellcol == 12)
  {
//Send the Email
  MailApp.sendEmail(recipients, subject, body);
  }
//End sendNotification
}

Just to clarify, you want the email to be sent automatically when you type into column 10 or column 12? Like with an onedit trigger? — Joshua Dawson
Correct, an automatic email when data is input into either column. — Ryan Helms

Joshua Dawson Joshua Dawson · Accepted Answer · 2017-02-23T07:32:03

You'll need to set up an onEdit installable trigger (if you haven't already) and assign it to the following function:

function onEditEmailSender(e) {
  var sheetName = e.range.getSheet().getName();

  if (sheetName === "tab1" || sheetName === "tab2") {
    var col = e.range.getColumn();

    if (col === 10)
      //send col 10 email
    else if (col === 12)
      //send col 12 email
  }
}

The onEdit trigger passes a parameter with all sorts of information, the most useful in your case being e.range. This Range object corresponds to the cell that was edited to trigger the onEdit event. You can use it to get the name of the sheet (what you call tab) and the column that was edited.

Using that information you can send the appropriate email. Good luck!

Need to sendNotification for multiple columns and sheets

1 Answers