CODEWITHSUNDEEP
notificationsgoogle-sheets
Jayson DeMar
Jayson DeMar

Reputation: 3

Google Sheets Notifications When Cell is Edited?

I have a spreadsheet that I would like to have send an e-mail to a specified user whenever a particular cell is edited. So, for example, the trigger column is column J. My goal is that when a cell (J4, for example) is edited, it will send an automatic e-mail to the e-mail address that is provided in cell A4. I know this will require a script.

Upvotes: 0

Views: 1735

Answers (1)

Rowen Chumacera
Rowen Chumacera

Reputation: 529

Here's what I came up with:

function onEditTrigger(e){
  var range = e.range;
  var intCol = range.getColumn();

  if (intCol == 10)                                           // Check for column J
  {
    var intRow = range.getRow();                              // Get the row number of the edited cell
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataRange = sheet.getRange(intRow, 1, 1, 3);          // Select columns A to C on the same row as the edited cell
    var dataValues = dataRange.getValues();                   // Get the values in the selected range and store them in the dataValues array

    MailApp.sendEmail(dataValues[0][0], "Notification", "Value in column C: " + dataValues[0][2]); // dataValues[0][0] = e-mail address, dataValues[0][2] = additional information
  }
}

Then manually set the trigger to onEdit on the Resources menu of the Script Editor.

The script is pretty much self-explanatory as I put some comments there. But basically, the script first checks if a user edited the "trigger" column. If it is the "trigger" column, it gets the row number of that edited cell and gets the corresponding e-mail address in column A, and the additional information in column C of the same row. A notification e-mail is then sent to that e-mail address with the subject "Notification" and the value in column C as the body of the e-mail message.

Upvotes: 1

Related Questions