Google Sheets - send notification based on value in cell (text) and give row number

1
votes

I am comparing two sheets. One sheet includes data that gets automatically extracted from an intern data storage. An arrayformula sets the status column (Sheet1!D:D) on "consent received" as soon as there is a match in the e-mail address.

Here's an example.

What I would like to do, is to get an automate e-mail notification as soon as the status was set on "consent received". Further, I would need to have the information in the e-mail, in which row of the sheet the change has been made and to limit the script only to new changes. Right now I get a notification for every "consent received" status in the column and not only for the ones that have been recently updated.

This is what I've got so far:

function sendEmail(email_address, email_subject, email_message) {
  var status = ScriptProperties.getProperty('AlertStatus') + "";
  var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("D2:D100").getValues().toString();
  if (value.match("consent received") && status.match("")) {;
    ScriptProperties.setProperty('AlertStatus', '')
    MailApp.sendEmail('j***.bla****@m****.com', 'New Opt In', 'It seems as if there has been an Opt In. Congratulations! Please check our JCFC Sheet! ');
  } else {
    if (!value.match("consent received"))
      ScriptProperties.setProperty('AlertStatus', '')
  }
}​
2
google-sheets

2 Answers

1
votes

Depends of the structure of your sheet and data. There are several ways to go with this. You can for example insert a column with time/date that is updated each time new data is inserted in a cell and then test against this time before mailing. Or you can mark all "old" cells as "processed" in a similar column (or alternatively mark every cell as "new" in an extra column when new data gets inserted"). Or you can even use some styling, if you do not want to use extra column. Just style the "old cells" with different colors than new ones and then check the color before emailing the update. Just remember to update the cell status AFTER emailing so that your data gets marked as processed/old. (Of course you need to style the new data with proper color when inserting as well)

1
votes

Alright, just solved it by myself. Just needed a hint. Just wrote an onEdit () function and set the trigger. Now it's working properly. Still a long way to go and to learn^^

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 4 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
          }
  }
}