Can I automate an email off google sheets based on specific updated columns and rows?

Question

I was wondering if you could help me. I know this code is incomplete it's been kind of a frankensteined piece I made from many other peoples issues close to mine. I just can't find exactly what I am looking for. I don't know Java and I am new to google sheets and google-apps-scripts

Problem: I want to build a script that automatically sends me an email when a spreadsheet is updated and tell me what Cell was updated. The issue I am having is I need it to meet certain criteria. For Example, if cellvalue in column i = "Name" and on that row columns w, x , ae or af are updated or changed. Then send out an automated email.

If you guys could help I would really appreciate it.

Thank you!

I tried an import range and filtered only the person i need and the columns I need but the roadblock I hit there is that when it gets updated it updates the cell that contains the importrange formula. I also read it can be inconsistent as well.

function sendNotification(e){
  var ss = SpreadsheetApp.getActive().getSheetByName('Datasheet');
  //var gg = ss.getActiveCell().getValue().toString().getFilter('Person');
  //var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var row = ss.getActiveRange().getRow();
  var column = ss.getActiveRange().getColumn().toString();
  var cellvalue = ss.getActiveCell().getValue().toString();
  var subject = '____ '+ ss.getName();
  var lastColumn = ss.getLastColumn();
  var lastRow = ss.getLastRow();
  var range = ss.getRange(1,1,lastRow,lastColumn);

  MailApp.sendEmail({
    to: "Email@gmail",
    subject: subject,
    htmlBody: "<br><br>"+
    "The following cell has been updated. <br><br>"+
    "<font size=\"3\" color=\"black\"><b> Cell: </b></font>" +
    cell + "<br><br>" +
    "The update was the following: " + cell + "<br>" + "<br><br>" + 
    "Column: " + column + "<br>Updated Cell: " + cellvalue + "<br><br>" + 
    "<br><br>Thank you. <br>"
  });
};

So I want to be able to filter out a specific person and specific columns that get updated. Currently I am trying it off of an importrange in a new sheet. When I update the masterlist though I get an email telling me it updated. But the updated cell is the cell that houses the importrange formula. I would love to just insert the code straight into the master list if I can filter out what I need.

Answer 1

You need an if statement to achieve your goal. I've also changed a lot of your variables to use the e event object, this makes it much easier to grab ranges that have been edited etc.

function sendNotification(e){
  var ss = e.source.getSheetByName('Datasheet');
  var cell = e.range.getA1Notation();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var cellvalue = e.range.getValue();
  var subject = '____ '+ ss.getSheetName();
  var name = ss.getRange(row, 9).getValue(); //get column 9 for current row

  if (name === 'Name' && (col === 23 || col === 24 || col === 31 || col === 32) === true) {

    MailApp.sendEmail({
      to: "Email@gmail",
      subject: subject,
      htmlBody: "<br><br>"+
      "The following cell has been updated. <br><br>"+
      "<font size=\"3\" color=\"black\"><b> Cell: </b></font>" +
      cell + "<br><br>" +
      "The update was the following: " + cell + "<br>" + "<br><br>" + 
      "Column: " + col + "<br>Updated Cell: " + cellvalue + "<br><br>" + 
      "<br><br>Thank you. <br>"
    })
  }
}

The if statement checks to make sure variable name (I took this literally from your question), which is column I, is set to 'Name'. I took this literally but you can change this check to whatever you'd like the script to run for. It then checks that either columns W, X, AE or AF are updated, then sends an email if both criteria are matched.

if (name === 'Name' && (col === 23 || col === 24 || col === 31 || col === 32) === true) {