CODEWITHSUNDEEP
emailgoogle-apps-scriptgoogle-sheets
Craig
Craig

Reputation: 23

Email notification when changes are made to specific data in Google sheets

I have a general script that will send email notification of changes made to a live roster in Google sheets and have set up a trigger for the event to occur 'On change'. All data in the sheet is retrieved via IMPORTRANGE. This script is performing as expected, however I wish to make a couple of changes that am not sure how to go about. Here is a sample sheet: link to sheet

  1. Wish to only send email notification when changes concern the name 'Craig'. For example my name is either added or taken off a rostered day. The script currently sends emails for all changes made across the sheet.

  2. ID the row that this change occurs so as to reference the date and venue in the email body.

Thanks for any help and suggestions.

Full script:

function onEdit() {

    var getProps = PropertiesService.getUserProperties();

    var lenProp = getProps.getProperty('Len');

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster');

    var data = sheet.getRange('E4:ACB562').getValues().toString().length;

    if (data != lenProp) {
        getProps.setProperty('Len', data );
        MailApp.sendEmail('[email protected]', 'Changes have been made to your live roster', 'Previous value: ' + lenProp + ' New value: ' + data);
    }
}

New revised script:

function installedOnEdit(e) {
  var range = e.range;
  var value = range.getValue();
  if (value == "Craig" || (e.oldValue == "Craig" && value == "")) {
    var rowNumber = range.getRow(); // You can retrieve the row number of the editor cell.
    console.log(rowNumber)
    var [colA, colB] = e.source.getActiveSheet().getRange(rowNumber, 1, 1, 2).getValues()[0];

    var getProps = PropertiesService.getUserProperties();
    var lenProp = getProps.getProperty('Len');
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster_LIVE');
    var data = sheet.getRange('E4:ACB562').getValues().toString().length;
    if (data != lenProp) {
      getProps.setProperty('Len', data);
      MailApp.sendEmail('[email protected]', 'Changes have been made to your roster!', 'Your live roster has been edited. These changes concern ' + colB + ' on ' + colA);
    }
  }
}

Upvotes: 0

Views: 925

Answers (2)

doubleunary
doubleunary

Reputation: 18751

This sheet does not get edited, but rather changes occur through IMPORTRANGE.

No events get triggered when a formula result changes, so I do not think that you can get an automatic notification when values get updated in the imported data.

To make it work, you will have to use a trigger in the source spreadsheet rather than the target spreadsheet.

Upvotes: 1

Tanaike
Tanaike

Reputation: 201348

I believe your goal as follows.

  • You want to send an email using OnEdit trigger when the value of Craig is put to a cell.
  • You want to retrieve the edited row number.
  • You want to run the script when the value of Craig is removed.

Modification points:

  • From I have a general script that will send email notification of changes made to a live roster in Google sheets and have set up a trigger for the event to occur 'On change'., I understood that you are using the installable OnChange trigger. If it's so, in your situation, I would like to recommend to use the installable OnEdit trigger.
  • And, when I saw your script, the function name is onEdit. If the installable OnEdit trigger installs to onEdit, when a cell is edited, onEdit is run 2 times by the simple trigger and the installable trigger with the asynchronous process. Ref So, please rename the function name from onEdit to other and reinstall the installable OnEdit trigger to the renamed function name.
  • In order to retrieve the row number of the edited cell, you can use the event object.

When above points are reflected to your script, it becomes as follows.

Modified script:

After the script was modified, please install the OnEdit trigger to the function of installedOnEdit. In this script, when you put the value of Craig to a cell, the script below the if statement is run.

From: 
function onEdit() {

    var getProps = PropertiesService.getUserProperties();

    var lenProp = getProps.getProperty('Len');

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster');

    var data = sheet.getRange('E4:ACB562').getValues().toString().length;

    if (data != lenProp) {
        getProps.setProperty('Len', data );
        MailApp.sendEmail('[email protected]', 'Changes have been made to your live roster', 'Previous value: ' + lenProp + ' New value: ' + data);
    }
}
To: 
function installedOnEdit(e) {
  var range = e.range;
  var value = range.getValue();
  if (value == "Craig" || (e.oldValue == "Craig" && value == "")) {
    var rowNumber = range.getRow(); // You can retrieve the row number of the editor cell.
    console.log(rowNumber)

    var getProps = PropertiesService.getUserProperties();
    var lenProp = getProps.getProperty('Len');
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Roster');
    var data = sheet.getRange('E4:ACB562').getValues().toString().length;
    if (data != lenProp) {
      getProps.setProperty('Len', data);
      MailApp.sendEmail('[email protected]', 'Changes have been made to your live roster', 'Previous value: ' + lenProp + ' New value: ' + data);
    }
  }
}
  • In this modified script, when the value of Craig is put to a cell, the script below the if statement is run. If you want to also check the column and row, please tell me.

References:

Upvotes: 1

Related Questions