google apps script - Spreadsheet Email Trigger

Question

I have Written Script on Google Spreadsheet to send Email when spreadsheet is modified or any Data is added. Email Trigger is working but whenever any data is entered in next Row it send Email to previous email address also.

Please suggest solution

The below is written script :

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1 , numRows,3) // Fetch the range of cells A2:B3

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
for (i in data) {
    var row = data[i];
    var emailAddress = row[2];  // First column
    var message = row[0] + "requested" + row [1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Answer 1

Your question is unclear... nowhere in the script I see something that reads which cell is actually modified... your target range is hardcoded on row 2 so the only row that can be processed is row 2 (and the mail can only be sent once)...

So can you :

• explain how it should work
• explain how it works now , especially what do you mean by 'previous email'
• remove typos in your code (row[2] is not First column)
• explain how you trigger this function : the name onEdit(e) suggest an onEdit trigger but simple triggers cannot send mail so I suppose you have set some other trigger.
• explain why (e) in your function parameter and not using it ?

---

EDIT : thanks for the complement of information. The script you suggest is not sufficient to achieve what you want. The idea here is to check if something in the sheet has been modified either by adding (or inserting) a row of data or (if I understood well) by editing any row in the sheet with a new value.

This is not really as simple as it looks at the first glance ;-)

What I would do it to take a 'snapshot' of the sheet and -based on a timer or onEdit - compare that snapshot to the sheet's current state.

There is more than one way to get that result, you could have a second sheet in your spreadsheet that no one could modify and that is a copy of the main sheet that you update after each modification/mail send. So before updating the script should look for any difference between the sheets and send a report to the corresponding email when a difference is found.

Another way to do that is to store the sheet data converted to a string in the script properties, the principle is the same but it's more 'invisible' for normal users accessing the spreadsheet.

You could also use scriptDb or your userproperties but the script properties is probably better suited (simpler) for this use case.

Tell us what you think/prefer and I (or someone else) could probably give you some code to start with.