CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsemail-notifications
Ducatia
Ducatia

Reputation: 31

Send email notification when cell has been edited on a specific google sheets tab

I'm completely new to writing app scripts. Have been trying to find an answer to my question online but unfortunately without any success. Therefore I'm reaching out to the community for help.

In my g sheet, I have two tabs one is an input tab (X) and the other one is an output (Y). What I am trying to accomplish is to send an email notification to a given email address every time a cell has been edited in the output tab (Y). The output tab has an editable range from B2:Y61. I have found plenty of stuff online on the "on edit" feature. However, non of them was close to my example mostly because they have been referring to getActivetSheet and not a specific one. The simpler code the better.

For instance, I found the following post very interesting. Unfortunately have no idea how to make it work with a specific tab name. Furthermore, if copied and pasted exactly, the formula returns an error, that row reference has not been declared.

https://spreadsheet.dev/send-email-when-google-sheet-is-edited

Look forward to hearing from you!

Best, D

Upvotes: 0

Views: 3075

Answers (1)

Kristkun
Kristkun

Reputation: 5953

You can use this:

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet();
  var editedCell = e.range;
  var row = editedCell.getRow();
  var col = editedCell.getColumn();
  
  //Check if B2:Y61 was modified in Sheet "Y" based on its row and column index
  if(row>=2 && row<=61 && col>=2 && col<=25 && editedSheet.getName()=="Y"){
    //Send email
    var subject = "Sheet: "+editedSheet.getName();
    var message = "Cell "+editedCell.getA1Notation()+" was modified from '"+e.oldvalue+"' to '"+e.value+"'";
    MailApp.sendEmail("email address",subject,message);
  }
}

What it does?

  • Using the Google Sheets events, you can get the active sheet that is being modified using e.source, get the cell being modified using e.range and even get the old and the new value of the cell using e.oldValue and e.value
  • You just need to include a condition to check if the cell modified is within the range that you prefer B2:Y61 which should have a min row = 2, max row = 61, min col = 2 and max col = 25 and sheet name should be "Y".
  • Send an email using MailApp.sendEmail() if all the conditions were met.

Note:

You need to create this as an installable trigger since it will use mail service which requires authentication.

To manually create an installable trigger in the script editor, follow these steps:

enter image description here

enter image description here

OUTPUT: enter image description here

(UPDATE)

This code will copy changes done in X_input sheet to Y_input sheet and check if email should be sent when B2:Y61 range was modified.

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet();
  var editedCell = e.range;
  var row = editedCell.getRow();
  var col = editedCell.getColumn();
  
  var ySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Y_output");

  //Check if edit is done in Sheet "X_input"
  if(editedSheet.getName()=="X_input"){
    //copy modified cell to Y_output sheet
    ySheet.getRange(row,col).setValue(e.value);


    //check if modified cell is within B2:Y61 range and send an email
    if(row>=2 && row<=61 && col>=2 && col<=25){
      //Send email
     var subject = "Sheet: "+editedSheet.getName();
      var message = "Cell "+editedCell.getA1Notation()+" was modified from '"+e.oldvalue+"' to '"+e.value+"'";
      MailApp.sendEmail("[email protected]",subject,message);
    }
  }
 
}

Upvotes: 1

Related Questions