Reputation: 71

OnChange trigger when change in certain sheet, not in any sheet of workbook

I have a tab called "Notif Inv" in a sheets document that has also several other tabs. This is a tab with only 2 rows, the second of which gets updated with the latest filtered row of a range from another tab from the sheet.

In the other hand, I have this Function that sends emails grabbing data from the 2nd row of my "Notif Inv" tab.

    function Email_NewInvs() {
  // Get the sheet where the data is, in sheet 'Notif Inv'
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Notif Inv");
  var startRow = 2; // First row of data to process since there is a header row
  var numRows = sheet.getRange(1,5).getValue(); // Number of rows to process is set by a formula which counts rows
  // Fetch the range of cells A2:B6 where the emails and messages are
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range to input into the mailing system
  var data = dataRange.getValues();
  // This processes the emails you want to send
  for (i in data) { var row = data[i];
      var emailAddress = row[0]; // First column is the email address
      var message = row[1]; // Second column is the message
      var subject = "Nueva Factura"; // This is the subject of the email
      // This parses the data for the email to send
      MailApp.sendEmail(emailAddress, subject, message);
   }
}

My goal is to trigger this function when the 2nd row in "Notif Inv" tab changes automatically.

I have tried to use an onChange trigger for this script but it actually triggers it when anything changes in all the sheets document, meaning also changes made in tabs that have nothing to do with this one.

I have also tried to change the function name to an onChange(e) but it doesn't do anything when in the tab "Notif Inv" something changes.

Is there any workaround?

Thanks so much

Upvotes: 2

Answers (2)

Víctor Enrich

Reputation: 71

Thanks for your answer. I've used your code but unfortunately it won't work. I've set an installable trigger to this function with an onChange event type but I only see the executions of it...but emails are not being sent. in the other hand, it still sends execution when I change something in other tabs... :(

Upvotes: 0

ross

Reputation: 2774

Requirement:

Script using onChange trigger should only run if sheet "Notif Inv" is changed.

Solution:

Pass the change event to your function and use getName() of the sheet to determine which sheet is being edited, then run an if statement based on the result.

Example:

function Email_NewInvs(e) {
  // Get the sheet where the data is, in sheet 'Notif Inv'
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === 'Notif Inv') {
    var startRow = 2; // First row of data to process since there is a header row
    var numRows = sheet.getRange(1,5).getValue(); // Number of rows to process is set by a formula which counts rows
    // Fetch the range of cells A2:B6 where the emails and messages are
    var dataRange = sheet.getRange(startRow, 1, numRows, 2)
    // Fetch values for each row in the Range to input into the mailing system
    var data = dataRange.getValues();
    // This processes the emails you want to send
    for (i in data) { 
      var row = data[i];
      var emailAddress = row[0]; // First column is the email address
      var message = row[1]; // Second column is the message
      var subject = "Nueva Factura"; // This is the subject of the email
      // This parses the data for the email to send
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Explanation:

We're now using event objects with your function Email_NewInvs(e) to define var sheet, so that we can check which sheet has been changed:

var sheet = e.source.getActiveSheet();

I've added an if statement to check the name of the sheet that has been changed:

if (sheet.getName() === 'Notif Inv') {

So now the script will only run if the name of the sheet being changed matches 'Notif Inv'.

Notes:

You'll need to set up an Installable Trigger for this script so that it has enough authorization to send emails.
You will NOT be able to run this script manually from Script Editor, it'll run automatically when the sheet is changed.

References:

Upvotes: 1